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 .
source to share
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
});
source to share
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;
}
source to share
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
});
source to share