Union paging /group/join complex query (. NET Core/Framework)

Unoin is a special query. Paging, associating, and grouping a union need to be wrapped in the outer layer. If other associating and grouping are performed on the union result, the complexity increases sharply to solve this problem

  1. Install the NuGet package: CRL
  2. using CRL;

The following is the default data source implementation

If using EF Core and ADO. NET see :Data/ EFtest · Hubroxxl /CRL – Code Cloud – Open Source China (gitee.com)

Define the data source

var builder = DBConfigRegister.GetInstance(); builder.RegisterDBAccessBuild(dbLocation => { return new DBAccessBuild(DBType.MSSQL, "server=.; database=testDb; uid=sa; pwd=123;" ); });

Define the object manager

public class ProductRepository:BaseProvider<ProductData> { public static ProductRepository Instance { get { return new ProductRepository(); }}}

Create ILAMBDAQuery using the getLambdaQuery method

ILAMBDAQuery can implement subqueries and nested queries, and can use ILAMBDAQueryResultSelect infinite stack as long as it complies with T-SQL semantic logic

Such as:

  • Join the group
  • The join before you join
  • Group before you join
  • Join a group result
  • Join a UNION result
  • Group and rejoin the UNION
  • .

Simple 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();

Generating SQL for

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

Paging the 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();

Generating SQL for

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 RowNumber

The union before you 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();

Generating SQL for

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')

After the 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();

Generating SQL for

select a2 from (select t1.[Id] as a1,t1.[ProductName1] as a2 from [ProductData] t1 with (nolock) where (t1.[Id]<'200') union all select 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

Source sample reference

Data/QueryTest/test · hubroxxl/CRL – code cloud – open source China (gitee.com)