1

I have an object Customer with a one-to-many relation to Visit. The visits have certain VisitType.

I want to fetch only Visits after a certain date and this for all my Customers. Therefor i pass the parameter referenceDate

Linq query i can think of that would give me the desired result:

customers.Include(c => c.Visits.Where(v => v.VisitDate >= referenceDate)).ToList();

Offcourse this does not work since Include actually requires a path.

Is there a Linq alternative for this problem or should i write custom queries for this?

JMan
  • 2,611
  • 3
  • 30
  • 51
  • possible duplicate of [EF: Include with where clause](http://stackoverflow.com/questions/16798796/ef-include-with-where-clause) – Yuliam Chandra Sep 26 '14 at 09:16
  • This is not a good solution. When performing the .Select(x => x.b) following error occurs: Column addition failed for symbol type 'Devart.Common.Entity.ae' and current expression 'Devart.Common.Entity.ae'. I have other properties on my object that are causing these problems. – JMan Sep 26 '14 at 09:44
  • `customers.Select(c => new { c, Visits = c.Visits.Where(v => v.VisitDate >= referenceDate) }).AsEnumerable().Select(a => a.c).ToList()` – Yuliam Chandra Sep 26 '14 at 09:50

1 Answers1

0

Maybe you need something like that: 

customers.Include(c => c.Visits).Where(c => c.Visits.VisitDate >= referenceDate)).ToList();

or

customers.Include("Visits").Where(c => c.Visits.VisitDate >= referenceDate)).ToList();

Greenhorn
  • 74
  • 6
  • This would exclude my Customers without visits. – JMan Sep 26 '14 at 09:23
  • Ah ok... And something like this? customers.Include(c => c.Visits).Where(c => c.Visits.VisitDate >= referenceDate || c => c.Visits == null || c.Visits.Count == 0 )).ToList(); I am not on my development machine now, so that code is not tested ;) Also i am pretty sure that Visits don't hast to be checked for null and count = 0. One of them should be enough... – Greenhorn Sep 26 '14 at 09:25
  • this would still include all my visits, it's a filter on the customer – JMan Sep 26 '14 at 09:26