Hi there,

I have a table that contains the columns - ID: PK - FK_ID: Non-unique ID from a related table - Modified: Datetime

I have the following SQL query that returns the most recent entry for a all FK_IDs.

With TableWithRank as
(
    Select *, Rnk = ROW_NUMBER() over (partition by FK_ID order by Modified desc)
    from Table
)

Select ID from TableWithRank where Rnk=1

I tried the following Linq

            var l = from c in metaData.Table
                    group c by c.FK_ID into grp
                    select grp.OrderByDescending(x => x.Modified).First();

I am getting the exception

Couldn't create any correlation filter lambda because the nested query didn't have any correlation filters. Please specify a filter in the nested query to tie the nested query to the parent query

What am I doing wrong?

thank you for your help on this!

Best regards

Which runtime library version are you using? We need the "OrmSupport"dll & the "LinqSupport" dll build numbers.

Oh sorry!

SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll 3.1.11.1129 SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll 3.1.11.1115

Best Regards

The group by you defined is actually a nested query, as you returns entities grouped. Grouping in linq is different from grouping in sql, as grouping in linq is grouping elements on a field/set of fields, and still return the whole element grouped (e.g. an entity). Try to reformulate your query:

var l = from c in metaData.Table
     group c by c.FK_ID into grp
     orderby grp.Modified
     select grp).First();

These are relevant discussions about that: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=18793&StartAtMessage=0&#105499 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=17537&StartAtMessage=0&#98194