Conditional Join with LINQ

Question

I would like to create a LINQ join statement equivalent of a Left Join

My tables are set up like so:

Recipe
    RecipeID
    ...

Instruction
    RecipeID
    StepID
    SomeFlag
    ...

Equivalent SQL:

SELECT *
FROM Recipe r
LEFT JOIN Instruction i
    ON r.RecipeID = i.RecipeID
    AND SomeFlag > 0

This is what I have so far:

var tmp = db.Recipe
    .GroupJoin(
        db.Instruction,
        r => r.RecipeID,
        i => i.RecipeID,
        (r, i) => new {r, i},
        ???);

Firstly, is GroupJoin the correct choice for this type of operation? From what I understand, Join is equivalent to the SQL 'Inner Join' and GroupJoin is equivalent to 'Left Join'. Second, what is the correct syntax to obtain my desired result? I have been searching for a while and I can't seem to find a suitable answer using extension methods.

look at http://stackoverflow.com/questions/1092562/left-join-in-linq?rq=1 to see how to make left join. To apply SomeFlag > 0 you can use where condition before join — gabba, Jan 21 '13 at 17:42
What are you trying to retrieve? There are many different ways to retrieve the same data. — Bob., Jan 21 '13 at 17:44
@gabba I suppose I could have put this in italics or bold, but I was specifically looking for a solution using `extention methods`. Cedric's answer below had informed me of the ability of filter prior to a join, which I suppose could have been extrapolated from your provided link. — Jeff, Jan 21 '13 at 17:59
@Bob. Is it not clear with the provided SQL? I wish to join `Recipe` and `Instruction` on equivalent `RecipeID` and `SomeFlag > 0` — Jeff, Jan 21 '13 at 18:00

score 6 · Accepted Answer · edited Oct 06 '14 at 15:20

Don't forget to read the help from (GroupJoin: MSDN http://msdn.microsoft.com/en-us/library/bb535047.aspx and Join MSDN http://msdn.microsoft.com/fr-fr/library/bb534675.aspx)

The last argument of the GroupJoin and Join is optional (by overload) and is not usually used. It is a function that allow you to specify how to compare r.RecipeID with i.RecipeID. As RecipeID must be an integer, using the default comparer is a good choice. So let it with:

var tmp = db.Recipe
    .Join(db.Instruction,
          r => r.RecipeID,
          i => i.RecipeID,
          (r, i) => new {r, i});

Now what you want to have is to remove all the instructions that have SomeFlag > 0. Why not do this before joining? Like this:

var tmp = db.Recipe
    .Join(db.Instruction.Where(instruction => instruction.SomeFlag > 0),
          r => r.RecipeID,
          i => i.RecipeID,
          (r, i) => new {r, i});

Update

@usr has perfectly commented saying Join performs an INNER JOIN.

As you may have remarked, LINQ does not have different methods for INNER, OUTER, LEFT, RIGHT joins. To know the equivalent LINQ of a particular SQL join you may find help on MSDN ( http://msdn.microsoft.com/en-us/library/vstudio/bb397676.aspx ).

var tmp = from recipe in Recipes
          join instruction in
              from instruction in Instructions
              where instruction.SomeFlag > 0
              select instruction
          on recipe.RecipeID equals instruction.RecipeID into gj
          from instruction in gj.DefaultIfEmpty()
          select new
          {
              recipe,
              instruction
          };

using extension methods it is a bit of an ugly solution:

var tmp = Recipes.GroupJoin(Instructions.Where(instruction => instruction.SomeFlag > 0),
                            recipe => recipe.RecipeID,
                            instruction => instruction.RecipeID,
                            (recipe, gj) => new { recipe, gj })
                 .SelectMany(@t => @t.gj.DefaultIfEmpty(), 
                             (@t, instruction) => new
                             {
                                 @t.recipe,
                                 instruction
                             });

@CédricBignon That interesting. I didn't know it was possible to filter a right hand table prior to joining. Just for kicks, how would you utilize a value from the recipe table in your conditional join? For instance, if we added `SomeFlag` to the `Recipe` table, how would you write a conditional join on `Recipe.SomeFlag == Instruction.SomeFlag`? — Jeff, Jan 21 '13 at 17:55
Here you have to use the last argument of the GroupJoin and Join methods. To answer to the previous comment, I'll modify my post to take into account the LEFT OUTER JOIN. — Cédric Bignon, Jan 21 '13 at 17:59

gabba · Answer 2 · 2013-01-21T18:08:53.383

1

Please tell me if I did't understand you, but this extension method returns the same result that you priveded in sql.

public static IEnumerable<ResultType> GetLeftJoinWith(this IEnumerable<Recipe>, IEnumerable<Instructions> ins)
{
    var filteredInstructions = ins.Where(x => x.SomeFlag > 0);

    var res = from r in rec
              join tmpIns in filteredInstructions on r.RecipeID equals t.RecipeID into instructions
              from instruction in instructions.DefaultIfEmpty()
              select new { r, instruction };

   return res;
}

edited Jan 21 '13 at 18:08

answered Jan 21 '13 at 18:01

gabba

2,815
2
27
48

1

Clever solution - but I don't believe that is good candidate for its own extension method. I think if this was changed to use a `Func` in the existing method it would be a much cleaner solution. I'm really surprised MS hasn't added a more straight forward way of doing multi-column left joins in linq. – James Jan 21 '13 at 18:13
James, You are right, but I just want to give an idea rather than make clear solution. – gabba Jan 21 '13 at 18:19
I was not looking to create an extension method. I simply meant I was looking for a solution that used extension method syntax rather than LINQ syntax. [This](http://stackoverflow.com/questions/279701/linq-extension-methods-vs-linq-syntax) link will clearly show the difference between the two syntaxes. I do appreciate the extra effort you took to actually _creating_ an extension method. – Jeff Jan 21 '13 at 18:24

score 0 · Answer 3 · answered Oct 07 '19 at 13:17

try this

var model = db.Recipe
            .GroupJoin(db.Instructions.Where(instruction => instruction.SomeFlag > 0),r => r.RecipeID,i => i.RecipeID, (r, i) => new { Recipe = r, Instructions = i })
            .SelectMany(t => t.Instructions.DefaultIfEmpty(),(t, Instructions) => new
            {
                 Recipe = t.Recipe,
                 Instructions = Instructions
            });

Conditional Join with LINQ

3 Answers3

Update