LINQ to SQL 中 Concat、Union、Intersect、Except 方法的使用

时间:2022-06-14 15:47:31

Ø  前言

LINQ to SQL 中需要对两个或多个数据集进行操作,比如:合并、取交集等,主要使用下面四个方法,这四个方法都是 System.Linq.IQueryable<out T> 接口的扩展方法,并且都是延迟加载方法,下面是使用的简单示例。

 

Ø  示例数据,数据表:Subject(科目表)、Score(成绩表)

LINQ to SQL 中 Concat、Union、Intersect、Except 方法的使用

 

1.   Concat() 方法

1)   方法声明

public static IQueryable<TSource> Concat<TSource>(this IQueryable<TSource> source1, IEnumerable<TSource> source2);

2)   描述

合并两个数据集,不去重复,对应的 SQL 语句是 UNION ALL

3)   示例1:获取所有科目Id 和 考试过的科目Id 列表(科目Id 可以重复)

1.   C# 代码

var query = (from t1 in DbContext.Subject

                select t1.SubjectId

            ).Concat(

                from t1 in DbContext.Score

                select t1.SubjectId

            );

var result = query.ToList();

2.   生成SQL

SELECT

    [UnionAll1].[SubjectId] AS [C1]

    FROM  (SELECT

        [Extent1].[SubjectId] AS [SubjectId]

        FROM [dbo].[Subject] AS [Extent1]

    UNION ALL

        SELECT

        [Extent2].[SubjectId] AS [SubjectId]

        FROM [dbo].[Score] AS [Extent2]) AS [UnionAll1]

3.   执行结果

LINQ to SQL 中 Concat、Union、Intersect、Except 方法的使用

 

2.   Union() 方法

1)   方法声明

public static IQueryable<TSource> Union<TSource>(this IQueryable<TSource> source1, IEnumerable<TSource> source2);

2)   描述

合并两个数据集,去重复,对应的 SQL 语句是 UNION ALL + DISTINCT。与想象中似乎不太一样,实际写 SQL 时,一般都会用 UNION 就好了哇。

3)   示例1:获取所有科目Id 和 考试过的科目Id 列表(科目Id 不能重复)

1.   C# 代码

var query = (from t1 in DbContext.Subject

                select t1.SubjectId

            ).Union(

                from t1 in DbContext.Score

                select t1.SubjectId

            );

var result = query.ToList();

2.   生成SQL

SELECT

    [Distinct1].[C1] AS [C1]

    FROM ( SELECT DISTINCT

        [UnionAll1].[SubjectId] AS [C1]

        FROM  (SELECT

            [Extent1].[SubjectId] AS [SubjectId]

            FROM [dbo].[Subject] AS [Extent1]

        UNION ALL

            SELECT

            [Extent2].[SubjectId] AS [SubjectId]

            FROM [dbo].[Score] AS [Extent2]) AS [UnionAll1]

    )  AS [Distinct1]

3.   执行结果

LINQ to SQL 中 Concat、Union、Intersect、Except 方法的使用

 

3.   Intersect() 方法

1)   方法声明(2个重载)

public static IQueryable<TSource> Intersect<TSource>(this IQueryable<TSource> source1, IEnumerable<TSource> source2);

public static IQueryable<TSource> Intersect<TSource>(this IQueryable<TSource> source1, IEnumerable<TSource> source2, IEqualityComparer<TSource> comparer);

2)   描述

取两个数据集的交集(在数据集A出现的记录,并且在数据集B也出现的记录),对应的 SQL 语句是 INTERSECT

3)   示例1:获取考试过的科目Id

1.   C# 代码

var query = (from t1 in DbContext.Subject

                select t1.SubjectId

            ).Intersect(

                from t1 in DbContext.Score

                select t1.SubjectId

            );

var result = query.ToList();

2.   生成SQL

SELECT

    [Intersect1].[SubjectId] AS [C1]

    FROM  (SELECT

        [Extent1].[SubjectId] AS [SubjectId]

        FROM [dbo].[Subject] AS [Extent1]

    INTERSECT

        SELECT

        [Extent2].[SubjectId] AS [SubjectId]

        FROM [dbo].[Score] AS [Extent2]) AS [Intersect1]

3.   执行结果

LINQ to SQL 中 Concat、Union、Intersect、Except 方法的使用

 

4.   Concat() 方法

1)   方法声明(2个重载)

public static IQueryable<TSource> Except<TSource>(this IQueryable<TSource> source1, IEnumerable<TSource> source2);

public static IQueryable<TSource> Except<TSource>(this IQueryable<TSource> source1, IEnumerable<TSource> source2, IEqualityComparer<TSource> comparer);

2)   描述

取两个数据集的差集(在数据集A出现的记录,在数据集B没出现的记录),对应的 SQL 语句是 EXCEPT

3)   示例1:获取没有考试过的科目Id

1.   C# 代码

var query = (from t1 in DbContext.Subject

                select t1.SubjectId

            ).Except(

                from t1 in DbContext.Score

                select t1.SubjectId

            );

var result = query.ToList();

2.   生成SQL

SELECT

    [Except1].[SubjectId] AS [C1]

    FROM  (SELECT

        [Extent1].[SubjectId] AS [SubjectId]

        FROM [dbo].[Subject] AS [Extent1]

    EXCEPT

        SELECT

        [Extent2].[SubjectId] AS [SubjectId]

        FROM [dbo].[Score] AS [Extent2]) AS [Except1]

3.   执行结果

LINQ to SQL 中 Concat、Union、Intersect、Except 方法的使用

 

Ø  总结

简单测试,以备不时之需。