How to convert this SQL query to Linq

Question

I have an existing LINQ query that needs two right joins on it, I know how I would write it in SQL but im not sure about LINQ.

This is not a question on how to do one join in linq. I need the two.

consider the following pseudo SQL query:

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
)

I want to get records in the Project Item table, where there is a record in either the UserCostingItem table OR the UserCostingItemType table.

Currently I'm joining on one table, but I need to right join on both and return only the records that exist in one of the joins.

here is the code I have so far which is only inner joining on one table:

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

possible duplicate of [LINQ Left Join And Right Join](http://stackoverflow.com/questions/4497086/linq-left-join-and-right-join) — user1666620, Sep 04 '15 at 14:20
Non duplicate of linked question. The question is more of how to I do two right joins and select the record related to either of them, rather than how do I do a left / right join. — WraithNath, Sep 04 '15 at 14:23
First of all - LINQ to what? EF, SQL, Objects? Anyway, if you define the proper relations between your entities and mappings to the underlying tables, you won't have to create any joins. Using joins instead of relations is a strong smell that suggests the object model is wrong or that LINQ and the ORM are used as if they were plain ADO.NET — Panagiotis Kanavos, Sep 04 '15 at 14:24
Linq to objects - Thanks for the info but that does not really answer the question. The reason this is being done in linq is because the objects are from data on potentially two different SQL servers, so to avoid cross server joins two queries are run and the results selected with LINQ. — WraithNath, Sep 04 '15 at 14:29
What you are doing though *is* a cross-server join, only it's done on a *third* machine without benefit of indexing. The reason people want to avoid cross-server queries is that the linked server has to send a lot of data to the primary server, which then goes on to do its own query using indexes etc. In your case though, *two* servers have to send a lot of data to the application, which will do the final join without benefit of any indexes — Panagiotis Kanavos, Sep 04 '15 at 14:44
Have you looked into using [Linqer](http://www.sqltolinq.com/) to help you learn SQL to Linq? — superfurryanimals, Sep 04 '15 at 15:35
Yes I tired to download the trial a few minutes ago but the installer errored with a licence error message — WraithNath, Sep 04 '15 at 15:35

score 0 · Accepted Answer · edited Sep 05 '15 at 10:02

Well this seems to give the results I require:

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();

How to convert this SQL query to Linq

1 Answers1