asp.net mvc - Apply filters to a table based on conditions using LINQ and MVC C# -

i trying create list of records (partners) based on several list of conditions. problem sql generated linq selecting partners respect @ least 1 of conditions, , want partners respect conditions have 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; }  }  // partner can have multiple services, brands , 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 apply conditions based on filters 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 is:

select[extent1].[id] as[id], [extent1].[name] as[name]     where(exists (select 1 as[c1]     [dbo].[partnerservicebrands] [extent2]     ([extent1].[id] = [extent2].[partner_id]) and([extent2].[service_id] = 7)                         )) and(exists (select 1 as[c1]                                         [dbo].[partnerservicebrands] [extent3]                                         ([extent1].[id] = [extent3].[partner_id]) and([extent3].[brand_id] = 1153)                         )) and(exists (select 1 as[c1]                                         [dbo].[partnerservicebrands] [extent4]                                         ([extent1].[id] = [extent4].[partner_id]) and(1 = [extent4].[trailerservice])                         )) 

with query, partners have least 1 service trailer service, , that's not want. want partners respect conditions.

try 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 group not have duplicated partners. adapt final "select" obtain necessary info need.