代码之家 › 专栏 › 技术社区 › Vlad

不能在LINQ中连续进行5次“连接”?

sql-server-2008 linq c#

Vlad · 技术社区 · 6 年前

我在SQL server 2008中有一个相当复杂的查询:

declare @LanguageID as int = 1

select k.datePublish, k.dateEditing, k.dateTables

from TableAreasLevel1 as areaL1 

inner join TableAreasLevel2 as areaL2 
on areaL1.LanguageID = areaL2.LanguageID and 
areaL1.CodeAreaLevel1 = areaL2.CodeAreaLevel1

inner join TableAreasLink as link
on areaL2.CodeAreaLevel1 = link.CodeAreaLevel1 and
areaL2.CodeAreaLevel2 = link.CodeAreaLevel2 and

inner join TableProducts as tblProds
on tblProds.CodeAreaLevel1 = areaL1.CodeAreaLevel1 and
tblProds.CodeAreaLevel2 = areaL2.CodeAreaLevel2

inner join TableSI_Products as prod 
on prod.SiAreaCode = link.SiAreaCode

inner join TableCalendar as k
on k.KodTableSI_Products = tblProds.KodTableSI_Products

where areaL1.LanguageID = @LanguageID and 
prod.Code = 'some string' and 
k.LanguageID = @LanguageID and 
tblProds.LanguageID = @LanguageID;

我试图在LINQ中开发相同的查询,但是当我尝试连接TableProducts表(即第三个连续连接)时出错。

以下是我的LINQ查询:

List<Tuple<DateTime, DateTime, DateTime>> dates = (from areaL1 in gpe.TableAreasLevel1 
    join areaL2 in gpe.TableAreasLevel2 
        on new { areaL1.CodeAreaLevel1, areaL1.LanguageID } equals 
            new { areaL2.CodeAreaLevel1, areaL2.LanguageID } 

    join link in gpe.TableAreasLink 
        on new { areaL2.CodeAreaLevel1, areaL2.CodeAreaLevel2, areaL2.RbrOblastNivo2} equals 
            new {link.CodeAreaLevel1, link.CodeAreaLevel2}

    join tblProds in gpe.TableProducts
        on tblProds. // The name tblProds is not in the scope of the left side of 'equals'

    );

这个问题是与表格的设计有关,还是与我应该检查的其他问题有关?

有什么想法吗,为什么 TBL棒 在LINQ查询的范围内不可见?

1 回复 | 直到 6 年前

Vijunav Vastivch 6 年前

您使用Query作为指南,例如:

on k.KodTableSI_Products = tblProds.KodTableSI_Products

但是看看你的linq:

on new { areaL1.CodeAreaLevel1, areaL1.LanguageID } equals

它有两个字段。我认为这不是个好主意。

linq加入类似

var dates = (from areaL1 in gpe.TableAreasLevel1 
    join areaL2 in gpe.TableAreasLevel2 
        on areaL1.PKFields  equals areaL2.PKFields
          where areaL1.CodeAreaLevel1== areaL2.CodeAreaLevel1 && areaL1.LanguageID =  areaL2.LanguageID
Select new YournewClass{YournewClass.Field1=areaL1.fields1, And so on}

)

您可以使用别名连接其他表。

抱歉,我现在得走了。我给你一个主意。希望有帮助。