HasKey with expression?

Question

I have a DbSet which database object has the key of multiple column with expression. For example, the database object (view or table) has the key of cast(Time as Date), Col2 (Time has the type of DateTime). (I'm not the owner of the SQL Server database)

However, the following way to setup the key got error.

public class MyDbContext : DbContext
{
    public DbSet<MyEntity> MyEntity { get; set; }

    protected override void OnModelCreating(ModelBuilder mb)
    {
        mb.Entity<MyEntity>().HasKey( x => new { x.Time.Date, x.Col2 } ); // Error

The database object (Table or View) can have the unique constraint of

cast(Time as Date), Col2

COL2 has the type of varchar(50).

What is your RDBMS? I am curious which one would allow a primary key like that. — Igor, Dec 16 '19 at 20:48
@Igor Sql Server. I have limited permissions on the server. I don't own the database objects. — ca9163d9, Dec 16 '19 at 20:49
Can you script the table as a `CREATE` statement and show that? Your `HasKey` call needs to match the primary key constraint definition. — madreflection, Dec 16 '19 at 20:51
@madreflection, I understand the `HasKey` need to match the column names. However, I'm not the owner of the database object and I had to find a way to work around my the C# side. — ca9163d9, Dec 16 '19 at 20:53
Can you script the primary key constraint and include it in your question? (in ssms you can right click on the object and choose to generate a create script) That should clarify what the corresponding c# mapping code should be. — Igor, Dec 16 '19 at 20:53
@Igor, I already mentioned the key of the view/table is `cast(Time as Date), Col2` — ca9163d9, Dec 16 '19 at 20:55
Declare the `Time` property in your entity as `System.DateTime` and use `.HasKey(x => new { x.Time, x.Col2 })`. By the time EF sees the data, it's just a column, not an expression. — madreflection, Dec 16 '19 at 20:57
You will only read this table or you need to insert, update and delete too? — Lutti Coelho, Dec 16 '19 at 20:58
@madreflection, you meant declare the `Date` property? Maybe `DateTime Date { get => Time.Date }`? — ca9163d9, Dec 16 '19 at 20:59
@ca9163d9 if my answer not work for you, please share your table script and your entity on the question. — Lutti Coelho, Dec 16 '19 at 21:07
`HasKey` is used to define the Primary Key on the table. In Sql Server a primary key can never be `cast(Time as Date)`, that is not allowed. It must always be a column value. This is why I asked you to script the table because once you provide that it becomes more clear to the rest of us what it is you are actually wanting / needing to do. So the above last edit is of no use because it is not possible. — Igor, Dec 16 '19 at 21:13

David Browne - Microsoft · Accepted Answer · 2019-12-16T21:20:30.963

3

Using a query type is probably the right answer if you aren't updating the table, but it would be safe to simply configure the model as

mb.Entity<MyEntity>().HasKey( x => new { x.Time, x.Col2 } );

EF wouldn't know about the constraint, but any attempt to violate it would simply fail at the SQL Server.

BTW in SQL Server this is either being enforced by an Indexed View, or an Index on a Computed Column, not a regular PRIMARY KEY or UNIQUE constraint.

edited Dec 16 '19 at 21:20

answered Dec 16 '19 at 21:16

David Browne - Microsoft

80,331
6
39
67

The underline table actually doesn't have any physical key. I'm trying to resolve the issue in https://stackoverflow.com/questions/59363290/mock-dbcontext-with-no-key-entity-error-on-context-myenities-addrange-how-to-i/59363683#59363683 without big change to my existing code (to use moq to mock `DbContext`) – ca9163d9 Dec 16 '19 at 21:20
2

I think your approach should work since `new { x.Time, x.Col2 }` will be unique if `new { x.Time.Date, x.Col2 }` is unique. – ca9163d9 Dec 16 '19 at 21:24

Lutti Coelho · Answer 2 · 2019-12-16T21:05:24.093

As you only need to read the data of this table you can use DbQuery instead of DbSet.

public class MyDbContext : DbContext
{
    public DbQuery<MyEntity> MyEntity { get; set; }

    //... Other tables

}

EDIT: Aparently DbQuery is now obsolete

Instead of that you can use .HasNoKey(); on modelBuilder

public class MyDbContext : DbContext
{
    public DbSet<MyEntity> MyEntity { get; set; }

    protected override void OnModelCreating(ModelBuilder mb)
    {
        mb.Entity<MyEntity>().HasNoKey(); // No key
    }
}

HasKey with expression?

2 Answers2