EF Core 7.0 with a SQL Table (DB First) having an Identity Column and a Computed Column (Primary Key)

Question

EF Core 7.0 with a SQL Table (DB First) having an Identity Column and a Computed Column (Primary Key)

amjee84 6

Hi There,

I have a fairly simple table in SQL DB as below

[dbo].TBL_CUST

Column	Comments
ID	Int; Identity (1, 1)
D_ID	Computed column Calculated as = "DID" + RIGHT('000' + Cast(ID as varchar(3)) , 3) i.e.... Like DID001 / DID019 etc...
Name	Varchar (100); Nullable
Address	Varchar(500); Nullable

There is one After Trigger for this Table which captured all the deleted / inserted / updated record....

I'm doing a CRUD from EF Core 7 as below....

Model Looks like this

namespace Cust.Models.dsCust
{
  [Table("TBL_CUST", Schema = "dbo")]
  public partial class dboTblCust
  {
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID
    {
      get;
      set;
    }
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public string DID
    {
      get;
      set;
    }
    [ConcurrencyCheck]
    public string NAME
    {
      get;
      set;
    }
    [ConcurrencyCheck]
    public string ADDRESS
    {
      get;
      set;
    }
    [ConcurrencyCheck]
    public string IS_ACTIVE
    {
      get;
      set;
    }
  }
}

And DBContext has below

  builder.Entity<Cust.Models.dsCust.dboTblCust>()
              .ToTable(tb => tb.HasTrigger("trigger"))
              .Property(p => p.IS_ACTIVE)
              .HasDefaultValueSql("((1))").ValueGeneratedNever();

Although I am able to view and update data from UI but I am unable to insert any record. Everytime (despite setting DID as database generated column), the insert query from EF Core 7 is trying to insert data from UI and eventually throwing error.

What other Options I tried (Needless to mention, none worked)

Setting the following for DID column (instead of DatabaseGeneratedOption.Computed)

   DatabaseGeneratedOption.Identity

Setting private set; in DID column
Adding the following in DBContext

  builder.Entity<Cust.Models.dsCust.dboTblCust>()
              .ToTable(tb => tb.HasTrigger("trigger"))
              .Property(p => p.IS_ACTIVE)
              .HasDefaultValueSql("((1))").ValueGeneratedNever();

  builder.Entity<Cust.Models.dsCust.dboTblCust>()
              .Ignore(p => p.DID);

I already am experiencing the difficulty with EF Core 7 for Tables with Trigger & Computed Column. But has anyone came across such and managed any work around already, if yes kindly provide me some help here please.

1 answer

Your answer

Answer 1

Karen Payne MVP 35,586 Volunteer Moderator

Consider seeing the following project which is similar to what you are doing in regards to computed columns, article here. It may or may not serve your purpose but it shows adding, modify and view.

amjee84 6 Reputation points

2023-03-20T14:59:15.5233333+00:00

Apologies! I tried checking your code but I am not sure how /where I can fit it in my context... Sorry for my lack of experience here. I am totally blocked now for Several Days !!!!!!!
Karen Payne MVP 35,586 Reputation points Volunteer Moderator

2023-03-20T23:17:54.38+00:00

Best way to learn is to clone my repository as it may not make sense how I have done the configuration for the computed column outside of the DbContext as shown here. I used EF Core Power Tools which does all the work for you.

Share via

EF Core 7.0 with a SQL Table (DB First) having an Identity Column and a Computed Column (Primary Key)

1 answer

Your answer