Reputation: 550
In the DB, I have a two tables with a one-to-many relationship:
orders suborders
----------- -----------
id id
name order_id
name
I'd like to query these tables and end up with a list of order objects, each of which contains a list (or empty list) of suborder objects. I'd also like to do this in a single DB query so it performs well.
In traditional SQL query land, I'd do something like (forgive the pseudocode):
rs = "select o.id, o.name, so.id, so.name from orders o left join suborders so on o.id = so.order_id order by o.id"
orders = new List<Order>
order = null
foreach (row in rs) {
if (order == null || row.get(o.id) != order.id) {
order = new Order(row.get(o.id), row.get(o.name), new List<Suborders>)
orders.add(order)
}
if (row.get(so.id) != null) {
order.suborders.add(new Suborder(row.get(so.id) row.get(so.name))
}
}
Is there a way to get this same resulting object structure using LINQ-to-Entities? Note that I want to get new objects out of the query, not the Entity Framework generated objects.
The following gets me close, but throws an exception: "LINQ to Entities does not recognize the method..."
var orders =
(from o in Context.orders
join so in Context.suborders on o.id equals so.order_id into gj
select new Order
{
id = o.id,
name = o.name,
suborders = (from so in gj select new Suborder
{
so.id,
so.name
}).ToList()
}).ToList();
Upvotes: 3
Views: 9311
Reputation: 550
The solution ends up being pretty simple. The key is to use a group join to get SQL to do the left join to suborders, and add a second ToList() call to force the query to be run so you're not trying to do object creation on the SQL server.
orders = Context.orders
.GroupJoin(
Context.suborders,
o => o.id,
so => so.order_id,
(o, so) => new { order = o, suborders = so })
.ToList()
.Select(r => new Order
{
id = r.order.id,
name = r.order.name,
suborders = r.suborders.Select(so => new Suborder
{
id = so.id,
name = so.name
}.ToList()
}).ToList();
This code only makes a single query to SQL for all objects and their child objects. It also lets you transform the EF objects into whatever you need.
Upvotes: 2
Reputation: 374
How about this code ?
You can get a local cache.
List<Orders> orders = new List<Orders>();
private void UpdateCache(List<int> idList)
{
using (var db = new Test(Settings.Default.testConnectionString))
{
DataLoadOptions opt = new DataLoadOptions();
opt.LoadWith<Orders>(x => x.Suborders);
db.LoadOptions = opt;
orders = db.Orders.Where(x => idList.Contains(x.Id)).ToList();
}
}
private void DumpOrders()
{
foreach (var order in orders)
{
Console.WriteLine("*** order");
Console.WriteLine("id:{0},name:{1}", order.Id, order.Name);
if (order.Suborders.Any())
{
Console.WriteLine("****** sub order");
foreach (var suborder in order.Suborders)
{
Console.WriteLine("\torder id:{0},id{1},name:{2}", suborder.Order_id, suborder.Id, suborder.Name);
}
}
}
}
private void button1_Click(object sender, EventArgs e)
{
UpdateCache(new List<int> { 0, 1, 2 });
DumpOrders();
}
Output example below
*** order
id:0,name:A
****** sub order
order id:0,id0,name:Item001
order id:0,id1,name:Item002
order id:0,id2,name:Item003
*** order
id:1,name:B
****** sub order
order id:1,id0,name:Item003
*** order
id:2,name:C
****** sub order
order id:2,id0,name:Item004
order id:2,id1,name:Item005
Upvotes: 0
Reputation: 646
I Always create a virtualized Property for Relations
so just extend (add a property) to your order class :
public class Order{
...
List<Suborder> _suborders;
public List<Suborder> Suborders{
get {
return _suborders ?? (_suborders = MyContext.Suborders.Where(X=>X.order_id==this.id).ToList());
}
...
}
so data will be fetched (pulled) only when you call the getters
Upvotes: 0