Entity Framework: LINQ Include () does not work after updating the database, why?

Question

I am new to Entity Framework and LINQ and have come across a rather strange scenario.

I used the following query to return account information:

var account = ((from acct in _entities.Account join m in _entities.Item on acct.Id equals m.Account.Id where acct.Id == accountId && m.ItemNumber.EndsWith(itemNumber) select acct) as ObjectQuery<Account>).Include("Item.ItemDetails"); 

We recently made some changes to the database and created a new edmx file. After the change, the above request still returns the account and the item associated with it, but ItemDetails is no longer included.

I checked the SQL returned by the query and it seems that something is wrong as the correct data.

In addition, I do not see anything else in the edmx file between the Item and ItemDetails objects, since they have not been changed and the navigation property exists.

Has anyone seen this before?

thanks

Answer 1

Include (...) uses the name of the navigation property, so it will be useful to check the exact name of the property from .edmx (especially if it is singular or multiple).

You can also try changing the query as follows:

 var account = from acct in _entities.Account.Include("Item.ItemDetails") join m in _entities.Item on acct.Id equals m.Account.Id where acct.Id == accountId && m.ItemNumber.EndsWith(itemNumber) select acct; 

Answer 2

You have one of two possible scenarios:

• Item is related to Account (expressed in your Entity model as EntityAssociation and in DB as a foreign key):

• There is no connection between Item set and Account , so you must specify the connection in LINQ, as you did.

Case 1: if this is the case, then you do not need a union statement ... by selecting Acount.Item , naturally, it will give you all the elements where Item.AccountID is equal to Account.ID

So your join m in _entities.Item on acct.Id equals m.Account.Id statement: join m in _entities.Item on acct.Id equals m.Account.Id basically told Item to return to Account to check the identifier. If they are not connected yet, you could not get m.Account.ID

Case 2: if there is no relationship between Account and Item , then .Include() will certainly not work, because the navigation property DOES NOT exist in your model.

Conclusion Check your new model to see if there is a relationship between Account and Item . If so, uninstall Join. If there is no relationship, then you did something wrong.

Here is the select statement assuming script 1, and that Account.Item not a collection:

 var account = from acct in _entities.Account.Include("Item.ItemDetails") where acct.Id == accountId && acct.Item.ItemNumber.EndsWith(itemNumber) select acct;