Michael.NET

Hello.
In the May 2006 CTP for VS2005 there was a neat feature when working with a DataContext to eagerly load 1:M relations like [Customer, Order], by simply tagging along the .Including(c => c.Orders)

With Mars 2007 CTP on Orcas this extention method is not there anymore. After a long session with google, I finally had a look at the updated samples from Mr Calverts blog. In those updated samples the eager loading demo using Including was commented out and instead there was an example using DataShape() and LoadWith<T>.

I am curious on the status of eager loading. Using datashapes on the DataContext is not very agile, nor safe; as you get side-effects if you forget to set the shape to null after use and continue to use the same instance of DataContext

I was hoping there would be a c# keyword for eager loading, possible with the option to subquery. This is something I just dreamed up, replacing 'from' with 'with' for eager loading.

//I have no idea how this will format as this is my first post
var q =
from c in db.Customers
where c.City == "London"
orderby c.CompanyName
select c
with o from c.Orders
orderby o.OrderDate
select o
with oi from o.OrderItems
select oi;

Now that would be neat. The closests thing you can come to that in Mars CTP is to project new types, but then you loose coupling to the DataContext.

Any thoughts or insights on this






Re: LINQ Project General Is LoadWith Broken?

Matt Warren - MSFT

The DataShape actually eanbles consistency. Once the first result is returned from a query, you can no longer changed the DataShape. It is a fixed part of the DataContext. You can, however, pre-define a DataShape and re-use it across many DataContexts.




Re: LINQ Project General Is LoadWith Broken?

Michael.NET

Sure, but what about eager loading How does that fit when .Including(x => x.y) are gone




Re: LINQ Project General Is LoadWith Broken?

Anders Hejlsberg

You use the LoadWith method on DataShape to specify the relationships you want to eager load along with a particular entity. For example

DataShape ds = new DataShape();
ds.LoadWith<Customer>(c => c.Orders);
myContext.Shape = ds;

specifies that when a Customer is loaded, the customer's orders should be eager loaded as well.

DataShape also allows you to filter and order relationships through the AssociateWith method. For example, the following causes only the orders in 2007 to be loaded into a customer's Orders collection.

DataShape ds = new DataShape();
ds.LoadWith<Customer>(c => c.Orders);
ds.AssociateWith<Customer>(c => c.Orders.Where(o => o.OrderDate.Year == 2007));

myContext.Shape = ds;

Anders





Re: LINQ Project General Is LoadWith Broken?

Michael.NET

Thanks Matt and Anders.

I was playing around with DataShapes, but found them cumbersome and a bit oldschool, and not like linq. Maybe because you can't hook them

if LoadWith and AssioaciateWith would return current datashape it would be possible to hook.

myContext.Shape = (new DataShape()).LoadWith<Customer>(c => c.Orders).AssociateWith<Customer>(c => c.Orders.Where(o => o.OrderDate.Year == 2007))

But as this is quite unreadable, is there any plan to hide the datashape with some syntactic sugar as I suggested above The .Including() was brilliant as you saw the 'eagerness' in your query.

But as Matt said, as the shape is sticky and forever bound to the context, this sounds impossible.

*sob*
(That is me crying, I am not saying your mothers were dogs *s*)





Re: LINQ Project General Is LoadWith Broken?

Michael.NET

This is the most readable I can make it:

DataShape shape = new DataShape();
shape.LoadWith<Customer>(c => c.Orders);
shape.AssociateWith<Customer>(
c => c.Orders
.Where(o => o.OrderDate.Value.Year > 1996)
.OrderByDescending(o => o.OrderDate)
);
context.Shape = shape;


INQish, but not very LINQish =)

If the datashape is here to stay, perhaps we could use C# new syntax on a With that combines LoadWitth and AssociateWith

Me dreaming:

DataShape shape = new DataShape();
shape.With<Customer>(
from o in Orders
where o.OrderDate.Value.Year > 1996
orderby o.OrderDate descending
);
context.Shape = shape;

Now that looks like LINQ.







