CODEWITHSUNDEEP
c#sqllinqentity-framework
Matt Cashatt
Matt Cashatt

Reputation: 24208

Using LINQ, How do I return objects from a GroupBy clause?

Thanks for looking!

Background

Within my C# code, I am calling a stored procedure from a MSSQL database that returns a history of orders that a user has made. I use the Entity Framework for this. Here is the code:

var db = new CustomerEntities();
return db.GetOrderHistoryByUserId(id);

The output of this stored procedure is a list of orders with multiple records having the same OrderNumber because there may have been multiple products in a given order. So I modified my code to look like this:

var db = new CustomerEntities();
return db.GetOrderHistoryByUserId(id).GroupBy(p => p.OrderNumber);

I was hoping to now have a list of Order objects with nested Product objects, but instead this code essentially produced the same response as before.

Ultimately, I just want to convert this query into a JSON response that looks something like this:

Orders : [
{
OrderNumber : 1,
OrderTotal: $500,
Products: [
  {ProductSku : 11111, ProductPrice: $200},
  {ProductSku : 22222, ProductPrice: $300}
]
}
]

I am using MVC 4 to aid in producing the JSON output, so I am already clear on that part, I just need to know how to consume the results of the stored procedure in a manner that produces an array of objects with the desired structure.

Question

Is there a way of producing this desired object structure with the original LINQ call to the stored procedure, or am I going to need to iterate the results of the stored procedure and construct a new object?

Thanks for your help!

Upvotes: 0

Views: 842

Answers (2)

Lukas Winzenried
Lukas Winzenried

Reputation: 1919

var serializer = new JavaScriptSerializer();
var rows = db.GetOrderHistoryByUserId(id);
var json = serializer.Serialize(
       new { Orders = rows.GroupBy(o => o.Number)
                          .Select(g =>
                                new
                                {
                                    OrderNumber = g.Key,
                                    OrderTotal = g.Sum(o => o.Price),
                                    Products = g.Select(
                                       o => new {SKU = o.Sku, ProductPrice = o.Price}
                                    )
                                 })
            });

Upvotes: 2

Matt Cashatt
Matt Cashatt

Reputation: 24208

I actually did search for help on this earlier before posting, but I happened upon the answer just now. I needed a Select clause:

var db = new CustomerEntities();
return db.GetOrderHistoryByUserId(id).GroupBy(o => o.OrderNumber).Select(g => new {Order = g.Key, Items = g});

Click here to see the webpage that finally helped me.

Hope this helps someone.

Upvotes: 1

Related Questions