Geek Answers Handbook

Linq to Entity Framework: select only a subset of children

The following setting is correct:

  • A is the parent class
  • A has one to many of B

  • A has one to many of C

  • B has one to many of X

  • C has one to many of X

  • X has an EndDate property: this is a filter criterion

I want to get a collection containing A, B, C and X (ABX and ACX) I want to filter the collection so that only those elements that have X.EndDate = Date are X.EndDate = Date .

I do not want the elements of X not to pass these criteria. Can this be done in one Select?

I am currently trying to use the following code:

 var set = _context.Set<A>() .Include("BX") .Include("CX") .Where(a => aBAny(b => bXAny(x => x.EndDate == date)) || aCAny(c => cXAny(x => x.EndDate == date))) .ToList();

However, when the BX is within the filter criteria, it will also include CX. And when one of the BX (X may be many) is true to the criteria, it will return all BX entities

I tried my best to give an example:

 ABX X.EndDate A1 B1 BX1 2015-01-01 A1 B1 BX2 2015-01-02 A1 B2 BX3 2015-01-09 ACX X.EndDate A1 C1 CX1 2015-01-03 A1 C1 CX2 2015-01-03 A1 C2 CX3 2015-01-02 When date == 2015-01-02 Results are: A1 B1 BX1 BX2 B2 BX3 C1 CX1 CX2 C2 CX3 Desired results are: A1 B1 BX2 C2 CX2

Note. I can only use this notation, and not as an SQL-like notation. There are more objects that need to be included, and this can (apparently) only be done using .Include ("") with quotes

+5
source share
2 answers

What you want to do is filter out the included tables, and this is not what the Entity Framework supports right now: http://entityframework.codeplex.com/workitem/47 . If you are dealing with simple classes, then you can create a new instance of A in your next expression as follows:

 var set = _context.Set<A>() .Include("BX") .Include("CX") .Where(a => aBAny(b => bXAny(x => x.EndDate == date)) || aCAny(c => cXAny(x => x.EndDate == date))) .Select(a => new A() { B = aB .Where(b => bXAny(x => x.EndDate == DatePicker)) .Select(b => new B() { X = bXWhere(x => x.EndDate == DatePicker) }), C = aC .Where(c => cXAny(x => x.EndDate == DatePicker)) .Select(c => new C() { X = cXWhere(x => x.EndDate == DatePicker) }) });

If your classes are not so simple i.e. If you have many properties that you would have to display in your application of choice, or if you have complex rules for verifying the business, the best option would be to continue your request and simply delete B, C and X that you do not want to receive from your result set before using it.

+3
source

Something like that?

 from a in _context.Set<A>() from b in aB from c in aC let bx = bXWhere( x => x.EndDate == date ) let cx = cXWhere( x => x.EndDate == date ) where bx.Any() || cx.Any() select { a, b, c, bx, cx };

Note that in the result set you can get duplicate a , b and / or c .

Update
If you cannot use LINQ syntax, this trivially (albeit rather ugly) translates to extension method calls:

 _context.Set<A>() .SelectMany( a => aB, (a, b) => new { a, b, bx = bXWhere( ... ) } ) .SelectMany( x => xaC, (x, c) => new { xa, xb, x.bx, c, cx = cXWhere( ... ) } ) .Where( x => x.bx.Any() || x.cx.Any() )
+1
source

More articles:


All Articles