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()') ?