How do I make Entity Framework 6 (DB First) explicitly insert a Guid/UniqueIdentifier primary key?

Question

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.

I need to be able to both :

1. insert a new record and let the ID be automatically created for it,
2. insert a new record with a specified ID.

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

---

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

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:

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)

Answer 1

I see 2 challenges:

1. Making your Id field an identity with auto generated value will prevent you from specifying your own GUID.
2. Removing the auto generated option may create duplicate key exceptions if the user forgets to explicitly create a new id.

Simplest solution:

1. Remove auto generated value
2. Ensure Id is a PK and is required
3. Generate a new Guid for your Id in the default constructor of your models.

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).

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() };

Answer 2

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.

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

Override all 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.

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.

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.

The following is in small steps; if desired you can create one big 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.

Addition: 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.

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.

// 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.

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.

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.

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

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.

It's in Nuget IdGen package

Answer 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.

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.

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

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

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

Answer 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.

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.