How use EF Core code first with azure synapse

I'm trying to use C# and EF Core migrations to create the database in Azure synapse, but I came across a problem, when I try to run the update-database command I get the following error:

Enforced unique constraints are not supported. To create an unenforced unique constraint you must include the NOT ENFORCED syntax as part of your statement.

The examples below demonstrate the form accepted by azure synapse for creating a table using a PRIMARY KEY:

CREATE TABLE dbo.sample_table
(
    c1 int IDENTITY(1,1) NOT NULL,
    c2 char(10) NULL,
    c3 datetime NULL
)

ALTER TABLE t1 
    ADD CONSTRAINT PK_t1_c1 
        PRIMARY KEY NONCLUSTERED (c1) NOT ENFORCED

This is because to use a primary key it is necessary to include:

NONCLUSTERED (myPrimaryKey) NOT ENFORCED

To include NONCLUSTERED just use a configuração abaixo.

public class ManifestationMap : IEntityTypeConfiguration<Manifestation>
{
    public void Configure(EntityTypeBuilder<Manifestation> builder)
    {
        builder.ToTable("Manifestation");

        builder.HasKey(prop => prop.Id)
           .IsClustered(false); // Include NONCLUSTERED 
        ...

But I found no solution to include NOT ENFORCED.

Obs: I don't have this problem when I use Azure SQL Server, nor when I use SQL Server installed locally.

Solution

Synapse uses a different database engine than Azure SQL or SQL Server, and it isn't not currently supported in Entity Framework, so you may run into compatibility issues like this one. You can upvote the feature request here: https://feedback.azure.com/forums/307516-azure-synapse-analytics/suggestions/12868725-support-for-entity-framework

In the meantime, your best option is going to be to use custom SQL scripts when you run into these sorts of issues instead of relying on the auto-generated scripts via MigrationBuilder.Sql() to work around them.