query syntax LINQ could not be translated to SQL

Question

I have used this query to get the user and his list of followers

var userAndFollowersQuery =
    from us in _unitOfRepository.User.Where(i => i.Index == currentUserId)
    let fl = _unitOfRepository.TradingAccount
        .Where(i => !i.IsMaster && i.IsActive && i.UserId == us.Index)
        .ToList()
    select new
    {
        User = new
        {
            us.Index,
            us.AccountBalance
        },
        Followers = fl
    };

    var userAndFollowers = await userAndFollowersQuery.FirstOrDefaultAsync(cancellationToken);

But EF framework throw me the below exception

The LINQ expression 'i => !(i.IsMaster) && i.IsActive && i.UserId == EntityShaperExpression: 
    BackOffice.Service.Data.Models.User
    ValueBufferExpression: 
        ProjectionBindingExpression: Outer
    IsNullable: False
.Index' 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.

I guess this was caused by the fl object which was not translated to SQL, it has taken me quite long finding a solution by far. Any help would be appreciated, thank you guys.

Answer 1

The question doesn't contain EF. EF neither has nor needs "repositories" and "units of work" as a DbContext already is a multi-entity Unit-of-Work, a DbSet already is a single-entity Repository. They aren't CRUD DAOs. Objects that provide single record access and CRUD operations are Data Access Objects, not repositories.

The error complains that i => !(i.IsMaster) && i.IsActive && i.UserId == EntityShaperExpression can't be translated. That's because, assuming _unitOfRepository.User and _unitOfRepository.TradingAccount return IQueryable's, there are two independent queries. The one on TradingAccount is trying to use the one produced from _unitOfRepository.User.Where(i => i.Index == currentUserId) without knowing whether the queries are related or not.

This seems to be an attempt to recreate a correlated subquery in LINQ. There's no need for this. LINQ isn't SQL and a DbContext isn't a database connection. LINQ is EF's query language. EF itself will translate a LINQ query to SQL using the schema and relations configured in the DbContext. There's no need for JOINs or correlated subqueries if the classes have properties.

Normally, the User object should have a List<TradingAccount> TradingAccounts {get;set;} property. Assuming the classes follow EF's naming conventions, EF will be able to guess the relations just by the type and property names. Which is why Index is a bad idea. ID means Identity, not Index.

public class User
{
    public long Id{get;set;}
    public decimal AccountBalance {get;set;}

    public List<TradingAccount> TradingAccounts {get;set;}
}

public class TradingAccount
{
    public long Id {get;set;}
    public bool IsMaster {get;set;}
    public bool IsActive {get;set;}

    public User User {get;set;}
}

This allows writing the following queries:

var user = _context.Users.Where(u=> u.Id== currentUserId)
                         .Select(u => new {
                             User = new {
                                        u.Id,
                                        u.AccountBalance,                          
                                   },
                             Followers = u.TradingAccounts.Where(a => !a.IsMaster && a.IsActive)
                                                          .ToList()
                         })
                         .FirstOrDefaultAsync();

In query form:

var user = (from u in _context.Users
            where u.Id== currentUserId
            select new { User = new { u.Id,
                                      u.AccountBalance,                          
                                    },
                          Followers = u.TradingAccounts.Where(a => !a.IsMaster && a.IsActive).ToList()
                        }
           ).FirstOrDefaultAsync();

or, if we want to load the entire user object:

var user = _context.Users
                   .Include(u=>u.TradingAccounts.Where(a => !a.IsMaster && a.IsActive))
                   .Where(u=> u.Id== currentUserId)
                   .FirstOrDefaultAsync();

This would eagerly load only the TradingAccounts that match the filter