Include vs select projects in EfCore-CodePudding

I have this query

            var answers = await context.QuestionnaireUserAnswers

                .Include(a => a.QuestionAnsweres)
                .Include(a => a.QuestionnaireSentOut).ThenInclude(q => q.Questionnaire)
                .Include(a => a.User).ThenInclude(w => w.Organization)

                .Where(a => a.QuestionnaireSentOut.Questionnaire.Status != QuestionnaireStatus.ArchivedDraft &&
                    a.QuestionnaireSentOut.Questionnaire.Status != QuestionnaireStatus.ArchivedPublished)
                .Where(a => a.QuestionnaireSentOut.QuestionnaireType.Equals(QuestionnaireSentType.Normal))
                .Where(a => a.Status.Equals(QuestionAnsweredStatus.Draft))
                .Where(a => a.NextReminderDate < DateTime.UtcNow)
                .ToListAsync();

which I changed to this

            var answers = await context.QuestionnaireUserAnswers
                .Where(a => a.QuestionnaireSentOut.Questionnaire.Status != QuestionnaireStatus.ArchivedDraft &&
                    a.QuestionnaireSentOut.Questionnaire.Status != QuestionnaireStatus.ArchivedPublished)
                .Where(a => a.QuestionnaireSentOut.QuestionnaireType.Equals(QuestionnaireSentType.Normal))
                .Where(a => a.Status.Equals(QuestionAnsweredStatus.Draft))
                .Where(a => a.NextReminderDate < DateTime.UtcNow)
                .Select(ans => new QuestionnaireApiServiceReminderModel
                {
                    AnswerId = ans.Id,
                    Created = ans.Created,
                    NextReminderDate = ans.NextReminderDate,
                    QuestionnaireSentOutQuestionnaireTitle = ans.QuestionnaireSentOut.Questionnaire.Title,
                    QuestionnaireSentOutTitle = ans.QuestionnaireSentOut.Questionnaire.Title,
                    User = new SimpleUserWithOrganizationId
                    {
                        OrganizationId = ans.User.OrganizationId,
                        UserId = ans.UserId,
                        Email = ans.User.Email,
                        Name = ans.User.Name,
                    }
                })
                .ToListAsync();

so that I only get those properties that I need.

Down below my code does this

foreach (var answer in answers) {
  .. // some calls
   answer.NextReminderDate = DateTime.UtcNow.AddDays(7);
}



await context.SaveChangesAsync();

Now if I go with my updated version then I need to fetch the corresponding QuestionnaireUserAnswers before updating it. Which will be multiple roundtrips to database. Does this mean that in this case it is better to use the first query with includes?

One way to handle this could be write something like this

       await context.Database.ExecuteSqlInterpolatedAsync($"UPDATE QuestionnaireUserAnswers SET NextReminderDate = {newNextReminderDate} WHERE Id IN ({string.Join(',', answers.Select(x => x.AnswerId))})");

but is this an approved efcore way?

So is this a better solution in this scanerio?

        var answers = await context.QuestionnaireUserAnswers
            .Where(a => a.QuestionnaireSentOut.Questionnaire.Status != QuestionnaireStatus.ArchivedDraft &&
                a.QuestionnaireSentOut.Questionnaire.Status != QuestionnaireStatus.ArchivedPublished)
            .Where(a => a.QuestionnaireSentOut.QuestionnaireType.Equals(QuestionnaireSentType.Normal))
            .Where(a => a.Status.Equals(QuestionAnsweredStatus.Draft))
            .Where(a => a.NextReminderDate < DateTime.UtcNow)
            .Select(ans => new QuestionnaireApiServiceReminderModel
            {
                Answer = ans,
                AnswerId = ans.Id,
                Created = ans.Created,
                NextReminderDate = ans.NextReminderDate,
                QuestionnaireSentOutQuestionnaireTitle = ans.QuestionnaireSentOut.Questionnaire.Title,
                QuestionnaireSentOutTitle = ans.QuestionnaireSentOut.Questionnaire.Title,
                User = new SimpleUserWithOrganizationId
                {
                    OrganizationId = ans.User.OrganizationId,
                    UserId = ans.UserId,
                    Email = ans.User.Email,
                    Name = ans.User.Name,
                }
            })
            .ToListAsync();


        //Sending reminders
        foreach (var answer in answers)
        {
            try
            {
                 answer.Answer.NextReminderDate = DateTime.UtcNow.AddDays(7);
                QuestionnaireReminder(
                       answer.User.Name,
                       answer.User.Email,
                       answer.QuestionnaireSentOutQuestionnaireTitle,
                       answer.QuestionnaireSentOutTitle,
                       answer.Created.ToString(),
                       answer.AnswerId,
                       appServer,
                       answer.User.OrganizationId, _configuration);

            }
            catch (Exception)
            {
                await ErrorHandleService.HandleError("Cound not send questionnaire reminder", 
                    "Cound not send questionnaire reminder email to: "   answer.User.Email, 
                    null, null, null, _configuration, sendDeveloperMails, currentVersion, whoAmIName);
            }
        }
        await context.SaveChangesAsync();

