10

I have a specific query in my code which needs to eager load all related entities (both ->1 FKs and ->N FKs) because the context will be disposed right after that.

I made a generic "Query" method that takes params Expression<Func<MyItem, object>>[] includes and then internally chains them. That part works fine.

The query looks like this:

var item = facade.Query<MyItem>(
                c => c.Childs.Select(x => x.Parent),
                c => c.Childs.Select(x => x.SubChild1),
                c => c.Childs.Select(x => x.SubChildNotWorking),
                c => c.Childs.Select(x => x.SubChild2),
                c => c.Childs.Select(x => x.SubChild3),
                c => c.Childs.Select(x => x.SubChildrens)
                ).FirstOrDefault(c => c.Name == name);

The mapping for the not working property (placed in the configuration of SubChildNotWorking):

this.HasMany(scnw => scnw.Childs).WithOptional(c => c.SubChildNotWorking).HasForeignKey(c => c.MyForeignKey);

Of all the includes, only the SubChildNotWorking doesn't actually work. When inspecting with the debugger the returned object, I see the proxies on all the properties. Opening the proxies gives me the correct data for all other relations, and a "The objectcontext has already been disposed exception" for the SubChildNotWorking property.

The only difference I was able to spot is that the SubChildNotWorking is a nullable FK (with nullable column on the DB and WithOptional configuration in the dbcontext) while all the others are non nullable FKs configured with WithRequired.

The database also is a legacy DB not created with Code First and not following its conventions, I just made the mappings in the DbContext. Everything else works fine.

I am trying to figure out if eager loading doesn't work on nullable FKs but I couldn't find any documentation about that.

Is that a bug, or intended behavior? But most of all, how do I solve this?

Thanks.

Matteo Mosca
  • 7,380
  • 4
  • 44
  • 80
  • What happens if you include only the `SubChildNotWorking` without the other subchildren? Does it work then? – Slauma Apr 11 '12 at 11:38
  • 1
    Can you check if the SQL looks correct (with `var sql = facade.Query(c => c.Childs.Select(x => x.SubChildNotWorking)).ToString();`) and test the query manually in SSMS if it returns the expected result rows. – Slauma Apr 11 '12 at 12:05
  • Could you show mapping for SubChildNotWorking and its navigation property? – Kirill Bestemyanov Dec 08 '12 at 17:26
  • It's already stated, it uses WithOptional and a nullable FK instead of WithRequired with a non nullable FK, anyway I'll add the mapping code. – Matteo Mosca Dec 09 '12 at 12:18
  • In my scenario, analogous optional foreignkey works properly. It seems, you have in for foreign key in db. – Kirill Bestemyanov Dec 12 '12 at 05:24
  • Hey you tried executing this using a standard dbcontext statement. i.e. not using your Query method? Do you get the same result? – Ben Tidman Dec 12 '12 at 13:23
  • Also I think there is already an extension method that does the same thing that you implemented. http://msdn.microsoft.com/en-us/library/gg671236(v=vs.103).aspx. Just include System.Data.Entity. I've not used to to load sub collections, but from the link it looks like you can. – Ben Tidman Dec 12 '12 at 14:05
  • Have you tried to profile the generated SQL? – Lionel D Dec 13 '12 at 13:13
  • Another thing, disable lazy loading just in case. But as said above, try it with TSQL direct from SSMS and see what happens! – trinaldi Oct 04 '13 at 21:30
  • Either your model is not representing your tables record correct somewhere, or the generated query being run on SQL server independently will yield the same results. Can't see any 3rd option there. – G.Y Oct 13 '13 at 13:47
  • Is this property virtual like in `public virtual SubChildNotWorking SubChildNotWorking { get; set; }`? And does it have a parameterless constructor? – Marcos Dimitrio Nov 15 '17 at 15:53

2 Answers2

0

Did you define 

[DataContract]

on your class (entities)? If so, don't forget to annotate your SubChild with

[DataMember]

or it might end up loaded but not showing up when you perform your GET call on entities.

LulalaBoss
  • 127
  • 1
  • 9
0

When you are working with the include method, you are talking about the eager load pattern. Entities with relationship will be loaded as collections. For any not-nullable foreign key, there's a trivial collection starting empty, but for nullable ones, there is not. On the Code First you could make the navigation property virtual, but it's not your case. You can try load it directly on Context, for example:

var someEntity = context.someEntities.Find(1);
context.Entry(someEntity ).Reference(e => e.EntityWithFKNullable).Load();

or the shorter version:

context.EntitiesWithFKNullable.Load();
Daniel Cruz
  • 54
  • 1
  • 5