English 中文(简体)
LINQ 多个东西方加入“或”条款
原标题: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 

我基本上知道,问询应该像以下一点:

(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
select new ApplicationEditEventTypeModel
{
    ApplicationMessageTypeID = amt.ApplicationMessageTypeID == null ? Guid.NewGuid() : amt.ApplicationMessageTypeID,
    Description = mtd.Description,
    MessageTypeID = mtd.MessageTypeID,
    EventForwardingRuleID = amt.EventForwardingRuleID == null ? 0 : amt.EventForwardingRuleID
});

我确实不相信的那部分是“ApplicationMessageTypes”。 多个左翼加入询问一,即使用<代码>new{}等于新的{>>,但在此情形下,我有两个条款<代码>[AMT].[应用ID] = @p1 OR [AMT]。

如果我使用<代码>新版{mt”。 InformationTypeID, new{app 页: 1 InformationTypeID, amt.ApplicationID }? 这似乎过于奇怪。

最佳回答
(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
});
问题回答

我认为,“应用Id”条款实际上并不像日本国际交易日志的一部分,即它并不真正属于外国关键关系的一部分,而是真正只是一个正常的WHERE条件。

因此,我建议把申请转至LQ和LINQ中的WHERE。

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

以及

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

如果你想保留申请,你可能还会使用一个nes选(或观点)。 更接近原申请表的Id条款





相关问题
Anyone feel like passing it forward?

I m the only developer in my company, and am getting along well as an autodidact, but I know I m missing out on the education one gets from working with and having code reviewed by more senior devs. ...

NSArray s, Primitive types and Boxing Oh My!

I m pretty new to the Objective-C world and I have a long history with .net/C# so naturally I m inclined to use my C# wits. Now here s the question: I feel really inclined to create some type of ...

C# Marshal / Pinvoke CBitmap?

I cannot figure out how to marshal a C++ CBitmap to a C# Bitmap or Image class. My import looks like this: [DllImport(@"test.dll", CharSet = CharSet.Unicode)] public static extern IntPtr ...

How to Use Ghostscript DLL to convert PDF to PDF/A

How to user GhostScript DLL to convert PDF to PDF/A. I know I kind of have to call the exported function of gsdll32.dll whose name is gsapi_init_with_args, but how do i pass the right arguments? BTW, ...

Linqy no matchy

Maybe it s something I m doing wrong. I m just learning Linq because I m bored. And so far so good. I made a little program and it basically just outputs all matches (foreach) into a label control. ...

热门标签