CRL快速开发框架系列教程十三(嵌套查询)

本系列目录

正文

像LINQ TO SQL一样,任意查询是众多ORM的终级梦想,一般做个关联查询就差不多了,再来嵌套查询,就很麻烦了

因为需要语法表达嵌套关系和筛选结果,一般基于Lambda表达式的ORM才能实现(拼串的就算了)

CRL4.2版本解决了匿名对象解析和子查询表达的问题,现在查询可以任意组合了
为了兼容以前的写法,增加了LambdaQuery.SelectV方法,返回LambdaQueryResultSelect类型
同时增加了以下几种方式

关联子查询
public LambdaQueryViewJoin<T, TJoinResult> Join<TJoinResult>(LambdaQueryResultSelect<TJoinResult> resultSelect, Expression<Func<T, TJoinResult, bool>> expression, JoinType joinType = JoinType.Inner)
联合查询
public LambdaQueryResultSelect<TResult> Union<TResult2>(LambdaQueryResultSelect<TResult2> resultSelect, UnionType unionType = UnionType.UnionAll)
In,Exists查询也改为子查询形式
返回多种结果
public List<TResult> ToList()//返回匿名对象
public List<TResult2> ToList<TResult2>()//返回指定类型
public List<dynamic> ToDynamic()//返回动态类型

为了使子查询有关联性,需要调用CreateQuery方法创建查询
使用嵌套查询过程表示为:

主查询 => CreateQuery子查询 => 返回匿名对象筛选LambdaQueryResultSelect => 主查询嵌套子查询 => 返回结果

声明查询

var query = Code.ProductDataManage.Instance.GetLambdaQuery();
var query2 = query.CreateQuery<Code.Order>();

直接返回匿名结果

var result1 = query.SelectV(b => new { id = b.Id, name = b.CategoryName }).ToList();

SQL

select  t1.[Id] as id,t1.[CategoryName] as name  from [ProductData] t1  with(nolock)

关联一个子查询

只要是LambdaQuery创建的语法都支持,如GROUP,DISTINCT,或者已经是一个关联查询

var viewJoin = query2.Where(b => b.Id > 10).SelectV(b => b);
var result2 = query.Join(viewJoin, (a, b) => a.UserId == b.UserId).Select((a, b) => new { a.CategoryName, b.OrderId }).ToList();

SQL

select  t1.[CategoryName] as CategoryName,t2.[OrderId] as OrderId  from [ProductData] t1  with(nolock)  
Inner join (select  t2.[Id],t2.[Status],t2.[OrderId],t2.[Remark],t2.[UserId],t2.[Channel],t2.[AddTime]  from [OrderProduct] t2  with(nolock)  where (t2.[Id]>@par0) ) t2
on (t1.[UserId]=t2.[UserId])

联合查询(支持N个)

var view1 = query.SelectV(b => new { a1 = b.Id, a2 = b.ProductName });
var view2 = query2.SelectV(b => new { a1 = b.Id, a2 = b.Remark });
var result3 = view1.Union(view2).OrderBy(b => b.a1).OrderBy(b => b.a2, false).ToList();

SQL:

select  t1.[Id] as a1,t1.[ProductName1] as a2  from [ProductData] t1  with(nolock)  
union all 
select  t2.[Id] as a1,t2.[Remark] as a2  from [OrderProduct] t2  with(nolock) 
order by  [a1] desc,  [a2] asc

按In查询

var view = query2.Where(b => b.Name == "123").SelectV(b => b.Id);
query.In(view, b => b.UserId);
var sql2 = query.PrintQuery();

SQL:

select  t1.[Id],t1.[InterFaceUser],t1.[Date2],t1.[UserId],t1.[BarCode],t1.[TransType]
from [ProductData] t1  with(nolock)  where t1.[UserId] in(select  t2.[Id]  from [Member] t2  with(nolock)  where (t2.[Name]=@par0) )

来一个复杂的

var q1 = Code.OrderManage.Instance.GetLambdaQuery();
var q2 = q1.CreateQuery<Code.ProductData>();
q2.Where(b => b.Id > 0);
var view = q2.CreateQuery<Code.Member>().GroupBy(b => b.Name).Where(b => b.Id > 0).SelectV(b => new { b.Name, aa = b.Id.COUNT() });//GROUP查询
var view2 = q2.Join(view, (a, b) => a.CategoryName == b.Name).Select((a, b) => new { ss1 = a.UserId, ss2 = b.aa });//关联GROUP
q1.Join(view2, (a, b) => a.Id == b.ss1).Select((a, b) => new { a.Id, b.ss1 });//再关联
var result = view2.ToList();
var sql = q1.ToString();

SQL:

SELECT t1.[Id] AS Id,
       t2.[ss1] AS ss1
FROM [OrderProduct] t1 with(nolock)
INNER JOIN
  (SELECT t2.[UserId] AS ss1,
          t3.[aa] AS ss2
   FROM [ProductData] t2 with(nolock)
   INNER JOIN
     (SELECT t3.[Name] AS Name,
             COUNT(t3.Id) AS aa
      FROM [Member] t3 with(nolock)
      WHERE (t3.[Id]>@par1)
      GROUP BY t3.[Name]) t3 ON (t2.[CategoryName]=t3.[Name])
   WHERE (t2.[Id]>@par0) ) t2 ON (t1.[Id]=t2.[ss1])