Joe Rattz

I posted this question in another thread, but I am afraid it is too obscured and no one will see it, so I thought I would post it in its own thread.

From another thread:

Keith Farmer wrote:

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);

Does this actually work in beta1:

Code Snippet

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

I can't seem to be able to do immediate loading with more than one associated class of an entity class. Here is some code to demonstrate:

Code Snippet

Northwind db = new Northwind(@"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind");

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

IQueryable custs = (from c in db.Customers
where c.Country == "UK" &&
c.City == "London"
orderby c.CustomerID
select c);


db.Log = Console.Out;

foreach (Customer cust in custs)
{
// Just to cause the query to execute.
}

For some reason, it will not perform immediate loading of the Orders class. Initially, I was calling LoadWith for the Orders first and thought that perhaps it wasn't immediately loading Orders because the second call, the one with CustomerCustomerDemos specified, was overriding it, so I swapped the order of the two LoadWith calls. But still, the CustomerCustomerDemos were immediately loaded and the Orders were not.

Is this broken in beta1 Or am I doing something wrong

Thanks.





Re: LINQ Project General Is LoadWith Broken in Beta1?

Matt Warren - MSFT

What is telling you that it is not working Are you not seeing what you expect in the log Are you only getting a query for customers




Re: LINQ Project General Is LoadWith Broken in Beta1?

Joe Rattz

Matt Warren - MSFT wrote:
What is telling you that it is not working Are you not seeing what you expect in the log Are you only getting a query for customers

For the first post I made in this thread, I am not seeing what I would expect in the log. Let me back up a little and provide a starting example:

Code Snippet

Northwind db = new Northwind(@"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind");

DataShape ds = new DataShape();
//ds.LoadWith(c => c.Orders);
//ds.LoadWith(c => c.CustomerCustomerDemos);
db.Shape = ds;

IQueryable custs = (from c in db.Customers
where c.Country == "UK" &&
c.City == "London"
orderby c.CustomerID
select c);

db.Log = Console.Out;

foreach (Customer cust in custs)
{
Console.WriteLine("{0} - {1}", cust.CompanyName, cust.ContactName);
}

Notice that I have commented out the calls to LoadWith. Here are the results of this code:

Code Snippet

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address],

[t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [Customers] AS [t0]
WHERE ([t0].[Country] = @p0) AND ([t0].[City] = @p1)
ORDER BY [t0].[CustomerID]
-- @p0: Input NVarChar (Size = 2; Prec = 0; Scale = 0) NOT NULL [UK]
-- @p1: Input NVarChar (Size = 6; Prec = 0; Scale = 0) NOT NULL [London]
SqlProvider\AttributedMetaModel

Around the Horn - Thomas Hardy
B's Beverages - Victoria Ashworth
Consolidated Holdings - Elizabeth Brown
Eastern Connection - Ann Devon
North/South - Simon Crowther
Seven Seas Imports - Hari Kumar

Obviously, since nowhere in the results do we see any query for the Orders or CustomerCustomerDemo records, both of those associated EntityRefs are using deferred loading.

Now, I will uncomment the LoadWith for the Orders EntityRef:

Code Snippet

Northwind db = new Northwind(@"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind");

DataShape ds = new DataShape();
ds.LoadWith(c => c.Orders);
//ds.LoadWith(c => c.CustomerCustomerDemos);
db.Shape = ds;

IQueryable custs = (from c in db.Customers
where c.Country == "UK" &&
c.City == "London"
orderby c.CustomerID
select c);

db.Log = Console.Out;

foreach (Customer cust in custs)
{
Console.WriteLine("{0} - {1}", cust.CompanyName, cust.ContactName);
}

So now, the orders should be immediately loaded. Here are the results:

Code Snippet

SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].[ContactTitle], [t1].[Address],

[t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax], [t2].[OrderID],

[t2].[CustomerID] AS [CustomerID2], [t2].[EmployeeID], [t2].[OrderDate], [t2].[RequiredDate],

[t2].[ShippedDate], [t2].[ShipVia], [t2].[Freight], [t2].[ShipName], [t2].[ShipAddress], [t2].[ShipCity],

[t2].[ShipRegion], [t2].[ShipPostalCode], [t2].[ShipCountry], (
SELECT COUNT(*)
FROM [Orders] AS [t3]
WHERE [t3].[CustomerID] = [t1].[CustomerID]
) AS [count]
FROM (
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address],

[t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [Customers] AS [t0]
WHERE ([t0].[Country] = @p0) AND ([t0].[City] = @p1)
) AS [t1]
LEFT OUTER JOIN [Orders] AS [t2] ON [t2].[CustomerID] = [t1].[CustomerID]
ORDER BY [t1].[CustomerID], [t2].[OrderID]
-- @p0: Input NVarChar (Size = 2; Prec = 0; Scale = 0) NOT NULL [UK]
-- @p1: Input NVarChar (Size = 6; Prec = 0; Scale = 0) NOT NULL [London]
SqlProvider\AttributedMetaModel

Around the Horn - Thomas Hardy
B's Beverages - Victoria Ashworth
Consolidated Holdings - Elizabeth Brown
Eastern Connection - Ann Devon
North/South - Simon Crowther
Seven Seas Imports - Hari Kumar

Notice that the orders are retrieved with the customers by doing a LEFT OUTER JOIN on the orders table. They are all loaded immediately when the customers are loaded.

Now, I will uncomment the LoadWith for the CustomerCustomerDemos EntityRef:

Code Snippet

