Dapper Query return always null with nullable immutable struct

Question

With Dapper 2.0.123, SQL Server 2019, .NET6 :

Working

I can use an immutable struct like this :

// Immutable struct
public struct UserImmutable
{
    public UserImmutable(string firstName, string lastName)
    {
        this.LastName = lastName;
        this.FirstName = firstName;
    }
    public string LastName { get; }
    public string FirstName { get; }
}

// Dapper code
var result = connection.Query<UserImmutable>("SELECT FirstName, LastName FROM [User];").ToList();

The result is filled correctly with all values.

Not Working

Now if I change the generic type for UserImmutable? when calling Query, I have the same number of items, but they are all NULL, even if the the SQL query returns no NULL

var result = connection.Query<UserImmutable?>("SELECT FirstName, LastName FROM [User];").ToList();
// result[0] is null, result[1] is null, etc.

What I am missing ?

Answer 1

So this is still happening. The way I solved it was to check one of my non-nullable properties on my data object (usually the primary key) for a default value. I slightly modified your example above to demonstrate. Assuming you have a primary key 'UserId' on the User table:

// Immutable struct
public struct UserImmutable
{
    public UserImmutable(Guid userId, string firstName, string lastName)
    {
        this.UserId = userId;
        this.LastName = lastName;
        this.FirstName = firstName;
    }
    public Guid UserId { get; }
    public string LastName { get; }
    public string FirstName { get; }
}

// Dapper code
public UserImmutable? GetUserImmutable(Guid userId)
{
     var result = connection.Query<UserImmutable>("SELECT UserId, FirstName, LastName FROM [User] WHERE UserId = :userId;", new { userId }).FirstOrDefault();
     if (result.UserId == Guid.Empty) return null;
     return result;
 }

Definitely not ideal, but this should solve 99%+ of the times this comes up. Hope it helps.