Patrick Patrick - 17 days ago 7
C# Question

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

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

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.

Comments