Entity Framework and LINQ Include()

Question

Hi

I am new to Entity Framework and LINQ and have run into a rather odd scenario.

I have been using 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 generated a new edmx file. Following the change the above query still returns the account and associated Item but the ItemDetails is no longer being included.

I have validated the SQL returned by the query and there doesn't seem to be anything wrong as the correct data is being returned.

Furthermore I don't see anthing different in the edmx file between the Item and ItemDetails objects as these were not changed and the navigation property is there.

Has anyone seen this before?

Thanks

Answer 1

In Include(...) is used the name of the navigation property so it will be good to check the exact name of the property from the .edmx (especially if it is singular or plural).

Also you can try to change the query like this:

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:

1. Item has a relationship to Account (expressed in your Entity Model as a EntityAssociation and in DB as a foreign key):

2. There is no relationship between Item set and Account set, hence, you must specify a join in LINQ as you have done.

Case 1: if this is the case, then you don't need a join statement... by selecting Acount.Item will naturally give you all items where Item.AccountID is equal to Account.ID

So your join statement: join m in _entities.Item on acct.Id equals m.Account.Id has basically told Item to loop back onto Account to check the ID. If they were not already connected, then you could not have gotten m.Account.ID

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

Conclusion: Check your new model to see if a relationship exists between Account and Item. If yes, then remove the Join. If no relationship, then you've done something wrong.

Here is a select statement assuming scenario 1 and that Account.Item is not a collection:

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