CodePudding user response：

The short answer is Yes, and No.

When reading data, project to DTO/ViewModels like your updated code. This reduces the size of the resulting data to pull back just the info from the related entities that is needed.

When updating data, load the tracked entity/entities.

The "No" part of the answer is that in your first query, remove the Include statements, they are not needed, and should be removed to avoid extra data being sent across the wire, potentially forming a Cartesian Product across 1-to-many tables via JOINs.

var answers = await context.QuestionnaireUserAnswers
   .Where(a => a.QuestionnaireSentOut.Questionnaire.Status != QuestionnaireStatus.ArchivedDraft &&
       a.QuestionnaireSentOut.Questionnaire.Status != QuestionnaireStatus.ArchivedPublished)
   .Where(a => a.QuestionnaireSentOut.QuestionnaireType.Equals(QuestionnaireSentType.Normal))
   .Where(a => a.Status.Equals(QuestionAnsweredStatus.Draft))
   .Where(a => a.NextReminderDate < DateTime.UtcNow)
   .ToListAsync();

Include is not needed to perform Where conditions. EF will work those out into the query just fine. It is only needed in cases where you want to access those related entities within working with the resultset, otherwise they would be lazy loaded with potentially several DB round-trips getting queued up.

This is provided the "Some Calls" don't attempt to access related entities on the answer. If your logic does need some data from QuestionaireSentOut or Questionaire etc. then you can use a mix of projection with the entity reference:

.Select(ans => new
{
    QuestionnaireSentOutQuestionnaireTitle = ans.QuestionnaireSentOut.Questionnaire.Title,
    QuestionnaireSentOutTitle = ans.QuestionnaireSentOut.Questionnaire.Title,
    Answer = ans,
    User = ans.User.Select(u => new 
    {
        OrganizationId = u.OrganizationId,
        UserId = u.UserId,
        Email = u.Email,
        Name = u.Name,
    }
})

Similar to your projection, you can return the answer entity in the resulting query for processing purposes. Inside the loop if the logic needs details from the user or questionaire, it gets it from the resulting projection, then when you go to update the answer itself, update it via the .Answer reference which will be the tracked EF entity. This approach should not be used for Read-type operations where populating a ViewModel to be sent back to a View etc. It's advisable to not mix view models and entities to avoid errors and performance issues with serialization and such. Where I need details like this I will use anonymous types to discourage passing objects with entity references around.

CodePudding user response：

You should be able do this with the free library Entity Framework Plus using the Batch Update feature. The feature was created to work for multiple records, but it will work just as well for just one record.

It constructs an UPDATE ... SET ... WHERE ... query similar to what you wrote yourself, without loading any data from the database into the DbContext.

Sample code:

using Z.EntityFramework.Plus;

var id = .... ;
var newDate = DateTime.UtcNow.AddDays(7);

context.QuestionnaireUserAnswers
       .Where(a => a.Id == id)
       .Update(a => new QuestionnaireUserAnswer() { NextReminderDate = newDate });

Or for a collection of Id values:

using Z.EntityFramework.Plus;

var ids = new[] { ... , ... , ... };
var newDate = DateTime.UtcNow.AddDays(7);

context.QuestionnaireUserAnswers
       .Where(a => ids.Contains(a.Id))
       .Update(a => new QuestionnaireUserAnswer() { NextReminderDate = newDate });

A note: I believe this will not update any QuestionnaireUserAnswer objects that are already loaded in your DbContext. This may not be much of a problem in ASP .NET Core (or API) where contexts are short-lived, but it could be an issue if you use the context for a longer period.