Can't cast database type character to Guid

Question

I updated my solution from .Net 3.1 to .Net 6. Also updated the Npgsql nuget package from 5.0.10 to 6.0.4 as part of this upgrade.

Since then, I am receiving an error "Can't cast database type character to Guid" when I try to retreive data from the database.

My mapping in the context file is

entity.Property(e => e.UserId).HasColumnName("user_id").HasColumnType("CHAR(36)");

In C# class, this property is a GUID.

Is there some mapping update with the newer version of npgsql?

Answer 1

EF Core has a built-in value converter which implicitly converts .NET Guid properties to text columns (see docs. Note that PostgreSQL has a full UUID type - that's a better way to store GUIDs in the database, rather than as text.

This works in EF Core 6.0.4 - if you're encountering trouble, please produce a minimal, runnable code sample and add it to your question above.

Working 6.0 code:

await using var ctx = new BlogContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

ctx.Blogs.Add(new Blog { Guid = Guid.NewGuid()});
await ctx.SaveChangesAsync();

_ = await ctx.Blogs.ToListAsync();

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql(@"Host=localhost;Username=test;Password=test")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }
    [Column(TypeName = "CHAR(36)")]
    public Guid Guid { get; set; }
}