Hello, I'm desperatly looking for a linq provider that realizes that queries aren't always trivial 2 liners and thus generating millions of sub queries isn't acceptable, from looking at the code you generate, it seems i've found it. For context the reason i'm looking for another provider instead of linq to SQL / Entities is described in more details here : http://stackoverflow.com/questions/9191696/recommand-a-fitting-linq-provider-to-me-sql-server-complex-queries However i've stumbled on a few issues already in my first hour of working with llblgen and would really like some help sorting this out / working around them

A little bit of info: Version used : don't know, but downloaded the trial today so whatever version is up as trial now Generated for SQL Server & .net 4, using adapter Using SQL Server Express 2008 R2

1) Not an issue on the contrary : thanks for making a provider that doesn't ressort to generating 1 line per row in the parent query as soon as you do 2+ subqueries!

2) I can't seem to do more than 2 "levels" of subquery

bla.select(a=>new{ a.id, b.name items = a.subitems.select(b=>new{ b.id, b.name }) })

This works fine, add a third level and it throws:

bla.select(a=>new{ a.id, b.name items = a.subitems.select(b=>new{ b.id, b.name subitems = b.anotherlevel.select(c=>new{ c.id, c.name }) }) })

I get an ORMQueryExecutionException and get "{"An exception was caught during the execution of a retrieval query: L'identificateur en plusieurs parties \"LPLA_5.MODA_Id\" ne peut pas être lié.\r\nL'identificateur en plusieurs parties \"LPLA_5.MODA_Id\" ne peut pas être lié.\r\nL'identificateur en plusieurs parties \"LPLA_5.MODA_Id\" ne peut pas être lié.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."}"

which roughtly complains in english that a multipart identifier (which is the id linking tables b & c but also happens to be the same id linking a & b if that matters, also b is the table where that field is the PK) can not be linked.

3) I have a table with multiple possible inheritence, i'm fine with them treated as tables and don't want to map them as inherited, just as they are generated from the DB is fine, table A is the common table, and then either table B , C or D are populated for a given row in A, in linq to entities i had this code that worked to retrieve the property p1 from one of those subtables, with llblgen it somehow affects the parent result and ends up returning 0 parent items

var q = A.Select(item=>new { item.Id, item.Name, p1 = ((item.B != null) ? item.B.p1 : ((item.C != null) ? item.C.p1 : ((item.C != null) ? item.C.p1 : null))), }).ToList()

Basically if i comment out p1; q contains 12 000 result, if i don't, it contains 0 result, to me this can only be a bug as it should throw if not supported but definately not alter the number of parent items.

3) This crashes too but i'm unsure if it's because of the potentially null or not null property, or because of the hasvalue or something else, p1 is a nullable int:

mytable.Select(item=>new{ item.Id, item.Name, someobj = item.p1.HasValue ? new { prop = item.p1.value } : null })

4) There seems to be a generic issue with chained selects , seemingly when going throught multiple table in a single subquery throught multiple selects, exemple: A => many B => one C => one D

A.Select(item=>new{ bs1 = item.B .Select(b=>b.C) .Select(c=>new{ c.Id, c.Name }), bs2 = item.B .Select(b=>new{ b.C.Id, b.C.Name }) })

bs1 and bs2 are functionally strictly equivalent, but the code with bs2 works while the code with bs1 throws an ORMQueryConstructionException as such:

"Couldn't create any correlation filter lambda because the nested query didn't have any correlation filters. Please specify a filter in the nested query to tie the nested query to the parent query"

I eagerly await your answer and, if most of those concerns can be worked around or hotfixed and you actually support loading large graphs with many levels you've got 1 licence sold to me + potentially 3-15 more from another costumer that likes me "lives linq" and since it's inception has most of it's web service data retrieval methods start with "return context.sometable".

Also while i assume there has to be many like me who need large complex queries, from what i see online most are only interested in scenaris such as load a few customers, lazy load a few orders, bind to grid, autoupdate which doesn't quite stress test a framework such as yours, so if any of those bugs are new i'd be more than happy to begin translating my large query i'm trying to port and work with you on error reports untill it work ( max 5-6 nesting levels , about 24 subqueries at the root with many subqueries per subquery, some subqueries refering to 100 000lines).

Best regards.

ronan.thibaudau wrote:

