How to perform a sub query for a Navigation Property in EF Core?

Question

Following is my code. I want to select a specific guest from Guests list of an invitation. But the following code throws the following error. How can I achieve this? I saw in this question there are some similar answers but not sure how to adapt it into EF Core.

Error: "Lambda expression used inside Include is not valid."

invitation = await _dbContext.Invitations
                                            .Include(s => s.Host)
                                            .Include(s => s.Guests.Where(g => g.GuestId.ToString().Equals(model.GuestId)))
                                            .FirstOrDefaultAsync(i => i.InvitationId.ToString().Equals(model.InvitationId));

So you want a bunch of invitations, where its guest collection has just the one guest in it (determined by the id you supplied)? If not, you need to be specific about what you actually want — TheGeneral, Mar 04 '20 at 08:17
@MichaelRandall Yep. Correct. I don't want the whole guests list. I only want the guest I specify. — Heshan, Mar 04 '20 at 08:22
Look at this article [Include With Where Clause](https://entityframework.net/include-with-where-clause). It also links to a related SO question. — downernn, Mar 04 '20 at 08:27
@downernn I've seen it. It doesn't work. At least in EF Core. — Heshan, Mar 04 '20 at 09:01
What is the problem you're facing? Does it have something to do with lazy loading, as discussed in [the related SO question](https://stackoverflow.com/questions/16798796/ef-include-with-where-clause)? Would you like to share the code you wrote based on this approach? — downernn, Mar 04 '20 at 09:06

Flater · Answer 1 · 2020-03-04T11:21:58.650

4

The other answer posted by thatsalok is correct, an Include cannot be filtered. However, there are still ways to achieve what you want.

Include statements don't allow for filtering, but Select statements do. This means that you can ask EF to give you exactly what you want.

var result = await _dbContext
                       .Invitations
                       .Where(i => i.InvitationId.ToString().Equals(model.InvitationId))
                       .Select(i => new
                           {
                               Invitation = i,
                               Guest = i.Guests.FirstOrDefault(g => g.GuestId.ToString().Equals(model.GuestId))
                           })
                       .FirstOrDefaultAsync();

// demo usage
var invitation = result.Invitation;
var guest = result.Guest;

I strongly advise not using the entity class itself to store a limited list, as this may become a source of bugs if this entity will still be used in the database context. Instead, use a purpose built DTO (or viewmodel) class that contains the guest and invitation objects separately.

edited Mar 04 '20 at 11:21

answered Mar 04 '20 at 09:35

Flater

12,908
4
39
62

Thanks! This is very close to what I want but not quite it. Because this still contains the original invitation with unwanted Guests, you take out the matching Guest and put it into a different field. – Heshan Mar 05 '20 at 07:02
My intention is to filter the unwanted Guests and still maintain the original object so I don't have to change the underlying logic. If I change the code as follows, it does what I want with some extra effort to map all the properties. .Select(i => new Invitation { //Map all individual properties InvitationId = i.InvitationId, Guests = new List { i.Guests.FirstOrDefault(g => g.GuestId.ToString().Equals(model.GuestId)) } }) – Heshan Mar 05 '20 at 07:03
@Heshan: It can be done, the exact way you want it to be done, but it's not advisable as entities are supposed to represent the database, not your custom domain logic (or its representative DTO objects). Just because it fits doesn't mean it belongs. – Flater Mar 05 '20 at 09:02

score 1 · Answer 2 · answered Mar 04 '20 at 09:22

1

After using EF core for almost the year, having limited set of records in navigation property is not possible at least with EntityFramework Core 3.1.

However there are some third party provider who provide this type of capability like mentioned by @downernn in comment Include with where clause

There other officially supported extension for EF core, you may want to browse this page at MSDN

answered Mar 04 '20 at 09:22

thatsalok

135
1
8

Thanks for the info. I commented on the above answer on how I managed to do what I want. Maybe not the cleanest way but can't find a better way as of now. – Heshan Mar 05 '20 at 07:11

How to perform a sub query for a Navigation Property in EF Core?

2 Answers2