Query Data (original) (raw)

中文 | English

FreeSql has made great efforts in querying data, especially the functions such as chain query syntax, multi-table query, expression function, etc.

SqlServer WithLock/WithIndex

var list = fsql.Select() .WithLock() .Limit(1).ToList(); //SELECT TOP 1 ... FROM [Region] a With(NoLock)

var list = fsql.Select() .WithLock(SqlServerLock.NoLock | SqlServerLock.NoWait) .Limit(1).ToList(); //SELECT TOP 1 ... FROM [Region] a With(NoLock, NoWait)

var list = fsql.Select() .WithLock() .WithIndex("idx_01") .Limit(1).ToList(); //SELECT TOP 1 ... FROM [Region] a With(index=idx_01, NoLock)

Multi-Tables Query:

var list = Select<Region, T2>() .InnerJoin((a, b) => a.x == b.xx) .WithLock(SqlServerLock.NoLock, new Dictionary<Type, bool> { [typeof(T2)] = false }) .WithIndex("idx_01", new Dictionary<Type, string> { [typeof(T2)] = "idx_02" }) .Limit(1).ToList(); //SELECT TOP 1 .. //FROM [Region] a With(index=idx_01, NoLock) //INNER JOIN [T2] b With(index=idx_02) ON a.[x] = b.[xx]

Global NoLock:

//All entities fsql.SetGlobalSelectWithLock(SqlServerLock.NoLock, null);

//Effective designation fsql.SetGlobalSelectWithLock(SqlServerLock.NoLock, new Dictionary<Type, bool> { [typeof(Region)] = true, [typeof(T2)] = true });

Special introduction to WhereDynamicFilter

《高效理解 FreeSql WhereDynamicFilter，深入了解设计初衷》

The ISelect.WhereDynamicFilter method implements dynamic filter conditions (interacting with the front-end), supported operators:

DynamicFilterInfo dyfilter = JsonConvert.DeserializeObject(@" { ""Logic"": ""And"", ""Filters"": [ { ""Field"": ""id"", ""Operator"": ""Equals"", ""Value"": 1 }, { ""Logic"": ""Or"", ""Filters"": [ { ""Field"": ""id"", ""Operator"": ""Equals"", ""Value"": 2 }, { ""Field"": ""id"", ""Operator"": ""Equals"", ""Value"": 3 } ] } ] }"); fsql.Select().WhereDynamicFilter(dyfilter).ToList(); //WHERE id = 1 AND (id = 2 OR id = 3)

Dynamic tablename：ISelect.AsTable((t, old) => $"{old}_201903")

Dynamic sorting：ISelect.OrderByPropertyName("Parent.Code")

Dynamic return data：ISelect.ToDataTableByPropertyName(new string[] { "Parent.Code", "Id" })

Dynamic include：ISelect.IncludeByPropertyName("Parent.Parent").IncludeByPropertyName("Parent.Childs")

API

