get error when use first() after goupby()

Question

I have two table like this :

CREATE TABLE [dbo].[Transactions](
    [Id] uniqueidentifier NOT NULL,
    [CustomerId] uniqueidentifier NOT NULL, // equals to AspNetUsers.Id
    [CoinId] uniqueidentifier NOT NULL,
    [Amount] [decimal](18, 8) NOT NULL,
    [Balance] [decimal](18, 8) NOT NULL,
    [CreatedOn] [datetime2](7) NOT NULL
)

CREATE TABLE [dbo].[AspNetUsers](
    [Id] uniqueidentifier NOT NULL,
    [FullName] nvarchar(256) NOT NULL
)

CREATE TABLE [dbo].[Coins](
    [Id] uniqueidentifier NOT NULL,
    [Name] nvarchar(256) NOT NULL
)

I want to create a report that shows howmuch balance has every customer. my linq query is :

var q = (from t in _db.Transactions
         join u in _db.Users on t.CustomerId equals u.Id
         group t by new { t.CustomerId, u.FullName } into grp
         where grp.OrderByDescending(c => c.CreatedOn).Select(c => c.Balance).First() > 0
         select new  
         {
            CustomerId = grp.Key.CustomerId,
            CustomerFullName = grp.Key.FullName,
            Balance = grp.OrderByDescending(c => c.CreatedOn).Select(c => c.Balance).FirstOrDefault()
         });

var balances = q.ToList();

this query is ok in linqpad, but in the project (aspnet core 3.1 - netstandard2.1 (my query layer) - Microsoft.EntityFrameworkCore version 5.0.12) got error bellow:

The LINQ expression 'GroupByShaperExpression:
KeySelector: new { 
    CustomerId = t.CustomerId, 
    FullName = a.FullName
 }, 
ElementSelector:EntityShaperExpression: 
    EntityType: Transaction
    ValueBufferExpression: 
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False

    .OrderByDescending(c => c.CreatedOn)' could not be translated. Either rewrite the query in a 
form that can be translated, or switch to client evaluation explicitly by inserting a call to 
'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.
 See https://go.microsoft.com/fwlink/?linkid=2101038 for more information. 

Answer 1

Finally I realized that ef can't handle group by and First(), so I tried to use another strategy.

the solution is using subquery instead of grouping. here is the query :

var query = 
(from x in
   (from u in Users
    select new 
    {
      CustomerId = u.Id,
      CustomerFullName = u.FullName,
      LastTransaction = (from t in Transactions
                        join c in Coins on t.CoinId equals c.Id
                        where t.CustomerId == u.Id
                        orderby t.CreatedOn descending
                        select new { t.Balance, CoinSymbol = (string)c.Symbol, CoinId = c.Id, CoinName = (string)c.Name }).FirstOrDefault()
    })
select new 
{ 
    Balance = x.LastTransaction == null ? 0 : x.LastTransaction.Balance,
    x.CustomerId, 
    x.CustomerFullName, 
    CoinSymbol = x.LastTransaction == null ? null : x.LastTransaction.CoinSymbol,
    CoinName = x.LastTransaction == null ? null : x.LastTransaction.CoinName,
    CoinId = x.LastTransaction == null ? default(Guid) : x.LastTransaction.CoinId
});

var result = query
.Where(l => l.CoinSymbol != null && l.CoinSymbol == "USDT")
.Select(l => new 
{
    l.CustomerId, 
    TotalBalance = l.Balance,
    l.CustomerFullName, 
    l.CoinSymbol,
    l.CoinName,
    l.CoinId
}).ToList();

as you can see, I start with user table, add a subquery from transactions table in select part and check nullables.

at the end I simply use the 'query' to add some extra conditions

• string explicit casts in the query above '(string)' are bacause Coin.Name and Coin.Symbol are ValueObjects and to fetch them as string, I have to cast them to string explicitely