EF loading related entities with SQL query

Question

I have an sql statement that I'd like to return a list of Persons that have a related Department entity. The Person class has a Department ID and with the magic of entity framework it associates the correct Department object when I load it from the database.

My SQL statement is:

SELECT *
FROM PERSON
    INNER JOIN DEPARTMENT ON PERSON.DEPARTMENT_ID = DEPARTMENT.ID
WHERE UPPER(FORENAME) LIKE '%RALPH%'
ORDER BY SURNAME

In MSSMS when I run the query I get the following results:

However, when I run the sql using DbSet.SqlQuery() I get the following results:

Note how the department object is null in the first item, yet has been populated in the second item.

I know I haven't provided much information, I'm hoping there's just a simple answer for this.

By the way..

DataContext.Configuration.LazyLoadingEnabled = false;

LazyLoadingEnabled is set to false as you can see, I know changing this to true would fix the issue. I'd just rather not go enabling it for whichever call I fancy needs it.

Thanks guys

I have a list of People that I perform a filter on.. using the criteria provided by the user I generate an sql statement. There are many potential variations of the search so an sql statement seemed like the way to go. — Murphybro2
– Murphybro2, Commented Sep 26, 2017 at 12:43
It's almost certainly not the way to go, otherwise there's no point in using EF. — DavidG
– DavidG, Commented Sep 26, 2017 at 12:44
Okay, well I appreciate your comment and I shall see about changing that! Do you care to provide a reason of why the following is happening anyway? — Murphybro2
– Murphybro2, Commented Sep 26, 2017 at 12:46
Had you previously loaded the Department into that DbContext instance? If so EF will recognize that it already has the Department with DepartmentId=2, and will set the navigation property to that instance. — David Browne - Microsoft
– David Browne - Microsoft, Commented Sep 26, 2017 at 12:59

David Browne - Microsoft · Accepted Answer · 2017-09-26 13:15:46Z

1

If you have previously loaded the Department into that DbContext instance, EF Change Tracking will recognize that it already has the Department with DepartmentId=2, and will set the navigation property to that instance.

answered Sep 26, 2017 at 13:15

David Browne - Microsoft

90.8k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

EF loading related entities with SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related