具有多个内连接和别名的SQL查询的LINQ语法

本文关键字:SQL 查询 LINQ 语法 别名 连接 | 更新日期: 2023-09-27 18:11:48

我正在从一个具有多个连接和别名的SQL查询创建一个c# LINQ语句。我在构建LINQ时遇到了一些麻烦。

SQL:

SELECT 
 store.Name as 'Store', 
 store.CreatedOn as 'StoreCreated', 
 supplier.Name as 'Supplier',
 supplier.CreatedOn as 'SupplierCreated',
 farm.Name as 'Farm',
 farm.CreatedOn as 'FarmCreated', 
 FROM Users store 
INNER JOIN UserRelationship toSupplier on store.ID = toSupplier.YId 
INNER JOIN Users supplier ON supplier.ID = toSupplier.XId
INNER JOIN UserRelationship toFarm ON store.ID = toFarm.XId
INNER JOIN Users farm ON farm.ID = toFarm.YId
WHERE 
 store.Active= '1'
 AND
 supplier.Active = '1'
 AND
 farm.Active = '1'

返回显示三方和日期之间关系的行。

到目前为止,我已经得到了以下LINQ:
 var newUserList = from store in Users
                   join toSupplier in UserRelationship on store.ID = toSupplier.YId
                   join supplier in Users on supplier.ID = toSupplier.XId
                   join toFarm in UserRelationship on store.ID = toFarm.XId
                   join farm in Users on farm.ID = toFarm.YId

我讲对了吗?

具有多个内连接和别名的SQL查询的LINQ语法

LINQ join语法在连接条件中使用equals关键字而不是=:

var newUserList = from store in Users
                  join toSupplier in UserRelationship on store.ID equals toSupplier.YId
                  join supplier in Users on supplier.ID equals toSupplier.XId
                  join toFarm in UserRelationship on store.ID equals toFarm.XId
                  join farm in Users on farm.ID equals toFarm.YId
                  select ...