3

Consider the following entity framework model with TPT inheritance.

DB Tables:

Person (PersonID, Name)
Student (PersonID, Grade)

EF Entities:

Person (PersonID, Name)
Student (Grade) : inherits from Person

Now when you're trying to select a person entry from the database it will return Student type instead.

var person = db.Persons.First();
// person here is of type Student and has Grade peoperty populated
// SQL query generated by EF selects data from both tables with a JOIN

How to force this query select only the data from Person db table and not from both Person and Student db tables?

For example, it can be done with the following query: 

db.Persons.Select(x => new Person { PersonID = x.PersonID, Name = x.Name }).First()

but it looks lame, generates an extra SELECT statement over the existing query and this way returned Person entity object will not be tracked by EF context. So, I'm wondering why db.Persons.First() returns a Student object? Isn't it counterintuitive?

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
Grief Coder
  • 6,508
  • 9
  • 38
  • 51

2 Answers2

3

If you use a list of Person and stores a single Student to the list what will you receive when you call First? You will receive a Student instance because that is how object oriented code is supposed to work Student is a Person but you will never get just Person instance without creating a new instance and copying data from original Student instance.

EF works in the same way - entity is atomic. It doesn't matter how many tables it spans. If you query inheritance hierarchy you will always get the whole instance of the correct type because that is how object oriented code is supposed to work.

Your second example should not work at all if used in Linq-to-entities because you are creating instance of the entity inside the query - that is not allowed. Projections must not be done to mapped entities because it could break data consistency.

The way to go is using projection either to non entity type - custom not mapped class or anonymous type.

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • Assume that i have a `Document` base class and some my other classes inherited it, with some complex relations, what about if i want only get base class fields(for showing base properties of `Document`,i.e `DocumentOwner`, `DocumentCreationDate`,...)? `EF` generate a really complex sql, that decrease performance. – Masoud Jul 11 '13 at 08:27
  • 1
    @Masoud: You must use projection. – Ladislav Mrnka Jul 11 '13 at 12:03
1

try with this code

db.Persons.TypeOf<Person>().First();
Aghilas Yakoub
  • 28,516
  • 5
  • 46
  • 51
  • Nope, this way it just adds one more SELECT wrapper around two existing SELECTs and still grabs data from both db tables. `person` variable is of type `Student`. – Grief Coder Aug 29 '12 at 22:23
  • Grief i added with new syntax – Aghilas Yakoub Aug 29 '12 at 22:24
  • This syntax also doesn't fix the issue. `db.Persons.First()` and `db.Persons.OfType().First()` produce the same query and returning value. – Grief Coder Aug 29 '12 at 22:26
  • Grief you question i'am sorry but your question is : How to force this query select only the data from Person db table and not from both Person and Student db tables? – Aghilas Yakoub Aug 29 '12 at 22:29
  • i don't inderstand question so, please – Aghilas Yakoub Aug 29 '12 at 22:29
  • Candie, for example, it can be done with the following query: db.Persons.Select(x => new Person { PersonID = x.PersonID, Name = x.Name }).First() but it looks lame, generates an extra SELECT statement over the existing query and this way returned Person entity object will not be tracked by EF context. So, I'm wondering why db.Persons.First() returns a Student object? Isn't it counterintuitive? – Grief Coder Aug 29 '12 at 22:49