I'm such a begginer in sql&linq, and I have been stuck in a curious problem:
I have 3 tables, which simplified look like:
+-----------+ | Order | +-----------+ | ID | Name | +-----------+ +-----------------------+ | Ticket_01 | +-----------------------+ | ID | Value | Order_ID | +-----------------------+ +-----------------------+ | Ticket_02 | +-----------------------+ | ID | Value | Order_ID | +-----------------------+
The relation is 1 (Order) to Many (Ticket_01) and 1 (Order) to Many (Ticket_02) where each Order "may contain", for exemple, 2 Ticket_01 and 5 Ticket_02.
I have a class which contains the data for each order:
(simplified)
public class OrderData
{
string orderName;
decimal tickets_01_ValueSumatorium;
int numberOfTickets_01_withValueGreaterThan50;
decimal tickets_02_ValueSumatorium;
}
So I want to group by "Order", using linq, every "Ticket_01" and "Ticket_02".
I was trying something like this:
var list = (from Order in db.Orders
join Ticket_01 in db.Tickets_01 on Order.ID equals Ticket_01.Order.Id
join Ticket_02 in db.Tickets_02 on Order.ID equals Ticket_02.Order.Id
group new {Ticket_01, Ticket_02} by Order into group
select new OrderData
{
orderName = group.Key.Name,
tickets_01_ValueSumatorium = group.Sum(t => Tickets_01.Value),
// etc etc (filling each class field)
}).ToList();
But doing this this way, the results are duplicated or triplicated, or quadriplicated, etc. I "understand" the reason, but I also think there should be a ""easy/fast"" way to group/merge the tickets data having a common foreign key (Order_ID).
If there is no awesom/magic way to do it, I would like to know which you masters think is the good way to do this, since I'm gettig the data one by one and merging it one by one by savig the Order ID inside the class I mentioned :S
Thanks you all! First post here ^^. I'll be here in case of needing any clarification.
The db.Orders join db.Tickets_01 join db.Tickets_02 part joins all Ticket_01s with all Ticket_02s of a given order, giving you n x m rows, where n is the number of the order's Ticket_01s and m is the number of its Ticket_02s (which also means you'd receive nothing if an order has no ticket of either type).
What you seem to be trying to achieve is: one result per order, with aggregates on the order's respective tickets collections.
from order in db.Orders
select new
{
Order = order,
Tickets01 = from ticket in db.Tickets_01 where ticket.OrderId == order.ID select ticket,
Tickets02 = from ticket in db.Tickets_02 where ticket.OrderId == order.ID select ticket
} into orderWithTickets
select new OrderData
{
orderName = orderWithTickets.Order.Name,
tickets_01_ValueSumatorium = orderWithTickets.Tickets01.Sum(t => t.Value),
numberOfTickets_01_withValueGreaterThan50 = (from t in orderWithTickets.Tickets01 where t.Value > 50 select t).Sum(t => t.Value),
// ...
};
, or equivalently (as I would prefer 'cause I don't like the query syntax):
db.Orders.Select(order => new
{
Order = order,
Tickets01 = db.Tickets_01.Where(ticket => ticket.OrderId == order.ID),
Tickets02 = db.Tickets_02.Where(ticket => ticket.OrderId == order.ID)
})
.Select(orderWithTickets => new OrderData
{
orderName = orderWithTickets.Order.Name,
tickets_01_ValueSumatorium = orderWithTickets.Tickets01.Sum(t => t.Value),
numberOfTickets_01_withValueGreaterThan50 = orderWithTickets.Tickets01.Where(t => t.Value > 50).Sum(t => t.Value),
// ...
})
Feels like you should be modelling the order's tickets as navigation properties, in which case all you need is:
db.Orders.Select(order => new OrderData
{
orderName=order.Name,
tickets_01_ValueSumatorium = order.Tickets_01.Sum(t=>t.Value),
numberOfTickets_01_withValueGreaterThan50 = order.Tickets_01.Where(t => t.Value > 50).Sum(t => t.Value)
// ...
});