How can I define primary key with External table Sql Server 2017

.net-core entity-framework-core sql-server

Question

I am working on multiple azure sql databases and trying to join data using Elastic query.

  1. I created external tables.
  2. When I try to create entity from EF Core. this fails and I am getting below error.

Unable to identify the primary key for table 'xxxx'. Unable to generate entity type for table 'xxxx'.

How can I create primary key for external table or how can I have entity for external table with EF Core.

1
1
5/10/2019 2:22:15 PM

Popular Answer

SQL Server does not currently support the ability to add a PRIMARY KEY to an EXTERNAL TABLE. However, as you are using Entity Framework there are a couple of ways you can get around this.

The easiest solution would be to decorate the required property on the entity with the the KeyAttribute and if necessary, the DatabaseGeneratedAttribute and or ColumnAttribute like so:

public class Employee
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Column("Id")]
    public int EmployeeId { get; set; }
}

Your other option would be to override OnModelCreating and use Fluent API to define your models properties:

public class HumanResourcesContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }

    public override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder):

        modelBuilder.Entity<Employee>
                    .HasKey(x => x.EmployeeId);
        modelBuilder.Entity<Employee>
                    .Property(x => x.EmployeeId)
                    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)
                    .HasColumnName("Id");
    }
}

If the options above are not viable, you could always create a local table with the same structure, apply the primary key to it and then generate your model. Once you have your model definition, you can go back into SQL Server drop the local table and then recreate it as an external table.

0
1/11/2020 6:13:01 PM


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