Linq2Db / Access - error with anonymous group by multiple fields

Question

I have 2 tables: pob (with results of some activities) and names (with user data). I try to select top 5 users from table pob based on their last activity date. So I inner join names and pob, then select top 5 users based on calculated max(date).

This query is working:

SELECT TOP 5
    [u].[id], 
    [u].[name],
    max([p].[date]) As LastDateOfUse,
FROM
    [pob] [p]
INNER JOIN
    [users] [u] 
ON
    [p].[id_name] = [u].[id] 
WHERE
    [p].[date] >= #2017-01-01#
GROUP BY
    [u].[id],
    [u].[name] 
ORDER BY
    max([p].[date]) DESC

Now I need to transform it into Linq query. This my attempt but it's not working.

"Key" is not a member of type "System.Collections.Generic.IEnumerable'1[VB$AnonymousType_2'2[pob,users]]".

Using db = New DbContext() With {.InlineParameters = True}

    Dim query1 = From p In db.pob
                 Join u In db.users On p.id_name Equals u.id
                 Where p.date >= New Date(2017, 1, 1)
                 Group New With {p, u} By pu = New With {Key u.id, Key u.name} Into pg = Group
                 Select New RecentUser With
                 {
                     .id = pu.id,
                     .name = pu.name,
                     .LastDateOfUse = pg.Max(Function(f) f.p.date)
                 }

    query1 = query1.OrderByDescending(Function(f) f.LastDateOfUse).Take(5)

    Return query1.ToList

End Using

If I remove .LastDateOfUse = pg.Max(Function(f) f.p.Date) like below it works. By 'works' I mean there is no exception but of course result of query is wrong however grouping is done properly.

Using db = New DbContext() With {.InlineParameters = True}

    Dim query1 = From p In db.pob
                 Join u In db.users On p.id_name Equals u.id
                 Where p.date >= New Date(2017, 1, 1)
                 Group New With {p, u} By pu = New With {Key u.id, Key u.name} Into pg = Group
                 Select New RecentUser With
                 {
                     .id = pu.id,
                     .name = pu.name
                 }

    Return query1.ToList

End Using

Edit

I also tried going through navigation properties like below, but again I receive the same error.

Using db = New DbContext() With {.InlineParameters = True}

    Dim query1 = From p In db.pob
                 Where p.date >= New Date(2017, 1, 1)
                 Group p By pu = New With {Key u.User.id, Key u.User.name} Into pg = Group
                 Select New RecentUser With
                 {
                     .id = pu.id,
                     .name = pu.name
                     .LastDateOfUse = pg.Max(Function(f) f.date)
                 }

    query1 = query1.OrderByDescending(Function(f) f.LastDateOfUse).Take(5)

    Return query1.ToList

End Using

And again if I remove .LastDateOfUse = pg.Max(Function(f) f.p.Date) like below it starts to work (proper grouping, wrong overall result).

Using db = New DbContext() With {.InlineParameters = True}

    Dim query1 = From p In db.pob
                 Where p.date >= New Date(2017, 1, 1)
                 Group p By pu = New With {Key u.User.id, Key u.User.name} Into pg = Group
                 Select New RecentUser With
                 {
                     .id = pu.id,
                     .name = pu.name
                 }

    Return query1.ToList

End Using

How can I transform above Sql query to Linq? (preferable answer in VB.Net but C# is ok too)

Answer 1

Solution

There is no solution yet. It looks like VB has bad Linq queries resolver - it creates unexpected method chain that can not be converted to SQL.

So instead

Group By ... Into pg = Group 

we need

Group By ... Into LastDateOfUse = p.Max(Function(f) f.date).

See below full query.

Using db = New DbContext() With {.InlineParameters = True}

    Dim query1 = From p In db.pob
                 Where p.date >= New Date(2017, 1, 1)
                 Group p By pu = New With {Key u.User.id, Key u.User.name} Into LastDateOfUse = p.Max(Function(f) f.date)
                 Select New RecentUser With
                 {
                     .id = pu.id,
                     .name = pu.name
                     .LastDateOfUse = LastDateOfUse
                 }

    Return query1.ToList

End Using

Another problem with lambda syntax

Using lambda syntax we receive another exception.

Dim query = db.pob.
Where(Function(f) f.date >= New Date(2017, 1, 1).
GroupBy(Function(f) New With 
{
   Key .userid= f.user.id,
   Key .username = f.user.name
}).Select(Function(f) New RecentUser With
{
   .id = f.Key.userid,
   .name = f.Key.username,
   .LastDateOfUse = f.Max(Function(g) g.date)
}).ToList

Exception

VB.NET compiler adds unnecessary Convert to IEnumerable when generating Expression Tree.

An unhandled exception of type LinqToDB.Linq.LinqException occurred in linq2db.dll
Convert(f).Select(g => g.Date).Max() cannot be converted to SQL

GitHub

I posted an issue here.

Svyatoslav Danyliv based on my issue opened his own here.