EF Core warning - calling Include() and ThenInclude()

Question

I have the following entities (many to many relationship):

public class Fragrance
{
    public int FragranceId { get; set; }
    public string Brand { get; set; } = null!;
    public string Name { get; set; } = null!;
    public string Gender { get; set; } = null!;
    public int LaunchYear { get; set; }
    public virtual List<Note>? Notes { get; set; }
}

public class Note
{
    public int NoteId { get; set; }
    public string Name { get; set; } = null!;
    public string Description { get; set; } = null!;
    public virtual List<Fragrance>? Fragrances { get; set; }
}

public class FragranceNote
{
    public int FragranceId { get; set; }
    public Fragrance Fragrance { get; set; } = null!;
    public int NoteId { get; set; }
    public Note Note { get; set; } = null!;
}

When a user sends a http request that contains a fragrance id, I want a response that contains 2 random notes from the fragrance given, each containing 2 other random fragrances (different from the one sent by user).

[
    {
        "name": "Bergamot",
        "fragrances": [
            {
                "fullName": "Maison Francis Kurkdjian  Oud Silk Mood",
                "gender": "Unisex"
            },
            {
                "fullName": "Dolce & Gabbana Light Blue Forever",
                "gender": "Male"
            }
        ]
    },
    {
        "name": "Pink Pepper",
        "fragrances": [
            {
                "fullName": "Maison Margiela Replica Jazz Club",
                "gender": "Male"
            },
            {
                "fullName": "Dior Ambre Nuit",
                "gender": "Unisex"
            }
        ]
    }
]

My EF query looks like this:

var notes = _context.Fragrances
    .Where(f => f.FragranceId == fragranceId)
    .Include(
        f => f.Notes!
        .OrderBy(r => Guid.NewGuid())
        .Take(2)
    )
    .ThenInclude(
        n => n.Fragrances!
        .Where(f => f.FragranceId != fragranceId)
        .OrderBy(r => Guid.NewGuid())
        .Take(2)
    )
    .First().Notes;

It works, but I have this warning:

Compiling a query which loads related collections for more than one collection navigation, either via 'Include' or through projection, but no 'QuerySplittingBehavior' has been configured. By default, Entity Framework will use 'QuerySplittingBehavior.SingleQuery', which can potentially result in slow query performance.

I tried using AsSplitQuery(), but it messes up my response and doesn't load the correct data.

Answer 1

The culprit will be the OrderBy within the Include/ThenInclude This won't work with Split query because the split will attempt to load all Fragrances for the related Notes, then order the complete set randomly and take the top 2. So the resulting fragrances it picks for each note is taken from fragrances across all selected notes, resulting in mismatches.

The warning is just that, you are producing a potentially large Cartesian Product which EF is warning can affect performance. Split Queries can avoid this, but have limitations, particularly around sorting since it tries to split up the related queries without going as far as what happens with lazy loading.

It looks like you are looking to pick two random notes for a particular fragrance, then pick two random fragrances related to that note? (not excluding the fragrance you initially selected) You should be able to do this and avoid the warning:

var notes = _context.Notes
    .Where(n => n.Fragrances.Any(f => f.FragranceId == fragranceId)
    .Include(n => n.Fragrances
        .OrderBy(f => Guid.NewGuid())
        .Take(2))
    .OrderBy(n => Guid.NewGuid())
    .Take(2);

I beleieve that should give you the same expected results without the Cartesian warning. If you want to exclude the selected Fragrance ID then:

    .Include(n => n.Fragrances
        ,Where(f -> f.FragranceId != fragranceId)
        .OrderBy(f => Guid.NewGuid())
        .Take(2))