CODEWITHSUNDEEP
c#entity-frameworklinq-to-entities
Volnikh
Volnikh

Reputation: 31

EF get list of parent entities that have list of child

I have to next 2 entities in my project

public class Product
{
    public Product()
    {
        this.ProductImages = new HashSet<ProductImage>();
        this.ProductParams = new HashSet<ProductParam>();
    }
    public int ID { get; set; }
    public int BrandID { get; set; }
    public int CodeProductTypeID { get; set; }
    public string SeriaNumber { get; set; }
    public string ModelNumber { get; set; }
    public decimal Price { get; set; }
    public bool AvailableInStock { get; set; }

    public virtual Brand Brand { get; set; }
    public virtual CodeProductType CodeProductType { get; set; }
    public virtual ICollection<ProductImage> ProductImages { get; set; }
    public virtual ICollection<ProductParam> ProductParams { get; set; }

}

public class ProductParam
{
    public int Id { get; set; }
    public int ProductId { get; set; }
    public int CodeProductParamId { get; set; }
    public string Value { get; set; }

    public virtual Product Product { get; set; }
    public virtual CodeProductParam CodeProductParam { get; set; }
}

and I want to get list of Products which has list of specified parameters

var prodParamCritria = new List<ProductParam>() 
{
new ProductParam(){CodeProductParamId =1, Value="Black" }, 
new ProductParam(){CodeProductParamId =2, Value="Steal"}
};

in sql I can do it by using EXISTS clause twise 

SELECT *
FROM   Products p
WHERE  EXISTS (
           SELECT *
           FROM   ProductParams pp
           WHERE  pp.ProductId = p.ID
                  AND (pp.CodeProductParamId = 1 AND pp.[Value] = N'Black')
       )
       AND EXISTS (
               SELECT *
               FROM   ProductParams pp
               WHERE  pp.ProductId = p.ID
                      AND pp.CodeProductParamId = 2
                      AND pp.[Value] = N'Steal'
           )

How can i get same result by EF methods or linq

Upvotes: 3

Views: 2470

Answers (3)

Kodre
Kodre

Reputation: 181

I suppose something like that should work

db.Product.Where(x => x.ProductParams.FirstOrDefault(y => y.CodeProductParamId == 1) != null && x.ProductParams.FirstOrDefault(y => y.CodeProductParamId == 2) != null).ToList();

or better

db.Product.Where(x => x.ProductParams.Any(y => y.CodeProductParamId == 1) && x.ProductParams.Any(y => y.CodeProductParamId == 2)).ToList();

Ok, if you need to make query on parameters in list prodParamCriteria it will look like this:

db.Product.Where(x => prodParamCritria.All(c=> x.ProductParams.Any(p=>p.CodeProductParamId == c.CodeProductParamId && p.Value== c.Value))).ToList();

I forgot that complex types cannot be used in query database, so i propose you to convert your prodParamCriteria to dictionary and use it in query

Dictionary<int, string> dctParams = prodParamCritria.ToDictionary(x => x.CodeProductParamId , y=>y.Value);
db.Product.Where(x => dctParams.All(c => x.ProductParams.Any(p=> p.CodeProductParamId == c.Key && p.Value== c.Value))).ToList();

Upvotes: 1

ocuenca
ocuenca

Reputation: 39386

Try this:

  var products= db.Products.Where(p=>p.ProductParams.Any(pp=>pp.CodeProductParamId == 1 && pp.Value == "Black") && 
                                     p.ProductParams.Any(pp=>pp.CodeProductParamId == 2 && pp.Value == "Steal"));

Update

The problem in work with that list of ProductParam to use it as a filter is that EF doesn't know how to translate a PodructParam object to SQL, that's way if you execute a query like this:

 var products2 = db.Products.Where(p => prodParamCritria.All(pp => p.ProductParams.Any(e => pp.CodeProductParamId == e.CodeProductParamId && pp.Value == e.Value)));

You will get an NotSupportedException as you comment in the answer of @BostjanKodre.

I have a solution for you but probably you will not like it. To resolve that issue you could call the ToList method before call the Where. This way you will bring all products to memory and you would work with Linq to Object instead Linq to Entities, but this is extremely inefficient because you are filtering in memory and not in DB.

var products3 = db.Products.ToList().Where(p => prodParamCritria.All(pp => p.ProductParams.Any(e => pp.CodeProductParamId == e.CodeProductParamId && pp.Value == e.Value)));

If you want filter by one criteria then this could be more simple and you are going to be able filtering using a list of a particular primitive type. If you, for example, want to filter the products only by CodeProductParamId, then you could do this:

  var ids = new List<int> {1, 2};
  var products = db.Products.Where(p => ids.All(i=>p.ProductParams.Any(pp=>pp.CodeProductParamId==i))).ToList();

This is because you are working with a primitive type and not with a custom object.

Upvotes: 1

tschmit
tschmit

Reputation: 7800

another variation:

IEnumerable<Int32> lis = prodParamCritria.Select(x => x.CodeProductParamId).ToList();
var q = Products.Select(
        x => new { 
            p = x,
            cs = x.ProductParams.Where(y => lis.Contains(y.Id))
        }
    ).Where(y => y.cs.Count() == lis.Count()).
    ToList();

with a named class like (or maybe without, but not in linqpad)

public class daoClass {
    public Product p {get; set;}
    public Int32 cs {get; set;}
}

IEnumerable<Int32> lis = prodParamCritria.Select(x => x.CodeProductParamId).ToList();
var q = Products.Select(
        x => new daoClass { 
            p = x,
            cs = x.ProductParams.Where(y => lis.Contains(y.Id))
        }
    ).Where(y => y.cs.Count() == lis.Count()).
    SelectMany(y => y.p).
    ToList();

Upvotes: 0

Related Questions