Selecting list of subentities from parent generates invalid SQL

Posts   
 
    
AlexanderM
User
Posts: 41
Joined: 18-May-2012
# Posted on: 09-Dec-2014 11:35:49   

Hello,

I'm trying to select an entity with its related child entities using projection (select). All works well when I ask entities which are direct childs but when I ask childs from a Parent entity, LlblGen generates SQL which has mixed up table references.

Simplified example that fails in LinqPad (hence Dump): (from mp in MonProfile select mp.MonPoolProfile.MonPeriods).Dump();

So MonProfile has a FK to MonPoolProfile, and every MonProfile has one MonPoolProfile. MonPeriods also has a FK to MonPeriods so one MonPoolProfile has 0 to n Childs.

Generated SQL: SELECT 1 AS "LPFA_2", "LPLA_2"."ID" AS "Id" FROM "IP_OWNER"."MON_PROFILE" "LPLA_1"

EXception: ORA-00904: "LPLA_2"."ID": invalid identifier

StackTrace:

at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsDataReader(ITransaction transactionToUse, IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsProjection(List1 valueProjectors, IGeneralDataProjector projector, ITransaction transactionToUse, IRetrievalQuery queryToExecute, Dictionary2 typeConvertersToRun) at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsProjection(List1 valueProjectors, IGeneralDataProjector projector, ITransaction transactionToUse, QueryParameters parameters) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider.ExecuteHierarchicalValueListProjection(QueryExpression toExecute, IPredicate additionalFilter, ITemplateGroupSpecificCreator frameworkElementCreator) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider.ExecuteValueListProjection(QueryExpression toExecute) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery1.System.Collections.IEnumerable.GetEnumerator() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

I downloaded yesterday the latest available version of LlblGen (4.2.14.119). The reason I'm doing dot walking in the select and not later is query efficiency. If I do the dot walking later on queries will be executed in loops. And the syntax is very compact and readable.

Regards, Alexander.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Dec-2014 15:59:33   

The problem is this: the query you specified actually gives a list of sets. To be able to do that, we have to fetch an entity which is related to a base entity and for that fetched entity we have to fetch its related set and return that set combined with the other sets. However these are 2 hops apart, and therefore the nested query can't be related to the base entity in memory, and it hopelessly crashes as it thinks it can. It's one of those 'looks easy enough, but impossible to fix' problems currently in our linq provider, which is also a problem with Extend in WebAPI/OData, which generates the same kind of queries. The only solution is to join the base entity and its set together and return both, but that's currently not supported in our framework.

The workaround is to write out the join in the query and fetch what you want, or use:

(from mp in MonProfile select mp.MonPoolProfile).Select(p=>p.MonPeriods);

this will give you the sets you want, using 2 queries. So it's more efficient to actually do it in 1 query by writing out the query with the join.

Frans Bouma | Lead developer LLBLGen Pro
AlexanderM
User
Posts: 41
Joined: 18-May-2012
# Posted on: 09-Dec-2014 17:41:38   

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 stuck_out_tongue_winking_eye

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]

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 10-Dec-2014 05:58:02   

Is there a reason not to write the query with Joins? other than less lines of code.

AlexanderM
User
Posts: 41
Joined: 18-May-2012
# Posted on: 10-Dec-2014 09:05:24   

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 wink . 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?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 10-Dec-2014 11:24:49   

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 wink . 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?

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% disappointed

Frans Bouma | Lead developer LLBLGen Pro
AlexanderM
User
Posts: 41
Joined: 18-May-2012
# Posted on: 11-Dec-2014 21:41:57   

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 sunglasses 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 wink

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 12-Dec-2014 10:44:10   

It's rather difficult in this particular case, as the expression tree changes a lot during the passes of visitors over it, which means things can't always be related to something in the query directly (as in: translations happen and that eventually goes wrong, what was the original element that caused it?). It comes down to having check code for these cases which is the same as building special case code for a situation to handle it. We knew it would go wrong in the context of OData (which will generate the query for you), we didn't anticipate on the fact that people might be writing the query by hand as it's rather unusual to fetch sets of sets based on a multi-entity navigation. But I agree, in theory, it should give an error that things go wrong because it can't fulfill the query request.

I'll make sure that if we can't fix it (though I hope we can), we'll at least make sure error reporting will be better than a cryptic crash which can mean anything.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 16-Nov-2015 14:35:19   

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 simple_smile

Frans Bouma | Lead developer LLBLGen Pro
AlexanderM
User
Posts: 41
Joined: 18-May-2012
# Posted on: 25-Nov-2015 15:54:21   

Ah great. Perfect timing also for us. I am happy I checked my Spam folder, where the notification of your answer was "stored".

We just created a work item to start a test for compatibility with the newest LlblGen version. I think we will start next week.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 25-Nov-2015 18:27:09   

great simple_smile If you still run into a failing query, let me know. simple_smile

Frans Bouma | Lead developer LLBLGen Pro