GUID COMB strategy in EF

Question

Is there any way to implement the Guid COMB identity strategy for objects in the new Entity Framework 4.1 using the CodeFirst design? I thought setting the StoreGeneratedPattern would work, but it still gives me normal GUIDs.

Fistic

I presume you've already checked the DB-server-side default? – Integration

Yeah, I have. I did not find it to solve my problem. – Fistic

Answer 1

I guess you are using SQL server as your database. This is nice example of inconsistency among different MS tools. SQL server team doesn't recommend using newid() as default value for UNIQUEIDENTIFIER columns and ADO.NET team use it if you specify Guid property as autogenerated in the database. They should use newsequentialid() instead!

If you want sequential Guids generated by database you must modify generated table and it is really complex because you must find autogenerated default constraint, drop it and create new constraint. This all can be done in custom database initializer. Here you have my sample code:

class Program
{

    static void Main(string[] args)
    {
        Database.SetInitializer(new CustomInitializer());
        using (var context = new Context())
        {
            context.TestEntities.Add(new TestEntity() { Name = "A" });
            context.TestEntities.Add(new TestEntity() { Name = "B" });
            context.SaveChanges();
        }
    }
}

public class CustomInitializer : DropCreateDatabaseAlways<Context>
{
    protected override void Seed(Context context)
    {
        base.Seed(context);

        context.Database.ExecuteSqlCommand(@"
            DECLARE @Name VARCHAR(100)

            SELECT @Name = O.Name FROM sys.objects AS O
            INNER JOIN sys.tables AS T ON O.parent_object_id = T.object_id
            WHERE O.type_desc LIKE 'DEFAULT_CONSTRAINT' 
              AND O.Name LIKE 'DF__TestEntities__Id__%'
              AND T.Name = 'TestEntities'

            DECLARE @Sql NVARCHAR(2000) = 'ALTER TABLE TestEntities DROP Constraint ' + @Name

            EXEC sp_executesql @Sql

            ALTER TABLE TestEntities
            ADD CONSTRAINT IdDef DEFAULT NEWSEQUENTIALID() FOR Id");
    }
}

public class TestEntity
{
    public Guid Id { get; set; }
    public string Name { get; set; }
}

public class Context : DbContext
{
    public DbSet<TestEntity> TestEntities { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<TestEntity>()
            .Property(e => e.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
    }
}

Wisecrack

This seems unnecessary. I had really hoped the 4.1 EF would be more robust than this. Thank you very much for clearing this up for me, it worked perfectly. – Fistic

I would strongly recommend not doing stuff like this in the seed method. Instead it belongs in a separate migration. Seed will be called every time a migration is triggered. Not saying this is not a correct solution, just that it is triggered in the wrong place. – Quarter

@CasperLeonNielsen: I wrote this example long time before EF got migration API. You may add a new answer with migration to make it up-to-date. – Wisecrack

I'm not sure how this answers the question to be honest. Or even some of the other answers This is not COMB guid. One of the benefits of COMB guids is that you can get sequential-like behaviour while removing the need for a db roundtrip to get hold of your next guid. I'd rather point you to github.com/nhibernate/nhibernate-core/blob/master/src/… and grab NHibernates implementation. Then use Thomas' answer. – Prognosticate

Answer 2

Why worry about defaults for Guid columns in the database at all? Why not just generate the Guid on the client like any other value. That requires you have a method in your client code that will generate COMB-like guids:

public static Guid NewGuid()
{
    var guidBinary = new byte[16];
    Array.Copy( Guid.NewGuid().ToByteArray(), 0, guidBinary, 0, 8 );
    Array.Copy( BitConverter.GetBytes( DateTime.Now.Ticks ), 0, guidBinary, 8, 8 );
    return new Guid( guidBinary );
}

One of the advantages of the Guid is specifically that you can generate them on the client without a round trip to the database.

Greenburg

+1 If defining COMB like Guids is so easy you can combine this code with this: #5275806 and you get guid.comb initializer. – Wisecrack

I had a difficult time picking between the two answers, since both inevitably led to the same result. They are both useful. Thank you very much for this information, it works very well. – Fistic

Using sequentially generated Guids will result in indexing being less complex in SQL server. – Internment

Be careful of using the newsequencialid() on Sql Server. If the server reboots or the nic changes, then you'll get a new sequence and it may be lower than the initial sequence. – Decapitate

Answer 3

The simplest answer

public class User
{
    public User(Guid? id = null, DateTime? created = null)
    {
        if (id != null)
            Id = id;

        if (created != null)
            Created = created;
    }

    public User()
    {
    }

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public DateTime? Created { get; internal set; }

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid? Id { get; internal set; }
}

This assumes you have your database table set with the default of newsequentialid() which in my case is managed by FluentMigrator migrations.

Baltoslavic

In order for this to help others, it needs an example of "FluentMigrator migrations" – Quarter

@CasperLeonNielsen that's not really true, it requires the table schema to have a default set. How you set the default of newsequentialid() is really out of the scope of this question and becomes a generic sql schema concern. – Baltoslavic

#12257965 – Quarter

@CasperLeonNielsen what you linked there is about setting values of the rows of data in sql, what i'm talking about is the actual table schema dba.stackexchange.com/questions/7249/… – Baltoslavic

Answer 4

if you use SQL Server, when a GUID property is configured as value generated on add, the provider automatically performs value generation client-side, using an algorithm to generate optimal sequential GUID values. refer to for more.

https://learn.microsoft.com/en-us/ef/core/modeling/generated-properties?tabs=fluent-api

Djambi