How could I accomplish this in Linq?

Question

I have a feeling the following might be an atrocity, and maybe that's why it doesn't work. So here I am, asking for your help. :-)

Also please help me edit this question. I do not know what would be an appropiate title for it or a better way to formulate it. (Like you have probably guessed by now, English is not my native language).

Consider a class like:

public class Order { public int FareCode; public int Quantity; }

Then a Linq query like:

var orders = new []{ new Order {...}, ...}; var fareCodes = orders.Select(o => o.FareCode).ToArray(); using(var dc = new datacontext()) { var query = dc.Fares .Where(f => fareCodes.Contains(f.FareCode)) .Select(f => new { Fare = f, Quantity = orders.Single(o => o.FareCode == f.FareCode).Quantity //<-- This is not right! }) }

So Quantity = Orders.Single(o => o.FareCode == f.FareCode).Quantity ins't right. So what would be another option to accomplish this?

UPDATE: The reason it does not work is because the runtime throws this exception: Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator

Are you trying to update Quantity property values from a data source? — Jeff LaFay
– Jeff LaFay, Commented Apr 30, 2012 at 19:05
I'm trying to get the Fare Entities with the corresponding quantity. — Andres A.
– Andres A., Commented Apr 30, 2012 at 19:11
I'm trying to build an Service Order with the especified Fares (Tarifas in Spanish) — Andres A.
– Andres A., Commented Apr 30, 2012 at 19:12

empi · Accepted Answer · 2012-04-30 19:22:12Z

According to your last comment, try this solution:

var query = Fares .Where(f => fareCodes.Contains(f.FareCode)).ToList() .Select(f => new { Fare = f, Quantity = Orders.Single(o => o.FareCode == f.FareCode).Quantity });

Uchendu Nwachuku · Accepted Answer · 2012-04-30 19:48:51Z

So it looks like for each Order, you want to change the FareCode into the associated Fare, and sum the quantities of each Fare without hitting your datacontext for every single Order.

I would use GroupBy/ToDictionary combo on the Orders list, and ToDictionary() on the Fares list:

// generates a dictionary of fare codes and total quantity of orders per fare code var fareQuantities = Orders.GroupBy(o => o.FareCode).ToDictionary(og => og.Key, og => og.Sum(o => o.Quantity)); using(var dc = new datacontext()) { var query = dc.Fares .Where(f => fareQuantities.Keys.Contains(f.FareCode)) .ToDictionary(f => f, f => fareQuantities[f.FareCode]); // query is now a dictionary of fares and the total quantities of each fare ordered }

Hope that helps.

Collectives™ on Stack Overflow

How could I accomplish this in Linq?

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related