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

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