Asp.net-mvc – The specified type member ‘UsersCount’ is not supported in LINQ to Entities

asp.net-mvcentity-frameworkentity-framework-6.1linq-to-entities

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

}

Product access method

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

I get exception

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

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,
            };

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

Best Answer

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...

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

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.

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 does not recognize the method 'System.String ToString()'

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

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.

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