CODEWITHSUNDEEP
asp.net-core-mvcentity-framework-core
joacar
joacar

Reputation: 961

EF Core many-to-many fetch query

I have the following many-to-many relation modelled

public class Profile
{
    ICollection<Category> Categories { get; set;}
    // One-To-Many
    ICollection<Platform> Platforms { get; set; }
}

public class Category
{
    ICollection<Profile> Profiles { get; set; }
}

public class ProfileCategory
{
    public int ProfileId { get; set; }
    public Profile Profile { get; set; }
    public int CategoryId { get; set; }
    public Category Category { get; set;}
}

I'm using ASP.NET Core MVC and have a filter view model where a filter on some attributes on profile name and it works.

Trying to filter based on category proved much harder to implement (at least the solution isn't obvious to me :)

From the web the user can select zero, one or many categories to filter on so basically what is sent to my controller is a list of category ids.

IQueryable<Profile> query = _context.Profiles.Include(p => p.Categories).Include(p => p.Platforms);
if(string.IsNullOrEmpty(search.Name))
{
    query = query.Where(p => p.Name.IndexOf(search.Name StringComparison.OrdinalIgnoreCase) > 0);
}
if(search.Categories?.Any() != null)
{
    query = query.SelectMany(p => p.ProfileCategories)
            .Join(search.Categories, pc => pc.CategoryId, cId => cId, (pc,_) => pc.Profile);
}

From this point the Profile object is different and other navigational properties such as Platforms is null hence breaking other parts.

How can I perform the join while retaining the original instance of Profile object. I first thought that they would be the same, but I was wrong.

Upvotes: 4

Views: 3849

Answers (1)

Dmitry
Dmitry

Reputation: 16795

Currently, EF Core JOINs are not perfect, and I recommend make two queries:

1) Select list of ProfileId (based on category list):

var profileIds = await _context.ProfileCategory
    .Where(x => categoryIds.Contains(x.CategoryId)) // filtering goes here
    .Select(x => x.ProfileId)
    .Distinct()
    .ToListAsync();

2) Select required data based on known IDs:

var result = await _context.Profiles
    .Include(p => p.Categories).Include(p => p.Platforms)
    .Where(x => profileIds.Contains(x.ProfileId))
    .ToListAsync();

Yes, this is two queries instead one, but two simple queries, easily optimized using indexes.

Upvotes: 7

Related Questions