LINQ query to align master data with details
I have the following classes:
public class Order
{
public Order()
{
Items = new ObservableCollection<OrderItem>();
}
public string Code { get; set; }
public ICollection<OrderItem> Items { get; set; }
}
public class OrderItem
{
public OrderItem()
{
SubItems = new ObservableCollection<SubItem>();
}
public decimal Quantity { get; set; }
public decimal Price { get; set; }
public ICollection<SubItem> SubItems { get; set; }
}
public class SubItem
{
public DateTime Date { get; set; }
public decimal Quantity { get; set; }
public string UserName { get; set; }
}
Now the problem is that I want to show all the data of these classes in a datagrid for example. using code:
Order order = new Order();
order.Code = "123";
order.Items.Add(new OrderItem()
{
Price = 30,
Quantity = 3,
SubItems = new Collection<SubItem>()
{
new SubItem() { Date = DateTime.Now, Quantity = 1, UserName = "User1" },
new SubItem() { Date = DateTime.Now, Quantity = 2, UserName = "User2" }
}
});
order.Items.Add(new OrderItem()
{
Price = 500,
Quantity = 50,
SubItems = new Collection<SubItem>()
{
new SubItem() { Date = DateTime.Now, Quantity = 20, UserName = "User1" },
new SubItem() { Date = DateTime.Now, Quantity = 20, UserName = "User2" },
new SubItem() { Date = DateTime.Now, Quantity = 10, UserName = "User3" }
}
});
I need to show something like this in the DataGrid:
| Order.Code | Item.Price | Item.Quantity | SubItem.Quantity | SubItem.UserName |
| 123 | 30 | 3 | 1 | User1 |
| 123 | 30 | 3 | 2 | User2 |
| 123 | 500 | 50 | 20 | User1 |
| 123 | 500 | 50 | 20 | User2 |
| 123 | 500 | 50 | 10 | User3 |
Looks simple enough, but I just can't do it. The best I could do is put a reference on OrderItem
to SubItem
so I can use it in the databind of the column, but that only works when I have subItems (and if not SubItems
, I still need to show the data OrderItem
). So basically I need to show what I see if I do a SQL SELECT joining 3 tables.
Any linq magic can do this?
source to share
Try this, this is like a connection:
var table = from item in order.Items
from subItem in item.SubItems
select new
{
OrderCode = order.Code,
ItemPrice = item.Price,
ItemQuantity = item.Quantity,
SubItemQuantity = subItem.Quantity,
SubItemUserName = subItem.UserName
};
You can scale this up to multiple orders if you like:
var table = from order in orders
from item in order.Items
from subItem in item.SubItems
select new
{
OrderCode = order.Code,
ItemPrice = item.Price,
ItemQuantity = item.Quantity,
SubItemQuantity = subItem.Quantity,
SubItemUserName = subItem.UserName
};
And here's the link: http://msdn.microsoft.com/en-us/library/bb383978.aspx (see the section "Connection from a proposal")
If collections can be empty, use DefaultIfEmpty (), for example:
var table = from order in orders
from item in order.Items.DefaultIfEmpty(new OrderItem())
from subItem in item.SubItems.DefaultIfEmpty(new SubItem())
select new
{
OrderCode = order.Code,
ItemPrice = item.Price,
ItemQuantity = item.Quantity,
SubItemQuantity = subItem.Quantity,
SubItemUserName = subItem.UserName
};
If you don't pass the DefaultIfEmpty () argument, the "default" element will be empty, so you'll have to deal with that, for example:
var table = from order in orders
from item in order.Items.DefaultIfEmpty()
from subItem in (item != null ? item.SubItems : Enumerable.Empty<SubItem>()).DefaultIfEmpty()
select new
{
OrderCode = order.Code,
ItemPrice = item != null ? item.Price.ToString() : "n/a",
ItemQuantity = item != null ? item.Quantity.ToString() : "n/a",
SubItemQuantity = subItem != null ? subItem.Quantity.ToString() : "n/a",
SubItemUserName = subItem != null ? subItem.UserName : "n/a"
};
Another alternative:
var table = from order in orders
from item in order.Items.DefaultIfEmpty()
from subItem in (item != null ? item.SubItems : Enumerable.Empty<SubItem>()).DefaultIfEmpty()
select new
{
OrderCode = order.Code,
ItemPrice = item != null ? item.Price : default(decimal?),
ItemQuantity = item != null ? item.Quantity : default(decimal?),
SubItemQuantity = subItem != null ? subItem.Quantity : default(decimal?),
SubItemUserName = subItem != null ? subItem.UserName : null
};
source to share
Anti-aliasing using the SelectMany method :
var res = order.Items
.SelectMany(i => i.SubItems, (Item, Sub) => new { Item, Sub })
.Select(r => new { order.Code,
r.Item.Price,
ItemQuantity = r.Item.Quantity,
SubItemQuantity = r.Sub.Quantity,
r.Sub.UserName });
If the collection SubItems
can be empty, you can use the DefaultIfEmpty method :
var res = order.Items
.SelectMany(i => i.SubItems.DefaultIfEmpty(), (Item, Sub) => new { Item, Sub })
.Select(r => new { order.Code,
r.Item.Price,
ItemQuantity = r.Item.Quantity,
SubItemQuantity = r.Sub == null ? null : (Decimal?) r.Sub.Quantity ,
UserName = r.Sub == null ? (string)null : r.Sub.UserName });
Try Ideone .
source to share