Methods Return Parameters Description
ToSql string Return the SQL statement to be executed
ToList List Execute SQL query and return the records of all fields of the T1 entity. If there are navigation properties, they will be queried and returned together. If the record does not exist, it will return a list with Count being 0
ToList List Lambda Execute SQL query, return the record of the specified field, if the record does not exist, return the list with Count as 0
ToList List string field Execute SQL query, return the record of the field specified by field, and receive it as a tuple or basic type (int, string, long). If the record does not exist, return a list with Count as 0
ToOne T1 Execute SQL query and return the first record of all fields of the T1 entity, or null if the record does not exist
ToAggregate List Lambda Execute SQL query and return the aggregated result of the specified field (suitable for scenarios where GroupBy is not needed)
Any bool Execute SQL query, if there are records, return true, otherwise return false.
Sum T Lambda Specify a column and calculate the sum.
Min T Lambda Specify a column and calculate the minimum value.
Max T Lambda Specify a column and calculate the maximum value.
Avg T Lambda Specify a column, calculate the average value.
【Pagination】
Count long The number of queried records
Count out long The number of queried records, returned in the form of parameter out
Skip int offset Query the number of rows shifted backward
Offset int offset Query the number of rows shifted backward
Limit int limit Query a specified amount of data
Take int limit Query a specified amount of data
Page int pageIndex, int pageSize Pagination
【Where】
Where Lambda Supports multi-table query expressions, multiple use is equivalent to AND.
WhereIf bool, Lambda Support multi-table query expression
Where string, parms Native Sql syntax conditions, Where("id = @id", new {id = 1 }) Note that the prefix @ will be determined according to the specific database
WhereIf bool, string, parms Native Sql syntax conditions, WhereIf(true, "id = @id", new {id = 1 }) Note that the prefix @ will be determined according to the specific database
WhereCascade Lambda When querying multiple tables, add conditions to each table.
WhereDynamicFilter DynamicFilterInfo Dynamic filter conditions (interact with the front end)
【Group】
GroupBy Lambda Group by selected column, GroupBy(a => a.Name)
GroupBy string, parms Group by native sql syntax GroupBy("concat(name, @cc)", new { cc = 1 }). Note that the prefix @ will be determined according to the specific database
Having string, parms Filter by aggregation conditions of native sql syntax Having("count(name) = @cc", new { cc = 1 }). Note that the prefix @ will be determined according to the specific database
Distinct .Distinct().ToList(x => x.GroupName) is to perform DISTINCT for the specified field.
【Order】
OrderBy Lambda Sort by column, OrderBy(a => a.Time), can be used multiple times
OrderByDescending Lambda 按列倒向排序，OrderByDescending(a => a.Time)
OrderBy string, parms 按原生sql语法排序，OrderBy("count(name) + @cc", new { cc = 1 })
OrderByPropertyName string, bool Sort by attribute name string (support navigation attributes)
【Join】
LeftJoin Lambda Left-join query, you can use navigation properties, or specify the associated entity type
InnerJoin Lambda Inner-join query, you can use navigation properties, or specify the associated entity type
RightJoin Lambda Right-join query, you can use navigation properties, or specify the associated entity type
LeftJoin string, parms Left-join query, using native sql syntax, LeftJoin("type b on b.id = a.id and b.clicks> @clicks", new {clicks = 1 })
InnerJoin string, parms Inner-join query, using native sql syntax, InnerJoin("type b on b.id = a.id and b.clicks > @clicks", new { clicks = 1 })
RightJoin string, parms Right-join query, using native sql syntax, RightJoin("type b on b.id = a.id and b.clicks > @clicks", new { clicks = 1 })
From Lambda Multi-table query, more than 3 tables will be very convenient to use (currently supports up to 10 tables by design)
FromQuery ISelect<T1, T2> ISelect Query of linking single table to double table
WithTempQuery ISelect Lambda Nested single table or multi table queries into single table queries
WithMemory ISelect List Query using memory data
UnionAll ISelect ISelect[] Union ALL query
【Other】
As string alias = "a" Specify alias
Master Specify query from the main database (default query from the slave database)
CommandTimeout int Command timeout setting (seconds)
WithTransaction DbTransaction Set the transaction object
WithConnection DbConnection Set the connection object
WithLock Enum Specific settings such as SqlServer NoLock
ForUpdate bool Exclusive update lock, adapted to different databases, see notes for details
AsQueryable IQueryable Convert ISelect to IQueryable. This method is mainly used for extensions, for example: Abp's IRepository GetAll() interface method needs to return an IQueryable object. Note: IQueryable method is more polluted, please try to avoid this conversion.
InsertInto int string, Lambda Convert the query to INSERT INTO tableName SELECT ... FROM t and perform the insert.
ToUpdate IUpdate - Convert the query to IUpdate
ToDelete IDelete - Convert the query to IDelete
ToTreeList List - Return the data of the parent-child relationship in the form of a TreeList
AsTreeCte ISelect (up, pathSelector, level) Recursively query the parent-child relationship table