Apply filters to a table based on conditions using LINQ and MVC C#

Question

I am trying to create a list of records (partners) based on a several list of conditions. The problem is that the SQL generated by the LINQ is selecting partners that respect at least one of the conditions, and I only want the partners that respect the conditions that have to be applied (serviceId or/and brandId and/or TraillerService).

Models (simplified):

public class Partner
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<PartnerServiceBrand> PartnerServiceBrands { get; set; }

}

// A partner can have multiple services, brands and in each case, can have, or not have trailler service
public class PartnerServiceBrand
{
    [Key]
    public int Id { get; set; }

    public virtual Partner Partner { get; set; }
    public virtual Service Service { get; set; }
    public virtual Brand Brand { get; set; }

    public bool TrailerService { get; set; }
}

public class Service
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<PartnerServiceBrand> PartnerServiceBrands { get; set; }
}

public class Brand
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<PartnerServiceBrand> PartnerServiceBrands { get; set; }
}

I want to apply conditions based on filters that I have:

IQueryable<Partner> partners = dbContext.Partners;

if (search.ServiceId > 0)
{
    partners = dbContext.Partners.Where(p => p.PartnerServiceBrands.Select(psb => psb.Service.Id).Contains(search.ServiceId));
}

if (search.BrandId > 0)
{
    partners = partners.Where(p => p.PartnerServiceBrands.Select(psb => psb.Brand.Id).Contains(search.BrandId));
}

if (search.TrailerService == true)
{
    partners = partners.Where(x => x.PartnerServiceBrands.Any(y => y.TrailerService == true));
}

The query that I get is:

SELECT[Extent1].[Id] AS[Id], [Extent1].[Name] AS[Name]
    WHERE(EXISTS (SELECT 1 AS[C1]
    FROM [dbo].[PartnerServiceBrands] AS [Extent2]
    WHERE ([Extent1].[Id] = [Extent2].[Partner_Id]) AND([Extent2].[Service_Id] = 7)
                        )) AND(EXISTS (SELECT 1 AS[C1]
                                        FROM [dbo].[PartnerServiceBrands] AS [Extent3]
                                        WHERE ([Extent1].[Id] = [Extent3].[Partner_Id]) AND([Extent3].[Brand_Id] = 1153)
                        )) AND(EXISTS (SELECT 1 AS[C1]
                                        FROM [dbo].[PartnerServiceBrands] AS [Extent4]
                                        WHERE ([Extent1].[Id] = [Extent4].[Partner_Id]) AND(1 = [Extent4].[TrailerService])
                        ))

With this query, I get the partners that have a least 1 service with trailer service, and that's not what I want. I want only partners that respect all the conditions.

Answer 1

Try something like this:

var baseQuery = db.PartnerServiceBrands.AsNoTracking().AsQueryable();

if(filterTrailer.hasValue){
    baseQuery = baseQuery.Where(x=> x.TrailerService == filterTrailer.Value);
}

if(filterServiceId.hasValue){
    baseQuery = baseQuery.Where(x=>x.ServiceId == filterServiceId); 
}

if(filterServiceId.hasValue){
    baseQuery = baseQuery.Where(x=>x.BrandId == filterBrandId); 
}

var results = baseQuery.Select(x=>x.Partner)
                       .ToList();

TODO: add also a group by to not have duplicated Partners. Adapt the final "Select" to obtain the necessary extra info you need.

Answer 2

flip your logic around.

if (search.ServiceId == 0)
{
    return new Partner[] {};
}

if (search.BrandId == 0)
{
    return new Partner[] {};
}

if (search.TrailerService != true)
{
    return new Partner[] {};
}

return dbContext.Partners
    .Where(p => p.PartnerServiceBrands
                 .Select(psb => psb.Service.Id)
                 .Contains(search.ServiceId))
    .Where(p => p.PartnerServiceBrands
                 .Select(psb => psb.Brand.Id)
                 .Contains(search.BrandId))
    .Where(x => x.PartnerServiceBrands
                 .Any(y => y.TrailerService == true));