search

dotnet / EntityFramework.Docs

Documentation for Entity Framework Core and Entity Framework 6
https://docs.microsoft.com/ef/
Creative Commons Attribution 4.0 International
1.63k stars 1.96k forks source link

Table with Non-Primary ID (Identity) column and a Computed [Based on ID] Primary Key (String) Column - EF 7.0 ADD Not Working #4350

Closed amukjee closed 1 year ago

amukjee commented 1 year ago

In My use case (DB [MS SQL 2019] First), we have a Table as below... We got an Identity Column ID but it is not the Primary Key, rather, there is [DATAMASTER_DB_ID] column which is Database derived/Calculated (based on ID column) column and this column is acting as Primary Key and it is having associated Foreign Key relationship with associated T_DM_DATADETAIL table.

Any suggestion how we can fit it with Entity Framework (using 7) for Model and CRUD page (Specially for ADD operation)?

_/****** Object:  Table [msi].[T_DM_DATAMASTER]    Script Date: 20/5/2023 3:01:19 pm ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [msi].[T_DM_DATAMASTER](
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [DATAMASTER_DB_ID]  AS ('DTM'+RIGHT('000000'+CONVERT([VARCHAR](6),[ID]),(6))) PERSISTED NOT NULL,
    [DATAMASTER_CODE] [VARCHAR](20) NOT NULL,
    [DAILYCUTOFFDAYOFFSET] [SMALLINT] NOT NULL,
    [DAILYCUTOFFTIME] [TIME](7) NOT NULL,
    [DAILYKICKOFFDAYOFFSET] [SMALLINT] NOT NULL,
    [DAILYKICKOFFTIME] [TIME](7) NOT NULL,
    [DAILYCYCLETIMEZONE] [VARCHAR](100) NOT NULL,
    [IS_ENABLED] [BIT] NOT NULL,
    [NOTE] [VARCHAR](2000) NULL,
    [DATAMASTER_LONGNAME] [VARCHAR](200) NOT NULL,
 CONSTRAINT [Pk_T_DM_DATAMASTER_DATAMASTER_DB_ID] PRIMARY KEY CLUSTERED 
(
    [DATAMASTER_DB_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [UNQ_T_DM_DATAMASTER_DATAMASTER_CODE] UNIQUE NONCLUSTERED 
(
    [DATAMASTER_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [msi].[T_DM_DATAMASTER]  WITH CHECK ADD  CONSTRAINT [CHK_T_DM_DATAMASTER_APP_CODE] CHECK  ((NOT [DATAMASTER_CODE] LIKE '%[^A-Z0-9_]%'))
GO

ALTER TABLE [msi].[T_DM_DATAMASTER] CHECK CONSTRAINT [CHK_T_DM_DATAMASTER_APP_CODE]
GO_

What we tried:

Model:

[Table("T_DM_DATAMASTER", Schema = "msi")]
    public partial class MsiTdmdatamaster
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ID { get; set; }

        [Key]
        [Required]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public string DATAMASTER_DB_ID { get; set; }

        [Required]
        public string DATAMASTER_CODE { get; set; }

        [Required]
        public short DAILYCUTOFFDAYOFFSET { get; set; }

        [Required]
        public TimeSpan DAILYCUTOFFTIME { get; set; }

        [Required]
        public short DAILYKICKOFFDAYOFFSET { get; set; }

        [Required]
        public TimeSpan DAILYKICKOFFTIME { get; set; }

        [Required]
        public string DAILYCYCLETIMEZONE { get; set; }

        public bool IS_ENABLED { get; set; }

        public string NOTE { get; set; }

        [Required]
        public string DATAMASTER_LONGNAME { get; set; }

        public ICollection<MsiTdmdataheader> MsiTdmdataheaders { get; set; }

Context / OnModelCreating

builder.Entity<EDQTrial.Models.UITest.MsiTdmdatamaster>()
              .Property(p => p.ID)
              .ValueGeneratedOnAdd()
              .Metadata.SetAfterSaveBehavior(Microsoft.EntityFrameworkCore.Metadata.PropertySaveBehavior.Throw);

            builder.Entity<EDQTrial.Models.UITest.MsiTdmdatamaster>()
              .Property(p => p.ID)
              .ValueGeneratedOnAdd()
              .Metadata.SetAfterSaveBehavior(Microsoft.EntityFrameworkCore.Metadata.PropertySaveBehavior.Throw);

Irrespective of all trial and error so far, I only see - EF is continuously trying to Push a NULL value in DATAMASTER_DB_ID during insert and obviously Insert not working.

So far only workaround I could utilize is Not using EF and make insert via Stored Proc. But I am constantly doubting there must be some ways in EF to handle this situation. Any suggestion please?

Document Details

⚠ Do not edit this section. It is required for learn.microsoft.com ➟ GitHub issue linking.

ajcvickers commented 1 year ago

@amukjee This works for me (full code below):

var property = modelBuilder
    .Entity<MsiTdmdatamaster>()
    .Property(e => e.DATAMASTER_DB_ID)
    .ValueGeneratedOnAdd()
    .HasComputedColumnSql(@"('DTM'+RIGHT('000000'+CONVERT([VARCHAR](6),[ID]),(6)))")
    .Metadata;

property.SetBeforeSaveBehavior(PropertySaveBehavior.Ignore);
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }

    [Key]
    [Required]
    public string DATAMASTER_DB_ID { get; set; }
info: 5/22/2023 11:00:02.783 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (28ms) [Parameters=[@p0='0', @p1='00:00:00', @p2='Y' (Nullable = false) (Size = 4000), @p3='0', @p4='00:00:00', @p5='638203464019392364' (Nullable = false) (Size = 4000), @p6='Z' (Nullable = false) (Size = 4000), @p7='False', @p8=NULL (Size = 4000)], CommandType='Text', CommandTimeout='
30']
      SET IMPLICIT_TRANSACTIONS OFF;
      SET NOCOUNT ON;
      INSERT INTO [dbo].[T_DM_DATAMASTER] ([DAILYCUTOFFDAYOFFSET], [DAILYCUTOFFTIME], [DAILYCYCLETIMEZONE], [DAILYKICKOFFDAYOFFSET], [DAILYKICKOFFTIME], [DATAMASTER_CODE], [DATAMASTER_LONGNAME], [IS_ENABLED], [NOTE])
      OUTPUT INSERTED.[DATAMASTER_DB_ID], INSERTED.[ID]
      VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8);
ID = 7, DATAMASTER_DB_ID = DTM000007
info: 5/22/2023 11:00:02.999 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [t].[DATAMASTER_DB_ID], [t].[DAILYCUTOFFDAYOFFSET], [t].[DAILYCUTOFFTIME], [t].[DAILYCYCLETIMEZONE], [t].[DAILYKICKOFFDAYOFFSET], [t].[DAILYKICKOFFTIME], [t].[DATAMASTER_CODE], [t].[DATAMASTER_LONGNAME], [t].[ID], [t].[IS_ENABLED], [t].[NOTE]
      FROM [dbo].[T_DM_DATAMASTER] AS [t]
ID = 4, DATAMASTER_DB_ID = DTM000004
ID = 6, DATAMASTER_DB_ID = DTM000006
ID = 7, DATAMASTER_DB_ID = DTM000007
#nullable disable

using (var context = new SomeDbContext())
{
    var entity = new MsiTdmdatamaster()
    {
        DATAMASTER_CODE = DateTime.UtcNow.Ticks.ToString(),
        DAILYCYCLETIMEZONE = "Y",
        DATAMASTER_LONGNAME = "Z",
    };

    context.Add(entity);
    context.SaveChanges();

    Console.WriteLine($"ID = {entity.ID}, DATAMASTER_DB_ID = {entity.DATAMASTER_DB_ID}");
}

using (var context = new SomeDbContext())
{
    foreach (var msiTdmdatamaster in context.Set<MsiTdmdatamaster>().ToList())
    {
        Console.WriteLine($"ID = {msiTdmdatamaster.ID}, DATAMASTER_DB_ID = {msiTdmdatamaster.DATAMASTER_DB_ID}");
    }
}

public class SomeDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Data Source=(LocalDb)\MSSQLLocalDB;Database=AllTogetherNow")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        var property = modelBuilder
            .Entity<MsiTdmdatamaster>()
            .Property(e => e.DATAMASTER_DB_ID)
            .ValueGeneratedOnAdd()
            .HasComputedColumnSql(@"('DTM'+RIGHT('000000'+CONVERT([VARCHAR](6),[ID]),(6)))")
            .Metadata;

        property.SetBeforeSaveBehavior(PropertySaveBehavior.Ignore);
    }
}

[Table("T_DM_DATAMASTER", Schema = "dbo")]
public partial class MsiTdmdatamaster
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }

    [Key]
    [Required]
    public string DATAMASTER_DB_ID { get; set; }

    [Required]
    public string DATAMASTER_CODE { get; set; }

    [Required]
    public short DAILYCUTOFFDAYOFFSET { get; set; }

    [Required]
    public TimeSpan DAILYCUTOFFTIME { get; set; }

    [Required]
    public short DAILYKICKOFFDAYOFFSET { get; set; }

    [Required]
    public TimeSpan DAILYKICKOFFTIME { get; set; }

    [Required]
    public string DAILYCYCLETIMEZONE { get; set; }

    public bool IS_ENABLED { get; set; }

    public string NOTE { get; set; }

    [Required]
    public string DATAMASTER_LONGNAME { get; set; }
}
amukjee commented 1 year ago

Thank you @ajcvickers really appreciate your help.

So earlier as I mentioned in my code, I used: .Metadata.SetAfterSaveBehavior(Microsoft.EntityFrameworkCore.Metadata.PropertySaveBehavior.Throw);

The only thing I had to change to make (Add /Create ) it work is : (From AfterSave to BeforeSave) .Metadata.SetBeforeSaveBehavior(Microsoft.EntityFrameworkCore.Metadata.PropertySaveBehavior.ignore);

Its a Key column - So NULL entry not allowed, we must have a value before saving, that's I recon was the tweak and DB managed to calculate and generate the Value now.

Thank a lot again... I will proceed to mark the issue closed.