How to write EF Core Query to filter on multiple tables?

Question

Using ASP Core 2 with EF Core and SQL Server. I have, what I think is a straightforward task of retrieving a list of manufacturers (or individual manufacturer) for a given distributor.

The Users table provides the authenticated user and each is associated with one distributor (and represented in the model as _user). So, when the action GetManufacturers() is called on the ManufacturersController, it should return all manufacturers for the given distributor. Likewise GetManufacturers(int id) should return a single manufacturer iff it is associated with the authenticated distributor.

To do this I'm trying various formulations like:

await _context.Manufacturers
    .Include(a => a.Addresses)
    .Include(m => m.DistributorManufacturers)
    .Where (a => a.AddressesNavigation.State = "CA")
    .Where (m => m.Id == id)   // Manufacturers Id
    .Where (d => d.DistributorManufacturers.DistributorId == _user.DistributorId)
    .AsNoTracking()
    .ToListAsyc()

VS is complaining that ICollection<DistributorManufacturers> does not contain a definition for DistributorId (even though I copied/pasted it from the class). It is not conceptually different from my filter on Addresses.

I've also tried .ThenInclude to add the Distributors table but no luck.

The DistributorManufacturers table was created with Scaffold-DbContext and has the foreign keys and navigation properties defined.

Answer 1

So, did some work to re-create your models. The only thing I changed was I added the userId in the Distributor table instead of the opposite. This will be a long answer.. so hang on

Models (omitted User and Address entities because there's nothing special with them)

public abstract class Entity
{
    public int Id { get; set; }
}

public class Distributor : Entity
{   
    public User User { get; set; }

    public int UserId { get; set; }

    public Address Address { get; set; }

    public int AddressId { get; set; }

    public ICollection<DistributorManufacturer> DistributorManufacturers { get; set; }
}

public class Manufacturer : Entity
{
    public Address Address { get; set; }

    public int AddressId { get; set; }

    public ICollection<DistributorManufacturer> DistributorManufacturers { get; set; }
}

public class DistributorManufacturer
{
    public Distributor Distributor { get; set; }

    public int DistributorId { get; set; }

    public Manufacturer Manufacturer { get; set; }

    public int ManufacturerId { get; set; }
}

Configured like this:

modelBuilder.Entity<Distributor>()
    .HasOne(p => p.User)
    .WithMany()
    .HasForeignKey(p => p.UserId);

modelBuilder.Entity<Distributor>()
    .HasOne(p => p.Address)
    .WithMany()
    .HasForeignKey(p => p.AddressId);

modelBuilder.Entity<Manufacturer>()
    .HasOne(p => p.Address)
    .WithMany()
    .HasForeignKey(p => p.AddressId);

// many to many mapping
modelBuilder.Entity<DistributorManufacturer>()
    .HasKey(bc => new { bc.DistributorId, bc.ManufacturerId });

modelBuilder.Entity<DistributorManufacturer>()
    .HasOne(bc => bc.Distributor)
    .WithMany(b => b.DistributorManufacturers)
    .HasForeignKey(bc => bc.DistributorId)
    .OnDelete(DeleteBehavior.Restrict);

modelBuilder.Entity<DistributorManufacturer>()
    .HasOne(bc => bc.Manufacturer)
    .WithMany(c => c.DistributorManufacturers)
    .HasForeignKey(bc => bc.ManufacturerId)
    .OnDelete(DeleteBehavior.Restrict);

Inserted this values:

select * from Users
select * from Distributors
select * from Manufacturers
select * from DistributorManufacturers

Then, in the GetManufacturers() action you wanted to return all Manufacturers for the logged in Distributor, AKA User. (This is my assumption from your question.. correct me if I'm wrong). So, down to the query:

// Simulate getting the Id of the logged in User.
var userId = 1;

var query = (from m in _context.Manufacturers
             join dm in _context.DistributorManufacturers on m.Id equals dm.ManufacturerId
             join dist in _context.Distributors on dm.DistributorId equals dist.Id
             join adrs in _context.Addresses on m.AddressId equals adrs.Id
             where dist.UserId == userId
             select new
             {
                 ManufacturerId = m.Id,
                 ManufacturerName = m.Name,
                 DistributorId = dist.Id,
                 DistributorName = dist.Name,
                 Address = adrs
             }).ToList();

Resulting in this:

[
    {
        "manufacturerId": 1,
        "manufacturerName": "Manufacturer 1",
        "distributorId": 1,
        "distributorName": "Distributor 1",
        "address": {
            "street": "Street 1",
            "city": "New York",
            "state": "NY",
            "id": 1
        }
    },
    {
        "manufacturerId": 2,
        "manufacturerName": "Manufacturer 2",
        "distributorId": 1,
        "distributorName": "Distributor 1",
        "address": {
            "street": "Street 2",
            "city": "New York",
            "state": "NY",
            "id": 2
        }
    }
]

To get the GetManufacturers(int id) working, just add the Manufacturer Id to the where clause. Since it's doing a inner join on DistributorManufacturer, if there's no relationship with the logged in user it will return null.

Note: In EF Core, when you have a many-to-many relationship, you need (for now at least..) to have the joint table as an entity. You can check the discussion about this here: https://github.com/aspnet/EntityFrameworkCore/issues/1368

Answer 2

You can query with foreign table data like:

_context.MainTable
  .Include(i=>i.ForeignTable)
    .Where(w=>w.ForeignTable
        .Where(wh=>wh.ForeignId==userInput).Count()>0)
      .ToList();

Your query thus can be:

await _context.Manufacturers
    .Include(a => a.Addresses)
    .Include(m => m.DistributorManufacturers)
    .Where (a => a.AddressesNavigation.State = "CA")
    .Where (m => m.Id == id)   
    .Where (d => d.DistributorManufacturers
       .Where(w=>w.DistributorId == _user.DistributorId).Count()>0)
    .AsNoTracking()
    .ToListAsnyc()