Query Data

nicyeNovember 21, 2021About 4 minAbout 1183 words

Query Data

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

Special introduction to 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)
DynamicFilterInfo dyfilter = JsonConvert.DeserializeObject<DynamicFilterInfo>(@"
{
  ""Logic"" : ""Or"",
  ""Filters"" :
  [
    {
      ""Field"" : ""Code"", ""Operator"" : ""NotContains"", ""Value"" : ""val1"", 
      ""Filters"" : [{ ""Field"" : ""Name"", ""Operator"" : ""NotStartsWith"", ""Value"" : ""val2"" }]
    },
    {
      ""Field"" : ""Parent.Code"", ""Operator"" : ""Equals"", ""Value"" : ""val11"",
      ""Filters"" : [{ ""Field"" : ""Parent.Name"", ""Operator"" : ""Contains"", ""Value"" : ""val22"" }]
    }
  ]
}");
fsql.Select<VM_District_Parent>().WhereDynamicFilter(dyfilter).ToList();

//SELECT a.""Code"", a.""Name"", a.""ParentCode"", a__Parent.""Code"" as4, a__Parent.""Name"" as5, a__Parent.""ParentCode"" as6 
//FROM ""D_District"" a 
//LEFT JOIN ""D_District"" a__Parent ON a__Parent.""Code"" = a.""ParentCode"" 
//WHERE (not((a.""Code"") LIKE '%val1%') AND not((a.""Name"") LIKE 'val2%') OR a__Parent.""Code"" = 'val11' AND (a__Parent.""Name"") LIKE '%val22%')

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

API

MethodsReturnParametersDescription
ToSqlstringReturn the SQL statement to be executed
ToListList<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>LambdaExecute 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 fieldExecute 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
ToOneT1Execute 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>LambdaExecute SQL query and return the aggregated result of the specified field (suitable for scenarios where GroupBy is not needed)
AnyboolExecute SQL query, if there are records, return true, otherwise return false.
SumTLambdaSpecify a column and calculate the sum.
MinTLambdaSpecify a column and calculate the minimum value.
MaxTLambdaSpecify a column and calculate the maximum value.
AvgTLambdaSpecify a column, calculate the average value.
【Pagination】
CountlongThe number of queried records
Count<this>out longThe number of queried records, returned in the form of parameter out
Skip<this>int offsetQuery the number of rows shifted backward
Offset<this>int offsetQuery the number of rows shifted backward
Limit<this>int limitQuery a specified amount of data
Take<this>int limitQuery a specified amount of data
Page<this>int pageIndex, int pageSizePagination
【Where】
Where<this>LambdaSupports multi-table query expressions, multiple use is equivalent to AND.
WhereIf<this>bool, LambdaSupport multi-table query expression
Where<this>string, parmsNative 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, parmsNative Sql syntax conditions, WhereIf(true, "id = @id", new {id = 1 }) Note that the prefix @ will be determined according to the specific database
WhereCascade<this>LambdaWhen querying multiple tables, add conditions to each table.
WhereDynamicFilter<this>DynamicFilterInfoDynamic filter conditions (interact with the front end)
【Group】
GroupBy<this>LambdaGroup by selected column, GroupBy(a => a.Name)
GroupBy<this>string, parmsGroup 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, parmsFilter 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>LambdaSort 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 })
OrderByPropertyNamestring, boolSort by attribute name string (support navigation attributes)
【Join】
LeftJoin<this>LambdaLeft-join query, you can use navigation properties, or specify the associated entity type
InnerJoin<this>LambdaInner-join query, you can use navigation properties, or specify the associated entity type
RightJoin<this>LambdaRight-join query, you can use navigation properties, or specify the associated entity type
LeftJoin<this>string, parmsLeft-join query, using native sql syntax, LeftJoin("type b on b.id = a.id and b.clicks> @clicks", new {clicks = 1 })
InnerJoin<this>string, parmsInner-join query, using native sql syntax, InnerJoin("type b on b.id = a.id and b.clicks > @clicks", new { clicks = 1 })
RightJoin<this>string, parmsRight-join query, using native sql syntax, RightJoin("type b on b.id = a.id and b.clicks > @clicks", new { clicks = 1 })
From<this>LambdaMulti-table query, more than 3 tables will be very convenient to use (currently supports up to 10 tables by design)
【Other】
As<this>string alias = "a"Specify alias
Master<this>Specify query from the main database (default query from the slave database)
CommandTimeout<this>intCommand timeout setting (seconds)
WithTransaction<this>DbTransactionSet the transaction object
WithConnection<this>DbConnectionSet the connection object
WithLock<this>EnumSpecific settings such as SqlServer NoLock
ForUpdate<this>boolExclusive update lock, adapted to different databases, see notes for details
AsQueryableIQueryableConvert 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.
InsertIntointstring, LambdaConvert the query to INSERT INTO tableName SELECT ... FROM t and perform the insert.
ToUpdateIUpdate<TEntity>-Convert the query to IUpdate<TEntity>
ToDeleteIDelete<TEntity>-Convert the query to IDelete<TEntity>
ToTreeListList<TEntity>-Return the data of the parent-child relationship in the form of a TreeList
AsTreeCteISelect(up, pathSelector, level)Recursively query the parent-child relationship table