Databases
StackOverflow2013
Postsdbo
POLambda expression Group by in C#
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLambda expression Group by in C#
    text
    copied!<p>I would like to group my LINQ query by <code>ItemNumber</code> and return the whole table with the total for <code>Quantity</code>.</p> <pre><code>Example: ItemNumber - ItemName - Quantity 100 Item1 1 150 Item2 2 100 Item1 2 200 Item3 1 150 Item2 2 Should be: ItemNumber - ItemName - Quantity 100 Item1 3 150 Item2 4 200 Item3 1 </code></pre> <p>This is the query I am trying to group:</p> <pre><code>public IQueryable&lt;WebsiteOrderStatus&gt; GetOrderStatusByAccountNumberWithoutDeleted (string accountNumber) { return db.WebsiteOrderStatus .Where(x =&gt; x.AccountNumber == accountNumber &amp;&amp; x.LastUpdatedStatus != 1); } </code></pre> <p>And my best result so far(this can't compile though):</p> <pre><code>public IQueryable&lt;IGrouping&lt;Int32?, WebsiteOrderStatus&gt;&gt; lol(string accountNumber) { db.WebsiteOrderStatus .Where(x =&gt; x.AccountNumber == accountNumber &amp;&amp; x.LastUpdatedStatus != 1) .GroupBy(g =&gt; g.ItemNumber) .Select(g =&gt; new { g.Key.ItemNumber, Column1 = (Int32?)g.Sum(p =&gt; p.Quantity) }); } </code></pre> <p><strong>EDIT:</strong></p> <p>Thanks for the replies everyone, I must face it. Theese anonymous types are pretty hard to work with in my opinion, so I found another solution.</p> <p>I made another method, which sums the quantity of the users items and grouped the first one.</p> <pre><code>public IQueryable&lt;WebsiteOrderStatus&gt; GetOrderStatusByAccountNumberWithoutDeleted(string accountNumber) { return db.WebsiteOrderStatus.Where(x =&gt; x.AccountNumber == accountNumber &amp;&amp; x.LastUpdatedStatus != 1).GroupBy(x =&gt; x.ItemNumber).Select(grp =&gt; grp.First()); } public int GetQuantityOfUsersItem(string accountNumber, string itemNumber) { return db.WebsiteOrderStatus.Where(x =&gt; x.ItemNumber == itemNumber &amp;&amp; x.AccountNumber == accountNumber).Sum(x =&gt; x.Quantity); } </code></pre> <p>At the page where I have my gridview I did:</p> <pre><code>var query = websiteOrderStatusRep.GetOrderStatusByAccountNumberWithoutDeleted(AppSession.CurrentLoginTicket.AccountNumber).Select(x =&gt; new { x.ItemName, x.ItemNumber, x.FormatName, x.Price, x.Status, x.Levering, Quantity = websiteOrderStatusRep.GetQuantityOfUsersItem(x.AccountNumber, x.ItemNumber)}); </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload