Frans are you sure that you attached the right assembly? Or is there something that I didn't understand?

As it didn't work with the version I had sent you, I put all four queries into one method, to make sure that the problem doesn't come from retrieving part of the query from a different method. My method now read as follows:

public static IQueryable<StoreArticleWithOpenOrderAmountEntiy> 
    StoreArticlesWithOpenOrderAmount(DateTime ReportingDate) {

    var metaData = new LinqMetaData();

    var lastOrderStateRelevantOrderLogEntryIds =
            from entries in metaData.OrderLogEntry
            where entries.EventDate <= ReportingDate &&
            entries.OrderEvent.OrderStateId != null
            group entries by entries.OrderId into g
            select new { LastEntryId = g.Max(d => d.Id) };

    var latestOrderLogEntries =
            from logEntries in metaData.OrderLogEntry
            join ids in lastOrderStateRelevantOrderLogEntryIds
            on logEntries.Id equals ids.LastEntryId
            select logEntries;

    var orderlogs = 
        from events in metaData.OrderEvent
        join entries in latestOrderLogEntries
        on events.Id equals entries.OrderEventId
        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 StoreArticleWithOpenOrderAmountEntiy {
                StoreArticleId = grp.Key,
                OpenOrderAmount = grp.Sum(l => l.Amount)
            };

    //For debugging purpose!
    foreach (var itm in items) {
            int i = itm.StoreArticleId;
            int j = itm.OpenOrderAmount;
    }

    return items;
}

where StoreArticleWithOpenOrderAmountEntiy is:

public class StoreArticleWithOpenOrderAmountEntiy {
    public int StoreArticleId { get; set; }
    public int OpenOrderAmount { get; set; }
}

When trying to loop through the items I still get the "Unknow column Amount" Exception. Here's the 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 [LastEntryId]
                                                                            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].[LastEntryId])) [LPA_L5]
                                                        INNER JOIN [KIS2008].[dbo].[OrderEvent] [LPA_L6]
                                                            ON [LPA_L6].[Id] = [LPA_L5].[OrderEventId])
                                                     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]

If I change

var orderlogs = 
        from events in metaData.OrderEvent
        join entries in latestOrderLogEntries
        on events.Id equals entries.OrderEventId
        join orderstates in metaData.OrderState
        on events.OrderStateId equals orderstates.Id
        where orderstates.Name == "Open"
        select entries;

to

    var orderlogs = 
        from events in metaData.OrderEvent
        join entries in latestOrderLogEntries
        on events.Id equals entries.OrderEventId
        where events.OrderState.Name == "Open"
        select entries;

I get the following error:

The property 'OrderState' isn't mapped to a field or database construct of entity type 'OrderEventEntity'. Did you mean to call an extension method instead? ('Count' vs. 'Count()') ?

true, I fixed that too, but after I attached the dll. Sorry for this, as it was in another thread and another issue. please see this post: http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=74138&ThreadID=13327

