union 分页/group/join 简单查问(.net core/framework)
unoin是一个比拟非凡的查问，对union进行分页，关联，分组须要在最里面包装一层，如果对union后果再进行其它关联，分组，复杂度直线回升，解决此问题
- 装置nuget包：CRL
- using CRL;
以下为默认数据源实现
如果应用ef core和ado.net 见:Data/EFTest · hubroxxl/CRL - 码云 - 开源中国 (gitee.com)
定义数据源
var builder = DBConfigRegister.GetInstance();builder.RegisterDBAccessBuild(dbLocation => { return new DBAccessBuild(DBType.MSSQL, "server=.;database=testDb; uid=sa;pwd=123;"); });定义对象管理器
public class ProductRepository:BaseProvider<ProductData>{ public static ProductRepository Instance { get { return new ProductRepository(); } }}通过GetLambdaQuery办法创立ILambdaQuery
ILambdaQuery能实现子查问和嵌套查问，只有合乎T-SQL语义逻辑，能够应用ILambdaQueryResultSelect有限叠加
如：
- join后group
- join后再join
- group后再join
- join一个group后果
- join一个union后果
- 对union进行group再join
- ...
简略的union
var query = ProductRepository.Instance.GetLambdaQuery().Where(b => b.Id < 200); var query2 = query.CreateQuery<Code.Order>().Where(b => b.Id < 200); var view1 = query.Select(b => new { a1 = b.Id, a2 = b.ProductName }); var view2 = query2.Select(b => new { a1 = b.Id, a2 = b.Remark }); var result = view1.Union(view2).OrderBy(b => b.a1).OrderBy(b => b.a2, false).ToList(); var sql = query.PrintQuery();生成SQL为
select t1.[Id] as a1,t1.[ProductName1] as a2 from [ProductData] t1 with (nolock) where (t1.[Id]<'200') union allselect t2.[Id] as a1,t2.[Remark] as a2 from [OrderProduct] t2 with (nolock) where (t2.[Id]<'200') order by [a1] desc,[a2] asc对union进行分页
var query = ProductRepository.Instance.GetLambdaQuery().Where(b => b.Id < 200); query.Take(10); var query2 = query.CreateQuery<Code.Order>().Where(b => b.Id < 200).Take(5); var view1 = query.Select(b => new { a1 = b.Id, a2 = b.ProductName }); var view2 = query2.Select(b => new { a1 = b.Id, a2 = b.Remark }); var union = view1.Union(view2).OrderBy(b => b.a1).OrderBy(b => b.a2, false); union.UnionPage(15, 1);//分页参数 var result = union.ToList(); var sql = query.PrintQuery();生成SQL为
SELECT * FROM (select a1,a2,ROW_NUMBER() OVER ( Order by [a1] desc,[a2] asc ) AS RowNumber from (select top 10 t1.[Id] as a1,t1.[ProductName1] as a2 from [ProductData] t1 with (nolock) where (t1.[Id]<200) union all select top 5 t2.[Id] as a1,t2.[Remark] as a2 from [OrderProduct] t2 with (nolock) where (t2.[Id]<200)) tu) T WHERE T.RowNumber BETWEEN 1 AND 15 order by RowNumberunion后再join
var query = ProductRepository.Instance.GetLambdaQuery().Where(b => b.Id < 200); query.Take(10); var query2 = query.CreateQuery<Code.Order>().Where(b => b.Id < 200).Take(5); var view1 = query.Select(b => new { a1 = b.Id, a2 = b.ProductName }); var view2 = query2.Select(b => new { a1 = b.Id, a2 = b.Remark }); var union = view2.Union(view1).OrderBy(b => b.a1).OrderBy(b => b.a2, false); var join = query.Join(union, (a, b) => a.Id == b.a1).Select((a, b) => new { a.Id, b.a2 });//join var result = join.ToList(); var sql = query.PrintQuery();生成SQL为
select top 10 t1.[Id],t3.[a2] as a2 from [ProductData] t1 with (nolock) Inner join (select top 5 t2.[Id] as a1,t2.[Remark] as a2 from [OrderProduct] t2 with (nolock) where (t2.[Id]<'200') union all select top 10 t1.[Id] as a1,t1.[ProductName1] as a2 from [ProductData] t1 with (nolock) where (t1.[Id]<'200') order by [a1] desc,[a2] asc) t3 on t1.[Id]=t3.a1 where (t1.[Id]<'200')union后再group
var query = ProductRepository.Instance.GetLambdaQuery().Where(b => b.Id < 200); var query2 = query.CreateQuery<Code.Order>().Where(b => b.Id < 200); var view1 = query.Select(b => new { a1 = b.Id, a2 = b.ProductName }); var view2 = query2.Select(b => new { a1 = b.Id, a2 = b.Remark }); var union = view1.Union(view2).OrderBy(b => b.a2, false); var group = union.UnionGroupBy(b => b.a2);//group var result = group.Select(b => new { b.a2 }).ToList(); var sql = query.PrintQuery();生成SQL为
select a2 from (select t1.[Id] as a1,t1.[ProductName1] as a2 from [ProductData] t1 with (nolock) where (t1.[Id]<'200') union allselect t2.[Id] as a1,t2.[Remark] as a2 from [OrderProduct] t2 with (nolock) where (t2.[Id]<'200')) tu group by a2 order by [a2] asc源码示例参考
Data/QueryTest/test · hubroxxl/CRL - 码云 - 开源中国 (gitee.com)