Navigation Properties Missing After Left Join

Question

I'm trying to do a left join on two entities that have navigation properties. I have disabled lazy loading.

Here is my code:

var awis =
            from ai in Context.AdItems
                            .Include(ai => ai.Item)
                            .Include(ai => ai.Item.Buyer)
                            .Include(ai => ai.Item.OrderHeader)
                            .Where(ai => ai.AdYear == adYear && ai.AdNumber == adNumber)
            join si in Context.StoreItems
                            .Include(si => si.Store)
                            .Where(si => si.StoreId == storeId) on ai.ItemId equals si.ItemId into x
            from r in x.DefaultIfEmpty()
            select new AdWeekItem
            {
                AdItemId = ai.AdItemId,
                AdItem = ai,
                StoreItemId = r == null ? 0 : r.StoreItemId,
                StoreItem = r
            };

The outer join works fine but I do not have my navigation properties included in the projected AdWeekItem.

That is, AdWeekItem.AdItem.Item is null, etc.

How do I include these navigation properties and do a left outer join?

I have explicitly disabled lazy loading. I do not want to use lazy loading. — jlavallet, Dec 21 '17 at 16:11
Have you define appropriate foreign key in context creation? — programtreasures, Dec 21 '17 at 16:12
`public int ItemId { get; set; } [ForeignKey(nameof(ItemId))] public Item Item { get; set; }` — jlavallet, Dec 21 '17 at 16:16
you should apply foreign key to foreign key property not navigation property like `[ForeignKey("Item")] public int ItemId { get; set; } public Item Item { get; set; }` — programtreasures, Dec 21 '17 at 16:19
Here's what I have: `///
/// A foreign key to the item. ///
[ForeignKey(nameof(Item))] public int ItemId { get; set; } ///
/// A navigation property to the item. ///
[ForeignKey(nameof(ItemId))] public Item Item { get; set; }` I have both. This works for every other relationship In all my other queries, when I use the Include method. — jlavallet, Dec 21 '17 at 16:22
OK, in EF <= 6, Includes are always completely ignored in projections (`select new ...`). — Gert Arnold, Dec 21 '17 at 16:26
Is there a way to efficiently join in my navigation properties _after_ the left join? — jlavallet, Dec 21 '17 at 16:29

score 3 · Accepted Answer · answered Dec 21 '17 at 16:43

In EF6, Includes are always ignored when a LINQ query ends in a projection (select new ...). Your only option is to explicitly query the navigation properties you want to have included and then project to the end result:

var temp = from ai in Context.AdItems
                             .Where(ai => ai.AdYear == adYear && ai.AdNumber == adNumber)
            join si in Context.StoreItems
                              .Where(si => si.StoreId == storeId) on ai.ItemId equals si.ItemId into x
            from r in x.DefaultIfEmpty()
            select new 
            {
                AdItemId = ai.AdItemId,
                AdItem = ai,
                ai.Item,
                ai.Item.Buyer,
                ai.Item.OrderHeader,
                StoreItemId = r == null ? 0 : r.StoreItemId,
                StoreItem = r,
                r.Store
            };

var awis = from x in temp.AsEnumerable() // pull into memory and continue           
            select new AdWeekItem
            {
                AdItemId = x.AdItemId,
                AdItem = x.AdItem,
                StoreItemId = x.StoreItemId,
                StoreItem = x.StoreItem
            };

EF will have populated AdItem.Item etc. by relationship fixup (i.e. auto-population of navigation properties).

Navigation Properties Missing After Left Join

1 Answers1