How do I include only the question values here that have certain property values?

Question

Here's the setup I have currently

public async Task<Logic.Objects.Profile> GetProfileByEmailAsync(string email)
{
    try
    {
        Logic.Objects.Profile logicProfile = Mapper.MapProfile(await santaContext.Client
            .Include(r => r.ClientRelationXrefSenderClient)
                .ThenInclude(clXref => clXref.RecipientClient) 
                    .ThenInclude(c => c.SurveyResponse)
                        .ThenInclude(sr => sr.Survey)
                            .ThenInclude(s => s.EventType)
            .Include(r => r.ClientRelationXrefSenderClient)
                .ThenInclude(clXref => clXref.RecipientClient)
                    .ThenInclude(c => c.SurveyResponse)
                        .ThenInclude(sr => sr.SurveyQuestion) // This has a property named SenderCanView. I want to get only the questions where this is set to true
            .Include(r => r.ClientRelationXrefSenderClient)
                .ThenInclude(e => e.EventType)
            .Include(s => s.ClientStatus)
            .Include(c => c.SurveyResponse)
                .ThenInclude(s => s.SurveyQuestion)
            .Include(c => c.SurveyResponse)
                .ThenInclude(sr => sr.Survey)
                    .ThenInclude(s => s.EventType)
            .FirstOrDefaultAsync(c => c.Email == email)) ;
        return logicProfile;
    }
    catch (Exception e)
    {
        throw e.InnerException;
    }
}

This gets all the various things I need through my entities and such, but I want to have it only include the survey response objects where the SurveyQuestion object has the SenderCanView property equal to true. How is something like this done? I wasn't able to do it with lambdas, as apparently doing .ThenInclude(sr => sr.SurveyQuestion.SenderCanView == true) is not a valid option to use. What should I do in this case?

More information edit:

This is in essence what I want to accomplish, but in the LINQ query, rather than outside it in a double foreach

Logic.Objects.Profile logicProfile = Mapper.MapProfile(await santaContext.Client
                .Include(r => r.ClientRelationXrefSenderClient)
                    .ThenInclude(clXref => clXref.RecipientClient)
                        .ThenInclude(c => c.SurveyResponse)
                            .ThenInclude(sr => sr.Survey)
                                .ThenInclude(s => s.EventType)
                .Include(r => r.ClientRelationXrefSenderClient)
                    .ThenInclude(clXref => clXref.RecipientClient)
                        .ThenInclude(c => c.SurveyResponse)
                            .ThenInclude(sr => sr.SurveyQuestion)
                .Include(r => r.ClientRelationXrefSenderClient)
                    .ThenInclude(e => e.EventType)
                .Include(s => s.ClientStatus)
                .Include(c => c.SurveyResponse)
                    .ThenInclude(s => s.SurveyQuestion)
                .Include(c => c.SurveyResponse)
                    .ThenInclude(sr => sr.Survey)
                        .ThenInclude(s => s.EventType)
                .FirstOrDefaultAsync(c => c.Email == email));

foreach(ProfileRecipient recipient in logicProfile.recipients)
{
    List<Response> responsesToHide = recipient.responses.Where(r => r.surveyQuestion.senderCanView == false).ToList();

    foreach(Response response in responsesToHide)
    {
        recipient.responses.Remove(response);
    }
}

So you still want to see a result for the question even if `SenderCanView` is false? — Chris Schaller, Jul 21 '20 at 05:39
@Karan Latest ChrisSchaller, Included an edit with more information to the goal I want to try and achieve. Effectively want to do the logic I want within the LINQ query itself rather than map everything, then take it out after the fact — kmanrulze, Jul 21 '20 at 05:48

score 0 · Accepted Answer · answered Jul 21 '20 at 05:50

0

If you are using EF Core 5.0 then you can use Filtered include like below.

.ThenInclude(c => c.SurveyResponse.Where(r => r.surveyQuestion.senderCanView == true))

answered Jul 21 '20 at 05:50

Karan

12,059
3
24
40

That works! I didn't even consider EF 5 was updated to have filtered options. Thank ya! – kmanrulze Jul 21 '20 at 06:08

How do I include only the question values here that have certain property values?

1 Answers1