That has a dll build with the fix (it also contains your fix, it's a later build)

The unknown column 'Amount' error... I'll see if I can repro that.

(edit) reproduced... Hmm...

I commented out some parts of your query to nail down the root cause of the previous error. This seems like the same one which was popping up yesterday...

I'm sorry, as this must be really frustrating for you, but the dll from the other thread produces exactly the same errors.

edit: Yeah, back to square one!

kamiwa wrote:

I'm sorry, as this must be really frustrating for you, but the dll from the other thread produces exactly the same errors.

edit: Yeah, back to square one!

It does produce the same "Amount" error, and that's indeed frustrating It's the same error as we had yesterday, and the fix added yesterday is pretty much meaningless. The problem is that the aggregate 'Sum' targets a groupby query. In the Expression tree, it refers to the groupby expression subtree. However, as the SUM has to aggregate over the grouped data, it has to be placed inside the same query scope as the group by (SELECT SUM()... FROM ... GROUP BY ... ), otherwise the aggregation doesn't work obviously.

The thing is: as it refers to the groupby as a whole, the field used comes from a table. That table is inside the join! (one side of it, either left or right). But... which side to pick?

So I thought to be clever and select one side based on some criteria. But it turns out, as your queries showed, that it's undoable to pick either side.

So the solution is actually to turn the whole join into a query with its own select. Then there's no problem, as there's just 1 source: the whole query (it simply returns all fields in the join).

I have made it do so but some code falls over because of this, so I have to correct that (as things changed now), but that should be minor. So almost there!

And I had thought, hey, to make my code more readable, avoid nested queries (like I normally would have used in T-SQL).

Seems it would have saved you quite some work, if I had coded the thing like I used to in SQL.

What a royal PITA!

kamiwa wrote:

And I had thought, hey, to make my code more readable, avoid nested queries (like I normally would have used in T-SQL).

Seems it would have saved you quite some work, if I had coded the thing like I used to in SQL.

What a royal PITA!

hehehe Well, it should work with nested queries , so these bugs would otherwise pop up sooner or later (read: 1 day after RTM release ) so better fix them now.

I'm almost there, I have still an issue with multiple times the same field in the query, but that should be trackable without a lot of effort. Fingers crossed.

Fingers crossed.

K uBt ti's veyr hadr to tpye.

Sorry to pester you again.

var db = new Linq.LinqMetaData();
var lastLog =   from logs in db.OrderLogEntry 
            where logs.OrderId == Id 
            orderby logs.Id descending 
            select logs;

throws:

Method not found: Void SD.LLBLGen.Pro.ORMSupportClasses.ISortClause.set_EmitAliasForExpressionAggregateField(Boolean).

(edit) works without orderby

kamiwa wrote:

Sorry to pester you again.

var db = new Linq.LinqMetaData();
var lastLog =   from logs in db.OrderLogEntry 
            where logs.OrderId == Id 
            orderby logs.Id descending 
            select logs;

throws:

Method not found: Void SD.LLBLGen.Pro.ORMSupportClasses.ISortClause.set_EmitAliasForExpressionAggregateField(Boolean).

(edit) works without orderby

THat's because you use an ormsupportclasses dll of abuild earlier than yesterday. I'll attach one again for you as I had to change something in it.

I fixed the issue.

SELECT  [LPA_L1].[StoreArticleId], 
        [LPA_L1].[LPAV_] AS [OpenOrderAmount] 
FROM 
(
    SELECT  [LPA_L3].[StoreArticleId], 
            SUM([LPA_L3].[Amount]) AS [LPAV_] 
    FROM 
    (
        SELECT DISTINCT [LPA_L5].[Id], [LPA_L5].[OrderId], [LPA_L5].[StoreArticleId], [LPA_L5].[Amount], 
                    [LPA_L4].[Id] AS [Id0], [LPA_L4].[OrderId] AS [OrderId1], [LPA_L4].[OrderEventId], 
                    [LPA_L4].[EventDate], [LPA_L4].[UserId], [LPA_L4].[UserIp], [LPA_L4].[OrderErrorId], 
                    [LPA_L4].[OrderErrorDescription] 
        FROM 
        (
            (
                SELECT  [LPA_L6].[Id], [LPA_L6].[OrderId], [LPA_L6].[OrderEventId], [LPA_L6].[EventDate], 
                        [LPA_L6].[UserId], [LPA_L6].[UserIp], [LPA_L6].[OrderErrorId], [LPA_L6].[OrderErrorDescription] 
                FROM 
                (
                    (
                        (
                            SELECT  [LPA_L10].[Id], [LPA_L10].[OrderId], [LPA_L10].[OrderEventId], [LPA_L10].[EventDate], [
                                    LPA_L10].[UserId], [LPA_L10].[UserIP] AS [UserIp], [LPA_L10].[OrderErrorId], [LPA_L10].[OrderErrorDescription] 
                            FROM 
                            (
                                (
                                    SELECT  [LPA_L11].[LPAV_] AS [LastEntryId] 
                                    FROM 
                                    (
                                        SELECT  [LPA_L13].[OrderId], 
                                                MAX([LPA_L13].[Id]) AS [LPAV_] 
                                        FROM 
                                        ( 
                                            [KIS2008].[dbo].[OrderEvent] [LPA_L12]  INNER JOIN [KIS2008].[dbo].[OrderLogEntry] [LPA_L13]  
                                                ON  [LPA_L12].[Id]=[LPA_L13].[OrderEventId]
                                        ) 
                                        WHERE (((([LPA_L13].[EventDate] <= @EventDate1) AND ( [LPA_L12].[OrderStateId] IS NOT NULL)))) 
                                        GROUP BY [LPA_L13].[OrderId]
                                    ) [LPA_L11]
                                ) [LPA_L9]  INNER JOIN [KIS2008].[dbo].[OrderLogEntry] [LPA_L10]  ON  [LPA_L10].[Id] = [LPA_L9].[LastEntryId]
                            )
                        ) [LPA_L6]  INNER JOIN [KIS2008].[dbo].[OrderEvent] [LPA_L7]  ON  [LPA_L6].[OrderEventId] = [LPA_L7].[Id]
                    ) INNER JOIN [KIS2008].[dbo].[OrderState] [LPA_L8]  ON  [LPA_L7].[OrderStateId] = [LPA_L8].[Id]
                ) 
                WHERE ((([LPA_L8].[Name] = @Name2)))
            ) [LPA_L4]  INNER JOIN [KIS2008].[dbo].[OrderItem] [LPA_L5]  ON  [LPA_L5].[OrderId] = [LPA_L4].[OrderId]
        )
    ) [LPA_L3] 
    GROUP BY [LPA_L3].[StoreArticleId]
) [LPA_L1]

Needless to say, it was worth it

I'll wrap things up and will attach a new build to this post. Just a second

(edit) See attached build of runtime + linq dll Was old file, removed.

SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll is version 2.6.8.513, the one from the other thread you had pointed me to before was 2.6.8.514.

Is that still correct ?

(edit) Still getting the same errors with the new build.

(edit 2) the orderby error has gone. The unknown column 'Amount' respectively the The property 'OrderState' isn't mapped to a field or database construct of entity type 'OrderEventEntity'. Did you mean to call an extension method instead? ('Count' vs. 'Count()') ? are still there.

kamiwa wrote:

SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll is version 2.6.8.513, the one from the other thread you had pointed me to before was 2.6.8.514.

Is that still correct ?

(edit) Still getting the same errors with the new build.

(edit 2) the orderby error has gone. The unknown column 'Amount' respectively the The property 'OrderState' isn't mapped to a field or database construct of entity type 'OrderEventEntity'. Did you mean to call an extension method instead? ('Count' vs. 'Count()') ? are still there.

No that's not correct. Let me check if I have attached the right files.

(edit) I did attach the wrong files. My appologies

I've now made new builds. I'll attach them to this post

(edit) see attached files.

YEAH! You're my heroe! It finally works!

At least as long as I manually join OrderState.

If I change

var orderlogs = 
        from events in metaData.OrderEvent
        join entries in latestOrderLogEntries
        on events.Id equals entries.OrderEventId
        join orderstates in metaData.OrderState
        on events.OrderStateId equals orderstates.Id
        where orderstates.Name == "Open"
        select entries;

to

    var orderlogs = 
        from events in metaData.OrderEvent
        join entries in latestOrderLogEntries
        on events.Id equals entries.OrderEventId
        where events.OrderState.Name == "Open"
        select entries;

I still get:

The property 'OrderState' isn't mapped to a field or database construct of entity type 'OrderEventEntity'. Did you mean to call an extension method instead? ('Count' vs. 'Count()') ?

But that's something I can live with.

Should you have planned to go out tonight (lovely weather here), have a few Heineken and send me the bill. :-) IOU.

