跳至主要內容

查询

nicye大约 6 分钟约 1832 字

查询

FreeSql 在查询数据下足了功夫,链式风格、多表查询、表达式函数、导航属性支持得非常到位。

《分页查询》《仓储层 Repository》《读写分离》
《单表查询》《过滤器》《LinqToSql》
《多表查询》《延时加载》《性能》
《嵌套查询》《贪婪加载》《分表分库》
《分组聚合查询》《表达式函数》《多租户》
《返回数据》

表达式函数

说明Lambda 表达式SQL
inWhere(a => new [] { 1,2,3 }.Contains(a.Id))[Id] in (1,2,3)
not inWhere(a => !new [] { 1,2,3 }.Contains(a.Id))[Id] not in (1,2,3)
多列inWhere(a => list.Any(b => b.Item1 == a.Id && b.Item2 == a.ct1))([Id]=1 and [ct1]=1) or ([Id]=2 and [ct1]=2)
like '%xxx%'Where(a => a.Title.Contains("xxx"))[Title] like '%xxx%'
like 'xxx%'Where(a => a.Title.StartsWith("xxx"))[Title] like 'xxx%'
like '%xxx'Where(a => a.Title.EndsWith("xxx"))[Title] like '%xxx'
日期范围Where(a => a.Time.Between(time1, time2))[Time] between @time1 and @time2
是否存在.Any()select 1 from ...
总数.Count()select count(*) from ...
求和.Sum(a => a.Score)select sum([Score]) from ...
平均.Avg(a => a.Score)select avg([Score]) from ...
最大值.Max(a => a.Score)select max([Score]) from ...
最小值.Min(a => a.Score)select min([Score]) from ...

更详细请前往《表达式函数》

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)

多表:

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]

全局设置 NoLock:

//所有实体类生效
fsql.SetGlobalSelectWithLock(SqlServerLock.NoLock, null);

//【指定】实体类生效
fsql.SetGlobalSelectWithLock(SqlServerLock.NoLock, new Dictionary<Type, bool>
{
    [typeof(Region)] = true,
    [typeof(T2)] = true
});

动态过滤 WhereDynamicFilter

《高效理解 FreeSql WhereDynamicFilter,深入了解设计初衷》open in new window

ISelect.WhereDynamicFilter 方法实现动态过滤条件(与前端交互),支持的操作符:

  • Contains/StartsWith/EndsWith/NotContains/NotStartsWith/NotEndsWith:包含/不包含,like '%xx%',或者 like 'xx%',或者 like '%xx'
  • Equal/NotEqual:等于/不等于
  • GreaterThan/GreaterThanOrEqual:大于/大于等于
  • LessThan/LessThanOrEqual:小于/小于等于
  • Range:范围查询
  • DateRange:日期范围,有特殊处理 value[1] + 1
  • Any/NotAny:是否符合 value 中任何一项(直白的说是 SQL IN)
  • Custom:自定义解析open in new window
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)

动态表名:ISelect.AsTable((t, old) => $"{old}_201903")

动态排序:ISelect.OrderByPropertyName("Parent.Code")

动态返回:ISelect.ToDataTableByPropertyName(new string[] { "Parent.Code", "Id" })

动态贪婪加载:ISelect.IncludeByPropertyName("Parent.Parent").IncludeByPropertyName("Parent.Childs")

克隆查询 ISelect

科普:csharp 7.0 支持本地函数,方法内再定义临时方法,这个特性向大家推荐,在很多时候都非常有效。

方法内还可以定义方法,那就称它:本地函数/嵌套方法。

public void Test()
{
    ISelect<AdmRoute> getSelect() => fsql.Select<AdmRoute>().Include(a => a.Parent)
        .WhereIf(!string.IsNullOrEmpty(key), a => a.Name.Contains(key) || ...)
        .WhereIf(Parent_Id?.Any() == true, a => Parent_Id.Contains(a.ParentId))
        .WhereIf(mn_Roles_Id?.Any() == true, a => a.Roles.Any(b => mn_Roles_Id.Contains(b.Id)));

    var select1 = getSelect();
    var select2 = getSelect();
    select1.Where(a => a.Status == 0);
    //此时 select2 不会附加 a.Status == 0 条件
}

API

