Selecting list of subentities from parent generates invalid SQL
Joined: 18-May-2012
Joined: 18-May-2012
I just tried the Northwind database to make it easy to reproduce it: As expected this fails: var x = (from o in Order select o.Customer.Orders).ToList();
Running the same in Linq To Sql works. I think if they can make it work you also want/should make it work 
var x = (from o in Orders select o.Customer.Orders).ToList();
Btw: This is the query generated by Linq To Sql:
SELECT [t2].[OrderID], [t2].[CustomerID], [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 [value] FROM [Orders] AS [t0] LEFT OUTER JOIN [Customers] AS [t1] ON [t1].[CustomerID] = [t0].[CustomerID] LEFT OUTER JOIN [Orders] AS [t2] ON [t2].[CustomerID] = [t1].[CustomerID] ORDER BY [t0].[OrderID], [t1].[CustomerID], [t2].[OrderID]
Joined: 18-May-2012
Walaa wrote:
Is there a reason not to write the query with Joins? other than less lines of code.
Yes, I simplified my code just to reproduce the error. In my real code I return much more like for instance mp (MonProfile) which is now unavailable.
Anyway it doesn't feel right to say, you can use our library but in some circumstances it might not work. This example is not pushing Linq to the limits and is easy to write for a developer. I know this is easy from an end user perspective and this is totally different for the library developer 
.
So if this doesn't work what else doesn't work? Can we trust Linq To LLblGen or should we use it very careful or abandon?
AlexanderM wrote:
Walaa wrote:
Is there a reason not to write the query with Joins? other than less lines of code.
Yes, I simplified my code just to reproduce the error. In my real code I return much more like for instance mp (MonProfile) which is now unavailable.
Did you see the workaround I gave?
Anyway it doesn't feel right to say, you can use our library but in some circumstances it might not work. This example is not pushing Linq to the limits and is easy to write for a developer. I know this is easy from an end user perspective and this is totally different for the library developer
It's one of the very few things which fail. In general everything else works fine, this is one thing that doesn't, and we did try to fix it in v4.2, but it would require an overhaul of the fetch system and there was no time for that. We also experimented with a converter so it would transform the query into prefetch path queries, but that too was not successful, as it can be the original query does more than just fetching entities and then things go wrong.
Linq to sql and EF do work with this query because they join the different sets into 1 query: linq to sql has special case code for this specific query (the sql query is specifically crafted for that so it can be used to build the set of sets you're requesting for this particular case), and EF builds one big resultset by joining everything together resulting in potentially a massive number of rows to be returned.
The thing is that linq providers have to have a lot of special case code for each special case query, as you can see with e.g. Linq to sql and EF too: it's not possible to write a general purpose provider which translates intend from the linq provider to sql statements and it will work in all cases: there is a tremendous amount of edge cases which require a special piece of code for each edge case. We added a lot of them, this is one which is not yet covered. If it was simple, it would have gotten its special case code already, like others before it (e.g. grouping on a constant, group by into var and select var, to name a couple). The problem with these cases is that you only know about them till you run into them, or figure out what might pop up beforehand.
Other linq providers go wrong in other cases: linq to sql for example flips to lazy loading (and thus select n+1!) when you prefetch paths deeper than 1 level, EF brings databases to a halt with massive queries spanning sometimes 1000s of lines when fetching simple inheritance entities... it's sadly not a 1:1 mapping from linq expression trees to sql statements, so translations have to happen and sometimes code has to be written which will interpret the crafted query so it will in the end give the result the user wanted. Like we do with nested queries in the projection.
It is planned though but you're not helped with this at all, I know. The thing I can offer you is a workaround and an explanation why this pops up (which I tried to do in my earlier post above). I know it sounds like excuses but it's sadly what we can offer today.
I'm sorry for this inconvenience, trust me there's nothing more I want than a 100% working linq provider after all the work that's been put into it, but due to the edge cases there seems to be no way to make it work 100% 
Joined: 18-May-2012
I can understand your message, like I said before, easy for us difficult for you. But is it possible to recognize that we end-users do something that you don't support and throw a specific error. And also is this documented or can you document what is and isn't supported? I had a big working query changed just a little bit and it broke down. Took me quite some time to see what went wrong. A clear error message would have been helpful.
And, yes I did see the work-around, but this was a fundamental discussion 
not interested in work arounds because I had simplified my query and the work around will not work in my real code, but I already had another work-around
Recent fixes in v4.2's linq provider has made this now possible.
[Test]
public void SelectOfRelatedSetOfRelatedEntity()
{
using(var adapter = new DataAccessAdapter())
{
var metaData = new LinqMetaData(adapter);
var q = from o in metaData.Order
where o.CustomerId=="ALFKI"
select o.Employee.EmployeeTerritories;
var results = q.ToList();
Assert.AreEqual(6, results.Count);
}
}
This now succeeds 
Joined: 18-May-2012
great If you still run into a failing query, let me know.