LINQ to Entities 不支持指定的类型成员“UsersCount" [英] The specified type member 'UsersCount' is not supported in LINQ to Entities

问题描述

我有这样的 POCO 实体

I have such POCO entity

  public class Product : Entity

 {
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int Id { get; set; }
        [Required]
        [MaxLength(50)]
        public string Name { get; set; }              
        public virtual ICollection<Order> Orders { get; set; }

        [NotMapped]
        public int UsersCount
        {
            get
            {
                return  Orders.Count(); 
            }
        }

}

产品访问方式

 public IQueryable<Product> GetAll()
        {
            return _context.Product.Include(I=>I.Orders);          
        }    

当我将所有产品加载到视图中时

When I load all products into View

      var model = _productService.GetAll().Select(p => new AdminProductViewModel
        {
            Active = p.Active,
            Id = p.Id,
            Name = p.Name,
            UsersCount = p.UsersCount
        }).ToList();

我得到异常

LINQ 不支持指定的类型成员UsersCount"实体.

The specified type member 'UsersCount' is not supported in LINQ to Entities.

我真的不明白为什么 Linq to Entity 会给出异常.也许有人解释出了什么问题?

I really can't understand why Linq to Entity gives exception. Maybe someone explain what is wrong?

我们还在类似的另一个应用程序中使用计算字段

We also use calculated fields in another application like this

 public class User : Entity
    {
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int Id { get; set; }               
        [Required]
        [MaxLength(50)]
        public string Email { get; set; }
        [MaxLength(50)]
        public string FirstName { get; set; }
        [MaxLength(50)]
        public string LastName { get; set; }

        public virtual ICollection<Order> Orders { get; set; }
        public virtual ICollection<Statistic> Statistics { get; set; }

        [NotMapped]
        public bool Active
        {
            get
            {
                return Orders.Any(c => c.Active && (c.TransactionType == TransactionType.Order || c.TransactionType == TransactionType.Subscription));
            }
        }

        [NotMapped]
        public int CreditsLeft
        {
            get
            {
                return Orders.Where(w => w.Active).Sum(p => p.Credits != null ? p.Credits.Value : 0);
            }
        }
}

    public User Get(int id)
    {
        return _context.User.FirstOrDefault(u => u.Id == id);
    }

var user = _userService.Get(_authUser.Id);

 var model = new UserViewModel
            {
                Active = user.Active,
                FullName = user.FullName,
                Email = user.Email,
            };

没有问题，EF6 没有给出任何异常，虽然它也有两个计算字段 User.Active 和 User.CreditsLeft

and have no problems, EF6 don't give any exception though it also has two calculated fields User.Active and User.CreditsLeft

推荐答案

请记住，LINQ to Entities 会尝试将每个 LINQ 语句转换为 SQL.你的声明...

Keep in mind that LINQ to Entities tries to translate each LINQ statement into SQL. Your statement...

var model = _productService.GetAll().Select(p => new AdminProductViewModel...

...是针对 IQueryable (_productService.GetAll()) 的 LINQ 扩展方法 (Select).正如 文档 所示，此方法需要 <代码>表达式作为参数.

...is a LINQ extension method (Select) against an IQueryable (_productService.GetAll()). As the documentation shows, this method takes an Expression as argument.

您可以将表达式视为一个标记树，表达它应该执行的任务.简而言之，LINQ 提供程序是表达式语言"中的标记到某种其他语言(在本例中为 SQL)中的标记的字典.整个语句被翻译成 SQL 并由数据库执行..Net 运行时仅将语句发送出去并处理返回的结果.

You can see an expression as a tree of tokens that express the task it should execute. A LINQ provider, simply said, is a dictionary of tokens in "expression language" to tokens in some other language, SQL in this case. The whole statement is translated into SQL and executed by the database. The .Net runtime only sends the statement away and processes the returned result.

检查 EF 的源代码发现许多标记是硬编码的:所有 SQL 关键字、许多内置(规范")函数(如 DATEDIFF)和 .Net 方法的选择.通过将实体属性映射到数据库列来添加其他标记.最近，ToString() 被添加到字典的 .Net 部分.在 EF6 中我们可以写...

Inspecting EF's source code reveals that many tokens are hard-coded: all SQL keywords, a number of built-in ("canonical") functions (like DATEDIFF) and a selection of .Net methods. Other tokens are added by mapping entity properties to database columns. Recently, ToString() was added to the .Net part of the dictionary. In EF6 we can write...

_context.Product.Select(p => p.Id.ToString())

在此之前，这会引起臭名昭著的

Before that, this would raise the infamous

LINQ to Entities 无法识别方法System.String ToString()"

LINQ to Entities does not recognize the method 'System.String ToString()'

您的异常具有相同的原因，但它与成员而不是方法有关.p.UsersCount 不在字典中，因为它没有被映射.

Your exception has the same cause, but it pertains to members in stead of methods. p.UsersCount is not in the dictionary because it is not mapped.

我们还在类似的另一个应用程序中使用计算字段

We also use calculated fields in another application like this

这里的 User 已从数据库中获取并具体化为 C# 对象.现在，当您访问它的属性时，它只是在运行 .Net 代码.这里没有进行 SQL 翻译.嗯...它可能会触发延迟加载(订单和信用)，但访问属性的行为不会发生在表达式的上下文中.

Here a User has been fetched from the database and materialized as a C# object. Now when you access its properties it's just .Net code running. There's no SQL translation going on here. Well... it probably triggers lazy loading (of orders and credits), but the act of accessing the property does not happen in the context of an expression.

同样，一旦获得了 Product 对象，您就可以访问 UsersCount.如果您希望数据库完成计算订单的繁重工作，则必须使用 LINQ 语句中的表达式:

Likewise, you can access UsersCount once you've got a Product object. If you want the database to do the heavy lifting of counting the orders, you'll have to use the expression in the LINQ statement:

var model = _productService.GetAll().Select(p => new AdminProductViewModel
    {
        Active = p.Active,
        Id = p.Id,
        Name = p.Name,
        UsersCount = p.Orders.Count()
    }).ToList();

这篇关于LINQ to Entities 不支持指定的类型成员“UsersCount"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！