如何将此SQL查询转换为Linq

本文关键字：转换 Linq 查询 SQL | 更新日期: 2023-09-27 18:11:08

我有一个现有的LINQ查询，需要两个右连接，我知道我将如何在SQL中编写它，但我不确定LINQ。

这不是一个关于如何在linq中做一个连接的问题。我需要这两个。

考虑以下伪SQL查询:

SELECT      [ProjectItem].*
FROM        [ProjectItem]
RIGHT JOIN  [UserCostingItem]
ON          [UserCostingItem].[CostingItemID] 
=           [ProjectItem].[ProjectItemID]
RIGHT JOIN  [UserCostingItemType]
ON          [UserCostingItemType].[CostingItemType]
=           [ProjectItem].[ProjectItemType]
WHERE 
(
    [UserCostingItem].[PrimaryKey] IS NOT NULL
    OR 
    [UserCostingItemType].[PrimaryKey] IS NOT NULL
)

我想在项目项目表中获得记录，其中在UserCostingItem表或UserCostingItemType表中都有记录。

目前我正在连接一个表，但我需要对两个表进行右连接，并只返回存在于其中一个连接中的记录。

这是我目前为止只在一个表上进行内部连接的代码:

List<PCProjectItem> projectItems = new List<PCProjectItem>();
List<UserCostingItem> userCostingItems = new List<UserCostingItem>();
List<UserCostingItemType> userCostingItemTypes = new List<UserCostingItemType>();
projectItems = PCProjectItem.GetProjectItems(projectID, sageDatabaseID, PCIntegrationOption);
userCostingItems = UserCostingItem.GetUserCostingItems(userID, sageDatabaseID, documentType == null ? string.Empty : documentType.Value.ToString());
userCostingItemTypes = UserCostingItemType.GetUserCostingItemTypes(userID, sageDatabaseID);
//If there are no project items or allocations, return a new list now
if (projectItems.Count == 0 || (userCostingItems.Count == 0 && userCostingItemTypes.Count == 0))
{
     return new List<PCProjectItem>();
}
//Get the project Items the user has access to only
return (from PCProjectItem projectItem in projectItems
        join UserCostingItem userCostingItem in userCostingItems on projectItem.PCCostItemID equals userCostingItem.CostingItemID
        select projectItem)
        .Distinct() // Distinct Records
        .ToList(); // Convert to list

如何将此SQL查询转换为Linq

这似乎给出了我需要的结果:

List<PCProjectItem> projectItems = new List<PCProjectItem>();
List<UserCostingItem> userCostingItems = new List<UserCostingItem>();
List<UserCostingItemType> userCostingItemTypes = new List<UserCostingItemType>();
projectItems = PCProjectItem.GetProjectItems(projectID, sageDatabaseID, PCIntegrationOption);
userCostingItems = UserCostingItem.GetUserCostingItems(userID, sageDatabaseID, documentType == null ? string.Empty : documentType.Value.ToString());
userCostingItemTypes = UserCostingItemType.GetUserCostingItemTypes(userID, sageDatabaseID);
//If there are no project items or allocations, return a new list now
if (projectItems.Count == 0 || (userCostingItems.Count == 0 && userCostingItemTypes.Count == 0))
{
    return new List<PCProjectItem>();
}
var results = from PCProjectItem projectItem in projectItems
              join UserCostingItem userCostingItem in userCostingItems on projectItem.PCCostItemID equals userCostingItem.CostingItemID into costingItemJoin
              from costItemRec in costingItemJoin.DefaultIfEmpty()
              join UserCostingItemType userCostingItemType in userCostingItemTypes on projectItem.PCCostItemTypeID equals userCostingItemType.CostingItemTypeID into costingItemTypeJoin
              from costItemTypeRec in costingItemTypeJoin.DefaultIfEmpty()
              where costItemTypeRec != null || costItemRec != null
              select projectItem;
return results.Distinct().ToList();
相关文章：