Question 348986 in ASP.NET Question posted in
The official documentation can be found here.

Asp.net – Slow EF Core query with Include

I have an issue with EF Core and Linq queries.

I have this query:

_db.TrainingsExercises
   .Include(t => t.ExerciseSteps)
   .Where(te => te.UserId == userId);

This is using a dbContext with these two models:

public class TrainingExercise
{
    [Key]
    public Guid Id { get; set; }
    public Guid UserId { get; set; }
    public string Name { get; set; }
    public bool IsPublic { get; set; }
    [ForeignKey(nameof(UserId))]
    public User User { get; set; }
    public ICollection<TrainingExerciseStep> ExerciseSteps { get; set; }
    public ICollection<TeamTrainingExercise>? TeamTrainings { get; set; }
}

public class TrainingExerciseStep
{
    [Key]
    public Guid Id { get; set; }
    public Guid TrainingExerciseId { get; set; }
    public int Order { get; set; }
    public string? Name { get; set; }
    public string? Image { get; set; }
    [ForeignKey(nameof(TrainingExerciseId))]
    public TrainingExercise TrainingExercise { get; set; }
    public List<TrainingExerciseStepComponents> ExerciseStepComponents { get; set; }
}

The appContext is set:

public DbSet<TrainingExercise> TrainingsExercises { get; set; }
public DbSet<TrainingExerciseStep> TrainingExerciseSteps { get; set; }

builder.Entity<TrainingExercise>(entity =>
{
    entity.HasMany(u => u.ExerciseSteps)
          .WithOne(s => s.TrainingExercise);
});

This is creating a simple query with a left join that I execute on SQL Server and it takes no seconds, but the Linq query takes more than one minute.

Thanks in advance.

I tried to remove the include and then it works perfectly. Also added AsNoTracking and it’s the same.

Tags:

2

Answers


  1. 0

    This two option are available:

    var trainingExercises = _db.TrainingsExercises
                            .Where(te => te.UserId == userId)
                            .Select(te => new 
                            {
                                te.Id,
                                te.Name,
                                te.IsPublic,
                                ExerciseSteps = te.ExerciseSteps.Select(es => new 
                                {
                                    es.Id,
                                    es.Order,
                                    es.Name,
                                    es.Image
                                }).ToList()
                            })
                            .ToList();



var userIdParameter = new SqlParameter("UserId", userId);
var trainingExercises = _db.TrainingsExercises
                            .FromSqlRaw("SELECT * FROM TrainingsExercises WHERE UserId = @UserId", userIdParameter)
                            .ToList();

    Is it what you want ?

    Login or Signup to reply.
  2. 0

    This query:

    _db.TrainingsExercises
   .Include(t => t.ExerciseSteps)
   .Where(te => te.UserId == userId);

    … will not actually execute anything, so this line alone can not be causing your 1-minute delay. Queries are executed when you consume the IQueryable such as using ToList, First, etc. or iterating with foreach. So seeing how this query is consumed may shed some light on possible performance impacting issues.

    A simple step in investigating performance issues that you can reproduce in a development environment is to step through with a debugger and note any specific lines that are slow to execute. This may be one line taking a long time, or something within a loop taking a second or two but causing something to execute many times. This can be paired with a profiler against the database to see what SQL statements are being run.

    Adding .AsSplitQuery() can help with eliminating the Cartesian Product, though in a single one-to-many relationship with a relatively limited # of fields that shouldn’t normally be a huge factor. One culprit may be if some of the data in the exercise steps happens to be quite large. "Image" is a suspect if that contains something like a Base64 representation of a bitmap. If exercise steps have a large image component, you typically will not want/need that returned all of the time, especially when retrieving potentially large numbers of exercises with sets of steps. Here your options are to use projection where instead of loading and returning entities, you create simple C# classes to serve as view models or DTOs which contain just the columns you need for that query results using .Select() and exclude the expensive field(s). Better is to normalize out the expensive fields in the database: For instance a 1-to-1 table TrainingExerciseStepImage with the TrainingExerciseStepId and this Image column. This way you can load training exercises and their steps without the expensive column(s) coming back, and .Include() images only when those images are actually needed. (I.e. for single steps rather than every step for every exercise for a user.)

    If there is a use case to display an image when loading a list of steps you might consider adding support for an additional thumbnail image, which would be a scaled down copy of the full image if users are capable of uploading images of any size. For instance you might be displaying a list of steps with an image that is only 100×100 pixels but loading an image content that could be full screen at 600 dpi.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search