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

.net-core c# entity-framework entity-framework-core


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));
3/4/2020 9:00:42 AM

Popular Answer

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
                       .Where(i => i.InvitationId.ToString().Equals(model.InvitationId))
                       .Select(i => new
                               Invitation = i,
                               Guest = i.Guests.FirstOrDefault(g => g.GuestId.ToString().Equals(model.GuestId))

// 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.

3/4/2020 11:21:58 AM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow