LINQ multiple left outer join with a "or" clause

So basically, I try here to transfer this query in LINQ.

DECLARE @p1 UniqueIdentifier SET @p1 = 'AC1D85C1-28F1-46A3-9C6A-3B7446609A2A'
DECLARE @p2 UniqueIdentifier SET @p2 = NEWID()
SELECT
    [MTD].[Description],
    [MTD].[MessageTypeID],
    ISNULL([AMT].[ApplicationMessageTypeID], NEWID()),
    ISNULL([AMT].[EventForwardingRuleID], '1001')
FROM [dbo].[MessageType] as [MT]
INNER JOIN [dbo].[MessageTypeDescription] AS [MTD] 
        ON [MT].[MessageTypeID] = [MTD].[MessageTypeID]
LEFT OUTER JOIN [dbo].[ApplicationMessageType] AS [AMT] 
        ON [AMT].[MessageTypeID] = [MT].[MessageTypeID]
        AND ( [AMT].[ApplicationID] = @p1 OR [AMT].[ApplicationID] IS NULL )
WHERE [MTD].[Culture] = 'fr'

I know for the most part that the query should look like something like that:

(from mt in db.MessageTypes
join mtd in db.MessageTypeDescriptions
    on mt.MessageTypeID equals mtd.MessageTypeID
join amt in db.ApplicationMessageTypes
    on new { mt.MessageTypeID, (applicationId || null) } equals new { amt.MessageTypeID, amt.ApplicationID }
    into appMessageTypes
from amt in appMessageTypes.DefaultIfEmpty()
where mtd.Culture == culture
s开发者_如何学Pythonelect new ApplicationEditEventTypeModel
{
    ApplicationMessageTypeID = amt.ApplicationMessageTypeID == null ? Guid.NewGuid() : amt.ApplicationMessageTypeID,
    Description = mtd.Description,
    MessageTypeID = mtd.MessageTypeID,
    EventForwardingRuleID = amt.EventForwardingRuleID == null ? 0 : amt.EventForwardingRuleID
});

The part here where I'm really not sure is the "ApplicationMessageTypes" part. For a multiple left join query I'd use the new {} equals new {} construct but in this case, I have 2 clauses ( [AMT].[ApplicationID] = @p1 OR [AMT].[ApplicationID] IS NULL ).

Should I use something like new { mt.MessageTypeID, new { applicationId ,null }} equals new { amt.MessageTypeID, amt.ApplicationID }? This seems too strange to be real.

---

(from mt in db.MessageTypes
join mtd in db.MessageTypeDescriptions
    on mt.MessageTypeID equals mtd.MessageTypeID
from amt in db.ApplicationMessageTypes
    .Where(a => a.MessageTypeID == mt.MessageTypeID &&
          (a.ApplicationID == applicationId || !a.ApplicationID.HasValue)).DefaultIfEmpty()
where mtd.Culture == culture
select new ApplicationEditEventTypeModel
{
    ApplicationMessageTypeID = amt.ApplicationMessageTypeID ?? Guid.NewGuid(),
    Description = mtd.Description,
    MessageTypeID = mtd.MessageTypeID,
    EventForwardingRuleID = amt.EventForwardingRuleID ?? 0
});

---

I think the ApplicationId clause doesn't really look like its part of the JOIN - i.e. it's not really part of the foreign key relationship - instead it's really just a normal WHERE condition.

So I'd recommend moving the ApplicationId out to WHERE in both the SQL and in the LINQ

DECLARE @p1 UniqueIdentifier SET @p1 = 'AC1D85C1-28F1-46A3-9C6A-3B7446609A2A'
DECLARE @p2 UniqueIdentifier SET @p2 = NEWID()
SELECT
    [MTD].[Description],
    [MTD].[MessageTypeID],
    ISNULL([AMT].[ApplicationMessageTypeID], NEWID()),
    ISNULL([AMT].[EventForwardingRuleID], '1001')
FROM [dbo].[MessageType] as [MT]
INNER JOIN [dbo].[MessageTypeDescription] AS [MTD] 
        ON [MT].[MessageTypeID] = [MTD].[MessageTypeID]
LEFT OUTER JOIN [dbo].[ApplicationMessageType] AS [AMT] 
        ON [AMT].[MessageTypeID] = [MT].[MessageTypeID]
WHERE [MTD].[Culture] = 'fr'
        AND (AMT IS NULL OR ([AMT].[ApplicationID] = @p1 OR [AMT].[ApplicationID] IS NULL ))

and

(from mt in db.MessageTypes
join mtd in db.MessageTypeDescriptions
      on mt.MessageTypeID equals mtd.MessageTypeID
join amt in db.ApplicationMessageTypes
      on new mt.MessageTypeID equals amt.MessageTypeID
    into appMessageTypes
from amt in appMessageTypes.DefaultIfEmpty()
where mtd.Culture == culture
      && amt==null || (amt.ApplicationID == null || amt.ApplicationID == applicationId)
select new ApplicationEditEventTypeModel
{
 ApplicationMessageTypeID = amt.ApplicationMessageTypeID == null ? Guid.NewGuid() : amt.ApplicationMessageTypeID,
 Description = mtd.Description,
 MessageTypeID = mtd.MessageTypeID,
 EventForwardingRuleID = amt.EventForwardingRuleID == null ? 0 : amt.EventForwardingRuleID
});

You could probably also use a nested select (or a view) if you wanted to keep the ApplicationId clause closer to the original ApplicationMessageType table