EF: Include with where clause

Several guys ask me if it’s possible to add a condition in the Include method and the answer is no.

However you can do an equivalent like this:

from cWithP in

    (from c in context.Categories

     select new

     {

         Category = c,

         Products = from p in c.Products

                    where p.UnitPrice > 20

                    select p

     }).AsEnumerable()

select cWithP.Category;

or the condensed equivalent:

context.Categories.Select(c => new { Category = c, Products = c.Products.Where(p => p.UnitPrice > 20) }).AsEnumerable().Select(cp => cp.Category);

This entry was posted in 7671, 7674, 7675. Bookmark the permalink.

5 Responses to EF: Include with where clause

  1. sanvir says:

    Thank you very much for this excellent solution.

  2. Cindi says:

    Thanks for the post. It was very helpful!

  3. Craig says:

    Hi,

    This does not actually work.
    It appears to work because the EF context only has a subset of the entities loaded.

    If you subsequently load more of the child entities that will be magically added to the parent, and will be included in the attached children breaking your code.

    ChildTable.Select(_ => _).Load();
    var p = ParentTable.Select(o => new { Parent = o, Children = o.Children.Where(_ => _.Name.Length == 1)}).AsEnumerable().Select (_p => _p.Parent);
    p.Dump();

    You will get all the child entities, if you like it or not.

    Turn change tracking off, and you get no child entities.

    var p = ParentTable.AsNoTracking().Select(o => new { Parent = o, Children = o.Children.Where(_ => _.Name.Length == 1)}).AsEnumerable().Select (_p => _p.Parent);
    p.Dump();

    The query just ‘appears’ to work, and you need to be careful of what is or is not attached to the context at the time you execute the query.

  4. Matthieu MEZIL says:

    Hi
    Entity Framework associations work like this!
    The navigation properties are always filled with what you have in your context cache.

  5. Chris says:

    This would be easier if Filtered Includes were possible. You can vote for the feature here:
    https://entityframework.codeplex.com/workitem/47

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>