Deselecting N + 1 without. Enable

Question

Consider these far-fetched entity objects:

public class Consumer { public int Id { get; set; } public string Name { get; set; } public bool NeedsProcessed { get; set; } public virtual IList<Purchase> Purchases { get; set; } //virtual so EF can lazy-load } public class Purchase { public int Id { get; set; } public decimal TotalCost { get; set; } public int ConsumerId { get; set; } } 

Now let's say that I want to run this code:

 var consumers = Consumers.Where(consumer => consumer.NeedsProcessed); //assume that ProcessConsumers accesses the Consumer.Purchases property SomeExternalServiceICannotModify.ProcessConsumers(consumers); 

By default, this will be caused by selecting N + 1 inside the ProcessConsumers method. It will trigger a query when it lists consumers, then it will grab every 1 to 1 shopping collection. A standard solution to this problem would be to include include:

 var consumers = Consumers.Include("Purchases").Where(consumer => consumer.NeedsProcessed); //assume that ProcessConsumers accesses the Consumer.Purchases property SomeExternalServiceICannotModify.ProcessConsumers(consumers); 

This works well in many cases, but in some complex cases, inclusion can completely destroy performance by orders of magnitude. Is it possible to do something like this:

• Grab my consumers, var customers = _entityContext.Consumers.Where (...). ToList ()
• Grab my purchases, buy var = _entityContext.Purchases.Where (...). ToList ()
• Consumer hydrate. Acquires collections manually from purchases that I have already loaded into memory. Then, when I pass it to ProcessConsumers, it will not trigger any more db requests.

I'm not sure how to do # 3. If you try to access any consumer.Purchases collection that will cause a lazy load (and therefore Select N + 1). Perhaps I need to give consumers the correct type (instead of the proxy type EF), and then download the collection? Something like that:

 foreach (var consumer in Consumers) { //since the EF proxy overrides the Purchases property, this doesn't really work, I'm trying to figure out what would ((Consumer)consumer).Purchases = purchases.Where(x => x.ConsumerId = consumer.ConsumerId).ToList(); } 

EDIT: I rewrote the example a bit to hopefully show the problem more clearly.

Answer 1

If I understand correctly, you want to load as a filtered subset of "Consumers", each of which has a filtered subset of their "Purchases" in 1 request. If not, please forgive my understanding of your intentions. If this is correct, you can do something like:

 var consumersAndPurchases = db.Consumers.Where(...) .Select(c => new { Consumer = c, RelevantPurchases = c.Purchases.Where(...) }) .AsNoTracking() .ToList(); // loads in 1 query // this should be OK because we did AsNoTracking() consumersAndPurchases.ForEach(t => t.Consumer.Purchases = t.RelevantPurchases); CannotModify.Process(consumersAndPurchases.Select(t => t.Consumer)); 

Note that this will NOT work if the Process function expects changes to the consumer object and then returns these changes to the database.

Answer 2

EF will populate the consumer.Purchases collections for you if you use the same context to retrieve both collections:

 List<Consumer> consumers = null; using ( var ctx = new XXXEntities() ) { consumers = ctx.Consumers.Where( ... ).ToList(); // EF will populate consumers.Purchases when it loads these objects ctx.Purchases.Where( ... ).ToList(); } // the Purchase objects are now in the consumer.Purchases collections var sum = consumers.Sum( c => c.Purchases.Sum( p => p.TotalCost ) ); 

EDIT:

The result is only 2 dB of calls: 1 to get the Consumers collection and 1 to get the Purchases collection.

EF will look at each returned Purchase record and look for the corresponding Consumer record from Purchase.ConsumerId . Then it will add the Purchase object to the consumer.Purchases collection for you.

---

Option 2:

If there is a reason why you want to get two lists from different contexts and then link them, I will add another property to the Consumer class:

 partial class Consumer { public List<Purchase> UI_Purchases { get; set; } } 

You can then set this property from the Purchases collection and use it in your interface.

Answer 3

Grab my consumers

 var consumers = _entityContext.Consumers .Where(consumer => consumer.Id > 1000) .ToList(); 

Grab my purchases

 var purchases = consumers.Select(x => new { Id = x.Id, IList<Purchases> Purchases = x.Purchases }) .ToList() .GroupBy(x => x.Id) .Select( x => x.Aggregate((merged, next) => merged.Merge(next))) .ToList(); 

Hydrate the consumer. Acquires collections manually from a purchase that I have already loaded into memory.

 for(int i = 0; i < costumers.Lenght; i++) costumers[i].Purchases = purchases[i]; 

Answer 4

Is it impossible for you to get around the problem with multiple loops or inefficient queries while working on a database - essentially returning a projection instead of a specific entity, as shown below:

 var query = from c in db.Consumers where c.Id > 1000 select new { Consumer = c, Total = c.Purchases.Sum( p => p.TotalCost ) }; var total = query.Sum( cp => cp.Total ); 

I'm not an EF expert, so forgive me if this method does not work.