查询语法 LINQ 无法被翻译为 SQL。

huangapple go评论61阅读模式
英文:

query syntax LINQ could not be translated to SQL

问题

我已经使用这个查询来获取用户及其关注者列表。

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);

但是EF框架抛出了以下异常:

LINQ表达式 'i => !(i.IsMaster) && i.IsActive && i.UserId == EntityShaperExpression: 
    BackOffice.Service.Data.Models.User
    ValueBufferExpression: 
        ProjectionBindingExpression: Outer
    IsNullable: False
.Index' 无法被翻译。要么以可翻译的形式重写查询,要么明确切换到客户端评估,通过插入对 'AsEnumerable'、'AsAsyncEnumerable'、'ToList'  'ToListAsync' 的调用。有关更多信息,请参阅https://go.microsoft.com/fwlink/?linkid=2101038。

我猜这是由于 fl 对象未被转换为SQL引起的,到目前为止,我找了很长时间都没有找到解决方案。任何帮助都将不胜感激,谢谢大家。

英文:

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.

答案1

得分: 1

问题中没有包含 EF。EF 既没有也不需要 "repositories" 和 "units of work",因为 DbContext 已经是一个多实体的工作单元,DbSet 已经是一个单实体的仓储。它们不是 CRUD DAOs。提供单个记录访问和 CRUD 操作的对象称为数据访问对象,而不是仓储。

错误提示 i => !(i.IsMaster) && i.IsActive && i.UserId == EntityShaperExpression 无法翻译。这是因为,假设 _unitOfRepository.User_unitOfRepository.TradingAccount 返回 IQueryable's,存在两个独立的查询。在 TradingAccount 上的查询试图使用从 _unitOfRepository.User.Where(i => i.Index == currentUserId) 生成的查询,而不知道这些查询是否相关。

这似乎是在 LINQ 中尝试重新创建相关子查询。这是没有必要的。LINQ 不是 SQL,DbContext 不是数据库连接。LINQ 是 EF 的查询语言。EF 本身会使用在 DbContext 中配置的模式和 关系 将 LINQ 查询翻译为 SQL。如果类具有属性,则不需要 JOIN 或相关子查询。

通常,User 对象应该有一个 List<TradingAccount> TradingAccounts {get;set;} 属性。假设类遵循 EF 的命名约定,EF 将能够通过类型和属性名称来猜测关系。这就是为什么 Index 是一个 不好 的想法。ID 代表 Identity,而不是 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;}
}

这允许编写以下查询:

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();

查询形式如下:

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();

或者,如果我们想加载整个用户对象:

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

这将贪婪加载仅符合筛选条件的 TradingAccount

英文:

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

huangapple
  • 本文由 发表于 2023年7月6日 15:47:38
  • 转载请务必保留本文链接:https://go.coder-hub.com/76626599.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定