方法返回值参数描述
ToSqlstring返回即将执行的 SQL 语句
ToListList<T1>执行 SQL 查询,返回 T1 实体所有字段的记录,若存在导航属性则一起查询返回,记录不存在时返回 Count 为 0 的列表
ToList<T>List<T>Lambda执行 SQL 查询,返回指定字段的记录,记录不存在时返回 Count 为 0 的列表
ToList<T>List<T>string field执行 SQL 查询,返回 field 指定字段的记录,并以元组或基础类型(int,string,long)接收,记录不存在时返回 Count 为 0 的列表
ToOneT1执行 SQL 查询,返回 T1 实体所有字段的第一条记录,记录不存在时返回 null
ToAggregate<T>List<T>Lambda执行 SQL 查询,返回指定字段的聚合结果(适合不需要 GroupBy 的场景)
Anybool执行 SQL 查询,是否有记录
SumTLambda指定一个列求和
MinTLambda指定一个列求最小值
MaxTLambda指定一个列求最大值
AvgTLambda指定一个列求平均值
【分页】
Countlong查询的记录数量
Count<this>out long查询的记录数量,以参数 out 形式返回
Skip<this>int offset查询向后偏移行数
Offset<this>int offset查询向后偏移行数
Limit<this>int limit查询多少条数据
Take<this>int limit查询多少条数据
Page<this>int pageIndex, int pageSize分页
【条件】
Where<this>Lambda支持多表查询表达式,多次使用相当于 AND
WhereIf<this>bool, Lambda支持多表查询表达式
Where<this>string, parms原生 sql 语法条件,Where("id = @id", new { id = 1 } ,注意前缀@,根据具体数据库决定 其他地方不再说明。同理 )
WhereIf<this>bool, string, parms原生 sql 语法条件,WhereIf(true, "id = @id", new { id = 1 }
WhereCascade<this>Lambda实现多表查询时,向每个表中附加条件
WhereDynamicFilter<this>DynamicFilterInfo动态过滤条件(与前端交互)
【分组】
GroupBy<this>Lambda按选择的列分组,GroupBy(a => a.Name)GroupBy(a => new{a.Name,a.Time})
GroupBy<this>string, parms按原生 sql 语法分组,GroupBy("concat(name, @cc)", new { cc = 1 })
Having<this>string, parms按原生 sql 语法聚合条件过滤,Having("count(name) = @cc", new { cc = 1 })
Disdinct<this>.Distinct().ToList(x => x.GroupName) 是对指定字段
【排序】
OrderBy<this>Lambda按列排序,OrderBy(a => a.Time),可多次使用
OrderByDescending<this>Lambda按列倒向排序,OrderByDescending(a => a.Time)
OrderBy<this>string, parms按原生 sql 语法排序,OrderBy("count(name) + @cc", new { cc = 1 })
OrderByPropertyNamestring, bool按属性名字符串排序(支持导航属性)
【联表】
LeftJoin<this>Lambda左联查询,可使用导航属性,或指定关联的实体类型
InnerJoin<this>Lambda联接查询,可使用导航属性,或指定关联的实体类型
RightJoin<this>Lambda右联查询,可使用导航属性,或指定关联的实体类型
LeftJoin<this>string, parms左联查询,使用原生 sql 语法,LeftJoin("type b on b.id = a.id and b.clicks > @clicks", new { clicks = 1 })
InnerJoin<this>string, parms联接查询,使用原生 sql 语法,InnerJoin("type b on b.id = a.id and b.clicks > @clicks", new { clicks = 1 })
RightJoin<this>string, parms右联查询,使用原生 sql 语法,RightJoin("type b on b.id = a.id and b.clicks > @clicks", new { clicks = 1 })
From<this>Lambda多表查询,3 个表以上使用非常方便,目前设计最大支持 10 个表
FromQueryISelect<T1, T2>ISelect<T2>单表连成双表查询
WithTempQueryISelect<T1>Lambda将单表或多表查询嵌套成单表查询
WithMemoryISelect<T1>List<T1>使用内存数据查询
UnionAllISelect<T1>ISelect<T1>[]联合查询
【其他】
As<this>string alias = "a"指定别名
Master<this>指定从主库查询(默认查询从库)
CommandTimeout<this>int命令超时设置(秒)
WithTransaction<this>DbTransaction设置事务对象
WithConnection<this>DbConnection设置连接对象
WithLock<this>EnumSqlServer NoLock 等特有的设置
ForUpdate<this>bool排他更新锁,对不同的数据库已作适配,详细说明见注释
AsQueryableIQueryable将 ISelect 转换为 IQueryable,此方法主要用于扩展,比如:abp IRepository GetAll() 接口方法需要返回 IQueryable 对象。注意:IQueryable 方法污染较为严重,请尽量避免此转换
ToTreeList()List<TEntity>将父子关系的数据以 TreeList 的形式返回
AsTreeCte()ISelect(up, pathSelector, level)递归查询父子关系表