如何使实体框架6 (DB First)显式地插入Guid/UniqueIdentifier主键?

I am using Entity Framework 6 DB First with SQL Server tables that each have a uniqueidentifier primary key. The tables have a default on the primary key column that sets it to newid(). I have accordingly updated my .edmx to set the StoreGeneratedPattern for these columns to Identity. So I can create new records, add them to my database context and the IDs are generated automatically. But now I need to save a new record with a specific ID. I've read this article which says you have to execute SET IDENTITY_INSERT dbo.[TableName] ON before saving when using an int identity PK column. Since mine are Guid and not actually an identity column, that's essentially already done. Yet even though in my C# I set the ID to the correct Guid, that value is not even passed as a parameter to the generated SQL insert and a new ID is generated by the SQL Server for the primary key.

我首先使用实体框架6db来处理SQL Server表，每个表都有一个惟一标识符主键。表的主键列上有一个默认值，将其设置为newid()。因此，我已经更新了我的.edmx来设置这些列的StoreGeneratedPattern来标识。因此，我可以创建新的记录，将它们添加到数据库上下文，并自动生成id。但是现在我需要用一个特定的ID保存一个新的记录。[TableName]在使用int identity PK列保存之前。因为我的是Guid而不是一个恒等列，这基本上已经完成了。然而，即使在c#中，我将ID设置为正确的Guid，该值甚至不会作为参数传递给生成的SQL insert, SQL服务器也会为主键生成新的ID。

I need to be able to both :

我需要同时做到这两点:

1. insert a new record and let the ID be automatically created for it,
2. 插入一个新的记录，让ID自动为它创建，
3. insert a new record with a specified ID.
4. 插入一个具有指定ID的新记录。

I have # 1. How can I insert a new record with a specific primary key?

我有# 1。如何使用特定的主键插入新记录?

---

Edit:
Save code excerpt (Note accountMemberSpec.ID is the specific Guid value I want to be the AccountMember's primary key):

编辑:保存代码摘录(注意accountMemberSpec)。ID是具体的Guid值，我想成为AccountMember的主键):

IDbContextScopeFactory dbContextFactory = new DbContextScopeFactory();

using (var dbContextScope = dbContextFactory.Create())
{
    //Save the Account
    dbAccountMember = CRMEntity<AccountMember>.GetOrCreate(accountMemberSpec.ID);

    dbAccountMember.fk_AccountID = accountMemberSpec.AccountID;
    dbAccountMember.fk_PersonID = accountMemberSpec.PersonID;

    dbContextScope.SaveChanges();
}

--

- - -

public class CRMEntity<T> where T : CrmEntityBase, IGuid
{
    public static T GetOrCreate(Guid id)
    {
        T entity;

        CRMEntityAccess<T> entities = new CRMEntityAccess<T>();

        //Get or create the address
        entity = (id == Guid.Empty) ? null : entities.GetSingle(id, null);
        if (entity == null)
        {
            entity = Activator.CreateInstance<T>();
            entity.ID = id;
            entity = new CRMEntityAccess<T>().AddNew(entity);
        }

        return entity;
    }
}

--

- - -

public class CRMEntityAccess<T> where T : class, ICrmEntity, IGuid
{
    public virtual T AddNew(T newEntity)
    {
        return DBContext.Set<T>().Add(newEntity);
    }
}

And here is the logged, generated SQL for this:

这里是记录的，生成的SQL:

DECLARE @generated_keys table([pk_AccountMemberID] uniqueidentifier)
INSERT[dbo].[AccountMembers]
([fk_PersonID], [fk_AccountID], [fk_FacilityID])
OUTPUT inserted.[pk_AccountMemberID] INTO @generated_keys
VALUES(@0, @1, @2)
SELECT t.[pk_AccountMemberID], t.[CreatedDate], t.[LastModifiedDate]
FROM @generated_keys AS g JOIN [dbo].[AccountMembers] AS t ON g.[pk_AccountMemberID] = t.[pk_AccountMemberID]
WHERE @@ROWCOUNT > 0


