I think I found a bug!
Will look into it 
Thank you Frans!
Apart from that bug your implementation of Linq is just cool! 
kamiwa wrote:
Apart from that bug your implementation of Linq is just cool!
Thanks! 
Btw, I don't understand where the last 'where' comes from... it's not in your query... will try to repro it here.
(edit) When I try to reproduce it, I use this query:
LinqMetaData metaData = new LinqMetaData(adapter);
var q1 = from o in metaData.Order
where o.OrderDate <= new DateTime(1997, 8, 1)
&& o.EmployeeId != null
group o by o.CustomerId into g
select new { LastId = g.Max(x => x.OrderId) };
var q = from o in metaData.Order
join ids in q1
on o.OrderId equals ids.LastId
select o;
which IMHO comes down to the same query as you have. I run into a different issue: the select clause for q has the plain table name, not a reference to the alias, however I don't have the problem with the dangling where. Could you check again if you have copied the right query ?
Sorry Frans,
you're right indeed. I had problems formatting the SQL in the forum editor. So I reformatted it in SQL Server Management Studio, copied and pasted again. Apparently something went wrong there. 
The right query reads:
SELECT DISTINCT [KIS2008].[dbo].[OrderLogEntry].[Id],
[KIS2008].[dbo].[OrderLogEntry].[OrderId],
[KIS2008].[dbo].[OrderLogEntry].[OrderEventId],
[KIS2008].[dbo].[OrderLogEntry].[EventDate],
[KIS2008].[dbo].[OrderLogEntry].[UserId],
[KIS2008].[dbo].[OrderLogEntry].[UserIP] AS [UserIp],
[KIS2008].[dbo].[OrderLogEntry].[OrderErrorId],
[KIS2008].[dbo].[OrderLogEntry].[OrderErrorDescription]
FROM ((SELECT [LPA_L3].[LPAV_]
FROM (SELECT [LPA_L5].[OrderId],
MAX([LPA_L5].[Id]) AS [LPAV_]
FROM ([KIS2008].[dbo].[OrderEvent] [LPA_L4]
INNER JOIN [KIS2008].[dbo].[OrderLogEntry] [LPA_L5]
ON [LPA_L4].[Id] = [LPA_L5].[OrderEventId])
WHERE (((([LPA_L5].[EventDate] <= @EventDate1)
AND ([LPA_L4].[OrderStateId] IS NOT NULL))))
GROUP BY [LPA_L5].[OrderId]) LPA_L3) [LPA_L1]
INNER JOIN [KIS2008].[dbo].[OrderLogEntry] [LPA_L2]
ON [LPA_L2].[Id] = [LPA_L1].[LastEntryId])
However, the issues remains. The last join operation still tries to join the non existent LastEntryId column.
BTW: The difference btw. your first LINQ query and mine is, that my query joins an additional OrderEvent table which it checks for the null values.
But basically: Yeah, your LINQ statement does the same than mine.
EDIT: Corrected typo copy => copied!
EDIT2: Just found out why the query was different from yours!
Originally my 2nd LINQ statement was:
var latestOrderLogEntries =
from logEntries in metaData.OrderLogEntry
join ids in lastOrderStateRelevantOrderLogEntryIds
on logEntries.Id equals ids.LastId
where logEntries.Id == ids.LastId
select logEntries;
This produced the following SQL:
SELECT DISTINCT [LPA_L2].[Id],
[LPA_L2].[OrderId],
[LPA_L2].[OrderEventId],
[LPA_L2].[EventDate],
[LPA_L2].[UserId],
[LPA_L2].[UserIP] AS [UserIp],
[LPA_L2].[OrderErrorId],
[LPA_L2].[OrderErrorDescription]
FROM ((SELECT [LPA_L3].[LPAV_]
FROM (SELECT [LPA_L5].[OrderId],
MAX([LPA_L5].[Id]) AS [LPAV_]
FROM ([KIS2008].[dbo].[OrderEvent] [LPA_L4]
INNER JOIN [KIS2008].[dbo].[OrderLogEntry] [LPA_L5]
ON [LPA_L4].[Id] = [LPA_L5].[OrderEventId])
WHERE (((([LPA_L5].[EventDate] <= @EventDate1)
AND ([LPA_L4].[OrderStateId] IS NOT NULL))))
GROUP BY [LPA_L5].[OrderId]) LPA_L3) [LPA_L1]
INNER JOIN [KIS2008].[dbo].[OrderLogEntry] [LPA_L2]
ON [LPA_L2].[Id] = [LPA_L1].[LastId])
WHERE (((([LPA_L2].[Id] = [LPA_L1].[LastId]))))
That's where the additionally WHERE comes from. Somehow I seem to have mixed the two versions, when trying to produce some readable SQL. Sorry again.
Anyway, the interesting thing is, that when you use the redundant WHERE clause, the main SELECT statement will correctly reference the ALIAS instead of the table name. Without it, the SELECT statement will reference the table name and additionally will have the naming conflict for the LastId column.
Seems sommin' is rotten in the state of Denmark.
Heh Thanks for the new query
I found the issue. The code in the runtime which traverses the derived tables to see if there are fields targeting the derived table but aren't derived table targeting fields (see example below) are corrected. This 'correction' is a bit overreacting, as it can't express itself well enough and therefore the alias in the derived table targeting field (which pulls the MAX value) loses its alias of LastEntryId and is reset to its name LPAV_, which is of course wrong.
The routine which finds these fields, can find all fields in all elements, so also the filter fields.
Hopefully I have a fix in a few hours
kamiwa wrote:
That's what I call First Class Customer support! And that although it is a Beta version which is bound to have bugs!
Well, I hate to have bugs in my code, and the more bugs there are in a current build of a beta, the less people are interested in keep testing it.
I've fixed it btw.
See attached files.
Sorry Frans,
I can only give you 99 points out of 100.
It sure fixed the original problem but it seems that it doesn't do it 100%!
public static IQueryable<StoreArticleWithOpenOrderAmountEntiy> StoreArticlesWithOpenOrderAmount(DateTime ReportingDate) {
var metaData = new LinqMetaData();
//Retries the Data from first sample. Working fine now!
IQueryable<OrderLogEntryEntity> logs = LastOrderStateRelevantOrderLogEntries(ReportingDate);
var orderlogs = from entries in logs
join events in metaData.OrderEvent
on entries.OrderEventId equals events.Id
join orderstates in metaData.OrderState
on events.OrderStateId equals orderstates.Id
where orderstates.Name == "Open"
select entries;
var items = from orderItems in metaData.OrderItem
join order in orderlogs
on orderItems.OrderId equals order.OrderId
group orderItems by orderItems.StoreArticleId
into grp select new {
StoreArticleId = grp.Key,
OpenOrderAmount = grp.Sum(l => l.Amount)
};
return items.Cast<StoreArticleWithOpenOrderAmountEntiy>();
}
will now produce the following SQL:
SELECT [LPA_L1].[StoreArticleId],
[LPA_L1].[LPAV_] AS [OpenOrderAmount]
FROM (SELECT [LPA_L4].[StoreArticleId],
SUM([LPA_L3].[Amount]) AS [LPAV_]
FROM ((SELECT [LPA_L5].[Id],
[LPA_L5].[OrderId],
[LPA_L5].[OrderEventId],
[LPA_L5].[EventDate],
[LPA_L5].[UserId],
[LPA_L5].[UserIp],
[LPA_L5].[OrderErrorId],
[LPA_L5].[OrderErrorDescription]
FROM (((SELECT [LPA_L9].[Id],
[LPA_L9].[OrderId],
[LPA_L9].[OrderEventId],
[LPA_L9].[EventDate],
[LPA_L9].[UserId],
[LPA_L9].[UserIP] AS [UserIp],
[LPA_L9].[OrderErrorId],
[LPA_L9].[OrderErrorDescription]
FROM ((SELECT [LPA_L10].[LPAV_] AS [LastId]
FROM (SELECT [LPA_L12].[OrderId],
MAX([LPA_L12].[Id]) AS [LPAV_]
FROM ([KIS2008].[dbo].[OrderEvent] [LPA_L11]
INNER JOIN [KIS2008].[dbo].[OrderLogEntry] [LPA_L12]
ON [LPA_L11].[Id] = [LPA_L12].[OrderEventId])
WHERE (((([LPA_L12].[EventDate] <= @EventDate1)
AND ([LPA_L11].[OrderStateId] IS NOT NULL))))
GROUP BY [LPA_L12].[OrderId]) [LPA_L10]) [LPA_L8]
INNER JOIN [KIS2008].[dbo].[OrderLogEntry] [LPA_L9]
ON [LPA_L9].[Id] = [LPA_L8].[LastId])
WHERE ((([LPA_L9].[Id] = [LPA_L8].[LastId])))) [LPA_L5]
INNER JOIN [KIS2008].[dbo].[OrderEvent] [LPA_L6]
ON [LPA_L5].[OrderEventId] = [LPA_L6].[Id])
INNER JOIN [KIS2008].[dbo].[OrderState] [LPA_L7]
ON [LPA_L6].[OrderStateId] = [LPA_L7].[Id])
WHERE ((([LPA_L7].[Name] = @Name2)))) [LPA_L3]
INNER JOIN [KIS2008].[dbo].[OrderItem] [LPA_L4]
ON [LPA_L4].[OrderId] = [LPA_L3].[OrderId])
GROUP BY [LPA_L4].[StoreArticleId]) [LPA_L1]
This time it's the Amount column which is undefined!
Well, I hate to have bugs in my code, and the more bugs there are in a current build of a beta, the less people are interested in keep testing it.
Guess we all hate those darn bugs in our code. But after all it reminds us that we all are only humans. Even those with a MVP award for C#. Congratulations btw.
And ppl. using Beta versions, should be clearly aware that the purpose of them is bug squeezing.
kamiwa wrote:
Sorry Frans,
I can only give you 99 points out of 100.
It sure fixed the original problem but it seems that it doesn't do it 100%!
public static IQueryable<StoreArticleWithOpenOrderAmountEntiy> StoreArticlesWithOpenOrderAmount(DateTime ReportingDate) { var metaData = new LinqMetaData(); //Retries the Data from first sample. Working fine now! IQueryable<OrderLogEntryEntity> logs = LastOrderStateRelevantOrderLogEntries(ReportingDate); var orderlogs = from entries in logs join events in metaData.OrderEvent on entries.OrderEventId equals events.Id join orderstates in metaData.OrderState on events.OrderStateId equals orderstates.Id where orderstates.Name == "Open" select entries; var items = from orderItems in metaData.OrderItem join order in orderlogs on orderItems.OrderId equals order.OrderId group orderItems by orderItems.StoreArticleId into grp select new { StoreArticleId = grp.Key, OpenOrderAmount = grp.Sum(l => l.Amount) }; return items.Cast<StoreArticleWithOpenOrderAmountEntiy>(); }will now produce the following SQL: (snip)
This time it's the Amount column which is undefined!
Weird. The main thing is that 'Amount' should be a property of orderItem. Is that the case? If so, the alias is wrong: it then should be LPA_L4 instead of LPA_L3. I'll see if I can create a reproducing query
Well, I hate to have bugs in my code, and the more bugs there are in a current build of a beta, the less people are interested in keep testing it.
Guess we all hate those darn bugs in our code. But after all it reminds us that we all are only humans. Even those with a MVP award for C#.
Thanks
Weird. The main thing is that 'Amount' should be a property of orderItem. Is that the case? If so, the alias is wrong: it then should be LPA_L4 instead of LPA_L3. I'll see if I can create a reproducing query
Confirmed. Amount is a property in OrderItem.
Amount gets summed as LPAV_ (SUM([LPA_L3].[Amount]) AS [LPAV_]) but appart from that it doesn't get selected at all. So it's neither a member of LPA_L3 nor LPA_L4. 
Would it help if I attached a few CREATE TABLE statements? Might be easier to reproduce the bugs.
One of the things which goes wrong is that when you have joins with queries you already produced, you can have a join tree at the right side of the JOIN expression, and the code doesn't anticipate on that, as with normal queries like: var q = from c in metaData.Customer join o in metaData.Order on c.CustomerId equals o.CustomerId join od in metaData.OrderDetail on o.OrderId equals od.OrderId where c.Country=="Germany" select o;
the join tree is at the left side of the upper join expression. However a query with a dummy select (as the linq designers thought it would be 'great' to have a projection merged into the join statement 
) gets its select removed by the compiler and the projection is merged into the join, so the query is just a 'join' statement. if that query is then used at the RIGHT side of another join (as you do), this goes wrong as that would effectively make the join have a join tree at the right side.
I think this is one of reasons the alias goes wrong (my repro query dies with no join available at all, so there's another issue I've to fix first)
I managed to get my query running, and I indeed see that the SUM field is picked from the wrong side of the relation (from the query, while the field is in the entity joined). I'll dig deeper into this (as soon as I've fixed the dumb alias issue I have ran into as well... aliases... they're not my friends 
)
I've been playing around with some more LINQ statements, and must say it's currently not all too obvious what LINQ expressions will work and which ones won't. Intellisense and compiler say it's fine but the resulting query still won't worked.
Had expected a bit more from LINQ. I still think that once you found out what works and what doesn't it'll proove to be pretty time saving and its awesome that it has been implemented into LLBLGen.
Having used SQL for so many years, a LINQ statement still seems to be more natural, than all the predicate, filter and order expressions you normally would have had to specify in your LLBLGen code to retrieve the data you want.
So just keep up the good work, and lets hope that Microsoft's implementation of LINQ doesn't become too powerful.
If you read Scott Gu's latest blog entry about the beta release of Visual Studio 2008 SP 1, it makes you wonder how much longer it will take till Microsoft has finally integrated all the great features that formerly where the domain of 3rd party add ons.
Regards,
Kai alias kamiwa
GOT IT!
Maaan.. this was a deep one. The toughest things with debugging linq queries is that the tree is changed so many times and you've to track down teh root of a particular result in the tree. But it's fixed now.
I'll attach a tempbuild to this post. There are some small other issues left (see other threads) so expect some updates on this code
(edit) see attachment
Ok, I've received your email. I get a different error though. I've commented out the for loops on the intermediate queries which don't do anything.
I do: var q = KIS2008.Data.Linq.DataRetrieval.StoreArticlesWithOpenOrderAmount(DateTime.Now);
(I don't have any data in the db, just your schema) and I get: Unhandled Exception: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException: The property 'OrderEvent' isn't mapped to a field or database construct of entity type 'OrderLogEntryEntity'. Did you mean to call an extension method instead? ('Count' vs. 'Count()') ? at D.LLBLGen.Pro.LinqSupportClasses.LinqUtils.GetFieldObjectAndSetObjectAlias(IEntityCore entityInstance, String fieldName, SetAlias obj ectAlias) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v2.0\LinqSupport\LinqSupportClasses .NET 3.5\LinqUtils.cs:line 383
which is a similar error as this thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=13327
When I enable the for loops, I get the error you reported. Will look into both (though the for loops shouldn't be necessary, in fact you shouldn't use these, as you're re-using a query.)
Otis wrote:
I get: Unhandled Exception: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException: The property 'OrderEvent' isn't mapped to a field or database construct of entity type 'OrderLogEntryEntity'. Did you mean to call an extension method instead? ('Count' vs. 'Count()') ?
Yeah, I ran into that one as well!
When I enable the for loops, I get the error you reported. Will look into both
No, the loops aren't necessary. I just used them for debugging purpose.
I'll now throw an exception if a cast like the one in your code is encountered, as there's no other way to handle it.
The error with the null type is caused by the fact that the left side of the join is a query but it's seen as a normal join. This will result in the left side ending up like a set of relations without a projection. However the on clause refers to the whole left side as its source, and with just a bunch of relations that's not possible, as it has to refer to the REAL table inside the left side, which is impossible, as it's a full query (black box). The solution is to detect if its a full query and then simply set a flag which will make sure it ends up as a query. You can work around this now by switching left and right in the orderlogs query. So instead of having 'logs' at the left side, place it on the right side of the join. This works as the code assumes that if a join ends up at the right side it's always a query.
I'll add proper code which makes it work regardless where the query is placed, left or right.
kamiwa wrote:
Otis wrote:
The main problem I run into now is that the cast you specify is seen as part of the query, which means that the cast has to be done by our framework. As the type to cast to isn't an entity, it simply returns the complete expression, which isn't good either.
It seems that in general the whole casting thing isn't a good idea at all! You always should return etities from your LINQ statements. This has been discussed in Rick Strahl's Web Log as well and seems to be a problem which is LINQ specific and not LLBLGen related.
The cast statement you specified would, if it would be executed as the IEnumerable<U>.Cast<T>(), be running over the resultset, cast every element to T into a new IEnumerable<T>. so basicly would do: foreach(U u in source) { yield return (T)u; }
Not something you'd do normally. 'Cast' is indeed a weird statement. I also had some problems with it in the provider, as well as with the 'as' keyword, as they simply can't be converted to the target platform (SQL) so the behavior they SHOULD show isn't there..
If you got time for it, take a look at these two blog entries and the user comments: http://www.west-wind.com/weblog/posts/33570.aspx http://www.west-wind.com/weblog/posts/143814.aspx
Thanks for the links. Yes I agree with Rick on this (but that's not hard, Rick is mostly right on many things )
OK. Thanks again.