Linq2db: filter by nested property field

Question

I'm using a linq2db ORM in my project and I have the following query:

var query =
        from t in _db.Transactions
        from a in _db.Accounts.LeftJoin(a => a.Id == t.AccountId)
        from u in _db.Users.LeftJoin(u => u.Id == a.UserId)
        select Transaction.Build(t, u, a);

and Build method looks like this:

public static Transaction Build(Transaction transaction, User user, Account account)
{
  account.User = user;
  transaction.Account = account;
  return transaction;
}

The Users table contains the FullName column, and I wonder if it is possible to filter transactions by User's full name?

if (!string.IsNullOrWhiteSpace(userNameFilter))
{
  query = query.Where(t => t.Account.User.FullName.Contains(userNameFilter));
}

Is it possible to achieve it with linq2db?

Answer 1

After the factory method call Transaction.Build(t, u, a), linq2db loses information about fields mapping and query become good only for materialization but not filtering. This is true for any currently available LINQ providers.

What you can do with linq2db to make code reusable - rewrite your function Transaction.Build by using ExpressionMethodAttribute:

static Func<Transaction, User, Account, Transaction> _buildFunc;

static Expression<Func<Transaction, User, Account, Transaction>> BuildImpl()
{
   return (Transaction transaction, User user, Account account) => 
      new Transaction
      {
         Id = transaction.Id,
         ... // copy all needed fields

         Account = new Account 
         {
            Id = account.Id,
            ... // copy all needed fields

            User = user
         }
      }
   );
}

[ExpressionMethod(nameof(BuildImpl))]
public static Transaction Build(Transaction transaction, User user, Account account)
{
   // we left function usable even for non-query scenarios.
   _buildFunc ??= BuildImpl().Compile();
   return _buildFunc(transaction, user, account);
}

After these manipulations, your query can be filtered after Transaction.Build call.

Under hood, linq2db will find ExpressionMethodAttribute declaration and substitute Transaction.Build call by Expression defined in BuildImpl function. Schematically before analyzing LINQ query, it will be transformed to the following variant:

var query =
    from t in _db.Transactions
    from a in _db.Accounts.LeftJoin(a => a.Id == t.AccountId)
    from u in _db.Users.LeftJoin(u => u.Id == a.UserId)
    select new Transaction
    {
        Id = t.Id,
        ... // copy all needed fields

        Account = new Account 
        {
            Id = a.Id,
            ... // copy all needed fields

            User = u
        }
    };