CODEWITHSUNDEEP
databaselinqentity-frameworklinq-to-sql
Guillaume Schuermans
Guillaume Schuermans

Reputation: 906

Loading the last related record instantly for multiple parent records using Entity framework

Does anyone know a good approach using Entity Framework for the problem described below?

I am trying for our next release to come up with a performant way to show the placed orders for the logged on customer. Of course paging is always a good technique to use when a lot of data is available I would like to see an answer without any paging techniques.

Here's the story: a customer places an order which gets an orderstatus = PENDING. Depending on some strategy we move that order up the chain in order to get it APPROVED. Every change of status is logged so we can see a trace for statusses and maybe even an extra line of comment per status which can provide some extra valuable information to whoever sees this order in an interface.

So an Order is linked to a Customer. One order can have multiple orderstatusses stored in OrderStatusHistory.

In my testscenario I am using a customer which has 100+ Orders each with about 5 records in the OrderStatusHistory-table. I would for now like to see all orders in one page not using paging where for each Order I show the last relevant Status and the extra comment (if there is any for this last status; both fields coming from OrderStatusHistory; the record with the highest Id for the given OrderId).

There are multiple scenarios I have tried, but I would like to see any potential other solutions or comments on the things I have already tried.

In the end I am very familiar with SQL and Stored procedures, but since the rest of the data-layer uses Entity Framework I would like to stick to it as long as possible, even though I have my doubts about performance. Using the SQL approach I would write something like this:

WITH cte (RN, OrderId, [Status], Information)
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY OrderId ORDER BY Id DESC), OrderId,    [Status], Information
FROM OrderStatus
)
SELECT o.Id, cte.[Status], cte.Information AS StatusInformation, o.* FROM [Order] o
INNER JOIN cte ON o.Id = cte.OrderId AND cte.RN = 1
WHERE CustomerId = @CustomerId
ORDER BY 1 DESC;

which returns all orders for the customer with the statusinformation provided by the Common Table Expression.

Does anyone know a good approach using Entity Framework?

Upvotes: 0

Views: 185

Answers (1)

david.s
david.s

Reputation: 11403

Something like this should work as you want (make only 1 db call), but I didn't test it:

var result = from order in context.Orders
             where order.CustomerId == customerId
             let lastStatus = order.OrderStatusHistory.OrderBy(x => x.Id).Last()
             select new
             {
                 //you can return the whole order if you need
                 //Order = order,
                 //or only the information you actually need to display
                 Number = order.Number,
                 Status = lastStatus.Status,
                 ExtraComment = lastStatus.ExtraComment,
             };

This assumes your Order class looks something like this:

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public string Number { get; set; }
    ...
    public ICollection<OrderStatusHistory> OrderStatusHistory { get; set; }
}

If your Order class doesn't have something like an ICollection<OrderStatusHistory> OrderStatusHistory property then you need to do a join first. Let me know if that is the case and I will edit my answer to include the join.

Upvotes: 3

Related Questions