Hello, I'm desperatly looking for a linq provider that realizes that queries aren't always trivial 2 liners and thus generating millions of sub queries isn't acceptable, from looking at the code you generate, it seems i've found it. For context the reason i'm looking for another provider instead of linq to SQL / Entities is described in more details here : http://stackoverflow.com/questions/9191696/recommand-a-fitting-linq-provider-to-me-sql-server-complex-queries However i've stumbled on a few issues already in my first hour of working with llblgen and would really like some help sorting this out / working around them

Just to clarify: Are you using LLBLGen Runtime as the target framework or Linq2Sql?

ronan.thibaudau wrote:

2) I can't seem to do more than 2 "levels" of subquery

... add a third level and it throws:

bla.select(a=>new{ a.id, b.name items = a.subitems.select(b=>new{ b.id, b.name subitems = b.anotherlevel.select(c=>new{ c.id, c.name }) }) })

I get an ORMQueryExecutionException and get "{"An exception was caught during the execution of a retrieval query: L'identificateur en plusieurs parties \"LPLA_5.MODA_Id\" ne peut pas être lié.\r\nL'identificateur en plusieurs parties \"LPLA_5.MODA_Id\" ne peut pas être lié.\r\nL'identificateur en plusieurs parties \"LPLA_5.MODA_Id\" ne peut pas être lié.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."}"

What is the real code you are executing? Linq sometimes is tricky. We fixed mostly all the cases but there is some edge cases where the aliasing can't work correctly. To help you with this, please show us the code you are using, the generated sql you get, and also the SQL you would want to generate for it. Most of these complex queries can be re-written using joins.

ronan.thibaudau wrote:

3) I have a table with multiple possible inheritence, i'm fine with them treated as tables and don't want to map them as inherited, just as they are generated from the DB is fine, table A is the common table, and then either table B , C or D are populated for a given row in A, in linq to entities i had this code that worked to retrieve the property p1 from one of those subtables, with llblgen it somehow affects the parent result and ends up returning 0 parent items

var q = A.Select(item=>new { item.Id, item.Name, p1 = ((item.B != null) ? item.B.p1 : ((item.C != null) ? item.C.p1 : ((item.C != null) ? item.C.p1 : null))), }).ToList()

Basically if i comment out p1; q contains 12 000 result, if i don't, it contains 0 result, to me this can only be a bug as it should throw if not supported but definately not alter the number of parent items.

I think the

item.B

part creates join behind the scenes. On these cases, indicate the joins explicitly:

var q = from a in metaData.A
            join bSet in metaDaata.B on a.p1 = b.p1 into bJoin 
            from b in bJoin.DefaultIfEmpty() // this generates the left join
            ...
            select item.B.p1 ?? itemC.p1
            ...

ronan.thibaudau wrote:

3) This crashes too but i'm unsure if it's because of the potentially null or not null property, or because of the hasvalue or something else, p1 is a nullable int:

mytable.Select(item=>new{ item.Id, item.Name, someobj = item.p1.HasValue ? new { prop = item.p1.value } : null })

Just try this:

mytable.Select(item=>new{
    item.Id,
    item.Name,
    someobj = item.p1.Value
})

ronan.thibaudau wrote:

4) There seems to be a generic issue with chained selects , seemingly when going throught multiple table in a single subquery throught multiple selects, exemple: A => many B => one C => one D

A.Select(item=>new{ bs1 = item.B .Select(b=>b.C) .Select(c=>new{ c.Id, c.Name }), bs2 = item.B .Select(b=>new{ b.C.Id, b.C.Name }) })

bs1 and bs2 are functionally strictly equivalent, but the code with bs2 works while the code with bs1 throws an ORMQueryConstructionException as such:

"Couldn't create any correlation filter lambda because the nested query didn't have any correlation filters. Please specify a filter in the nested query to tie the nested query to the parent query"

I think this have to do with your first question. Please provide the SQL you want to generate and the actual SQL you are getting. Not over complicate the query if it's not necessary, all nested queries like these can be rewritten using joins.

ronan.thibaudau wrote:

Also while i assume there has to be many like me who need large complex queries, from what i see online most are only interested in scenaris such as load a few customers, lazy load a few orders, bind to grid, autoupdate which doesn't quite stress test a framework such as yours, so if any of those bugs are new i'd be more than happy to begin translating my large query i'm trying to port and work with you on error reports untill it work ( max 5-6 nesting levels , about 24 subqueries at the root with many subqueries per subquery, some subqueries refering to 100 000lines).

Also, now LLBLGen brings a fluent query engine called QuerySpec that we believe is the best choice for complex queries. You get the query you expect. QuerySpec is released on LLBLGen v3.5, which is now in beta, available for registered customers. So you can use LLBLGen API, LLBL2LINQ and QuerySpec in the same LLBLGen generated code, depending on your needs.

