How to make a basic linq query for relations 0..n?
Given that the classic Orders DB structure has zero or more OrderLines, and an OrderLine has exactly one Product, how can I write a LINQ query to express this?
The output will be
OrderNumber - OrderLine - Product Name
Order-1 null null // (this order has no lines)
Order-2 1 Red widget
I tried this query but didn't get orders without lines
var model = (from po in Orders
from line in po.OrderLines
select new
{
OrderNumber = po.Id,
OrderLine = line.LineNumber,
ProductName = line.Product.ProductDescription,
}
)
+2
a source to share
3 answers
Here is an article that appears to explain how to achieve exactly what you are trying to do.
public static void OuterJoinSimpleExample()
{
var customers = new List<Customer>() {
new Customer {Key = 1, Name = "Gottshall" },
new Customer {Key = 2, Name = "Valdes" },
new Customer {Key = 3, Name = "Gauwain" },
new Customer {Key = 4, Name = "Deane" },
new Customer {Key = 5, Name = "Zeeman" }
};
var orders = new List<Order>() {
new Order {Key = 1, OrderNumber = "Order 1" },
new Order {Key = 1, OrderNumber = "Order 2" },
new Order {Key = 4, OrderNumber = "Order 3" },
new Order {Key = 4, OrderNumber = "Order 4" },
new Order {Key = 5, OrderNumber = "Order 5" },
};
var q = from c in customers
join o in orders on c.Key equals o.Key into outer
from o in outer.DefaultIfEmpty()
select new {
Name = c.Name,
OrderNumber = ((o == null) ? "(no orders)" : o.OrderNumber)
};
foreach (var i in q) {
Console.WriteLine("Customer: {0} Order Number: {1}",
i.Name.PadRight(11, ' '), i.OrderNumber);
}
Console.ReadLine();
}
+4
a source to share
This is a working query (built using the above example):
var model = (from po in orders
join line in orderLines // note this is another var, it isn't po.Lines
on po.Id equals line.OrderId into g
from line in g.DefaultIfEmpty()
select new
{
OrderNumber = po.Id,
OrderLine = line == null ? 0 : line.LineNumber,
ProductName = line == null ? string.Empty : line.Product.ProductDescription,
}
)
0
a source to share