CODEWITHSUNDEEP
c#entity-frameworklinqiqueryable
Goba
Goba

Reputation: 13

One to many and search in many (IQueryable) (linq)

I have this kind of relationship in my database :

Order => historyStatus => Status

Order contains a list of HistoryStatus and HistoryStatus contains one Status.

My use case is: search all Order with a determined Status and get the latest HistoryStatus (based on date)

How can I do that in linq to entities and IQueryable?

Here are the entities :

public class Order 
{
   public int ID { get; set; }

   public virtual ICollection<HistoryStatusOrder> HistoriesStatusOrder { get; set; }
}

public class HistoryStatusOrder 
{
    public int ID { get; set; }
    public DateTime Date { get; set; }
    public string StatusOrderCode { get; 
    public int  OrderId { get; set; }

    public virtual StatusOrder StatusOrder { get; set; }
    public virtual Order Order { get; set; }
}

public class StatusOrder : IEntityBase<string>
{
    public string ID { get; set; }

    public virtual ICollection<HistoryStatusOrder> HistoriesStatusOrder { get; set; }
}

Here is my Search method:

// do multiple searches
if (searchOrderVM.OrderNumber != null)
    queryable = queryable.Where(x => x.OrderNumber.ToString().Contains(searchOrderVM.OrderNumber.ToString()));

// Next 
if (searchOrderVM.SelectedOrdersStatus != null)
{
    // Need to return queryable = ....
}

// Next search...
....

// Finally I do .ToList() to launch the query in database.

Thanks for your help :)

Upvotes: 0

Views: 805

Answers (1)

Ben Jenkinson
Ben Jenkinson

Reputation: 1834

This would find all Orders where the latest HistoryStatusOrder for that Order had a particular StatusOrderCode:

var results = orders.Where (o => o.HistoriesStatusOrder
    .OrderByDescending (hso => hso.Date)
    .First().StatusOrderCode == "foobar");

(Assuming that each Order always had at least one HistoryStatusOrder)

You can write this query in a different way to preserve the IQueryable<Order>:

var results = orders
    .Select (o => new {
        Order = o,
        LatestHistoryStatusOrder = o.HistoriesStatusOrder
            .OrderByDescending (hso => hso.Date)
            .First()
    })
    .Where (x => x.LatestHistoryStatusOrder.StatusOrderCode == "foobar")
    .Select (x => x.Order);

foreach(var order in results)
{
    // Here you can access:
    //  order.ID
}

If you want to keep access to the LatestHistoryStatusOrder, you can skip the last .Select(x => x.Order) statement:

var results = orders
    .Select (o => new {
        Order = o,
        LatestHistoryStatusOrder = o.HistoriesStatusOrder
            .OrderByDescending (hso => hso.Date)
            .First()
    })
    .Where (x => x.LatestHistoryStatusOrder.StatusOrderCode == "foobar");

foreach(var item in results)
{
    // Here you can access:
    //   item.Order.ID
    //   item.LatestHistoryStatusOrder
}

Upvotes: 2

Related Questions