Linq filter collection with EF

Question

Hi,

I'm trying to get Entity Framework to select an object and filter its collection at the same time. I have a JobSeries object which has a collection of jobs, what I need to do is select a jobseries by ID and filter all the jobs by SendDate but I can't believe how difficult this simple query is!

This is the basic query which works:

 var q = from c in KnowledgeStoreEntities.JobSeries
                    .Include("Jobs.Company")
                    .Include("Jobs.Status")
                    .Include("Category")
                    .Include("Category1")
                where c.Id == jobSeriesId
                select c;

Any help would be appreciated, I've been trying to find something in google and what I want to do is here:http://blogs.msdn.com/bethmassi/archive/2009/07/16/filtering-entity-framework-collections-in-master-detail-forms.aspx

It's in VB.NET though and I couldn't convert it to C#.

EDIT: I've tried this now and it doesn't work!:

            var q = from c in KnowledgeStoreEntities.JobSeries
                                      .Include("Jobs")
                                      .Include("Jobs.Company")
                                      .Include("Jobs.Status")
                                      .Include("Category")
                                      .Include("Category1")
                    where (c.Id == jobSeriesId & c.Jobs.Any(J => J.ArtworkId == "13"))
                    select c;

Thanks

Dan

Answer 1

I've long given up on .Include() and implemented Lazy loading for Entity Framework

Answer 2

Include can introduce performance problems. Lazy loading is guaranteed to introduce performance problems. Projection is cheap and easy:

var q = from c in KnowledgeStoreEntities.JobSeries
        where c.Id == jobSeriesId            
        select new 
        {
             SeriesName = c.Name,
             Jobs = from j in c.Jobs
                    where j.SendDate == sendDate
                    select new
                    {
                        Name = j.Name
                    }
             CategoryName = c.Category.Name
        };

Obviously, I'm guessing at the names. But note:

1. Filtering works.
2. SQL is much simpler.
3. No untyped strings anywhere.
4. You always get the data you need, without having to specify it in two places (Include and elsewhere).
5. No bandwith penalties for retrieving columns you don't need.
6. Free performance boost in EF 4.

The key is to think in LINQ, rather than in SQL or in materializing entire entities for no good reason as you would with older ORMs.