Re: LINQ Project General Is LoadWith Broken?

Anders Hejlsberg

LoadWith and AssociateWith actually do different things and it isn't really feasible to combine them. Specifically, LoadWith specifies the relationships you want to eager load with entities of a particular type, and AssociateWith specifies filters and/or orderings for particular relationships--regardless of whether those relationships are eager or lazy loaded.

When you specify an AssociateWith query you are free to use the dotted together method syntax or a query expression; the resulting expression tree is the same. For example, you could write:

            DataShape shape = new DataShape();
            shape.LoadWith<Customer>(c => c.Orders);
            shape.AssociateWith<Customer>(c =>
                from o in c.Orders
                where o.OrderDate.Value.Year > 1996
                orderby o.OrderDate descending
                select o

            );
            context.Shape = shape;

Not too far from what you're dreaming about :-)

Anders

 





Re: LINQ Project General Is LoadWith Broken?

Michael.NET

I am no longer dreaming; - Now I am true believer! =)

Being able to use c# syntax in AssociateWith is great. While single lambdas are readable, a chunk of them, with a few inlined, would be horror for a maintainer.

I spent some time playing around with this, and one DataShape is far more powerful than several .Including() spread all over your code.

As Matt said, you can re-use shapes and don't need to copy-paste them every time you want to write a query. As for a database, you don't want to have every query eagerly load tables all willy nilly, maybe giving zillions of tuples, but have some order and control. A typical database have a small set of relations you want to load eagerly.

Thanks to partial classes I wrote a singleton factory on my NorthWndDataContext with beautiful result.

var context = NorthWndDataContext.Shapes.CustomerOrder();

Pretty neat.





Re: LINQ Project General Is LoadWith Broken?

ardent

I have done quite a googling for DataShape objects and all the examples I have seen are

shape.LoadWith<Customer>(c => c.Orders);

Is it possible to load even deeper hierarchies. for example I want to eager load Customer, Order, OrderDetail, Product in one SQL trip.





Re: LINQ Project General Is LoadWith Broken?

Keith Farmer

This might give you an idea...

Code Snippet

DataShape shape = new DataShape();

shape.LoadWith<A>(a => a.B);

shape.LoadWith<A>(a => a.C);

shape.LoadWith<B>(b => b.D);

shape.LoadWith<C>(c => c.D);

You can also try it out with inheritance.

There are some limitations, of course. For example, adding the following would raise an exception:

Code Snippet

shape.LoadWith<D>(d => d.A); // Loop back from D to A to form a loop.






Re: LINQ Project General Is LoadWith Broken?

Joe Albahari

ardent wrote:

I have done quite a googling for DataShape objects and all the examples I have seen are

shape.LoadWith<Customer>(c => c.Orders);

Is it possible to load even deeper hierarchies. for example I want to eager load Customer, Order, OrderDetail, Product in one SQL trip.



Sure:

shape.LoadWith<Customer> (c => c.Orders);
shape.LoadWith<Order> (o => o.OrderDetails);
shape.LoadWith<OrderDetail> (od => od.Product);

Joe





Re: LINQ Project General Is LoadWith Broken?

ardent

I fired the following code

NorthwindDataContext ctx = new NorthwindDataContext();
DataShape shape = new DataShape();
shape.LoadWith<Customer>(c => c.Orders);
shape.LoadWith<Order>(o => o.Order_Details);
shape.LoadWith<Order_Detail>(od => od.Product);
ctx.Shape = shape;
Customer cc = ctx.Customers.Single(x => x.CustomerID == "ALFKI");

As it is obvious from the code, I created four entities form Northwind database. Customer, Order, Order_Detail and Product and then I selected a single customer. I found that there were, DLinq fired two queries to database. 1. to retrieve the single customer and other a join on Order, Order_Details and Product table. I then Modified the query to select the customer as follows

var q = from c in ctx.Customers
where c.City == "London"
select c;

This time I found that the DLinq fired 7 queries. 1 query to return the list of the customers in London (in my database total customer s living in London is 6) and 1 query each for each customer.

