EF 6 filtering child collections

Question

I'm trying to migrate old project from Linq2Sql to EF6 and I got following issue.

This project is multilingual (i.e. all texts have more than 1 translation) and I have following db structure:

What is the best way to get all ExampleEntity1 objects with all LocalizedContent records filtered by current language id?

I can load all ExampleEntity1 objects with all LocalizedContent records using following code: dc.ExampleEntity1.Include(ee => ee.TextEntry.LocalizedContents);

In Linq2Sql I can filter LocalizedContent records using loadOptions.AssociateWithbut I can't find any solution for EF6.

I saw similar old questions (posted like 2-3 years ago) and I'm just wondering if there is a solution for EF6. It is a very critical feature for me because I have dozens of entities in the project and I don't want to create custom objects for each select query.

I also found EntityFramework.DynamicFilters nuget package which can help with my issue, but I would prefer to use "native" EF6 functionality if possible..

One option that comes to mind but may not work for your situation: a custom database function that takes the current language id and TextEntryId and returns the correct LocalizedContents. Just throwing this out to hopefully ignite some ideas. — Michael Richardson, Dec 04 '15 at 01:27
just out of interest... why don't you move fakefield on textentry to localizedContent and drop table textentry, wouldnt this simplify things without loosing anything. as then you would not have to go through another linking table.... which i can not see what it adds other than fakefield... which could be move to localizedContent... — Seabizkit, Dec 04 '15 at 08:33
If you want to use native EF methods, you'll end up intercepting command trees by EF6's new interception API. If you do that, in the end you'll have reinvented EntityFramework.DynamicFilters. I'd go for the Nuget package. — Gert Arnold, Dec 05 '15 at 20:49

jnovo · Answer 1 · 2015-12-04T09:05:56.500

If you want to perform the filtering in the query to the database then (as of EF6) you have to use the Query method:

The Query method provides access to the underlying query that the Entity Framework will use when loading related entities. You can then use LINQ to apply filters to the query before executing it with a call to a LINQ extension method such as ToList, Load, etc.

using (var context = new BloggingContext()) 
{ 
  var blog = context.Blogs.Find(1); 

  // Load the posts with the 'entity-framework' tag related to a given blog 
  context.Entry(blog) 
    .Collection(b => b.Posts) 
    .Query() 
    .Where(p => p.Tags.Contains("entity-framework") 
    .Load(); 

   // Load the posts with the 'entity-framework' tag related to a given blog  
   // using a string to specify the relationship  
   context.Entry(blog) 
     .Collection("Posts") 
     .Query() 
     .Where(p => p.Tags.Contains("entity-framework") 
     .Load(); 
}

However, the obvious drawback is that you have to do this per entry and each Load call executes a query against the database.

Unless it's a hard requisite for you I would opt for just loading all the localizations and simply filter in memory to use the selected language one. I'm pretty sure the performance won't be an issue.

value in this... up 1 from me. – Seabizkit Dec 04 '15 at 09:04 — Seabizkit, Dec 04 '15 at 09:04

score 2 · Answer 2 · edited May 23 '17 at 12:13

Note that it is not currently possible to filter which related entities are loaded. Include will always bring in all related entities.

Msdn Reference

var result = dc.ExampleEntity1.Include(ee =>ee.TextEntry.LocalizedContents)
               .Select(x=>new
               {
                  //Try anonymous or a projection to your model.
                  //As this Select is IQuerable Extension it will execute in the data store and only retrieve filtered data.
                  exampleEntity = x,
                  localizedContetnt = x.TextEntry.LocalizedContents.Where(g=>g.Id==YourKey),
               }).FirstOrDefault();

You could try anonymous projection to filter contents in the Included entities

Entity framework team is working on this you could cast your vote here

Similar Answer

I thought in EF that all includes had to be specified.. ie 'dc.ExampleEntity1.Include(ee =>ee.TextEntry).Include(ee =>ee.TextEntry.LocalizedContents)' have they added this ability in that it knows that it needs the liniking table to access the LocalizedContents table. — Seabizkit, Dec 04 '15 at 08:36

score -1 · Answer 3 · answered Dec 04 '15 at 08:52

NOT tested AND not perfect in terms of performance due to how include works... I would do it manually but here is an example of what you could do.

var result = dc.ExampleEntity1
             .Include(x => x.TextEntry)
             .Include(x => x.TextEntry.LocalizedContents)
             .Include(x => x.TextEntry.LocalizedContents.Local)
             .Where(x => x.id == 'ExampleEntity1Key'
                      && x.TextEntity.LocalContent.Local.Id == 'Value'
              )
             .FirstOrDefault();

This would end up with an object of ExampleEntity1 with all navigation eager loaded.... where Local is matched on an id.

you could then get the Local like..

var listLocalsForExampleEnitity = result.TextEntry.LocalizedContents.Local.ToList();

or just call them from where ever as they are already in mem.

Hope this helps

EF 6 filtering child collections

3 Answers3

Linked