0

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

Improve this question
2
  • 1
    Can you explain what "filtering" means? Do you want the objects within a certain date range? Commented Dec 10, 2009 at 15:19
  • 1
    Basically I want to select a JobSeries where id = 1 and from the child collection of Jobs everything where Job.SendDate = 01/12/2009 for example. Thanks Commented Dec 10, 2009 at 15:43

2 Answers 2

Reset to default
9

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

That's a great answer and very informative but it's not quite working for me because I don't want to return an anonymous object, I need a JobSeries object with the jobs filtered. The end result I found a VB.NET example: Dim query = From c In db.Customers _ Where c.CustomerID = 1 _ Select Customer = c, _ Orders = From o In c.Orders _ Where o.OrderDate >= #1/1/2009# I guess I'm not experienced enough with EF and I hate the fact something so simple is taking me ages! Going back to Fluent NHibernate after this experiment!
It sounds to me like you're trying to use the EF as if it were NHibernate. That will never work, because NHibernate uses a (somewhat archaic, IMHO) different way of working, since it didn't have LINQ support at all for many years and is still very limited in that department. You don't have to use an anonymous type here; regular POCOs work fine, but if your code is totally dependent on getting entity types back then you are practically guaranteed to do way too much SQL in any tool, because it is typical to have far more properties on an entity than you need for any one operation.
@Dan - I ran into this problem yesterday and was able to solve it using AutoMapper. So going off of Craig's code above, to get the result you want, you'd do this: Mapper.DynamicMap<JobSeries>(q).
-1

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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.