My expectation was there is going to be only 1 query in both the cases. I am not sure what I describe above is the intended behavior or I am doing something wrong.





Re: LINQ Project General Is LoadWith Broken?

Michael.NET

ardent, as I started this thread, I think I should respond to your ponderings and solve some of your confusion.

They key insight is that you only get one query. This query holds, as you say, 1 + 6 SELECT statements.

But the whole idea with eager loading is that SQL Server is capable of returning multiple 'recordsets' (Table<T>) in one Response.

This was known as SHAPEing, which all who has ever tried it, knows it actually invloves a lot of hammering, sawing, gaffer-taping and finally praying. The whole thing was a mess. Or maybe I was doing it wrong. =)

With DataShape you get eager loading, and to continue using the HTTP methaphore; by sending one Request and get one Response, a few coders at microsoft have written nifty code to make sense of all 1 + 6 results and create a .NET object-graph that a DataContext understands.

If you expect a super-join on join on join 'view' of all the tables you defined in your DataShape, then the whole idea falls apart. You are back on square one with the 2-dimensional 'recordset' with redundant data.

Of course, an ORM-Engine could make sense of the classic 'rectangle-excell-row-column-recordset' and solve the redundancies into lists/objects, but I don't call that 'eager loading'. In my not so humble opinion, if you are working with a database that can only give you one 2-dimensional resultset, whatever you with the result, or what the ORM does with the result; - is 'magic', not 'eager loading'.

However, even that 'magic' is better than hammer/trashing your database with maybe thousands of selects becuase the right hand doesn't know what the left is doing. Maybe you are doing a SELECT in a for-loop, call a method someone else has written, that contains a for-loop and a SELECT, that calls a third method that contiains a for-loop that contains a transaction etc etc ad absurdum. It may sound like silly example, but I've been there and done that. And so have many others before me. =)

A real world experience of using SHAPEing was when I re-wrote a import/export-program taking 20 hours to run, down to 5 minutes. When VS 2007 is released, I'll re-write that program again, using DataShape, just to make the code readable (it is now in Delphi using shaping via a mix of ADO, OLEDB and COM, - oh the horror).

Anyway, what is so cool with the DataShape (when using SqlClient), is that you get proper eager loading. LINQ Rules!





Re: LINQ Project General Is LoadWith Broken?

Michael.NET

Matt, Anders:

While the DataShape class and .Including() exention method is spread out in like a dozen threads now, I'll keep my thoughts in my thread.

Now that Orcas Beta 1 is out, The C# 3.0 specs and 'keywords' must have been written in stone ages ago (or I feel sorry for the Orcas team *s*).

But for C# 3.1 or C# 4.0 why not have some syntactic sugar over a DataShape

Maybe a DataShape should be variant and changeable on a DataContext The 'from' -> 'with' I am suggesting above would be a nice thing to have.

I have no idea on how complex that would be to implement, but it would give us developers (not to forget maintainers) a really nice syntax to sport hierarchical queries.





Re: LINQ Project General Is LoadWith Broken?

Matt Warren - MSFT

Like Michael wrote, using LoadWith does not guarantee only one query, it doesn't even guarantee only one round trip to the database. What it means is that after the event of 'executing' the query all of the requested data will be loaded, or at least it will be observed to be loaded.

For example, when deferred loading is 'on' (the default), LoadWith's may be completely ignored (they aren't, but they could be) since examining the related collections will fire queries to fill themselves out. Unless you are monitoring traffic to the database you won't see the difference. What LoadWith's can do is give a 'hint' to the query translator to perhaps attempt to convert a hierarchy into a single query (or fewer queries) by using tricks like joins or potentially other non-standard commands to fetch data. LINQ to SQL will actually do this in some cases.

When deferred loading is 'off', the LoadWith's cannot be ignored since you'd definitely notice the difference. LINQ to SQL may still choose to employ a trick to reduce the number of queries fired against the database, but it is unlikely if you have more than one LoadWith that you'll get only one query.