Entity Framework left join multiple tables failure

Question

I'm using Entity Framework 6 and I have few entities and a query like the following:

var results = (from e1 in dataContext.Entity1
                   .Where(x => x.Key1 == 1)
               from e2 in dataContext.Entity2
                   .Where(x => x.Key2 == e1.Key1)
                   .DefaultIfEmpty()
               from e3 in dataContext.Entity3
                   .Where(x => x.Key3 == e1.Key1 || x.Key3 == e2.Key2)
                   .DefaultIfEmpty()
               select new 
               {
                   E1 = e1,
                   E2 = e2,
                   E3 = e3
               }).ToList();

Since the joins to Entity2 and Entity3 are left joins, e2 or e3 may be null. I found out if e2 is null, exception System.Reflection.TargetException is thrown with message "Non-static method requires a target". And if I change the join to Entity3 as the following, I still got the same error.

from e3 in dataContext.Entity3
               .Where(x => x.Key3 == e1.Key1 
                      || (e2 != null && x.Key3 == e2.Key2))
               .DefaultIfEmpty()

How can I change the query?

That's not really a join. Either use `GroupJoin` in method syntax or `from f in Foo join b in Bar on f.Foo_Id equals b.Foo_Id into g from result in g.DefaultIfEmpty() select new { Foo = f, Bar = result }`. Btw, using old school joins is even a [bad habit to kick](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) in pure sql. — Tim Schmelter, Sep 01 '16 at 07:52
@TimSchmelter is correct, that is not a join. Try looking at [Join C# reference](https://msdn.microsoft.com/en-us/library/bb311040.aspx) there is an example there. — Dave Williams, Sep 01 '16 at 07:54
Actually that's a clearer way of doing left joins, which I prefer. That code produces a left join in the SQL query, so it's perfectly valid. Check out this answer: http://stackoverflow.com/a/23558389/2321042 Steve, at which point does this error exactly occur? You can try calling `ToList` in a separate line to help you debug this. That error seems to appear when using outside variables inside the lambda expressions, but this doesn't seem to be the case. Is this your exact LINQ? — Andrew, Sep 01 '16 at 08:31

score 2 · Accepted Answer · answered Sep 02 '16 at 20:46

Thank you all the suggestions. Finally I figured out the problem.

To simplify the question, I didn't tell the Entity1 is actually the return from a stored procedure. I thought it is the same as a table since the stored procedure returns the whole records from the table. It turns out somebody changed the Entity Framework's wrapper of the stored procedure to return the Entity1 list. After I changed it back to the originally generated code from EF, it works fine.

score 0 · Answer 2 · answered Sep 01 '16 at 09:57

@Steve Li

For you, to anwer your question: As in the second comment mentioned, read the MSDN reference for C# Linq about the join-clause. You should try to avoid the Left Outer Join - using the DefaultIfEmpty Function.

To make your code working, try this:

var result =    from e1 in entities1
                join e2 in entities2 on e1.Key1 equals e2.Key2
                join e3 in entities3 on e1.Key1 equals e3.Key3
                where e1.Key1 == 1
                select new
                {
                    E1 = e1,
                    E2 = e2,
                    E3 = e3
                };

this will give you a joined table over your three entities. The code here (dotnetfiddle.net/dQuXbP) will give you a taste of how the result will look like.

Entity Framework left join multiple tables failure

2 Answers2

Linked