Tuesday, 6 August 2013

Using Linq to order multiple lists from multiple tables

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.

No comments:

Post a Comment