CODEWITHSUNDEEP
c#linqentity-framework
tinmac
tinmac

Reputation: 2580

How can I make a more efficient Entity Framework LINQ call than a nested loop

Is there a more efficent way to write this LINQ statement?

Feature uses the same model as Product (as they are almost identical).

var products = db.Products.Where(q => q.IsFeature == false).ToList();
        foreach (Product pr in products)
        {
            var features = db.Products.Where(q => q.ParentID == pr.ID).ToList();
            foreach(Product feature in features)
            {
                pr.Features.Add(feature);
            }
        }

And the model:

    public class Product
{
    //START
    public Product()
    {
        Features = new HashSet<Product>();
    }

    public int ID { get; set; }
    public string Code { get; set; }
    public string Name { get; set; }
    public bool IsFeature { get; set; }
    public int ParentID { get; set; }// If IsFeature is true we need to know its parent

    //Nav Props
    public ICollection<Product> Features { get; set; }// Features can be represented with the same model as product as they are almost identical
}

This does what I need ie a List of Product (Products), each with its own List of Feature (Features) but are there more elegant/efficient alternatives?

Upvotes: 2

Views: 505

Answers (4)

afrazier
afrazier

Reputation: 4902

You need to use GroupJoin for this. This is exactly what it's designed for.

var products = db.Products.Where(p => !p.IsFeature)
    .GroupJoin(db.Products, p => p.ID, q => q.ParentID, (p, eq) => {
        p.Features = eq.ToList();
        return p;
    })
    .ToList();

This code is untested, but should work.

Upvotes: 0

Fabjan
Fabjan

Reputation: 13676

You're almost there you can use Addrange method of List collection:

var products = db.Products.Where(q => !q.IsFeature).ToList();
foreach(Product pr in db.Products)
  if(!pr.IsFeature) pr.Features.AddRange(db.Products.Where(q => q.ParentID == pr.ID));

P.S. other idea is to extract features sort them, convert to array and use for loop :

var features = db.Products.Where(q => q.IsFeature).OrderBy(f => f.ParentID).ToArray();    
p = db.Products.First(p => p.ID == features[0].ParentID);
for(int i = 0; i < features.Length; i++ ) 
{
   if(i > 0 && features[i - 1].ParentID != features[i].ParentID)
     p = db.Products.First(p => p.ID == features[i].ParentID);
   p.Features.Add(features[i]);
}

Upvotes: 0

ΩmegaMan
ΩmegaMan

Reputation: 31686

If this operation has a complexity which rises to the point where the generated Linq's SQL becomes a hindrance or this business logic is done in multiple places; consider placing the logic in a stored procedure.

Once in the database simply map the results in EF which later can easily be consumed by the code. Hence the complexity is moved off of the client and directly on the database.

Steps (Database First)

  1. Create Stored procedure.
  2. Update the model from the database and choose the stored procedure.
  3. Verify the resultant mappings in EF's Model Browser.
  4. Consume the named stored procedure off of the data context.

I discuss the ins-and-outs of mapping stored procedures on my blog article Entity Framework Stored Procedure Instructions.

Upvotes: 2

paul
paul

Reputation: 22001

What you need is a join:

var productFeatures = 
    db.Products.Where(q => !q.IsFeature)
    .Join(db.Products, p => p.ID, f => f.ParentId, (p, f) => new { product: p, features: f.ToList() })
    .ToList();

For each object in db.Products.Where(q => !q.IsFeature) this will find each of the db.Products that have a ParentId equal to its ID field.

The results will be projected into a List() of anonymous types with the fields product (Product) and features (List<Product>)

Upvotes: 4

Related Questions