CODEWITHSUNDEEP
c#dapper
user5405648
user5405648

Reputation:

How can I map a Dapper QueryFirst to a class?

I am trying to map a QueryFirst call to this entity,

public class QueueItem
{
    public long Id { get; }
    
    public string Item { get; }
    
    [Column("type_id")]
    public int TypeId { get; }
}

I have also set this up for the Column attribute to work,

Dapper.SqlMapper.SetTypeMap(
    typeof(QueueItem),
    new Dapper.CustomPropertyTypeMap(
        typeof(QueueItem),
        (type, columnName) =>
            type.GetProperties().FirstOrDefault(prop =>
                prop.GetCustomAttributes(false)
                    .OfType<ColumnAttribute>()
                    .Any(attr => attr.Name == columnName) || prop.Name == columnName)));

Dapper throws an internal exception,

Unhandled exception. System.ArgumentNullException: Value cannot be null. (Parameter 'meth')
   at System.Reflection.Emit.DynamicILGenerator.Emit(OpCode opcode, MethodInfo meth)
   at Dapper.SqlMapper.GenerateDeserializerFromMap(Type type, IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing, ILGenerator il) in /_/Dapper/SqlMapper.cs:line 3289
   at Dapper.SqlMapper.GetTypeDeserializerImpl(Type type, IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in /_/Dapper/SqlMapper.cs:line 3075
   at Dapper.SqlMapper.TypeDeserializerCache.GetReader(IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in /_/Dapper/SqlMapper.TypeDeserializerCache.cs:line 153
   at Dapper.SqlMapper.TypeDeserializerCache.GetReader(Type type, IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in /_/Dapper/SqlMapper.TypeDeserializerCache.cs:line 50
   at Dapper.SqlMapper.GetTypeDeserializer(Type type, IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in /_/Dapper/SqlMapper.cs:line 3026
   at Dapper.SqlMapper.GetDeserializer(Type type, IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in /_/Dapper/SqlMapper.cs:line 1789
   at Dapper.SqlMapper.QueryRowImpl[T](IDbConnection cnn, Row row, CommandDefinition& command, Type effectiveType) in /_/Dapper/SqlMapper.cs:line 1192
   at Dapper.SqlMapper.QueryFirst[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) in /_/Dapper/SqlMapper.cs:line 741

I have tried adding Column attribute for all properties, the outcome is the same.

The fluent mapping approach results in the same exception.

Dapper initialization (with fluent mapping):

FluentMapper.Initialize(config =>
{
    config.AddMap(new QueueItemMap());
});

Call to QueryFirst:

public bool TryGetItem(out QueueItem item)
{
    const string sql = @"
            SELECT *
            FROM `queue_items` 
            WHERE `processed_at` IS NULL AND `completed_at` IS NULL ORDER BY `id` ASC
            LIMIT 1;";
    
    using var dbConnection = _dbConnection;
    item = dbConnection.QueryFirst<QueueItem>(sql);
    return item != null;
}

Upvotes: 0

Views: 2682

Answers (3)

Caius Jard
Caius Jard

Reputation: 74605

I find it a lot easier to just alias the sql columns to match the c# names:

SELECT 
  Id,
  Item,
  type_id as TypeId
...

Upvotes: 0

MestreDosMagros
MestreDosMagros

Reputation: 1030

I do not recomend using * on select statements, specially in this type of situation when you want map the result to a typed class.

Dapper is pretty good at mapping queries to classes without necessity of mapping packages, try do this:

Clear all your mapping classes and calls,

Leave the class like this:

public class QueueItem
{
    public long Id { get; }
    
    public string Item { get; }
    
    public int TypeId { get; }
}

And change the select statement to this:

    SELECT `ID` AS ID, 
           `ITEM` AS ITEM,
           `TYPE_ID` AS TYPEID
        FROM `QUEUE_ITEMS` 
            WHERE `PROCESSED_AT` IS NULL AND `COMPLETED_AT` IS NULL ORDER BY `ID` ASC
            LIMIT 1;

*Dapper is case insensitive to mapping, so dont worry about casing

If this dont work, you can try leave the changes as above and instead of doing this

var item = dbConnection.QueryFirst<QueueItem>(sql);

Try doing this and see if the error still occours:

var item = dbConnection.QueryFirst<dynamic>(sql);

As well, by using QueryFirst you have to assume that it aways will find something in the database, otherwise it will throw an exceotion, in your case youre checking for null values, so use QueryFirstOrDefault.

Upvotes: 0

tsvedas
tsvedas

Reputation: 1069

There's two ways to solve this.

Adding map for one specific class

Try using Dapper.FluentMap extension (NuGet) to make this a bit easier.

See medium.com post about it. Summing up information from it, first create a map:

internal class QueueItemMap : EntityMap<QueueItem>
{
    internal QueueItemMap()
    {
        Map(qi => qi.TypeId).ToColumn("type_id");
    }
}

Then add it when configuring services:

FluentMapper.Initialize(config =>
    {
        config.Add(new QueueItemMap());
    });

This should auto-map other properties as usual, but "type_id" column will be used for TypeId member.

Mapping PascalCase to snake_case for every member

If you want to generally use this convention that multiword_column_name would be mapped to MultiwordColumName property, use

Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;

when initializing dapper.

Upvotes: 1

Related Questions