Northwind db = new Northwind(@"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind");

DataShape ds = new DataShape();
ds.LoadWith(c => c.Orders);
ds.LoadWith(c => c.CustomerCustomerDemos);
db.Shape = ds;

IQueryable custs = (from c in db.Customers
where c.Country == "UK" &&
c.City == "London"
orderby c.CustomerID
select c);

db.Log = Console.Out;

foreach (Customer cust in custs)
{
Console.WriteLine("{0} - {1}", cust.CompanyName, cust.ContactName);
}

So now, orders and customercustomerdemos should be immediately loaded. Here are the results:

Code Snippet

SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].[ContactTitle], [t1].[Address],

[t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax], [t2].[CustomerID]

AS [CustomerID2], [t2].[CustomerTypeID], (
SELECT COUNT(*)
FROM [CustomerCustomerDemo] AS [t3]
WHERE [t3].[CustomerID] = [t1].[CustomerID]
) AS [count]
FROM (
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address],

[t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [Customers] AS [t0]
WHERE ([t0].[Country] = @p0) AND ([t0].[City] = @p1)
) AS [t1]
LEFT OUTER JOIN [CustomerCustomerDemo] AS [t2] ON [t2].[CustomerID] = [t1].[CustomerID]
ORDER BY [t1].[CustomerID], [t2].[CustomerTypeID]
-- @p0: Input NVarChar (Size = 2; Prec = 0; Scale = 0) NOT NULL [UK]
-- @p1: Input NVarChar (Size = 6; Prec = 0; Scale = 0) NOT NULL [London]
SqlProvider\AttributedMetaModel

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate],

[t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity],

[t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]
FROM [Orders] AS [t0]
WHERE [t0].[CustomerID] = @x1
-- @x1: Input NChar (Size = 5; Prec = 0; Scale = 0) NOT NULL [AROUT]
SqlProvider\AttributedMetaModel

Around the Horn - Thomas Hardy


SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate],

[t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity],

[t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]
FROM [Orders] AS [t0]
WHERE [t0].[CustomerID] = @x1
-- @x1: Input NChar (Size = 5; Prec = 0; Scale = 0) NOT NULL [BSBEV]
SqlProvider\AttributedMetaModel

B's Beverages - Victoria Ashworth

...

Notice that now, the query that retrieved the customers is only doing the left outer join on customercustomerdemo. The orders are queried separately as each Customer is referenced. How is this considered immediate loading for the Orders I thought the whole point here for the immediate loading was not to have a zillion queries going for each associated class

Now, each query for the orders is not happening until the Customer is referenced so there will be an order query for each customer. While the orders loading is more immediate that querying them when the orders are referenced, it is not as immediate as before I uncommented the LoadWith for the customercustomerdemo. By calling LoadWith for two different associated classes for the Customer class, I seem to have made the Orders less immediate. Again I ask because I thought the point of the immediate loading was to prevent queries being issued to the database for the associated class (Order) for each entity (Customer) to reduce the numebr of queries to the database and network traffic.

Can you perhaps clarify what is going on here and how this is supposed to work

Thanks.






Re: LINQ Project General Is LoadWith Broken in Beta1?

Joe Rattz

Matt Warren - MSFT wrote:
What is telling you that it is not working Are you not seeing what you expect in the log Are you only getting a query for customers

For the second post I made on this thread, the issue is similar to what I presented for the first post. Except, both orders and order details are only loaded when the customer is referenced. Again, this is more immediate than loading them when they (orders or order details) are referenced, but still results in an additional SQL query for each customer retrieved, which I thought the point of immediate loading was supposed to prevent.

Thanks.






Re: LINQ Project General Is LoadWith Broken in Beta1?

Matt Warren - MSFT

The behavior you as seeing when you use LoadWith for Orders and CustomerCustomerDemos is intended. Immediate loading does not gaurantee fewer queries. It gaurantees that all data is loaded before the objects are returned.

LINQ to SQL currently uses two mechanisms to retrieving hierarchical data structures. It can either fire separate queries to retrieve associated data or it can form a join to bring back related data in the same query. Both have drawbacks. Multiple queries cause multiple round-trips to the server, costing time. Joined queries produce redundant data that must be streamed back over the network costing time. It is often quicker to retrieve the redundant data of a single join than to fire n+1 queries. However, this is less often true for multiple joins, as the cost of redundancy increases non-linearly. For this reason, we chose to restrict the use of joining to a single join per query. Unfortunately, since this is done on the client/middle tier and not in the server, the choice on which to join is done w/o any information to discriminate which association would be better. We do, however, try to choose one of the 'lowest' association in the tree as this is more likely to save the greatest amount of queries. So far a tree including customers, orders and order-details, the association between order and order-details would get the join and the association between customers and orders would be left as separate queries.

There are possibilities for improvement. The solution illustrated above is designed to function over all possible hierarchical result structures due to projections, not just simple entity-to-entity associations. Given a simple entity association produced by a query for a set of distinct entities, it would be possible to produce two separate queries, one for each side of the association, and then manually join them on the client. The downsides of this solution is having to prove that you have a distinct set, not working for general hierachical projections and requiring at least one of the results to be cached. Other improvements could also be made in the server. SQL Server could implement nested tables (as per SQL 2003 spec) or some variation that would allow for arbitrary hierarchical results to be retrieved in a single query. This would be ideal. Another possibility would be to change the network protocol to optimize out redundant data sent as part of a join. That would make using multiple joins in some scenarios as economical as full nested table support.