Baklap4 Baklap4 - 16 days ago 8
C# Question

Two LINQ statements to one awaitable LINQ database query (Optimize)

Hey everyone i have a working LINQ query but i was wondering if it could be optimized even more..

this is what i'm doing right now:

var selectionsAnimals = await this.context.SelectionAnimals
.GroupBy(a => a.AnimalId)
.ToListAsync();

var animalsSelections = selectionsAnimals
.Select(a => new AnimalsSelection
{
AnimalId = a.First().AnimalId,
SelectionIds = a.Select(b => b.SelectionId)
.OrderBy(b => b)
.ToList()
})
.ToList();


I was wondering if these two LINQ statements could be merged and as one awaitable LINQ query to the database?

This is the Database entity:

public class SelectionAnimals
{
public int Id { get; set; }
public int AnimalId { get; set; }
public int SelectionId { get; set; }
public DateTime CreatedAt { get; set; }
public DateTime UpdatedAt { get; set; }
}


This is the model:

public class AnimalsSelection
{
public int AnimalId { get; set; }
public IList<int> SelectionIds { get; set; }
}


in the end i'm mapping animalsSelection to a
IEnumerable<AnimalsSelection>
(the model)

Answer

Michael Coxon is right in general, but EF Core currently is quite unstable in even basic query construct processing.

So (sadly) the most important question is what EF Core version are you using. It's really a trial and error process. For instance, in EF Core 1.1.0 (release), Michael's construct does not work but the following equivalent does:

var animalsSelections = await db.SelectionAnimals
    .GroupBy(a => a.AnimalId)
    .Select(g => new AnimalsSelection
    {
        AnimalId = g.Key,
        SelectionIds = g.OrderBy(a => a.SelectionId)
                        .Select(a => a.SelectionId)
                        .ToList()
    })
    .ToListAsync();