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

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()

Answer 2

It seems to me you wanted to configure a many-to-many relationship between Distributors and Manufacturers: Every Distributor has zero or more Manufacturers, every Manufacturer delivers to zero or more Distributors.

If you'd configured this many-to-many relationship according to the entity framework code first many-to-many conventions, you would have something like:

class Distributor
{
     public int Id {get; set;}
     public string Name {get; set;}

     // a distributor has exactly one Address using foreign key:
     public int AddressId {get; set;}
     public Address Address {get; set;}

     // a Distributor has zero or more Manufacturers: (many-to-many)
     public virtual ICollection<Manufacturer> Manufacturers {get; set;}

     // a Distirbutor has zero or more Users: (one-to-many)
     public virtual ICollection<User> Users {get; set;}
}

class Manufacturer
{
     public int Id {get; set;}
     public string Name {get; set;}

     // a Manufacturer has exactly one Address using foreign key:
     public int AddressId {get; set;}
     public Address Address {get; set;}

     // a Manufacturer has zero or more Distributors (many-to-many)
     public virtual ICollection<Distributor> Distributors {get; set;}
}

There is also a User: every User belongs to exactly one Distributor

class User
{
     public int Id {get; set;}

     // a user belongs to exactly one Distributor, using foreign key:
     public int DistributorId {get; set;}
     public virtual Distributor Distributor {get; set;}

     ...
}

Finally the DbContext

class MyDbContext : DbContext
{
    public DbSet<Distributor> Distributors {get; set;}
    public DbSet<Manufacturer> Manufacturers {get; set;}
    public DbSet<User> Users {get; set;}
    public DbSet<Address> Addresses {get; set;}
}

The above is all that entity framework needs to know to understand that you want a many-to-many between Distributors and ManuFacturers. Entity Framework will created a proper junction table for you, although you won't need it in your queries as I'll show you below. If you don't like the default junction table that entity framework creates for you, you can use fluent API to define the table and column names:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Distributor>()
            .HasMany(distributor=> distributor.Manufacturers)
            .WithMany(manufacturer => manufacturer.Distributors)
            .Map(map =>
            {
                map.MapLeftKey("DistributorId");
                map.MapRightKey("ManufacturerId");
                map.ToTable("DistributorsManufacturers");
            });

Although internally entity framework will use the junction table, you won't use it in your queries, just use the ICollections:

I've got a _user and I want several properties of the zero or one Distributor of this user, together with several properties of all the Manufacturers of this Distributor

Although the Include statement can be used, it is seldom wise to do so. One of the slower parts of database queries is the transfer of the selected data to your process, so you should limit the amount of transferred data to only the properties you really plan to use. Include will transfer all properties, and I highly doubt whether you'll use them, especially all the foreign keys with all the same values.

So your query using the ICollection:

var _user = ... // I've got a User
var result = dbContext.Distributers
    .Where(distributor => distributor.Id == _user.DistributorId)
    .Select(distributor => new
    {
        // select only the distributor properties you plan to use
        Id = distributor.Id,
        Name = distributor.Name,
        Address = new
        {
             // again: only the properties you plan to use
             Street = distributor.Address.Street,
             City = distributor.Address.City,
             Zip = distributor.Address.Zip,
        }),

        // fetch (all or some) manufacturers of this distributor
        Manufacturers = distributor.Manufacturers
            .Where(manufacturer => manufacturer.Address.NavigationState == "CA")
            .Select(manufacturer => new
            {
                 // select only the properties you plan to use
                 // probably not the foreign key to the junction table
                 Name = manufacturer .Name,
                 Address = new {...},
                 ...
            })
            .ToList(),
    })
    .SingleOrDefault();

It might be that you want some different properties, but you get the gist

Answer 3

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 4

From what I've read elsewhere, this is not do-able in the format that you are attempting. I still believe I've seen it done, but I didn't like what I saw and now don't recall the details.

My suggestion is that you turn your query around so that you are querying the Distributor (as specified by _user.DistributorId and then .Include() the Manufacturers.

However... I think you'll run into the same problem when querying the AddressesNavigation.State. As you say it is not conceptually different. You may only believe this is working due to seeing an error relating to the condition which comes later in your code - but that's no guarantee of the application of the conditions in the compiled query expression.