I'm having a really strange issue with Linq/LLBLGen that I've found a way to reproduce. I'm in the middle of a large project right now and this is kind of killing me.

Below is a very simplified version of what's happening. As an example, let's say Stores have Departments that have Products.

metaData.Products.Select(p => p.Department.Store.Departments.Count());
// result: [1, 3, 6, 8]

metaData.Products.Select(p => p.Department.Store.Departments.Where(d => d.StoreId == d.StoreId).Count());
// result: [1, 3, 6, 8]

metaData.Products.Select(p => p.Department.Store.Departments.Where(d => p.Department.StoreId == p.Department.StoreId).Count());
// result: [49815]

I have no clue why this is happening. Tracking this down took forever but I eventually worked my code down to this by trial and error. Seems like a bug, right?

What LLBLGen version and runtime library version are you using? (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=12769)

..and What is the real situation you are facing (your real need)? I mean, the last query doesn't make much sense, Does it?

I'm running LLBLGen Pro v3.1 released 02/24/2012.

And I realize that the query makes no sense. All I'm trying to do is point out the fact that introducing "p.Department.*" into the Where causes it to work incorrectly.

My real situation is much more complicated but boils down to this in the end. I need to retrieve a collection of entities (let's call them C) filtered by whether or not their parent (B) has the largest X value among all other Bs that belong to B's parent (A) that share the value of a particular property (Y) in common. So I'm doing something like:

metaData.C.Where(c => c.B.X == c.B.A.Bs.Where(b => b.Y == c.B.Y).Max(b => b.X))
// does not work

Although, the following works fine for retrieving a collection of Bs by the same logic:

metaData.B.Where(b => b.X == b.A.Bs.Where(bb => bb.Y == b.Y).Max(bb => bb.X))
// works

I'm not sure why placing the exact same code a level deeper is causing this to fail

You can include a correlation in your lambdas, however you need to have this in mind:

1. Every time you include a navigator, a join will be included in the generated SQL
2. If you include a correlation with the outer query, and you do in the way (X.Id == A.Y.Id) that query will contain an extra JOIN.

IMHO you are complicating things. You could just add appropriate joins and then select what you really want

var q = from x in metaData.A
join B in metaData.B on A.Id equals B.Id
join....
where C.SomeId == someValue
select B;

You can inspect the generated SQL to know how your linq is translated into sql.

I ended up writing a really ugly hack to get this working because of my time constraints. It's too bad that type of thing fails, and especially in such a weird way.

Thanks for the help though, much appreciated!

dizlexik wrote:

I ended up writing a really ugly hack to get this working because of my time constraints. It's too bad that type of thing fails, and especially in such a weird way.

We feel sorry about that. However that is how Linq works, even on other providers: if you walk through navigators it will result in joins and subqueries, and this is good, but should be used in certain situations. Whenever you could use joins, then use them directly.

In v3.5 QuerySpec was released officially, which is a query engine made by the creators of LLBLGen. The generated SQL is more predictable and it's intuitive as well. When you have some time give it a spin

dizlexik wrote:

Thanks for the help though, much appreciated!

You are very welcome

QuerySpec looks very cool! I'll definitely have to check it out.

Thanks again