Entity Framework left join multiple tables failure

c# entity-framework-6 left-join

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?

1
1
9/1/2016 7:50:11 AM

Accepted Answer

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.

2
9/2/2016 8:46:08 PM

Popular Answer

@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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow