Using Linq to bring back last 3,4...n orders for every customer

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.

Note: Customer 1 may have just made 12 orders in the last hr; but customer 2 may not have made any since last week.

I cant for the life of me work out how to write query in linq (lambda expressions) to get the data set back.

Any good ideas?

Edit: 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.

@CoreySunwold

My table Looks a bit like this:

MessageID, WebserviceID, SentTime, Status, Message, Error,

Or from a customer/order context if it makes it easer:

OrderID, CustomerID, StatusChangedDate, Status, WidgetName, Comments

Edit 2: I eventually worked out something

(Hat tip to @StephenChung who basically came up with the exact same, but in classic linq)

var q = myTable.Where(d => d.EndTime > DateTime.Now.AddDays(-1))

.GroupBy(g => g.ConfigID)

.Select(g =>new { ConfigID = g.Key, Data = g.OrderByDescending(d => d.EndTime) .Take(3).Select(s => new { s.Status, s.SentTime }) }).ToList();

It does take a while to execute. So I am not sure if this is the most efficient expression.

This should give the last 3 orders of each customer (if having orders at all):

from o in db.Orders
group o by o.CustomerID into g
select new {
  CustomerID=g.Key,
  LastOrders=g.OrderByDescending(o => o.TimeEntered).Take(3).ToList()
}

However, I suspect this will force the database to return the entire Orders table before picking out the last 3 for each customer. Check the SQL generated.

If you need to optimize, you'll have to manually construct a SQL to only return up to the last 3, then make it into a view.

You can use SelectMany for this purpose:

customers.SelectMany(x=>x.orders.OrderByDescending(y=>y.Date).Take(n)).ToList();

How about this? I know it'll work with regular collections but don't know about EF.

yourCollection.OrderByDescending(item=>item.Date).Take(n);

var ordersByCustomer = 
db.Customers.Select(c=>c.Orders.OrderByDescending(o=>o.OrderID).Take(n));

This will return the orders grouped by customer.

var orders = orders.Where(x => x.CustomerID == 1).OrderByDescending(x=>x.Date).Take(4);

This will take last 4 orders. Specific query depends on your table / entity structure.

Btw: You can take x as a order. So you can read it like: Get orders where order.CustomerID is equal to 1, OrderThem by order.Date and take first 4 'rows'.

Somebody might correct me here, but i think doing this is linq with a single query is probably very difficult if not impossible. I would use a store procedure and something like this

select 
    *
    ,RANK() OVER  (PARTITION BY c.id ORDER BY o.order_time DESC) AS 'RANK'
from 
    customers c 
inner join 
    order o
on 
    o.cust_id = c.id
where 
    RANK < 10 -- this is "n"

I've not used this syntax for a while so it might not be quite right, but if i understand the question then i think this is the best approach.