SQL到Linq查询与多个左外连接 [英] SQL to Linq query with multiple left outer joins

问题描述

我正在尝试将 SQL查询转换为包含几个左外连接的 Linq ，但我遇到奇怪的情况。

I'm trying to convert an SQL query to Linq containing several left outer joins, but I'm encountering an odd situation.

我的SQL的相关部分是：

The relevant part of my SQL is:

SELECT * FROM dbo.SessionDetails as sd
    left outer join dbo.VoipDetails as vd on vd.SessionIdTime = sd.SessionIdTime and vd.SessionIdSeq = sd.SessionIdSeq
    left outer join dbo.Gateways as fgw on vd.FromGatewayId = fgw.GatewayId

我的Linq查询到目前为止是：

My Linq query so far is:

var query = from sd in dbo.SessionDetails
    join vd in dbo.VoipDetails on new { sd.SessionIdTime, sd.SessionIdSeq } equals new { vd.SessionIdTime, vd.SessionIdSeq } into sdvd
    from v in sdvd.DefaultIfEmpty()
         join fgw  in dbo.Gateways on vd.FromGatewayId equals fgw.GatewayId   into sdgw
         from g in sdvd.DefaultIfEmpty()
             select sd;

我在上收到错误标记vd.FromGatewayId 告诉我名称vd不在equals左侧的范围内。考虑交换equals两边的表达式。

但是，如果我用 gw.GatewayId 那么我会收到相同的错误消息为$ code> vd 和 gw 。
有人可以在这里提出正确的语法吗？
请记住，在我得到基本语法之后，还有几个左连接添加。

I'm getting an error mark on vd.FromGatewayId telling me that The name 'vd' is not in scope on the left side of 'equals'. Consider swapping the expressions on either side of 'equals'.
However, if I do swap sides with gw.GatewayId then I get the same error message for both vd and gw. Can someone suggest the correct syntax here? Please bear in mind I have several more left-joins to add after I get the basic syntax down.

推荐答案

我相信该问题是您正在尝试访问一个查询中没有范围的值。我相信原因是您指定关系船，然后将这些值分配给一个名为 sdvd 的集合，此时您无法访问vd。也就是说，然后，您可以通过sdvd.DefaultIfEmpty（）中的v执行，方法是访问sdvd中与您认为的相同值的行 vd hold。您应该可以使用 v 而不是 vd 。我不得不模拟一些东西来测试，所以我无法测试这个查询，但以下应该运行。

I believe the issue is that you are trying to access a value that has no scope in the query. I believe the reason for this is that you specify the relation ship and then assign these values to a collection called sdvd at this point you can't get to vd. That said, you then do from v in sdvd.DefaultIfEmpty() by doing so you have access to the rows in sdvd which are the same values that are in what you think vd holds. You should be able to use v instead of vd. I had to mock something up to test so I couldn't test out exactly this query, but the following should run.

var query = from sd in dbo.SessionDetails
    join vd in dbo.VoipDetails on new { sd.SessionIdTime, sd.SessionIdSeq } equals new { vd.SessionIdTime, vd.SessionIdSeq } into sdvd
    from v in sdvd.DefaultIfEmpty()
        join fgw  in dbo.Gateways on v.FromGatewayId equals fgw.GatewayId   into sdgw
        from g in sdvd.DefaultIfEmpty()
            select sd;

EDIT 2014/12/08

EDIT 2014/12/08

为了查看linq语句如何转换为sql，我建议您安装 https://www.linqpad.net/ 。您可以设置连接并测试您的查询，并在结果视图中查看sql。

In order to see how the linq statement translates to sql, I would suggest you install https://www.linqpad.net/. You can set up a connection and test out your queries there and see the sql in the results view.

由于我没有这个问题的数据结构，这将很难。这表示我嘲笑了一些东西：

As i don't have the data structure for the question this will be hard. That said I mocked up something:

from sd in  Employees
    join vd in TimeEntries on new { sd.EmployeeID } equals new { vd.EmployeeID } into sdvd
    from v in sdvd.DefaultIfEmpty()
        join fgw  in EmployeeGroupDetails on v.EmployeeID equals fgw.EmployeeID   into sdgw
        from g in sdgw.DefaultIfEmpty()
            select  sd

这个YIELDS：

SELECT [t0].*
FROM [Employee] AS [t0]
LEFT OUTER JOIN [TimeEntry] AS [t1] ON [t0].[EmployeeID] = [t1].[EmployeeID]
LEFT OUTER JOIN [EmployeeGroupDetail] AS [t2] ON [t1].[EmployeeID] = [t2].[EmployeeID]

正确连接回来。

这篇关于SQL到Linq查询与多个左外连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！