Click or drag to resize
SqlExpressionEGetSelectSql Method
Creates a SELECT SQL based on the passed parameters.

Namespace: LiteRepository
Assembly: LiteRepository (in LiteRepository.dll) Version: 2.0.4
Syntax
C#
Copy
public string GetSelectSql(
	Type type = null,
	Expression<Func<E, bool>> where = null,
	Object param = null,
	Expression<Func<IEnumerable<E>, IEnumerable<E>>> orderBy = null
)

Parameters

type (Optional)
Type: SystemType
Type that contains subset of E members. Used for generate fields list.
where (Optional)
Type: System.Linq.ExpressionsExpressionFuncE, Boolean
Where expression. You can use members of E or param. Other values will be evaluated.
param (Optional)
Type: SystemObject
Query parameters.
orderBy (Optional)
Type: System.Linq.ExpressionsExpressionFuncIEnumerableE, IEnumerableE
Sort expression. You can use GroupBy and GroupByDescending methods with members from E.

Return Value

Type: String
A string with a select query.
Examples

Suppose we have a class:

C#
Copy
public class User {
    [SqlKey]
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string SecondName { get; set; }
    public DateTime Birthday { get; set; }
    [SqlIgnore]
    public string FullName { get { return FirstName + " " + SecondName; } }
}

Without any parameters method returns:

C#
Copy
var sql = GetSelectSql(); 
// select Id, FirstName, SecondName, Birthday from User;

If you need to select only parts of columns:

C#
Copy
var p = new { FirstName = "", SecondName = "" };
var sql = GetSelectSql(type:p.GetType()); 
// select FirstName, SecondName from User;

If you need to filter values with hard-coded parameters:

C#
Copy
var sql = GetSelectSql(where: e => e.FirstName.StartsWith("A")); 
// select Id, FirstName, SecondName, Birthday from User where FirstName like 'A%';

If you need to filter values with parameters:

C#
Copy
var parameters = { FirstLetter = "A" };
var sql = GetSelectSql(where: e => e.FirstName.StartsWith(parameters.FirstLetter), param: parameters); 
// select Id, FirstName, SecondName, Birthday from User where FirstName like @FirstLetter%';

If you need sort the results:

C#
Copy
var sql = GetSelectSql(l => l.OrderBy(x => x.FirstName).OrderByDescending(x => x.Id)); 
// select Id, FirstName, SecondName, Birthday from User order by FirstName, Id desc;

You can combine settings to achieve the desired result. If any option will be skipped - SQL will be generated without it.

See Also