Query: "The EF.Property<T> method may only be used within LINQ queries" and unnecessary client-side evaluation triggered in certain generic usages

[divega]

In certain usages of the EF.Property() method the compiler introduces a cast to object for the entity argument of the call. This apparently prevents EF Core from correctly parsing the LINQ expression.

I have seen this lead to two different outcomes:

1. An InvalidOperationException with message "The EF.Property method may only be used within LINQ queries."
2. Unnecessary client-side evaluation

I was able to produce a relatively small repro for the exception case (see below) but unfortunately the second effect is more elusive and so far only appears in a larger application (I can work with someone figuring out that part).

using System;
using System.Data.SqlClient;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

namespace EFPropertyFromObject
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            using (var context = new MyContex("deleteme", 4))
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();
                context.Add(new Customer {Name = "Alfred", Country = "USA"});
                context.Add(new Customer {Name = "Maria", Country = "USA"});

                context.SaveChanges();
            }
            using (var context = new MyContex("deleteme", 4))
            {
                var allCountries = context.QueryAllCountries();
                foreach (var country in allCountries) // exception is thrown here
                {
                    Console.WriteLine(country);
                }
            }
        }
    }

    public class Customer
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Country { get; set; }
    }

    public class MyContex : DbContext
    {
        private readonly string _connectionString;
        private readonly int _tenantId;

        public MyContex(string database, int tenantId)
        {
            _connectionString =
                new SqlConnectionStringBuilder
                {
                    DataSource = @"(localdb)\mssqllocaldb",
                    InitialCatalog = database,
                    IntegratedSecurity = true
                }.ToString();
            _tenantId = tenantId;
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Customer>(AddTenantId);
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(_connectionString);
        }

        public override EntityEntry<TEntity> Add<TEntity>(TEntity entity)
        {
            var entry = base.Add(entity);
            entry.Property<int>("TenantId").CurrentValue = _tenantId;
            return entry;
        }

        public IQueryable<string> QueryAllCountries()
            => QueryCustomers().Select(c => c.Country).Distinct();

        public IQueryable<Customer> QueryCustomers()
            => ApplyTenantFilter(Set<Customer>());

        private IQueryable<T> ApplyTenantFilter<T>(IQueryable<T> source) //where T : class
            => source.Where(e => EF.Property<int>(e, "TenantId") == _tenantId);

        private void AddTenantId<T>(EntityTypeBuilder<T> builder) where T : class
        {
            builder.Property<int>("TenantId");
        }
    }
}

Note that the cast to object is added by the compiler to account for value types which need to be boxed in order to be passed as objects, so adding the generic constraint where T: class to ApplyTenantFilter() prevents the compiler from introducing the cast. However that doesn't seem to be a very discoverable workaround.

[divega]

Some additional information and alternative repro steps in #3837.

[divega]

Working on a customer case today I learned that the compiler won't always introduce a convert to object in this situation. E.g. if TenantId was defined in an interface IHasTenant and the method looked like this:

        private IQueryable<T> ApplyTenantFilter<T>(IQueryable<T> source) where T : IHasTenant
            => source.Where(e => e.TenantId == _tenantId);

(Note that T is constrained to the interface but not to be a reference type) Then the compiler would introduce convert nodes to the interface everywhere. I am reopening so that we can validate if our fix would work for that. At a first glance it seems we only considered object, but perhaps this works for other reasons.

[maumar]

This works correctly in the current bits.
We produce the following sql:

SELECT DISTINCT [e].[Country]
FROM [Customer] AS [e]
WHERE [e].[TenantId] = @___tenantId_0

[wgutierrezr]

