PetaPoco fails inserting record with Guid newid primary key

Question

I'm experimenting with Peta POCO ORM and I quite like it, however there is one thing I can't make work. I'm using MS SQL and table with uniqueidentifier as primary key. However when I insert new record, POCO throws an InvalidCastException trying to cast DBNull to Guid. I created a trivial example. MS SQL table definition :

    CREATE TABLE MyTable
(
    id uniqueidentifier NOT NULL DEFAULT newid(),
    name nvarchar(50) NULL,
    surname nvarchar(50) NULL,
    CONSTRAINT pk_MyTable PRIMARY KEY (id)
)

and then in C# I have:

[PetaPoco.TableName("MyTable")]
[PetaPoco.PrimaryKey("id")]
public class MyTable
{
    public Guid id { get; set; }
    public string name { get; set; }
    public string surname { get; set; }
}

and finally I try to insert new records:

using (var conn = new SqlConnection(myConnString))
{
conn.Open()
using (var db = new PetaPoco.Database(conn))
            {
                for (int i = 0; i < 3; i++)
                {
                    var table = new MyTable() { name = "oh my", surname = i.ToString() };
                    db.Insert(table);
                }
            }
}

Thanks for any help

Answer 1

I believe you need to pre-populate guid type primary key before calling Insert. PetaPoco (at least in V4) support auto filling identity columns after inserts. For guid columns you need to generate guid yourself by calling Guid.NewGuid.

This is not PetaPoco "limitation". SQL Server keeps the last generated identity value in memory which can be retrieved right after an INSERT using SCOPE_IDENTITY(), @@IDENTITY, or CHECK_IDENT (depending on the scope you require). There is nothing similar to capture the last generated GUID value. If you use a GUID, you'll have to create your own mechanism to capture the last inserted value (i.e. retrieve the GUID prior to insertion.