Thanks. I'll be back should I discover another bug.

kamiwa wrote:

YEAH! You're my heroe! It finally works!

At least as long as I manually join OrderState.

If I change

var orderlogs = 
        from events in metaData.OrderEvent
        join entries in latestOrderLogEntries
        on events.Id equals entries.OrderEventId
        join orderstates in metaData.OrderState
        on events.OrderStateId equals orderstates.Id
        where orderstates.Name == "Open"
        select entries;

to

    var orderlogs = 
        from events in metaData.OrderEvent
        join entries in latestOrderLogEntries
        on events.Id equals entries.OrderEventId
        where events.OrderState.Name == "Open"
        select entries;

I still get:

The property 'OrderState' isn't mapped to a field or database construct of entity type 'OrderEventEntity'. Did you mean to call an extension method instead? ('Count' vs. 'Count()') ?

But that's something I can live with.

I'm glad, because I can't

I'll fix this too, it should work by now. The weirdest thing is... we have many queries with constructs like that which succeed...

Should you have planned to go out tonight (lovely weather here), have a few Heineken and send me the bill. :-) IOU.

hehe

The weirdest thing is... we have many queries with constructs like that which succeed...

I can probably tell you why that is: OrderState is joined on OrderEvent by its Id value and the field OrderStateId in the OrderEvent entity is nullable.