I'm still having this issue in my class:

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace emsnet.Code
{
    public class PaginatedList<T> : List<T>
    {
        public int PageIndex { get; private set; }
        public int TotalPages { get; private set; }
        public int PagesDisplay { get; private set; }
        public int PageStart { get; private set; }
        public int PageEnd { get; private set; }

        public PaginatedList(List<T> items, int count, int pageIndex, int pageSize, int pagesDisplay)
        {
            PageIndex = pageIndex;

            TotalPages = (int)Math.Ceiling(count / (double)pageSize);

            PagesDisplay = pagesDisplay >= TotalPages ? TotalPages : pagesDisplay;

            if ((pageIndex - 1) == 0)
            {
                // If it is the first run, or pull back
                PageStart = 1;
                PageEnd = PagesDisplay;
            }
            else if ((pageIndex >= PageStart && pageIndex <= PageEnd) == false)
            {
                // Check if it is greater or lesser
                if (PageIndex > PageEnd)
                {
                    // If pageIndex is greather than the actual range
                    PageStart = (PageEnd + 1);
                    PageEnd = PageStart + +(PagesDisplay - 1);
                }
                else if (PageIndex < PageStart)
                {
                    // If oageIndes is lesser than the actuial range
                    PageEnd = PageIndex;
                    PageStart = (PageEnd - PagesDisplay) + 1;
                }
            }

            this.AddRange(items);
        }

        public bool HasPreviousPage
        {
            get
            {
                return (PageStart > 1);
            }
        }

        public bool HasNextPage
        {
            get
            {
                return (PageEnd < TotalPages);
            }
        }


        public static async Task<PaginatedList<T>> CreateAsync(IQueryable<T> source, int pageIndex, int pageSize, int pagesDisplay)
        {
            var count = await source.CountAsync();
**ERROR AT THIS LINE**:   var items = await source.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync(); 
            return new PaginatedList<T>(items, count, pageIndex, pageSize, pagesDisplay);
        }
    }

}

When I pass the parameters
source :

var ret = _context.AppMenus
    .Where(m => m.ParentMenuId != 0 && m.Title.Contains(searchString))
    .Select(m => new MenuList 
    {
         MenuId = m.MenuId, 
         Title = m.Title, Selected = (m.AppProgramMenus.Where(
             p => p.ProgramId == programid && p.MenuId == m.MenuId).Count() > 0) });

What am I doing wrong?

Regards

[ajcvickers]

@wgutierrezr What version of EF Core are you using?

[wgutierrezr]

Hi, I'm using EF Core 1.1.2

[wgutierrezr]

This is the action where I get the data from the DB. I am sorting the resulting query.

        public async Task<IActionResult> Index(string sortField, string searchString, string CurrentSortField, string sortOrder, int? page, int? roleid)
        {
            bool descending = false;

            // check if null then take the first role on the table order alphabetically
            roleid = roleid ?? _context.AppRoles.OrderBy(p => p.Title).First().RoleId;

            ViewData["currentRoleId"] = roleid;


            // Check if it is the first run or if click over the same column
            if (sortField == CurrentSortField && CurrentSortField != null)
            {
                descending = (sortOrder ?? "D") == "A" ? true : false;
                ViewData["sortOrder"] = descending ? "D" : "A";
            }
            else
            {
                ViewData["sortOrder"] = "A";
                sortField = sortField ?? "Title";
            }
            ViewData["CurrentSortField"] = sortField;

            if (searchString != null)
            {
                page = 1;
            }
            else
            {
                searchString = "";
            }


            ViewData["currentFilter"] = searchString;

            // Get the current menu-programs assigned to a particular Role
            var ret = _context.AppRoleMenuPrograms.Where(p => 
                        p.RoleId == roleid && 
                        (p.ProgramMenu.Program.Title.Contains(searchString) || p.ProgramMenu.Menu.Title.Contains(searchString)))
                        .Select(p =>
                            new RolMenuProgram
                            {
                                Id = p.ProgramMenu.ProgramMenuId,
                                ProgramTitle = p.ProgramMenu.Program.Title,
                                MenuTitle = p.ProgramMenu.Menu.Title,
                                canCreate = p.AllowCreate,
                                canRead = p.AllowRead,
                                canUpdate = p.AllowUpdate,
                                canDelete = p.AllowDelete
                            });


            if (descending)
            {
               

> I think using this order by generates the problem (EF.Property<object>(e, sortField))

 ret = ret.OrderByDescending(e => EF.Property<object>(e, sortField));
            }
            else
            {
                ret = ret.OrderBy(e => EF.Property<object>(e, sortField));
            }

            ViewData["Roles"] = new SelectList(_context.AppRoles.OrderBy(p => p.Title).ToList(), "RoleId", "Title", roleid);

            return View(await PaginatedList<RolMenuProgram>.CreateAsync(ret, page ?? 1, 25, 10));
        }