Efcore: ID object as entity key not queryable

[bobvandevijver]

Like in #9194, I want to use a dedicated ID object as entity key, which should work fine when value converters are used (as stated by @ajcvickers). However, I seem to hit limitations in the implementation.

I've created a sample project which can be used to demonstrate the problem: https://github.com/bobvandevijver/EFCore_ObjectAsKey.

What happens? EF Core seems to be incapable of translating the key to the database value during query compile time, as this log part shows:

dbug: Microsoft.EntityFrameworkCore.Query[10101]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      Compiling query model: 
      '(from Book e in DbSet<Book>
      where Property([e], "id") == __get_Item_0
      select [e]).FirstOrDefault()'
dbug: Microsoft.EntityFrameworkCore.Query[10104]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      Optimized query model: 
      '(from Book e in DbSet<Book>
      where Property([e], "id") == __get_Item_0
      select [e]).FirstOrDefault()'
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'where (Property([e], "id") == __get_Item_0)' could not be translated and will be evaluated locally.

This means it will create a query that retrieves all objects, which it does according to the log:

 Executed DbCommand (14ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [e].[id], [e].[value]
      FROM [Books] AS [e]

Then, it uses the == comparator (which you need to implement, as otherwise it uses reference compare without any warning or whatsoever, which fails as you would expect). This subtle difference already had me stuck for hours (I previously only implemented the Equals method).

However, this has a massive performance impact: I want to select on a PK, the type has a decent conversion defined, but it will first retrieve the whole dataset to search for my PK in memory. This basically means that when you want to use a value conversion, you really shouldn't care about performance.

My demo project uses a simple int as key, but in my real project I want to use Guid as primary key. But without query support, it is a no go... I'm using the latest EF Core (2.1.5).

Am I missing something? Can I tell EF how to convert the object to the query? Why can it convert the object to a normal database value, but can it not use the same transformed value for a query?

[ajcvickers]

Note for triage: part of #10434, but we should probably keep this open as a specific case to fix.

[PopovKS]

Hello @bobvandevijver , i'm faced to same problem and quick fix for that :

    public class User
    {
        public UserId Id { get; set; }
        public Credentials Credentials { get; set; }
    }

    public class UserId
    {
        private UserId()
        {

        }

        private UserId(long id)
        {
            Id = id;
        }

        public long Id { get; private set; }

        public static implicit operator long(UserId beaconId)
        {
            return beaconId.Id;
        }

        public static implicit operator UserId(long id)
        {
            return new UserId(id);
        }
        public override bool Equals(object obj)
        {
            if (obj == null)
            {
                return false;
            }

            var userId = (UserId) obj;
            return this.Id == userId.Id;
        }

        public override int GetHashCode()
        {
            return Id.GetHashCode();
        }
    }

public class TestDbContext: DbContext
    {
        public TestDbContext(DbContextOptions options): base(options)
        {

        }

        public DbSet<User> Users { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {

           var userModelBuilder = modelBuilder.Entity<User>();

            userModelBuilder.Property(x => x.Id).HasConversion(x => x.Id, l => l).HasColumnName("Id").IsRequired();

            userModelBuilder.OwnsOne(x => x.Credentials, c =>
            {
                c.Property(x => x.Email);
                c.Property(x => x.Password);
            });
        }
    }

and query now :
DbContext.Users.Where(l => Convert.ToInt64(l.Id) == Convert.ToInt64(userId)).ToListAsync()

then generated sql :

exec sp_executesql N'SELECT [x].[Id],  [x].[Credentials_Password], [x].[Credentials_Salt]
FROM [Users] AS [x]
WHERE CONVERT(bigint, [x].[Id]) = @__ToInt64_0',N'@__ToInt64_0 bigint',@__ToInt64_0=5

it works fine.

[bobvandevijver]

@PopovKS That indeed does work, but does require you to use the Convert trick in every query you write, and it also creates a convert part in the SQL-query, which shouldn't be required.

I would still like to see a complete solution from the ef core team, so I'll wait for that :)

[latromi-technology]

I have the same problem, and decide implement @PopovKS approach temporarily...

[RouR]

It does not work in case public string Id { get; private set; }
Another problem is define it as primary key

[sygnowskip]

@bobvandevijver there is a way to make it queryable, but there are also some restrictions in current EF Core implementations (ID's needs to be generated on server side, so you're not able use database identity), you could find more details here: https://sygnowskip.github.io/domain/natural-identifiers-with-entity-framework-core/

[bobvandevijver]

@sygnowskip As explained in the earlier comments it is not about it not being queryable in the easy explanation of the word, but about converting the query to something the database actually uses. Your link exactly describes what I already did, but when you are going to look at the executed queries, you will see that the filter will be done in memory.

I'm not sure whether or not this now works with EF Core 3.0, I haven't had the chance to test that version yet.

[sygnowskip]

@bobvandevijver my queries will be correctly translated to SQL (as WHERE clause) and executed on database, check tests in my source code. It's working on both, EF Core 2.2 and 3.0.

Example:

dbContext.Customers.SingleOrDefault(c => c.Id == customer.Id);

is translated into

SELECT TOP(2) [c].[Id], [c].[FirstName], [c].[LastName], [t].[Id], [t].[Address_City],
[t].[Address_PostalCode], [t].[Address_Street]
FROM [Customer] AS [c]
LEFT JOIN (
SELECT [c0].[Id], [c0].[Address_City], [c0].[Address_PostalCode], [c0].[Address_Street], [c1].[Id] AS [Id0]
FROM [Customer] AS [c0]
INNER JOIN [Customer] AS [c1] ON [c0].[Id] = [c1].[Id]
WHERE [c0].[Address_Street] IS NOT NULL OR ([c0].[Address_PostalCode] IS NOT NULL OR [c0].[Address_City] IS NOT NULL)
) AS [t] ON [c].[Id] = [t].[Id]
WHERE ([c].[Id] = @__customer_Id_0) AND @__customer_Id_0 IS NOT NULL

[sygnowskip]

And after additional research, I was able to configure natural ID's and IDENTITY INSERT on database @bobvandevijver - solution for this is here (for EF Core 3.0) https://github.com/sygnowskip/sygnowskip.github.io/tree/feature/support-for-numeric-natural-ids/sources/2019-11-03-natural-identifiers-with-entity-framework-core/NaturalIdentifiers

[smitpatel]

Generated SQL in 3.1

      SELECT TOP(1) [b].[id], [b].[value]
      FROM [Books] AS [b]
      WHERE [b].[id] = @__p_0