我正试图将 statement言改成lin子,但也许我会做一些错误的事情,因为我会取得不同的结果。 我正在林克帕德试图发言。

Here is my sql statment:

SELECT set_id
        FROM TestTable
        WHERE rec_id In (25,32)
        AND set_id NOT IN
        (
        SELECT x.set_id FROM TestTable x
        WHERE x.rec_id NOT IN(25,32)
        )
        GROUP BY set_id
        HAVING COUNT(set_id) = 2

林克:

var recIdList = new List<int?>() { 25,32};

var query = from u in TestTables
                            where
                            (
                                recIdList.Contains(u.Rec_id) &&
                                !(from k in TestTables
                                  where !recIdList.Contains(u.Rec_id)
                                  select k.Set_id).Contains(u.Set_id)
                            )
                            group u by u.Set_id into userGroup
                            where userGroup.Count() == recIdList.Count
                            select userGroup.Key;
query.Dump();
TestTables.Dump();

The sql query generated by Linq:

-- Region Parameters
DECLARE @p0 Int = 25
DECLARE @p1 Int = 32
DECLARE @p2 Int = 25
DECLARE @p3 Int = 32
DECLARE @p4 Int = 2
-- EndRegion
SELECT [t2].[set_id]
FROM (
    SELECT COUNT(*) AS [value], [t0].[set_id]
    FROM [TestTable] AS [t0]
    WHERE ([t0].[rec_id] IN (@p0, @p1)) AND (NOT (EXISTS(
        SELECT NULL AS [EMPTY]
        FROM [TestTable] AS [t1]
        WHERE ([t1].[set_id] = [t0].[set_id]) AND (NOT ([t0].[rec_id] IN (@p2, @p3)))
        )))
    GROUP BY [t0].[set_id]
    ) AS [t2]
WHERE [t2].[value] = @p4

测试:

Create Table TestTable
(
  set_id int,
  rec_id int
)
Insert Into TestTable (set_id, rec_id) Values(10, 1)
Insert Into TestTable (set_id, rec_id) Values(10, 25)
Insert Into TestTable (set_id, rec_id) Values(10, 32)
Insert Into TestTable (set_id, rec_id) Values(20, 61)
Insert Into TestTable (set_id, rec_id) Values(20, 90)
Insert Into TestTable (set_id, rec_id) Values(30, 77)
Insert Into TestTable (set_id,rec_id)  Values(11,25)
Insert Into TestTable (set_id,rec_id)  Values(11,32)

GO

The result for my Sql query: set_id 11
The result for my Linq query: set_id (10 and 11)

想法是,如果补偿金确实存在(第_id 11:25,32页),那么(第10页:(1,25,32页)并不确切。

我增加了两个图像,显示使用LinqPad两种方法的结果:

SqlStatmentWithResultSet
LinqStatmentWihtResultSet

提前感谢你们的帮助......