For everything bellow : I can't paste the actual code here (waiting on authorisation from the customer) so i will give all exemples on adventureworks so that you can reproduce them too. This is using SQL Server express 2008 R2

1)Just to clarify: Are you using LLBLGen Runtime as the target framework or Linq2Sql?

I'm using raw LLBLGen classes as generated by your project generator, i was using linq to entities before but i don't have a need to "migrate" anything as in either cases it's db first + hit generate, so during the trial i saw no reason to reuse the entities, everything i write here refers to a test project i did with only llblgen and no other providers / api involved. Everything was left as default, i simply added the entities from the db via drag & drop & generated the project, i used the .net 4 exe launcher to generate the project. Right clicked relational model data, added sql server connection to adventureworks and selected all tables and nothing else (no views, no stored procs) then right clicked reverse engineer all tables to entites, and generated the project in C# / .net 4.0 with template group adapter. Anything not specified was left to default values. Hit start generator normal

I have attached an exemple with the generated entities for adventureworks and a reproctible sample for 2 of those bugs (third level + nesting and chained select ones) , i've had no issue reproducing them, they're not edge cases and seem to be 100% repro on any db

"I think the Code: item.B part creates join behind the scenes. On these cases, indicate the joins explicitly:"

I don't want joins, it does seem to create joins but it definately is a functional bug (when within item A, and selecting a property "of" item A, i don't expect item A itself to be removed if the property cannot be found). If this can be suported, it should be hotfixed as restricting the result set based on joins used for sub properties sound like a major bug, if it can't, then it really should throw an exception.

"I think this have to do with your first question. Please provide the SQL you want to generate and the actual SQL you are getting. Not over complicate the query if it's not necessary, all nested queries like these can be rewritten using joins"

I don't want any specific SQL to be generated, this is an implementation detail, but both queries are functionaly equivalent, and should thus product equivalent results. Overall you need to improve the code for chained selects to detect that A.Select(a=>a.B).Select(b=>b.C) and simplify it to A.Select(a=>a.B.C) as you seem to support the later scenario fine in generation. It's not really an issue for me, but definately sounds like a bug.

"Also, now LLBLGen brings a fluent query engine called QuerySpec that we believe is the best choice for complex queries. You get the query you expect. QuerySpec is released on LLBLGen v3.5, which is now in beta, available for registered customers. So you can use LLBLGen API, LLBL2LINQ and QuerySpec in the same LLBLGen generated code, depending on your needs. "

While switching providers is an option, switching away from linq isn't one as a lot of time was invested by this customer in LINQ training. Also the last thing i want is more control on the SQL, all i want is to never ever see SQL again in my life and express my needs in highter level languages like LINQ (and, for that matter, i can't wait for SQL to rot in hell and be replaced by direct LINQ => query plans translator removing the middle man), so i'm really just looking for something that will translate LINQ to sane SQL for complex queries, but not for more control over it.

As for all the workarounds with joins, as i said i already do this in entity framework, the goal for switching is neither ease of use nor performance nor multi db support, it's only to be able to rewrite the huge mess of a query we have (tons of joins everywhere, final query ends joins over 17 subqueries, themselves each joined by groups or 3 approximately) to something maintainable in LINQ that looks like the output graph and should be expressable in a single query without spliting it.

About the first problematic query (nested queries without correlation). This is some construct LINQ2LLBL doesn't support. The reason is explained in Linq2LLBL - Remarks and limitations:

Documentation wrote:

Nested queries in projection which have no correlation filter. A nested query inside the projection of a parent query has to have a correlation filter to tie the nested query's results to the parent's row, e.g. where o.CustomerID = parent.CustomerID. If this correlation filter isn't found and no correlation relation is found either through specification of a related set, e.g. c.Orders, an exception is thrown, as the nested set can't be tied to the right parent without this filter. The reason this isn't supported is that the alternative would be one query per parent row, which is very bad for performance overall.

... so, it's trying to avoid the EF problems you pointed in your first post. This performance gain has a little cost: you have to rework your query.

About the second problematic query (3-level nested correlated queries). The issue is the aliasing. You can confirm that if you see the exception. This is a known issue. See the remarks and limitation list in the link I posted. These are also some threads that address the same situation: http://llblgen.com/tinyforum/Messages.aspx?ThreadID=20387&StartAtMessage=0&#114991 http://llblgen.com/tinyforum/Messages.aspx?ThreadID=18563&StartAtMessage=0&#104059