Is that a possible explanation?

kamiwa wrote:

The weirdest thing is... we have many queries with constructs like that which succeed...

I can probably tell you why that is: OrderState is joined on OrderEvent by its Id value and the field OrderStateId in the OrderEvent entity is nullable.

Is that a possible explanation?

I don't think that's it, but it might. The preprocessor, the expression tree handler which runs first over the tree, has to convert all member access expressions into entity expressions if possible, if it doesn't, it will end up with this error in the last expression tree handler (as that one expects just fields, as all the other ones should have been handled earlier by the preprocessor).

So it's odd that the preprocessor misses this, so I've to dig with a debugger to find out

This query does the same and has the same error:

var q = from o in metaData.Order 
        join c in metaData.Customer on o.CustomerId equals c.CustomerId
        where o.Customer.Country=="Germany"
        select o;

The parameter isn't handled properly in the preprocessor so it fails to resolve the o.Customer part in the where to a customer entity.

Got an idea how to solve the issue?

As I already said, I can live with it as you just need to know that you must join all the tables manually which are used in the query. Just the same like in T-SQL.

BTW: Has somebody already "invented" a SQL to LINQ converter? You could visually design your queries in VS or MS SSMS, save the SQL and let the converter convert it to valid LINQ statements. Or even more handy, an add-in for the two that does the trick. Wouldn't that be awesome?

Fixed it. Some other fixes are pending so I'll wait till these are fixed as well.

Sql to linq? I don't think that's possible at all.

Seems I'm not the only one who had this idea:

https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=327373&wa=wsignin1.0

kamiwa wrote:

Seems I'm not the only one who had this idea:

https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=327373&wa=wsignin1.0

I don't think that's a good idea. If people want to use SQL, they shouldn't use a higher level language to express what they want to express, as the projection of C#/Linq/any o/r mapper query language to SQL isn't 1:1, so converting it back is useless.

Btw, it's more tougher than I thought... still fixing..

Just take your time!

kamiwa wrote:

Just take your time!

Heh Well, of course there's never enough time... but! I got it. I couldn't find a way to make the preprocessor convert ALL entity member references, so I re-used a piece of code in the phase after it, so the members it encounters there are properly handled.

Got a new build yet for me to test? Now that I know the feature is available, it suddenly became a MUST HAVE!

kamiwa wrote:

Got a new build yet for me to test? Now that I know the feature is available, it suddenly became a MUST HAVE!

See the attached build.

Awesome! Working great! So for now, no more issues found here.

Thanks again Frans and sorry for taking so long with the reply. Got momentarily distracted by today's release of another beta: http://labs.divx.com/ProjectRemoulade