Use EF Core code-first approach with Azure Synapse

Question

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);
            });

Could you please provide your complete code?

Bhavani
– Bhavani

2024-11-29 05:06:49 +00:00
Commented Nov 29, 2024 at 5:06 — Bhavani
– Bhavani, Commented Nov 29, 2024 at 5:06
I have added the code

Sonal Khatri
– Sonal Khatri

2024-11-30 16:16:12 +00:00
Commented Nov 30, 2024 at 16:16 — Sonal Khatri
– Sonal Khatri, Commented Nov 30, 2024 at 16:16

Bhavani · Accepted Answer · 2024-12-03 09:40:37Z

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:

Collectives™ on Stack Overflow

Use EF Core code-first approach with Azure Synapse

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related