EF Core dealing with alternate primary keys

ef-database-first entity-framework-core

Question

I have some EF Core models that were built using dotnet ef dbContext scaffold to produce models using a database first approach. My problem is the database uses an integer primary key, used for linking tables together but has a string based index that would be used as the sane index for searching the table.

However: When I attempt to use FindAsync("abc000") I get an entirely expected error The key value at position 0 of the call to 'DbSet<Entity>.Find' was of type 'string', which does not match the property type of 'long'.

So, two questions:

  1. How did EF Figure out what the primary key was?
  2. Is there any way I can re-jig this so I can use "Find" to search for entities by name, but keep the autoincrement primary keys?
  3. Am I being stupid for preferring auto increment integer keys as the fields to join tables on?

They look like this:

class Entity
{
    long Id;
    string Key;
};

And in OnModelCreating:

modelBuilder.Entity<Entity>(entity =>
{
    entity.ToTable("tb_entity", "main");

     entity.HasIndex(e => e.Key)
           .HasName("uq_entity_key")
           .IsUnique();

    entity.Property(e => e.Id).HasColumnName("_id");

    entity.Property(e => e.Key)
          .HasColumnName("key")
          .HasMaxLength(255);
}

The SQL that created the tables looks like:

CREATE TABLE [tb_entity]
(
    _id BIGINT PRIMARY KEY IDENTITY(1,1),
    key NVARCHAR(255) CONSTRAINT uq_entity_key UNIQUE NOT NULL,
);
1
1
2/27/2019 10:11:39 AM

Accepted Answer

  1. How did EF Figure out what the primary key was?

If not explicitly specified via [Key] attribute or HasKey fluent API, it's by convention:

By convention, a property named Id or <type name>Id will be configured as the key of an entity.

You can see that information by examining

var pk = context.Model.FindEntityType(typeof(Entity)).FindPrimaryKey();
  1. Is there any way I can re-jig this so I can use "Find" to search for entities by name, but keep the autoincrement primary keys?

You can lie EF Core using data annotations / fluent API that the PK of the Entity is Name, but I don't recommend that because that will lead to wrong assumptions for FK relationships, and in general is bad.

Instead, simply don't use Find / FindAsync methods which are dedicated for PKs. First, FirstOrDefault, Single and SingleOrDefault (and their Async counterparts) allow you searching by any criteria, e.g. instead of FindAsync("abc000") you would use FirstOrDefaultAsync(e => e.Name == "abc000").

The only difference is that Find methods first search in local cache, which has no much benefit in most of the usage scenarios. From the other side, Find methods don't support eager loading, while the later do. The later are executed against database, and since there is a unique index on that column, they should be performant enough.

  1. Am I being stupid for preferring auto increment integer keys as the fields to join tables on?

This is pretty standard db design and usually is preferable over natural PKs, I don't see any problem with that.

4
2/27/2019 10:53:45 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow