CODEWITHSUNDEEP
c#.net-coreef-code-firstentity-framework-coreef-core-2.2
Durgesh Padha
Durgesh Padha

Reputation: 339

How to create COMPUTED column in MS SQL by ENTITY FRAMEWORK CORE code first with condition (string + auto increment number)

I'm trying to create a computed column in MS SQL with the help of HasComputedColumnSql, But unable to understand How.
My condition for column is "ABCD-Auto incremental value minimum 8 character".
I want the column with value ABCD-00000001

I have done it in SQL query and it is working.

CREATE TABLE BikeParts ( BikeParts_GUID AS 'ABCD-' + RIGHT(REPLICATE('0', 8) + CONVERT(VARCHAR, BikePart_ID), 8), BikePart_ID INT IDENTITY(1, 1), BikePart_Name VARCHAR(100) )

INSERT INTO BikeParts VALUES ('Break Cable') INSERT INTO BikeParts VALUES ('Seat Cover')

SELECT * FROM BikeParts

But unable to do same in entity framework core 2.2 with code first approach.
I will be really thankful for any kind of help.

Upvotes: 0

Views: 2902

Answers (1)

Durgesh Padha
Durgesh Padha

Reputation: 339

After a lot of search, finally I got the correct answer.

Model is:-

public partial class BikeParts
    {
        public int ID { get; set; }
        public string BikePartsGuid { get; set; }
        public int BikePartId { get; set; }
        public string BikePartName { get; set; }
    }

And configuration of model is:-

 protected override void OnModelCreating(ModelBuilder modelBuilder)
 {

        modelBuilder.HasSequence<int>("BikePartIdHelper", schema: "dbo").StartsAt(1).IncrementsBy(1);

        modelBuilder.Entity<BikeParts>(entity =>
        {
                entity.ToTable("BikeParts1");
                entity.HasKey(e => e.ID);
                entity.Property(e => e.ID).HasColumnName("ID").UseSqlServerIdentityColumn();
                entity.Property(e => e.BikePartId).HasColumnName("BikePart_ID")
                .HasDefaultValueSql("NEXT VALUE FOR dbo.BikePartIdHelper");
                entity.Property(e => e.BikePartName)
                    .HasColumnName("BikePart_Name")
                    .HasMaxLength(100)
                    .IsUnicode(false);
                entity.Property(e => e.BikePartsGuid)
                    .HasColumnName("BikeParts_GUID")
                    .HasMaxLength(13)
                    .IsUnicode(false)
                    .HasComputedColumnSql("('ABCD-'+right(replicate('0',(8))+CONVERT([varchar],[BikePart_ID]),(8)))");
        });        
 }

I used sequences for auto-generate BikePartId with the help of HasDefaultValueSql and on BikePartsGuid column I used HasComputedColumnSql

Upvotes: 3

Related Questions