以下查询之间的区别是什么?一个是直接的SQL，另一个在LINQ中是等价的

Question

我这里有一个SQL查询，看起来像这样:

SELECT R.Extra3 AS 'Practice',
    SUM(DATEDIFF(s, R.Pickup, R.Hangup)) AS 'Seconds',
    COUNT(R.Extra3) AS 'Calls'
FROM Outbound.dbo.Results R
JOIN Outbound.dbo.Queue Q
    ON Q.QueueID = R.QueueID
    AND Q.Attempt = R.Attempt
WHERE R.CampaignId = 1
    AND DATEPART(m, R.Pickup) = DATEPART(m, DATEADD(m, -1, getdate()))
    AND DATEPART(y, R.Pickup) = DATEPART(y, DATEADD(m, -1, getdate()))
GROUP BY R.Extra3

我必须在一个程序中使用它，并决定走LINQ路线。这就是我想出来的:

IQueryable<PracticeSummary> query = db.Results
    .Join(
        db.Queues,
        r => new { Id = r.QueueID.Value, Attempt = r.Attempt.Value },
        q => new { Id = q.QueueID, Attempt = (byte)q.Attempt },
        (r, q) => r
    )
    .Where(
        r => r.CampaignID == 1
            && r.PickUp.Value.Month == lastMonth
            && r.PickUp.Value.Year == lastMonthYear
    )
    .GroupBy(g => g.Extra3)
    .Select(r => new PracticeSummary
    {
        Practice = r.Key,
        Calls = r.Count(),
        Seconds = (r.Sum(item => EntityFunctions.DiffSeconds(item.PickUp, item.HangUp).Value))
    });

我的SQL查询给了我正确的结果，而我的LINQ查询返回的行数超过10倍，因此，总和和计数更多。

我甚至查看了生成的TSQL。它看起来像这样:

SELECT 
1 AS [C1], 
[GroupBy1].[K1] AS [Extra3], 
[GroupBy1].[A1] AS [C2], 
[GroupBy1].[A2] AS [C3]
FROM ( SELECT 
        [Filter1].[K1] AS [K1], 
        COUNT([Filter1].[A1]) AS [A1], 
        SUM([Filter1].[A2]) AS [A2]
        FROM ( SELECT 
                [Extent1].[Extra3] AS [K1], 
                1 AS [A1], 
                DATEDIFF (second, [Extent1].[PickUp], [Extent1].[HangUp]) AS [A2]
                FROM  [dbo].[Results] AS [Extent1]
                INNER JOIN [dbo].[Queue] AS [Extent2] ON ([Extent1].[QueueID] = [Extent2].[QueueID]) AND (([Extent1].[Attempt] =  CAST( [Extent2].[Attempt] AS tinyint)) OR (([Extent1].[Attempt] IS NULL) AND ( CAST( [Extent2].[Attempt] AS tinyint) IS NULL)))
                WHERE (1 = [Extent1].[CampaignID]) AND ((DATEPART (month, [Extent1].[PickUp])) = @p__linq__0) AND ((DATEPART (year, [Extent1].[PickUp])) = @p__linq__1)
        )  AS [Filter1]
        GROUP BY [K1]
)  AS [GroupBy1]

据我所知，这与我所拥有的和想要的非常相似。

所以我的问题是为什么结果不同?我的SQL和LINQ查询有什么区别?我错过了什么?

提前感谢您的时间和精力!

编辑:

下面是Queue &结果:

public partial class Queue
{
    public long QueueID { get; set; }
    public long CampaignID { get; set; }
    public int Attempt { get; set; }
    public System.DateTime StartTime { get; set; }
    public System.DateTime EndTime { get; set; }
    public string Extra1 { get; set; }
    public string Extra2 { get; set; }
    public string Extra3 { get; set; }
}
public partial class Result
{
    public long ResultID { get; set; }
    public Nullable<long> QueueID { get; set; }
    public Nullable<long> CampaignID { get; set; }
    public Nullable<byte> Attempt { get; set; }
    public Nullable<System.DateTime> PickUp { get; set; }
    public Nullable<System.DateTime> HangUp { get; set; }
    public string Extra1 { get; set; }
    public string Extra2 { get; set; }
    public string Extra3 { get; set; }
}

Answer 1

如果得到的结果数是原来的十倍，则表明问题出在连接中。我建议从下往上构建查询，并测试结果以确定连接是否存在问题。

先试一下:

IQueryable<PracticeSummary> query = db.Results
    .Join(
        db.Queues,
        r => new { Id = r.QueueID.Value, Attempt = r.Attempt.Value },
        q => new { Id = q.QueueID, Attempt = (byte)q.Attempt },
        (r, q) => new { ResultID = r.ResultID, QueueID = q.QueueID
    );
foreach (var result in query)
{
    // See what you have got
}

也许先做where会更简单:

IQueryable<PracticeSummary> query = db.Results
    .Where(
        r => r.CampaignID == 1
            && r.PickUp.Value.Month == lastMonth
            && r.PickUp.Value.Year == lastMonthYear
    )
    .Join(
        db.Queues,
        r => new { Id = r.QueueID.Value, Attempt = r.Attempt.Value },
        q => new { Id = q.QueueID, Attempt = (byte)q.Attempt },
        (r, q) => new { ResultID = r.ResultID, QueueID = q.QueueID
    );
foreach (var result in query)
{
    // See what you have got
}

Answer 2

是否有可能您的结果。尝试和队列。尝试值包含一些null ??

如果是，下面的语句将把结果中所有为空的行相乘:

OR (([Results].[Attempt] IS NULL) AND ( CAST( [Queue].[Attempt] AS tinyint) IS NULL))