CODEWITHSUNDEEP
c#linqentity-framework
MichaelMcCabe
MichaelMcCabe

Reputation: 523

Using Linq to order multiple lists from multiple tables

At the moment, I have multiple tables in my Database with slightly varying columns to define different "history" elements for an item.

So I have my item table;

int ItemId {get;set}
string Name {get;set}
Location Loc {get;set}    
int Quantity {get;set}

I can do a few things to these items like Move, Increase Quantity, Decrease Quantity, Book to a Customer, "Pick" an item, things like that. So I have made multiple "History Tables" as they have different values to save E.g

 public class MoveHistory
 {
    public int MoveHistoryId { get; set; }

    public DateTime Date { get; set; }

    public Item Item { get; set; }

    public virtual Location Location1Id { get; set; }

    public virtual Location Location2Id { get; set; }
 }

 public class PickingHistory
 {
    public int PickingHistoryId { get; set; }

    public DateTime Date { get; set; }

    public Item Item { get; set; }

    public int WorksOrderCode { get; set; }
 }

This is fine apart from where I want to show a complete history for an item displayed in a list;

Item 123 was moved on 23/02/2013 from Location1 to Location2

Item 123 was picked on 24/02/2013 from work order 421

I am using Entity Framework, .NET 4.5, WPF, and querying using Linq but cannot figure a way of taking these lists of history elements, and ordering them out one by one based on their date.

I can think of messy ways, like one single history table with columns used if required. Or even create a third list containing the date and what list it came from, then cycle through that list picking the corresponding contents from the corresponding list. However, I feel there must be a better way!

Any help would be appreciated.

Upvotes: 1

Views: 785

Answers (3)

Rob Lyndon
Rob Lyndon

Reputation: 12651

If you implement a GetDescription() method on your history items (even as an extension method), you can do this:

db.PickingHistory.Where(ph => ph.Item.ItemId == 123)
    .Select(ph => new { Time = ph.Date, Description = ph.GetDescription() })
.Concat(db.MoveHistory.Where(mh => mh.ItemId == 123)
    .Select(mh => new { Time = mh.Date, Description = mh.GetDescription() })
.OrderByDescending(e => e.Time).Select(e => e.Description);

Upvotes: 1

Sheridan
Sheridan

Reputation: 69959

If you are implementing this in order to provide some sort of undo/redo history, then I think that you're going about it in the wrong way. Normally, you would have one collection of ICommand objects with associated parameter values, eg. you store the operations that have occurred. You would then be able to filter this collection for each item individually.

If you're not trying to implement some sort of undo/redo history, then I have misunderstood your question and you can ignore this.

Upvotes: 0

Ryan Amies
Ryan Amies

Reputation: 4922

The problem you are facing is that you're trying to use your database model as a display model and obviously are failing. You need to create a new class that represents your history grid and then populate it from your various queries. From your example output the display model may be:

public class HistoryRow{
    public DateTime EventDate { get; set; }
    public string ItemName { get; set; }
    public string Action { get; set; }
    public string Detail { get; set; }
}

You then load the data into this display model:

var historyRows = new List<HistoryRow>();

var pickingRows = _db.PickingHistory.Select(ph => new HistoryRow{
    EventDate = ph.Date,
    ItemName = ph.Item.Name,
    Action = "picked",
    Detail = "from works order " + ph.WorksOrderCode);
historyRows.AddRange(pickingRows);

var movingRows = _db.MoveHistory.Select(mh => new HistoryRow{
    EventDate = mh.Date,
    ItemName = ph.Item.Name,
    Action = "moved",
    Detail = "from location " + mh.Location1Id + " to location " + mh.Location2Id);
historyRows.AddRange(movingRows );

You can repeatedly add the rows from various tables to get a big list of the HistoryRow actions and then order that list and display the values as you wish.

foreach(var historyRow in historyRows)
{
    var rowAsString = historyRow.ItemName + " was " + historyRow.Action.....;
    Console.WriteLine(rowAsString);
}

Upvotes: 1

Related Questions