how to query this in linq

Question

Hi

i have this 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]  )

As you can see i want to get all the [LeftHand] objects (include the ones that don't have any [RightHand]), but in case they have a [RightHand] then it's ID must be from this list (59,60,63,64,65)

So how can i get the same result in LINQ ?

LINQ to Entities,
Framework 4

thanks

Answer 1

Could you clarify one point for me though, in your question you state that the ID must be from the list

but in case they have a [RightHand] then it's ID must be from this list (59,60,63,64,65)

but your SQL does the opposite; it excludes the IDs from the list

WHERE [Head].[RightHand_Id] Not IN (59,60,63,64,65)

Do you want to include the IDs from the list or exclude them?

---

Assuming that you do want to exclude them; this should do the trick for you.

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

If you want to include them instead; change this part of the code...

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

to look like this...

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

Answer 2

I solved it

in case any one need the answer

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

thank you DoctaJonez your answer helped me to get to this.