HowardRichards

Can anyone validate/refute this bug I found in beta2
https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx FeedbackID=289615

If you define a data shape -- sorry LoadOption for more than one level, the child tables are not loaded..

e.g. in Northwind you say load all Orders for a Customer also load all OrderDetails for an Order.

So if you selected say, ten customers using LINQ you would expect one query to load ten customers, their orders and the order details as well in one SQL connection. This doesn't seem to happen for me (there is a sample project on the bug report) - instead it loads the order and order details for a customer only when the loop hits that customer. So you get ten round trips to the database instead of one..

MS says they were unable to reproduce but on my beta2 it seems consistent



Re: ADO.NET (Pre-release) LINQ bug - just me?

Michael Pizzo - MSFT

Hmm... I am able to reproduce this. When I add LoadOptions for Orders to retrieve Order_Details I no longer get Orders spanned in for Customers (I actually do get the Order_Details spanned in for the Orders, when the orders are fetched).

I'll follow up internally and see if I can get you an answer...

-Mike






Re: ADO.NET (Pre-release) LINQ bug - just me?

HowardRichards

Thanks for the confirmation - I am not going crazy after all..

Howard






Re: ADO.NET (Pre-release) LINQ bug - just me?

Michael Pizzo - MSFT

I got a little more information on this; this is actually by design in LINQ to SQL.

In LINQ to SQL, in order to reduce the complexity and amount of data returned by the join query required to do span, only one association in any given query is actually spanned in. LINQ to SQL picks the deepest level association in the query to do the join in order to minimize the number of queries generated. So, in this case, by adding a span between orders and order_details, you are basically "hiding" any span of orders.

So no, you're not going crazy... :-)






Re: ADO.NET (Pre-release) LINQ bug - just me?

HowardRichards

Thanks for following it up.

Hmmm so one can only ever retrieve one table and either its direct parent or child tables using LoadWith..

My current home-brew datalayer allows to to link to more than one layer. I've found most of my performance issues happening when the main ObjectDataSource query didn't fetch all the related data. Otherwise lazy-loading kicks in and you get N round trips to the database.

I think that one might come back to bite you





Re: ADO.NET (Pre-release) LINQ bug - just me?

Frans Bouma - C# MVP

The bug report at connect says MS is NOT able to reproduce it. As you say it 's by design, I find that a little weird Smile. So is it:
1) reproducable as it's by design
or
2) not reproducable as it was a bug and it was fixed in b2

Anyway, I find the excuse of the complexity a bit moot. The thing is that you can do prefetch paths/spans in 2 ways: with joins and with subqueries. With subqueries you can fetch any graph, though it costs you 1 query per graph node (however you can optimize these queries a lot) .With joins you can only fetch a single path in a graph.

ALso, as this is a single path in the graph, I still don't see why it works on only one association...





Re: ADO.NET (Pre-release) LINQ bug - just me?

Frans Bouma - C# MVP

Actually, the whole shape loading doesn't work at all for me, perhaps I'm doing something wrong but if I do:

Code Snippet

NorthwindContext nc = new NorthwindContext();
DataLoadOptions dl = new DataLoadOptions();
dl.LoadWith<Customer>(c=>c.Orders);
dl.LoadWith<Order>(o=>o.Order_Details);
nc.LoadOptions = dl;
nc.DeferredLoadingEnabled = false;
var q = from c in nc.Customers
where c.CustomerID=="ALFKI"
select c;
nc.Log = Console.Out;

List<Customer> customers = q.ToList<Customer>();
Debug.WriteLine("# Customers = " + customers.Count);

and run this and check the output window I see 1 query for the customer, 1 for the orders of that one customer and for each order a query for the order details.

If I run the query to fetch all customers, I see for each customer a query for all the orders and for each order a query for all the order details. That is a truckload of queries. Smile

There is sparse documentation on DataLoadOptions, so perhaps it needs a different way of being setup, but at the moment it looks pretty erm... dead in the water as it doesn't do much...

(edit) I've to add: I'm running this against sqlserver 2000, but I don't think that's of any relevance.





Re: ADO.NET (Pre-release) LINQ bug - just me?

HowardRichards

Indeed. Hence my concern! A moot point now as I've come to the conclusion that LINQ 2 SQL is nice demo stuff but useless for writing an application with security (see my other thread here) and so is a moot point.






Re: ADO.NET (Pre-release) LINQ bug - just me?

Frans Bouma - C# MVP

But still worth mentioning so others will learn what's at stake, that they have to test the query output to see if it won't burn their servers down in production.

What I really wonder is why it doesn't work as it should. My prefetch path code for llblgen pro which optimizes a lot, is just a couple of hundred lines, so it's not as if it will take manyears to make this possible... Oh well. Smile





Re: ADO.NET (Pre-release) LINQ bug - just me?

Frans Bouma - C# MVP

Hello Microsoft Any time to respond please Why is the bug closed with not reproducable in Connect while others can reproduce it Is it by design or not





Re: ADO.NET (Pre-release) LINQ bug - just me?

HowardRichards

Don't worry on my account..

I've mentally dumped LINQ to SQL now as it has no way of allowing security to be added to the datalayer (see http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1949726&SiteID=1 )

LINQ to SQL is a great demonstration/sales tool, but put it near a real application and it falls apart like a chocolate fireguard.. shame really because it looked like MS were close.

So in essence you're better writing your own version of LINQ to SQL because Microsoft obviously can't.

LINQ is great.. LINQ to SQL ... sucks






Re: ADO.NET (Pre-release) LINQ bug - just me?

Frans Bouma - C# MVP

I've mentally dumped LINQ to SQL now as it has no way of allowing security to be added to the datalayer

That's a difficult topic indeed. I'm the lead developer of LLBLGen Pro, one of the leading o/r mappers on .net, and we have added this in v2.5 which will be released next week (and we'll add linq support later this year). You need authorizers which are called from the framework (for example through AOP or through hard-wired routes in the framework to authorizer objects injected with dependency injection, as we did it. Sort of AOP but then not flexible mix-ins) which are consulted if a given action can take place: e.g. can read property, can write property, can load entity, can add entity to this entity, etc.


So in essence you're better writing your own version of LINQ to SQL because Microsoft obviously can't.

It's indeed odd that they dropped the ball this big on elements so essential. But I'm not complaining Wink






Re: ADO.NET (Pre-release) LINQ bug - just me?

HowardRichards

I'll take a look at LLBLgen 2.5 when it comes out, thanks. Are you planning to make it LINQ-able





Re: ADO.NET (Pre-release) LINQ bug - just me?

Frans Bouma - C# MVP

Yes, the next couple of months are planned for that. Smile







Re: ADO.NET (Pre-release) LINQ bug - just me?

HowardRichards

Aha.. I re-opened and asked them to re-examine.. now they have recognised it as a bug, not intended behaviour!


Thanks for your feedback. We have reproduced this bug on Visual Studio 2008 Beta 2, and we are sending this bug to the appropriate group within the Visual Studio Product Team for triage and resolution.

Thank you,
Visual Studio Product Team.
Posted by Microsoft on 13/08/2007 at 20:23


many thanks for the help everyone..