How can specify ROWGUIDCOL property to Guid type column in code first or with ColumnBuilder?

Question

Consider this migration code:

       CreateTable(
            "dbo.Document",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Doc = c.String(),
                    RowGuid = c.Guid(nullable: false),
                    Person_Id = c.Int(),
                })
            .PrimaryKey(t => t.Id)
            .ForeignKey("dbo.Person", t => t.Person_Id)
            .Index(t => t.Person_Id);

i want the RowGuid be ROWGUIDCOL, and be defined like this (SQL):

 [RowGuid] [UNIQUEIDENTIFIER] not null RowGuidCol Unique default newid()

What is the equivalent code in EntityFramework/CodeFirst ? What is the solution?

Thanks.

Answer 1

I imagine you want this for a table with a FILESTREAM column. Hinting EF with an EntityTypeConfiguration makes it generate correct scripts and doesn't need you to manually edit it (EF Core 2.2).

Taking this sample entity:

public class FileEntity
{
    public Guid FileGuid { get; private set; }
    public byte[] Document { get; private set; }
}

And this configuration:

public class FileEntityConfiguration : IEntityTypeConfiguration<FileEntity>
{
    public void Configure(EntityTypeBuilder<FileEntity> builder)
    {
        builder
            .Property(m => m.Document)
            .HasColumnType("VARBINARY(MAX) FILESTREAM");

        builder
            .Property(m => m.FileGuid)
            .HasColumnType("UNIQUEIDENTIFIER ROWGUIDCOL")
            .IsRequired();

        builder
            .HasAlternateKey(m => m.FileGuid);
    }
}

EF generates a correct migration script:

FileGuid = table.Column<Guid>(type: "UNIQUEIDENTIFIER ROWGUIDCOL", nullable: false),
Document = table.Column<byte[]>(type: "VARBINARY(MAX) FILESTREAM", nullable: true)

Answer 2

It does not appear that the ROWGUIDCOL property can be set directly via Entity Framework, but it might be possible to inject the property into the generate SQL by making "creative" ;-) use of the storeType parameter (assuming storeType truly allows you to override the default datatype). Starting with the code from the original question, try something like the following:

 CreateTable(
            "dbo.Document",
            c => new
                {
                    RowGuid = c.Guid(nullable: false, identity: true,  
                                     defaultValueSql: "newid()",
                                     storeType: "UNIQUEIDENTIFIER ROWGUIDCOL"),
                    Person_Id = c.Int()
                })
            .Index(t => t.RowGuid, true);

Unfortunately, I do not have a way to test this, but given that the following SQL works, I think it is worth a shot:

CREATE TABLE dbo.Test1
(
    Col1 INT NOT NULL,
    Col2 UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWID()
)

The "UNIQUE" requirement is accomplished via a Unique Index created by the second parameter being "true" in the Index() method.

Please note that there might be some issue using "identity: true" in the Guid() method if the table already has a column marked with IDENTITY. I found this related question which addresses that situation: Entity Framework Code First Using Guid as Identity with another Identity Column