union 分页/group/join 复杂查询(.net core/framework)

### union 分页/group/join 复杂查询(.net core/framework)

unoin是一个比较特殊的查询，对union进行分页，关联，分组需要在最外面包装一层，如果对union结果再进行其它关联，分组，复杂度直线上升，解决此问题

1. 安装nuget包：CRL

2. using CRL;

以下为默认数据源实现

如果使用ef core和ado.net 见:[Data/EFTest · hubroxxl/CRL - 码云 - 开源中国 (gitee.com)](https://gitee.com/hubroxxl/crl/tree/master/Data/EFTest)

**定义数据源**

```c#

var builder = DBConfigRegister.GetInstance();

builder.RegisterDBAccessBuild(dbLocation =>

{

return new DBAccessBuild(DBType.MSSQL, "server=.;database=testDb; uid=sa;pwd=123;");

});

```

**定义对象管理器**

```c#

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**

```c#

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为

```sql

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

```

**对union进行分页**

```c#

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为

```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 RowNumbe

```

**union后再join**

```c#

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为

```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**

```c#

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为

```sql

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

```

源码示例参考

[Data/QueryTest/test · hubroxxl/CRL - 码云 - 开源中国 (gitee.com)](https://gitee.com/hubroxxl/crl/tree/master/Data/QueryTest/test)