I have this entities:
Product
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
}
PriceList
public class PriceList
{
public int Id { get; set; }
public string Name { get;set; }
}
PriceListProduct
public class PriceListProduct
{
public int Id { get; set; }
public int PriceListId { get; set; }
public int ProductId { get; set; }
public virtual Product Product { get; set; }
}
Question is, how can I get the products not in Price List using LINQ?
My first thought was the use of Contains, but the list of products could be greater than 100000, if Contains is translate as a query like WHERE NOT IN clause, SQL has an approximate limit of 2000 parameters, so besides performance, I think this is not the best approach.
Is there another way? Should I use raw queries?
Update # 1
I'm trying to understand GroupJoin following @Indregaard answer. So far I have this.
var productsWithNoPrice = db.Product()
.GroupJoin(db.PriceListProduct().Where(plp => plp.PriceListId == 2)
.Select(plp => plp.Product),
p => p.Id,
plp => plp.Id,
(p, product) => new { p.Id, Product = product })
.Where(p => !p.Product.Any())
.Select(p => p.Product);
With the filter
.Where(plp => plp.PriceListId == 2)
I'm filtering products from Price List with Id 2. I think this is close but the query generated by SQL returns a number of rows that corresponding to the number of products that not exists in the Price List but every single column is null.
Basically what I need is a query like this
select * from Product p
left join PriceListProduct plp on plp.ProductId = p.Id and plp.PriceListId = 2
where plp.Id is null