In general LINQ2LLBL works in most cases and, in general, all those problematic queries can be rewritten to make them to work. I know you don't want to rewrite anything, but in some cases you will have to. That's what we offer. There are a set of tests and examples of LINQ2LLBL showing how to make a lot of queries. They are not shipped in the trial installation. Let us know if you want we mail them to you.

We also offer other query approaches, like LLBLGen API and QuerySpec. I know you don't want to leave Linq, but just to let you know that LLBLGen API is full typed and QuerySpec is a fluent interface in top of our LLBLGen API, so they are still good for you. LINQ2LLBL is also a provider written in top of LLBLGen API.

daelmo wrote:

About the first problematic query (nested queries without correlation). This is some construct LINQ2LLBL doesn't support. The reason is explained in Linq2LLBL - Remarks and limitations:

Documentation wrote:

Nested queries in projection which have no correlation filter. A nested query inside the projection of a parent query has to have a correlation filter to tie the nested query's results to the parent's row, e.g. where o.CustomerID = parent.CustomerID. If this correlation filter isn't found and no correlation relation is found either through specification of a related set, e.g. c.Orders, an exception is thrown, as the nested set can't be tied to the right parent without this filter. The reason this isn't supported is that the alternative would be one query per parent row, which is very bad for performance overall.

... so, it's trying to avoid the EF problems you pointed in your first post. This performance gain has a little cost: you have to rework your query.

About the second problematic query (3-level nested correlated queries). The issue is the aliasing. You can confirm that if you see the exception. This is a known issue. See the remarks and limitation list in the link I posted. These are also some threads that address the same situation: http://llblgen.com/tinyforum/Messages.aspx?ThreadID=20387&StartAtMessage=0&#114991 http://llblgen.com/tinyforum/Messages.aspx?ThreadID=18563&StartAtMessage=0&#104059

In general LINQ2LLBL works in most cases and, in general, all those problematic queries can be rewritten to make them to work. I know you don't want to rewrite anything, but in some cases you will have to. That's what we offer. There are a set of tests and examples of LINQ2LLBL showing how to make a lot of queries. They are not shipped in the trial installation. Let us know if you want we mail them to you.

We also offer other query approaches, like LLBLGen API and QuerySpec. I know you don't want to leave Linq, but just to let you know that LLBLGen API is full typed and QuerySpec is a fluent interface in top of our LLBLGen API, so they are still good for you. LINQ2LLBL is also a provider written in top of LLBLGen API.

Well i actually don't mind leaving linq (so will consider queryspec) but i can't do it within the context of this project, i will however keep it in mind for other projects & internal projects. However i'm a little confused as to why you decided not to support 3+ level queries by design (but i'm pretty happy to learn that it's by design and not a bug , increasing my faith in the reliability of your product).

However i'm a little confused as to what part of the limitations page you refer to for both points.

1) For the nested subqueries, i only found issues there when starting a new query within the subquery, which isn't what i do, i keep chaining on properties of the current object, which itself contains the metadata to know how to navigate with keys from where he is, hence why i'm surprised that you can't generate the right code while i'm totally unsurprised about the exemples in the limitation page. There is a correlation filter, it's not in the where, it's from going throught a navigation property.

2) For 3+ levels subqueriesif i understand it well, when you parse the expression tree and fall on an expression, you generate a field and depend on it's fieldname being unique which fails if referencing the same field in the same row twice for diferent final actual entities, couldn't this be fixed simply by naming the properties PROP_Id1 PROP_Id2 etc (naming differently even the same data, which results in a bit of redundancy in the end result) and storing the id on an expression tree node basis to be able to remap it?

Also could you get back on me on the issue of the complex assignment with many (item !=null )? item.value : null and clarify if this is a limitation too or a bug (i expect it not being suported is a limitation, but it restricting the parent result set instead of throwing is a bug).

Also i would like the same bug or not supported clarification for the other bug i mentioned : A.Select(a=>a.B).Select(b=>b.C) failing while A.Select(a=>a.B.C) works

Thanks for the time you're spending on this, but it's really important for me to get all these answers and know which way you're moving toward (i was at the point i pondered the idea of making my own mini linq provider with only select & where support!)

Also, would it be possible to have a trial for 3.5 beta to try out queryspec?

v3.5 is beta, there is no trial for it. Only when it passes beta, there will be a trial version.

For the queries which are too complex for linq, try to use the normal LLBLGen Pro API code.