-- @0: '731e680c-1fd6-42d7-9fb3-ff5d36ab80d0' (Type = Guid)

-- @1: 'f6626a39-5de0-48e2-a82a-3cc31c59d4b9' (Type = Guid)

-- @2: '127527c0-42a6-40ee-aebd-88355f7ffa05' (Type = Guid)

4 个解决方案

#1

A solution could be to override DbContext SaveChanges. In this function find all added entries of the DbSets of which you want to specify the Id.

解决方案可以是覆盖DbContext SaveChanges。在此函数中，查找要指定Id的所有dbset的所有添加项。

If the Id is not specified yet, specify one, if it is already specified: use the specified one.

如果还没有指定Id，则指定一个，如果已经指定了，则指定一个:使用指定的Id。

Override all SaveChanges:

覆盖所有SaveChanges:

public override void SaveChanges()
{
    GenerateIds();
    return base.SaveChanges();
}
public override async Task<int> SaveChangesAsync()
{
    GenerateIds();
    return await base.SaveChangesAsync();
}
public override async Task<int> SaveChangesAsync(System.Threading CancellationToken token)
{
    GenerateIds();
    return await base.SaveChangesAsync(token);
}

GenerateIds should check if you already provided an Id for your added entries or not. If not, provide one.

GenerateIds应该检查是否已经为添加的条目提供了Id。如果没有,提供一个。

I'm not sure if all DbSets should have the requested feature, or only some. To check whether the primary key is already filled, I need to know the identifier of the primary key.

我不确定是否所有的DbSets都应该具有所请求的特性，或者只有一些。要检查主键是否已填充，我需要知道主键的标识符。

I see in your class CRMEntity that you know that every T has an Id, this is because this Id is in CRMEntityBase, or in IGuid, let's assume it is in IGuid. If it is in CRMEntityBase change the following accordingly.

我在你们的课程中看到你们知道每个T都有一个Id，这是因为这个Id在CRMEntityBase中，或者IGuid中，假设它在IGuid中。如果是在CRMEntityBase中，请相应更改以下内容。

The following is in small steps; if desired you can create one big LINQ.

以下是小步骤;如果需要，您可以创建一个大LINQ。

