Including an object with where clause in LINQ

Question

I would like to have a LINQ query that is supposed to return all members with VitalSigns where event in the vital signs is equal to surgery.

My Member.cs class:

public class Member
{
    public int Id { get; set; }
    public string FullName { get; set; }
    public ICollection<VitalSign> VitalSigns { get; set; }

    public Member()
    {
         VitalSigns = new Collection<VitalSign>();
    }
}

And my VitalSign.cs class is :

public class VitalSign
{
    public int Id { get; set; }
    public string Event { get; set; }

    // relationships
    public Member Member { get; set; }
    public int MemberId { get; set; }
}

The LINQ query that I wrote is:

 return await context. Members.Include(c => c.VitalSigns.Where(t => t.Event == "post surgery")).ToListAsync();

This returns a self-referenced loop. Because there are some data in the VitalSigns where the event is not equal to "post surgery". Am I writing the query wrong?

@RyanWilson The problem isn't the order (`Where` before or after `Include`), the problem is that the `Where` and the `Include` should be at the same level in the query, so the `Where` outside the `Include` — xanatos, May 21 '18 at 19:40
@xanatos Thanks for the explanation. Appreciate that. I'm still doing my database work using SQLClient and stored procedures, etc. So I'm not very familiar with the EntityFramework with Linq syntax. — Ryan Wilson, May 21 '18 at 19:42
@xanatos if they're in the same level then I can't get Event. — GoGo, May 21 '18 at 19:43
@gogo Because you have to use an inner `Any`... See my response — xanatos, May 21 '18 at 19:44

xanatos · Accepted Answer · 2018-05-21T19:46:37.113

4

The query should be:

context.Members.Where(t => t.VitalSigns.Any(u => u.Event == "post surgery"))
    .Include(c => c.VitalSigns)
    .ToListAsync()

The Include() is only an hint on what tables should be loaded when the query is executed.

The query is something like:

all the members WHERE there is ANY (at least) one VitalSign with Event == post surgery
together with the Members you'll get, please INCLUDE the VitalSigns (the alternative is that they'll be lazily loaded when you try to access them)
return a List<> (ToListAsync) of the elements in an asynchronous way

edited May 21 '18 at 19:46

answered May 21 '18 at 19:44

xanatos

109,618
12
197
280

Good explanation and thanks for breaking it down from the llambdas to plain linq syntax. +1 from me. – Ryan Wilson May 21 '18 at 19:46
Well this query returns all the vital signs no matter the event is equal or not equal to "post surgery" – GoGo May 21 '18 at 19:55
@GoGo It returns all the Members with at least one vital sign equal to post surgery. The other vital signs are included. [It is a limitation of Entity Framework](https://stackoverflow.com/questions/39636952/how-to-filter-include-entities-in-entity-framework). – xanatos May 21 '18 at 19:57
well, it is not currently possible to filter which related entities are loaded. Include will always bring in all related entities. That's not good.https://msdn.microsoft.com/en-us/data/jj574232#explicitFilter – GoGo May 21 '18 at 20:15

Including an object with where clause in LINQ

1 Answers1