none
EF insert data into a required, with default value, primary key column RRS feed

  • Question

  • Hi, 

    I have a table with following structure, it doesn't have the identity columm, just need to use the ID to persent the primakry key. i'm not allowed to modify the table structure.

    create table SysParaGroup
    (
    	ID varchar(36) primary key default('Sys'+replace(CAST( NEWID() as varchar(50)),'-','')),
    	ModuleName nvarchar(100)  not null,
    	GroupName nvarchar(100)  not null,
    	Desecription nvarchar(100)  not null,
    	IsShow bit  not null,
    	Sort bit  not null
    )
    I want to use db first EF to insert data to this table and let the table generate the default value, without passing the value in application.  When i add the attribute "        [Key,DatabaseGenerated(DatabaseGeneratedOption.Identity|Computed|None)]", none of them work. How can i implement it?


    Friday, May 5, 2017 10:55 AM

Answers

  • Generating a code-first model from that table, and setting the ID as "Identity" worked for me:

    public partial class Db : DbContext
        {
            public Db()
                : base("name=Db")
            {
            }
    
            public virtual DbSet<SysParaGroup> SysParaGroups { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<SysParaGroup>()
                    .Property(e => e.ID)
                    .IsUnicode(false)
                    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            }
        }

    and

    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace ConsoleApp6
    {
    
     
        class Program
        {
            static void Main(string[] args)
            {
                
                using (var db = new Db())
                {   
                    var g = db.SysParaGroups.Create();
                    g.ModuleName = "abc";
                    g.GroupName = "xyz";
                    g.Desecription = "something";
    
                    db.SysParaGroups.Add(g);
                    db.SaveChanges();
                    Console.WriteLine(g.ID);
                    Console.ReadLine();
                }
            }
        }
    }
    

    David


    Microsoft Technology Center - Dallas
    My blog

    Friday, May 5, 2017 2:46 PM

All replies

  • Generating a code-first model from that table, and setting the ID as "Identity" worked for me:

    public partial class Db : DbContext
        {
            public Db()
                : base("name=Db")
            {
            }
    
            public virtual DbSet<SysParaGroup> SysParaGroups { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<SysParaGroup>()
                    .Property(e => e.ID)
                    .IsUnicode(false)
                    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            }
        }

    and

    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace ConsoleApp6
    {
    
     
        class Program
        {
            static void Main(string[] args)
            {
                
                using (var db = new Db())
                {   
                    var g = db.SysParaGroups.Create();
                    g.ModuleName = "abc";
                    g.GroupName = "xyz";
                    g.Desecription = "something";
    
                    db.SysParaGroups.Add(g);
                    db.SaveChanges();
                    Console.WriteLine(g.ID);
                    Console.ReadLine();
                }
            }
        }
    }
    

    David


    Microsoft Technology Center - Dallas
    My blog

    Friday, May 5, 2017 2:46 PM
  • Nice workaround. I wonder how EF deals with that under the hood.

    william xifaras

    Friday, May 5, 2017 2:58 PM
  • >I wonder how EF deals with that under the hood.

    Like this:

    DECLARE @generated_keys table([ID] varchar(36))
    INSERT [dbo].[SysParaGroup]([ModuleName], [GroupName], [Desecription], [IsShow], [Sort])
    OUTPUT inserted.[ID] INTO @generated_keys
    VALUES (@0, @1, @2, @3, @4)
    SELECT t.[ID]
    FROM @generated_keys AS g JOIN [dbo].[SysParaGroup] AS t ON g.[ID] = t.[ID]
    WHERE @@ROWCOUNT > 0

    David


    Microsoft Technology Center - Dallas
    My blog

    Friday, May 5, 2017 3:06 PM
  • @davidbaxterbrowne, It's great and helps me enormously. thank you so much for your help. 
    Saturday, May 6, 2017 4:00 AM