private void GenerateIds()
{
    // fetch all added entries that have IGuid
    IEnumerable<IGuid> addedIGuidEntries = this.ChangeTracker.Entries()
        .Where(entry => entry.State == EntityState.Added)
        .OfType<IGuid>()

    // if IGuid.Id is default: generate a new Id, otherwise leave it
    foreach (IGuid entry in addedIGuidEntries)
    {
        if (entry.Id == default(Guid)
            // no value provided yet: provide it now
            entry.Id = GenerateGuidId() // TODO: implement function
        // else: Id already provided; use this Id.
    }
}

That is all. Because all your IGuid objects now have a non-default ID (either pre-defined, or generated inside GenerateId) EF will use that Id.

这是所有。因为所有IGuid对象现在都有一个非默认ID(预定义的或在GenerateId中生成的)EF将使用这个ID。

Addition: HasDatabaseGeneratedOption

另外:HasDatabaseGeneratedOption

As xr280xr pointed out in one of the comments, I forgot that you have to tell entity framework that entity framework should not (always) generate an Id.

正如xr280xr在其中一条注释中指出的，我忘记了必须告诉实体框架实体框架不(总是)生成Id。

As an example I do the same with a simple database with Blogs and Posts. A one-to-many relation between Blogs and Posts. To show that the idea does not depend on GUID, the primary key is a long.

举个例子，我用博客和帖子来做一个简单的数据库。博客和文章之间的一对多关系。为了表明这个想法不依赖于GUID，主键很长。

// If an entity class is derived from ISelfGeneratedId,
// entity framework should not generate Ids
interface ISelfGeneratedId
{
    public long Id {get; set;}
}
class Blog : ISelfGeneratedId
{
    public long Id {get; set;}          // Primary key

    // a Blog has zero or more Posts:
    public virtual ICollection><Post> Posts {get; set;}

    public string Author {get; set;}
    ...
}
class Post : ISelfGeneratedId
{
    public long Id {get; set;}           // Primary Key
    // every Post belongs to one Blog:
    public long BlogId {get; set;}
    public virtual Blog Blog {get; set;}

    public string Title {get; set;}
    ...
}

Now the interesting part: The fluent API that informs Entity Framework that the values for primary keys are already generated.

现在有趣的部分是:fluent API，它通知实体框架主键的值已经生成。

I prefer fluent API avobe the use of attributes, because the use of fluent API allows me to re-use the entity classes in different database models, simply by rewriting Dbcontext.OnModelCreating.

我更喜欢使用fluent API作为属性，因为使用fluent API可以在不同的数据库模型中重用实体类，只需重写dbcontext . onmodelcreation。

For example, in some databases I like my DateTime objects a DateTime2, and in some I need them to be simple DateTime. Sometimes I want self generated Ids, sometimes (like in unit tests) I don't need that.

例如，在一些数据库中，我喜欢DateTime对象是DateTime2，在一些数据库中，我需要它们是简单的DateTime。有时我需要自生成的id，有时(比如在单元测试中)我不需要它。

class MyDbContext : Dbcontext
{
    public DbSet<Blog> Blogs {get; set;}
    public DbSet<Post> Posts {get; set;}

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
         // Entity framework should not generate Id for Blogs:
         modelBuilder.Entity<Blog>()
             .Property(blog => blog.Id)
             .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
         // Entity framework should not generate Id for Posts:
         modelBuilder.Entity<Blog>()
             .Property(blog => blog.Id)
             .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

         ... // other fluent API
    }

SaveChanges is similar as I wrote above. GenerateIds is slightly different. In this example I have not the problem that sometimes the Id is already filled. Every added element that implements ISelfGeneratedId should generate an Id

SaveChanges与我上面所写的类似。GenerateIds略有不同。在本例中，我没有遇到有时Id已经被填充的问题。实现ISelfGeneratedId的每个添加元素都应该生成一个Id

private void GenerateIds()
{
    // fetch all added entries that implement ISelfGeneratedId
    var addedIdEntries = this.ChangeTracker.Entries()
        .Where(entry => entry.State == EntityState.Added)
        .OfType<ISelfGeneratedId>()

    foreach (ISelfGeneratedId entry in addedIdEntries)
    {
        entry.Id = this.GenerateId() ;// TODO: implement function
        // now you see why I need the interface:
        // I need to know the primary key
    }
}

For those who are looking for a neat Id generator: I often use the same generator as Twitter uses, one that can handle several servers, without the problem that everyone can guess from the primary key how many items are added.

对于那些正在寻找整洁的Id生成器的人来说:我经常使用与Twitter使用的相同的生成器，一个可以处理多个服务器的生成器，没有每个人都可以从主键猜测添加了多少项的问题。

It's in Nuget IdGen package

它在Nuget IdGen包中。

#2

I see 2 challenges:

我看到两个挑战:

1. Making your Id field an identity with auto generated value will prevent you from specifying your own GUID.
2. 将Id字段设置为具有自动生成值的标识将阻止您指定自己的GUID。
3. Removing the auto generated option may create duplicate key exceptions if the user forgets to explicitly create a new id.
4. 如果用户忘记显式地创建新的id，删除自动生成的选项可能会创建重复的关键异常。

Simplest solution:

最简单的解决方案:

1. Remove auto generated value
2. 删除自动生成的值
3. Ensure Id is a PK and is required
4. 确保Id是一个PK，并且是必需的。
5. Generate a new Guid for your Id in the default constructor of your models.
6. 在模型的默认构造函数中为您的Id生成一个新的Guid。

Example Model

示例模型

public class Person
{
    public Person()
    {
        this.Id = Guid.NewGuid();
    }

    public Guid Id { get; set; }
}

Usage

使用

// "Auto id"
var person1 = new Person();

// Manual
var person2 = new Person
{
    Id = new Guid("5d7aead1-e8de-4099-a035-4d17abb794b7")
}

This will satisfy both of your needs while keeping the db safe. The only down side of this is you have to do this for all models.

这样既能满足您的需求，又能保证数据库的安全。唯一的缺点是你必须对所有的模型都这样做。

If you go with this approach, I'd rather see a factory method on the model which will give me the object with default values (Id populated) and eliminate the default constructor. IMHO, hiding default value setters in the default constructor is never a good thing. I'd rather have my factory method do that for me and know that the new object is populated with default values (with intention).

如果您使用这种方法，我宁愿看到模型上的factory方法，它将为我提供具有默认值(Id填充)的对象，并消除默认构造函数。在默认构造函数中隐藏默认值设置并不是一件好事。我宁愿让我的factory方法为我做这些，并知道新对象是用默认值填充的(故意)。

public class Person
{
    public Guid Id { get; set; }

    public static Person Create()
    {
        return new Person { Id = Guid.NewGuid() };
    }
}

Usage

使用

// New person with default values (new Id)
var person1 = Person.Create();

// Empty Guid Id
var person2 = new Person();

// Manually populated Id
var person3 = new Person { Id = Guid.NewGuid() };

#3

I don't think there is a real answer for this one...

我认为这个问题没有真正的答案……

As said here How can I force entity framework to insert identity columns? you can enable the mode #2, but it'll break #1.

如前所述，如何强制实体框架插入标识列?你可以启用模式#2，但它会打破#1。

using (var dataContext = new DataModelContainer())
using (var transaction = dataContext.Database.BeginTransaction())
{
  var user = new User()
  {
    ID = id,
    Name = "John"
  };

  dataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[User] ON");

  dataContext.User.Add(user);
  dataContext.SaveChanges();

  dataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[User] OFF");

  transaction.Commit();
}

you should change value of StoreGeneratedPattern property of identity column from Identity to None in model designer.

在模型设计器中，应该将标识列的StoreGeneratedPattern属性的值从identity更改为None。

Note, changing of StoreGeneratedPattern to None will fail inserting of object without specified id

注意，将StoreGeneratedPattern更改为None将无法在没有指定id的情况下插入对象

As you can see, you're no longer able to insert without setting by yourself an ID.

如您所见，如果不设置ID，您将无法插入。

But, if you look on the bright side : Guid.NewGuid() will allow you to make a new GUID without the DB generation function.

但是，如果您从好的方面来看:guide . newguid()将允许您在不使用DB生成函数的情况下创建一个新的GUID。

#4

The solution is: write your own insert query. I've put together a quick project to test this, so the example has nothing to do with your domain, but you'll ge the ideea.

解决方案是:编写自己的插入查询。我已经构建了一个快速项目来测试它，因此这个示例与您的域无关，但是您将获取ideea。

using (var ctx = new Model())
{
    var ent = new MyEntity
    {
        Id = Guid.Empty,
        Name = "Test"
    };

    try
    {
        var result = ctx.Database.ExecuteSqlCommand("INSERT INTO MyEntities (Id, Name) VALUES ( @p0, @p1 )", ent.Id, ent.Name);
    }
    catch (SqlException e)
    {
        Console.WriteLine("id already exists");
    }
}

The ExecuteSqlCommand returns "rows affected" (in this case 1), or throws exception for duplicate key.

ExecuteSqlCommand返回“受影响的行”(在本例1中)，或为重复键抛出异常。