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)

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

Answer 2

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