Conditional filter using LINQ with C#

Question

I need to get a list of records and filter them based on a condition: if serviceId = 1 then I need to combine that result with the result for serviceId = 5.

Models:

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

public class PartnerServiceBrand
{
    [Key]
    public int Id { get; set; }

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

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

My code for just one filter:

var serviceId = 1;

var partners = dbContext.Partners.Where(p => p.PartnerServiceBrands.Select(psb => psb.Service.Id).Contains(serviceId));

I tried to do:

if (serviceId == 1)
{
    var partners2 = dbContext.Partners.Where(p => p.PartnerServiceBrands.Select(psb => psb.Service.Id).Contains(5));

    partners = partners.Union(partners2);  // Error
}

I also tried to use Contains with a List<int>, but I was not able to get it up and running.

EDIT

The error I get is:

Exception:Thrown: "The geography data type cannot be selected as DISTINCT because it is not comparable." (System.Data.SqlClient.SqlException)

It's not clear what you want to do. What do you need in the first list? Do you want to get all the partners that have a Service with id = 1? — ataravati
– ataravati, Commented May 13, 2015 at 0:44
@ataravati hi, I want to get all the partners that have the serviceId=5 joined with all the partners that have the serviceId=1 — Patrick
– Patrick, Commented May 13, 2015 at 21:24
@techkid hi, sorry I have edited the question with the error and the missing property that is causing the error (public DbGeography Location { get; set; }) — Patrick
– Patrick, Commented May 13, 2015 at 21:30

Brendan Green · Accepted Answer · 2015-05-13 01:14:17Z

1

My take on this.

Given the list of serviceIds that you want to filter on:

var serviceIds = new List<int>{1, 5};

var partners = dbContext.Services
    .Where(ser => serviceIds.Contains(ser.Id).SelectMany(ser => ser.PartnerServiceBrands)
    .Select(psb => psb.Partner).Distinct();

answered May 13, 2015 at 1:14

Brendan Green

11.9k5 gold badges47 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Patrick Over a year ago

Hi, I have to add a ")" after the Contains(ser.Id) to avoid red editor underline, but in execution I get the error I have included in the question

jjj · Accepted Answer · 2015-05-13 22:56:43Z

1

You're getting an error because Union() generates UNION ALL with SELECT DISTINCT. As an aside Concat() produces just a UNION ALL.

In any case, I think you should be able to get what you're looking for with something like below:

var serviceIds = new List<int>{ 1, 5 };
var partners = dbContext.Partners
    .Where(p => p.PartnerServiceBrands.Any(psb => serviceIds.Contains(psb.Service.Id))

edited May 13, 2015 at 22:56

answered May 13, 2015 at 22:18

jjj

5,0071 gold badge19 silver badges39 bronze badges

Comments

Huy Hoang Pham · Accepted Answer · 2015-05-13 00:50:49Z

0

Maybe this will works:

var partners = dbContext.Services
    .Where(ser => ser.Id == 1 || s.Id == 5).SelectMany(ser => ser.PartnerServiceBrands)
    .Select(psb => psb.Partner).Distinct();

answered May 13, 2015 at 0:50

Huy Hoang Pham

4,1471 gold badge19 silver badges28 bronze badges

Comments

jbl · Accepted Answer · 2015-05-13 21:27:26Z

0

Assuming you have a DbSet<Service> as Services, I would try :

var serviceId = ...;

var partners = dbContext.Services
      .Where(svc => svc.Id == serviceId || (serviceId == 1 && svc.Id == 5))
      .SelectMany(svc => svc.PartnerServiceBrands)
      .Select(psb => psb.Partner).Distinct();

answered May 13, 2015 at 21:27

jbl

15.5k3 gold badges37 silver badges104 bronze badges

1 Comment

Patrick Over a year ago

I get repeated partners :(

Collectives™ on Stack Overflow

Conditional filter using LINQ with C#

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related