使用LINQ和MVC c#根据条件对表应用过滤器

本文关键字：条件 应用 过滤器 LINQ MVC 使用 | 更新日期: 2023-09-27 18:17:11

我试图根据几个条件列表创建一个记录(合作伙伴)列表。问题是LINQ生成的SQL正在选择至少符合其中一个条件的合作伙伴，而我只想要符合必须应用的条件的合作伙伴(serviceId或/和branddid和/或trailerservice)。

模型(简体):

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; }
}

我想应用基于过滤器的条件:

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));
}
我得到的查询是: 
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])
                        ))

通过这个查询，我得到的是至少有1个带有拖车服务的服务的合作伙伴，这不是我想要的。我只想要尊重所有条件的合作伙伴。

使用LINQ和MVC c#根据条件对表应用过滤器

试试这样:

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

待办事项:也添加一个组by没有重复的伙伴。

调整最后的"Select"以获得必要的额外信息。

把你的逻辑倒过来。

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));
相关文章：