Skip to content

Query Data

Jump to bottom
2881099 edited this page Dec 14, 2023 · 18 revisions

中文 | 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<Region>()
    .WithLock()
    .Limit(1).ToList();
//SELECT TOP 1 ... FROM [Region] a With(NoLock)

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

var list = fsql.Select<Region>()
    .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:

  • Contains/StartsWith/EndsWith/NotContains/NotStartsWith/NotEndsWith: include/not include, like'%xx%', or like'xx%', or like'%xx'
  • Equal/NotEqual
  • GreaterThan/GreaterThanOrEqual
  • LessThan/LessThanOrEqual
  • Range: Range query
  • DateRange: date range, with special processing value[1] + 1
  • Any/NotAny: Does it match any item in value (to put it bluntly, SQL IN)
  • Custom：custom parser 
DynamicFilterInfo dyfilter = JsonConvert.DeserializeObject<DynamicFilterInfo>(@"
{
  ""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<Region>().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<T1> 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<T> List<T> 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<T> List<T> 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<T> List<T> 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 <this> out long The number of queried records, returned in the form of parameter out
Skip <this> int offset Query the number of rows shifted backward
Offset <this> int offset Query the number of rows shifted backward
Limit <this> int limit Query a specified amount of data
Take <this> int limit Query a specified amount of data
Page <this> int pageIndex, int pageSize Pagination
【Where】
Where <this> Lambda Supports multi-table query expressions, multiple use is equivalent to AND.
WhereIf <this> bool, Lambda Support multi-table query expression
Where <this> 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 <this> 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 <this> Lambda When querying multiple tables, add conditions to each table.
WhereDynamicFilter <this> DynamicFilterInfo Dynamic filter conditions (interact with the front end)
【Group】
GroupBy <this> Lambda Group by selected column, GroupBy(a => a.Name)
GroupBy <this> 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 <this> 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 <this> .Distinct().ToList(x => x.GroupName) is to perform DISTINCT for the specified field.
【Order】
OrderBy <this> Lambda Sort by column, OrderBy(a => a.Time), can be used multiple times
OrderByDescending <this> Lambda 按列倒向排序，OrderByDescending(a => a.Time)
OrderBy <this> string, parms 按原生sql语法排序，OrderBy("count(name) + @cc", new { cc = 1 })
OrderByPropertyName string, bool Sort by attribute name string (support navigation attributes)
【Join】
LeftJoin <this> Lambda Left-join query, you can use navigation properties, or specify the associated entity type
InnerJoin <this> Lambda Inner-join query, you can use navigation properties, or specify the associated entity type
RightJoin <this> Lambda Right-join query, you can use navigation properties, or specify the associated entity type
LeftJoin <this> 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 <this> 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 <this> 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 <this> 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<T2> Query of linking single table to double table
WithTempQuery ISelect<T1> Lambda Nested single table or multi table queries into single table queries
WithMemory ISelect<T1> List<T1> Query using memory data
UnionAll ISelect<T1> ISelect<T1>[] Union ALL query
【Other】
As <this> string alias = "a" Specify alias
Master <this> Specify query from the main database (default query from the slave database)
CommandTimeout <this> int Command timeout setting (seconds)
WithTransaction <this> DbTransaction Set the transaction object
WithConnection <this> DbConnection Set the connection object
WithLock <this> Enum Specific settings such as SqlServer NoLock
ForUpdate <this> 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<TEntity> - Convert the query to IUpdate<TEntity>
ToDelete IDelete<TEntity> - Convert the query to IDelete<TEntity>
ToTreeList List<TEntity> - 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

Basic

使用指南

学习与交流

信息

Clone this wiki locally