CODEWITHSUNDEEP
entity-framework-coreazure-synapseazure-synapse-analytics
Sonal Khatri
Sonal Khatri

Reputation: 1

Use EF Core code-first approach with Azure Synapse

I am trying to add a table in Azure Synapse using EF Core 9.0, but I am getting an error:

Parse error at line 2, column 1: Incorrect syntax near EXEC.

ActionItemStatus Class:

    public class ActionItemStatus
    {
        public int ActionItemStatusId { get; set; }
        public string Name { get; set; }
        public bool IsActive { get; set; }
        public DateTime CreatedDate { get; set; }
        public DateTime ModifiedDate { get; set; }
    }

DbContext:

    public class SynapseDbContext : DbContext
    {
        public DbSet<ActionItemStatus> ActionItemStatus { get; set; }

        public SynapseDbContext(DbContextOptions<SynapseDbContext> options)
            : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<ActionItemStatus>(builder =>
            {
                builder.HasKey(p => p.ActionItemStatusId);

                builder.Property(p => p.Name).HasMaxLength(100);

                builder.Property(p => p.IsActive);

                builder.Property(p => p.CreatedDate);
                builder.Property(p => p.ModifiedDate);
            });
        }
    }

I generated this migration using the 'Add-Migration' command

public partial class CreateTable : Migration
{
    /// <inheritdoc />
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "ActionItemStatus",
            columns: table => new
            {
                ActionItemStatusId = table.Column<int>(type: "int", nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                Name = table.Column<string>(type: "nvarchar(max)", nullable: false),
                IsActive = table.Column<bool>(type: "bit", nullable: false),
                CreatedDate = table.Column<DateTime>(type: "datetime2", nullable: false),
                ModifiedDate = table.Column<DateTime>(type: "datetime2", nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_ActionItemStatus", x => x.ActionItemStatusId);
            });
    }

    /// <inheritdoc />
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "ActionItemStatus");
    }
}

I also have this in my Startup class:

            builder.Services.AddDbContext<SynapseDbContext>(options =>
            {
                var connectionString = configuration.GetConnectionString("AzureSynapseConnectionString");
                options.UseAzureSynapse(connectionString);
            });

Upvotes: 0

Views: 69

Answers (1)

Bhavani
Bhavani

Reputation: 5317

If you are using synapse serverless SQL pool, then according to the MS document

enter image description here

That may be the reason to get the error while creating table. If it is dedicated SQL pool once check the syntax how you are creating table with identity type column. Create the table using custom SQL script as shown below:

migrationBuilder.Sql("CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1) NOT NULL
,    C2 INT NULL
)
WITH
(   DISTRIBUTION = HASH(C2)
,   CLUSTERED COLUMNSTORE INDEX
)
;");

Along with that you can now specify UseAzureSql or UseAzureSynapse. This allows EF to produce better SQL when using Azure SQL or Azure Synapse while using EF Core 9. For more information you can refer to below documents:

Upvotes: 0

Related Questions