Entity Framework with Include / Joining Issues

Question

I've implemented an Table Inheritance functionality demonstrated at http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server.

All the foreign keys and constraints are in place.

Now I am using Entity Framework to pull back my People, Students, Teachers, and Parents where the model looks something like the following (without all the EF specific attributes etc).

public partial class People : EntityObject
{
  public guid PeopleID { get; set; }
  public int Age { get; set; }  /Added for an example query
  public PeopleParent Parent { get; set; }
  public PeopleStudent Student { get; set; }
  public PeopleTeacher Teacher { get; set; }
}

Now I need to get all People regardless of type, who are 25 years old, no more than 100 records, and I want to include all the referenced data. I create my EF Query like:

IQueryable<People> query = Entities.People.Include("PeopleParent")
                                           .Include("PeopleStudent")
                                           .Include("PeopleTeacher");

query.Where(x => x.Age == 25)
     .Take(100);

IEnumerable<People> results = query.ToList();

Seems simple enough, but whatever table/entityset I've set to include first creates an INNER JOIN instead of the LEFT OUTER JOIN, which is not producing the right results.

Generated TSQL (incorrect for my needs):

SELECT 
  [Limit1].[C1] AS [C1],
  <A bunch of Limit1 Columns>
FROM (
  SELECT TOP (100) 
    [Extent1].[PeopleID] AS [PeopleID],
    <A bunch of Extent1 Columns>
    [Extent2].[PeopleID] AS [PeopleID1], 
    <A bunch of Extent2 Columns>
    [Extent3].[PeopleID] AS [PeopleID2], 
    <A bunch of Extent3 Columns>
    [Extent4].[PeopleID] AS [PeopleID3], 
    <A bunch of Extent4 Columns>
    1 AS [C1]
  FROM [rets].[People] AS [Extent1]
  INNER JOIN [rets].[PeopleParent] AS [Extent2] 
    ON [Extent1].[PeopleID] = [Extent2].[PeopleID]
  LEFT OUTER JOIN [rets].[PeopleStudent] AS [Extent3] 
    ON [Extent1].[PeopleID] = [Extent3].[PeopleID]
  LEFT OUTER JOIN [rets].[PeopleTeacher] AS [Extent4] 
    ON [Extent1].[PeopleID] = [Extent4].[PeopleID]
) AS [Limit1]

Why is the first Include used as an INNER JOIN, and is there a soluion to my problem?

** UPDATE 1**

Assuming I use Ladislav Mrnka's Answer, there are two additional requirements due to the significant change in Linq and Lambda querying.

Question: How do I search for specific People which have specific properties?

(All Students with a Grade of "A")

Answer:

context.People.OfType<Student>().Where(s => s.Grade == "A");

Question: How do I search for any People that have a specific property?

(All Students or Teachers who's PrimaryFocus = "Math")

Answer:

List<People> result = new List<People>();
result.AddRange(context.People.OfType<Student>()
                              .Where(x => x.PrimaryFocus == "Math")
                              .ToList());
result.AddRange(context.People.OfType<Teacher>()
                              .Where(x => x.PrimaryFocus == "Math")
                              .ToList());

score 6 · Accepted Answer · edited May 23 '17 at 12:32

6

The obvious solution for you should be using native EF support for inheritance. In your case TPT inheritance. Once you have inheritance you will simply call:

IEnumerable<People> results = Entities.People
                                      .Where(x => x.Age == 25)
                                      .Take(100)
                                      .ToList();

And it will return you instances of Student, Teachers, Parents etc.

In your solution the only advice is check that the relation is optional (1 - 0..1) - . If it is required it will use INNER JOIN. If it is optional and it still uses INNER JOIN it can be some bug or other problem in your model.

edited May 23 '17 at 12:32

Community

1
1

answered Dec 14 '11 at 10:13

Ladislav Mrnka

360,892
59
660
670

I haven't been using the inheritence support in EF. If I choose to go down this "rabbit hole" to use inheritence, how does it affect searching if I look for `.Where(x => x.Grade == "A")`, when Parents and Teachers don't have a `Grade` property (only students)? – Erik Philips Dec 14 '11 at 19:06
You cannot search for `People` but for `Students`: `context.People.OfType().Where(s => s.Grade == "A")`. Inheritance is tricky and it has significant performance costs (this will improve in .NET 4.5) but it's best solution for your database. – Ladislav Mrnka Dec 14 '11 at 19:09
The second question probably doesn't have easy answer. You will have to query students and teachers separately unless the shared property is in `Person` class. You can try some tricks with `Union` of two queries but there will be a problem with casting because union of `Student` and `Teacher` query will not work (they are of different type). EF is about thinking in object oriented queries so not every relational query can be directly translated. – Ladislav Mrnka Dec 14 '11 at 19:30
Since I am returning `IENumerable` I should be able to do two seperate querys and combine the two query results into the single result. – Erik Philips Dec 14 '11 at 19:32
It will work in Linq-to-objects where implicit cast to People works but probably not in Linq-to-entities where unioned sets must have same columns. So you must execute those queries separately and union them to single `IEnumerable` once returned from the database. – Ladislav Mrnka Dec 14 '11 at 19:36

Entity Framework with Include / Joining Issues

1 Answers1