Hi,

I want to to a group by loanId, and then get the top 1 repaymentId (Id). But i get an exception stating:

{"An exception was caught during the execution of a retrieval query: Invalid column name 'id'.\r\nInvalid column name 'id'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."}

This is the code i have written so fare.

        public Dictionary<int, int> GetLatestValidEnteredRepayments(int[] loanIds)
        {
            return DataSource.PostingSummary.
                Where(ps => ps.IsDeleted == false && loanIds.Contains(ps.LoanId)).
                GroupBy(groupBy => groupBy.LoanId).Select(t => new
                    {
                        LoanId = t.Key,
                        RepaymentId = t.OrderByDescending(orderBy => orderBy.Id).Select(p => p.Id).First()
                    }).
                ToDictionary(key => key.LoanId, value => value.RepaymentId);
        }

Here is an example of the data I'm querying: Orignal Data: ID LoanId 47848 3109 28249 3109 25237 3109 22263 3109 19318 3109 16390 3109 13687 3109 10651 3109 53642 4274 18709 4274 22284 4274 25255 4274 47795 4274 45343 4274 31408 4274

And the expected result: LoanID ID 4274 53642 3109 47848

How would I construct a query to acheive this?

this is the StackTrace: at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsDataReader(ITransaction transactionToUse, IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsProjection(List1 valueProjectors, IGeneralDataProjector projector, ITransaction transactionToUse, IRetrievalQuery queryToExecute, Dictionary2 typeConvertersToRun) at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsProjection(List1 valueProjectors, IGeneralDataProjector projector, ITransaction transactionToUse, IEntityFields fields, IPredicateExpression filter, IRelationCollection relations, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider.ExecuteValueListProjection(QueryExpression toExecute) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery1.Execute() at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() at System.Linq.Enumerable.ToDictionary[TSource,TKey,TElement](IEnumerable1 source, Func2 keySelector, Func2 elementSelector, IEqualityComparer1 comparer) at System.Linq.Enumerable.ToDictionary[TSource,TKey,TElement](IEnumerable1 source, Func2 keySelector, Func2 elementSelector) ...

This is the Query Executed: "\r\n\tQuery: SELECT [LPA_L1].[LoanId], (SELECT TOP(@top0) [LPA_L1].[id] AS [Id] FROM [c4world-main-repay-gui].[dbo].[PostingSummary] [LPLA_2] ORDER BY [LPA_L1].[id] DESC) AS [RepaymentId] FROM (SELECT [LPLA_1].[loan_id] AS [LoanId] FROM [c4world-main-repay-gui].[dbo].[PostingSummary] [LPLA_1] WHERE ( ( ( ( [LPLA_1].[is_deleted] = @IsDeleted2) AND ( [LPLA_1].[loan_id] IN (@LoanId3, @LoanId4, @LoanId5))))) GROUP BY [LPLA_1].[loan_id]) [LPA_L1]\r\n\tParameter: @top0 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.\r\n\tParameter: @IsDeleted2 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False.\r\n\tParameter: @LoanId3 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 3109.\r\n\tParameter: @LoanId4 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 3109.\r\n\tParameter: @LoanId5 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 4274.\r\n"

LLBLGen Pro version + buildnr: 2.6 Final October 9th, 2009 Runtime library version: 2.6.9.1005 Database Version: SQL Server 9.00.3042.00 SP2

I tried to use Linq To Sql, and it worked as espected: the query was:

PostingSummary.
Where(r=>r.Is_deleted == false &&  new []{3109,2334}.Contains(r.Loan_id) ).
GroupBy(m=>m.Loan_id).Select(t=> new {
t.Key,
t2= t.OrderByDescending(tt=>tt.Id).Select(tt=>tt.Id).First()}).
ToDictionary(key => key.Key, value => value.t2)

and the generated sql looks like:

-- Region Parameters
DECLARE @p0 Int SET @p0 = 3109
DECLARE @p1 Int SET @p1 = 2334
-- EndRegion
SELECT [t1].[loan_id] AS [Key], (
    SELECT TOP (1) [t2].[id]
    FROM [PostingSummary] AS [t2]
    WHERE ([t1].[loan_id] = [t2].[loan_id]) AND (NOT ([t2].[is_deleted] = 1)) AND ([t2].[loan_id] IN (@p0, @p1))
    ORDER BY [t2].[id] DESC
    ) AS [t2]
FROM (
    SELECT [t0].[loan_id]
    FROM [PostingSummary] AS [t0]
    WHERE (NOT ([t0].[is_deleted] = 1)) AND ([t0].[loan_id] IN (@p0, @p1))
    GROUP BY [t0].[loan_id]
    ) AS [t1]

please download the latest build of the runtimes, as your linq provider is from october. The latest hotfix for the linq provider available is the one attached to this post: http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=96960&ThreadID=17312

Thanks for the quick response.

I tried to update to the latest runtime and use the latest hotfix for the linq provider, but the problem is still there.

/Hallur

ok, we'll look into it and will get back to you.

To work around it, use the equivalent of obtaining the maximum value of a grouped set, by using the Max() aggregate function:

public Dictionary<int, int> GetLatestValidEnteredRepayments(int[] loanIds)
{
    return DataSource.PostingSummary.
        Where(ps => ps.IsDeleted == false && loanIds.Contains(ps.LoanId)).
        GroupBy(groupBy => groupBy.LoanId).Select(t => new
            {
                LoanId = t.Key,
                RepaymentId = t.Max(v => v.Id)
            }).
        ToDictionary(key => key.LoanId, value => value.RepaymentId);
}

We'll look into the error you got and will prepare a fix for it. The query above gives the same result as you wanted, namely per loanid the maximum id.

the group by query is referenced in both the projection itself and the nested query inside the projection. Our linq provider doesn't have code for this edge case where the group by query has to be copied and a correlated subquery has to be created out of it. This is an edge case, because in the situation where an aggregate is formulated on the grouped result (as is in my workaround), the group by query is also referenced twice, exactly the same, but the query then doesn't have to copied.

It turns out to be rather awkward to create the code for this edge case so we'll postpone it, as there's a more efficient workaround

I can confirm that the workaround works, so thanks for the quick help. /Hallur