Conditional connection with LINQ

I would like to create a LINQ join operator equivalent to Left Join

My tables are set up like this:

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

      

First, the right choice for this type of surgery GroupJoin

? From what I understand, Join is equivalent to SQL 'Inner Join' and GroupJoin is equivalent to "Left Join". Second, what is the correct syntax to get my desired output? I've searched for a while and I can't seem to find a suitable answer using extension methods .

+4


source to share


3 answers


Be sure to read the help ( 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 GroupJoin

and Join

is optional (by overloading) and not normally used. It is a function that allows you to specify how to compare r.RecipeID

with i.RecipeID

. Since it RecipeID

must be an integer, using the default comparison is a good choice. So let it be with:

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

      

Now you want to remove all instructions from SomeFlag > 0

. Why not do this before you join? 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 comments excellently saying that it Join

does an INNER JOIN.

As you may have noticed, LINQ does not have different methods for INNER, OUTER, LEFT, RIGHT connections. To find out the LINQ equivalent of a specific SQL connection, you can find the MSDN help ( 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 this is a slightly 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
                             });

      

+6


source


Please tell me if I didn't understand you, but this extension method returns the same result as you got 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;
}

      

+1


source


try it

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

      

0


source







All Articles