如何在linq中查询

Question

我有这个SQL

SELECT *
FROM  [dbo].[LeftHand]
        left outer JOIN [dbo].[Head]  ON [LeftHand].[ID] = [Head].[LeftHand_Id]
        WHERE [Head].[RightHand_Id] Not IN (59,60,63,64,65) or [Head].[RightHand_Id] is null 

        [Head]
        */  \*
        /    \
      1/      \1
[LeftHand]  [RightHand]       ([LeftHand may have more than one [RightHand]  )

正如您所看到的，我想获取所有的LeftHand对象(包括那些没有任何RightHand的对象)，但是如果它们有一个RightHand，那么它的ID必须来自这个列表(59,60,63,64,65)

，那么我如何在LINQ ?中获得相同的结果？

对实体来说，

框架4

谢谢

Answer 1

你能为我澄清一点吗?在你的问题中，你说身份证必须是从名单上

但是如果他们有一个RightHand，那么它的ID必须来自这个列表(59,60,63,64,65)

但是SQL却恰恰相反；它将IDs排除在列表之外。

，Head.RightHand_Id非IN (59,60,63,64,65)

您希望将ID包括在列表中还是将它们排除在外？

假设您确实希望将它们排除在外；这应该对您有好处。

using (var context = new ContextName())
{
    //here are the ids we want to filter by
    var ids = new List<int> { 59,60,63,64,65 };
    //get all left hands without a matching right hand
    var result = context.LeftHands.Where(l => l.Head.RightHand_Id == null
                                           //or right hands that aren't in the list
                                           || !ids.Contains(l.Head.RightHand_Id));
}

如果您想要包含它们，请更改代码的这一部分.

//or right hands that aren't in the list
|| !ids.Contains(l.Head.RightHand_Id));

像这样..。

//or right hands that are in the list
|| ids.Contains(l.Head.RightHand_Id));

Answer 2

我解决了

万一有人需要答案

using (var context = new ContextName())
{
    var ids = new List<int> { 59,60,63,64,65 };
    var result =
        from l in context.LeftHand
        join Head in contaxt.Head on l equals Head.LeftHand into ljh
        from j1 in ljh.DefaultIfEmpty()
        where !ids.Contains(j1.RightHand.ID) || j1 == null
        select l;
}

谢谢，DoctaJonez，你的回答帮助我做到了这一点。