0

I created a column in a table named [Id] and made its type nvarchar(50), and its value is automatically by newid(), which is a mixture of letters and numbers, when adding a new record inside the database through a (sql server managment) , the new number does not appear until I execute the table.

The problem is not here, the real problem is when creating a new record from within EF6, the record cannot be added without specifying the identity value, and the following message appears:

The key field 'Id' cannot have a value of null. A non-null value is required for the key fields defined on type 'Booking'.

this code for sqlserver table


CREATE TABLE [dbo].[TableName](
    [id] [varchar](10) NOT NULL,
    [Price] [nvarchar](10) NULL,
PRIMARY KEY CLUSTERED 
(
    [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]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TableName] ADD  DEFAULT (concat(left(newid(),(4)),'-',abs(checksum(newid()))%(10000))) FOR [id]
GO

In Ef6 Use Code

context Db = new context ();
t TableName = new TableName{
Price=10
};
db.TableName.add(t);
db.SaveChanges();
jarlh
  • 42,561
  • 8
  • 45
  • 63
EBRAHEM
  • 15
  • 4
  • 2
    FYI, that method of creating a "unique" value can (and will) cause collisions, and thus result in your `INSERT` statements failing. – Thom A Apr 25 '23 at 21:09
  • What does the definition of your model for the table look like? – Thom A Apr 25 '23 at 21:10
  • As an quick example, after a meer 100,000 rows there are [multiple collisions](https://dbfiddle.uk/3RzHDeuI) and at a million it gets [far worse](https://dbfiddle.uk/uXSKNQ5g). – Thom A Apr 25 '23 at 21:19
  • 2
    As a separate note, `nvarchar` seems like an odd data type choice for a column called `price`; what sort of "price" is `'apple'` or `'ampersand'`? – Thom A Apr 25 '23 at 21:22
  • Newid() returns a `uniqueidentifier` (also known as Guid - a 128 bit number) which is almost certainly unique. However when you use just a part of it, that is *not* unique – Hans Kesting Apr 26 '23 at 06:01
  • 1
    I strongly suggest using EF Core in the way shown in the documentation tutorials before trying to customize it. Use `long` Id that's defined as an `IDENTITY` instead of a GUID (which will cause huge table fragmentation). Definitely don't try to get clever with the primary key. Smart PKs aren't. Use the correct type for price, eg `numeric(10,2)`. Moreover, EF deals with *application entities*, not tables. A DbContext is a Repository, not a SqlConnection. You're storing your application objects, not table rows – Panagiotis Kanavos Apr 26 '23 at 09:10

1 Answers1

-1

you must add this code on Migration Class(My DbMigration's name is Initial) before level add-migration

this is code for only EF Classic

, defaultValueSql: "concat(left(newid(),(4)),'-',abs(checksum(newid()))%(10000))"

My class DbMigration


using System.Data.Entity.Migrations;
  
  public partial class Initial : DbMigration
  {
      public override void Up()
      {  CreateTable(
              "dbo.TableNames",
              c => new
                  {  Id = c.String(nullable: false, maxLength: 100, defaultValueSql: "concat(left(newid(),(4)),'-',abs(checksum(newid()))%(10000))"),
                      Price = c.String(maxLength: 100),
                  })  .PrimaryKey(t => t.Id);
            }
      
      public override void Down()
      {  DropTable("dbo.TableNames"); }
  }

my class :

public class TableName
{ 
    [Key]
    [MaxLength(100)]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public string Id { get; set; }

    [MaxLength(100)]
    public string Price { set; get; }
}

my DbContext:

public class MyContext : DbContext
{
    public MyContext()
        : base("Name=ConnectionString")
    { }
    public DbSet<TableName> TableName { get; set; }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {   base.OnModelCreating(modelBuilder); }
 }

add TableName

var Db = new MyContext();
var TableName = new TableName{ Price = "40"};
Db.TableName.Add(TableName);
Db.SaveChanges();

my Package:

  • EntityFramework:6.4.4
abolfazl sadeghi
  • 2,277
  • 2
  • 12
  • 20
  • thankk for you, but code is error: – EBRAHEM Apr 26 '23 at 01:10
  • Severity Code Description Project File Line Suppression State Error CS1061 'StringPropertyConfiguration' does not contain a definition for 'HasDefaultValue' and no accessible extension method 'HasDefaultValue' accepting a first argument of type 'StringPropertyConfiguration' could be found (are you missing a using directive or an assembly reference?) – EBRAHEM Apr 26 '23 at 01:10
  • I tested and that My code is okay and add result,I used ef core 6.16 and efcore and ef core sql server and ef core design.this is useful for hasdefultvalue https://learn.microsoft.com/en-us/ef/core/modeling/generated-properties?tabs=data-annotations#default-values – abolfazl sadeghi Apr 26 '23 at 01:25
  • I add my Package with version,my DbContext, add TableName ,I hope help you – abolfazl sadeghi Apr 26 '23 at 09:02
  • You list both EF classic and EF Core in the package list. The question is only about EF Classic. Are you sure you used the correct package? Conventions have changed a lot between EF Classic and EF Core. – Panagiotis Kanavos Apr 26 '23 at 09:15
  • my code for this post is okay because TableName mr EBRAHEM generated by the database(he added script create table ).The question didn't talk about version EF ,I added final Result – abolfazl sadeghi Apr 26 '23 at 09:24
  • Thanks brother abolfazl sadeghi , for your patience with me and the impossibility of making my problem work, I use:EF Classic – EBRAHEM Apr 27 '23 at 01:13
  • I edited my code for EF calssis and Tested my code ? if willn't worked please send error for me.if my code work.please upvote. – abolfazl sadeghi Apr 27 '23 at 16:45