Databases
StackOverflow2013
Postsdbo
POUsing Linq to bring back last 3,4...n orders for every customer
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing Linq to bring back last 3,4...n orders for every customer
    text
    copied!<p>I have a database with customers orders. I want to use Linq (to EF) to query the db to bring back the last(most recent) 3,4...n orders for every customer.</p> <p>Note: Customer 1 may have just made 12 orders in the last hr; but customer 2 may not have made any since last week.</p> <p>I cant for the life of me work out how to write query in linq (lambda expressions) to get the data set back.</p> <p>Any good ideas?</p> <p><strong>Edit:</strong> Customers and orders is a simplification. The table I am querying is actually a record of outbound messages to various web services. It just seemed easer to describe as customers and orders. The relationship is the same. I am building a task that checks the last n messages for each web service to see if there were any failures. We are wanting a semi real time Health status of the webservices.</p> <p>@CoreySunwold<br> My table Looks a bit like this:<br> <strong>MessageID, WebserviceID, SentTime, Status, Message, Error,</strong></p> <p>Or from a customer/order context if it makes it easer:<br> <strong>OrderID, CustomerID, StatusChangedDate, Status, WidgetName, Comments</strong></p> <p><strong>Edit 2:</strong> I eventually worked out something<br> (Hat tip to @StephenChung who basically came up with the exact same, but in classic linq) </p> <p><code>var q = myTable.Where(d =&gt; d.EndTime &gt; DateTime.Now.AddDays(-1))<br> .GroupBy(g =&gt; g.ConfigID)<br> .Select(g =&gt;new { ConfigID = g.Key, Data = g.OrderByDescending(d =&gt; d.EndTime) .Take(3).Select(s =&gt; new { s.Status, s.SentTime }) }).ToList(); </code></p> <p>It does take a while to execute. So I am not sure if this is the most efficient expression.</p>
 

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