.NET Dapper - Mapping computed properties

Question

I'm using Dapper for handling database connection in a .NET project. The automatic column mapping works well and I can also map columns to different property names in the model. However, how can I map computed properties? For example, my model is

class User
{
    public int Id {get; set;}
    public string Name {get; set;}
    public bool IsPremiumUser {get; set;}
}

And the table is

Id | Name | CreationDate | IsPremiumUser

Now, IsPremiumUser can be null in db but not in the model. I'd like it to be mapped by the following logic:

if (row.IsPremiumUser != null)
{
    model.IsPremiumUser = row.IsPremiumUser;
}
else
{
    model.IsPremiumUser = row.CreationDate < 1.1.2000;
}

In other words, its value depends on 2 columns. Also there are multiple cases where I'd like to set a boolean property based on if a certain relationship exists. How to handle these more complex mapping cases?

How this mapping should work when you will try to save an item back to database? if IsPremiumUser was null (when you read that), should it be updated and has a value? — MaKCbIMKo
– MaKCbIMKo, Commented Mar 15, 2017 at 11:51
Maybe you just need to and additional property that will be calculated but not mapped to db table? — MaKCbIMKo
– MaKCbIMKo, Commented Mar 15, 2017 at 11:52
Yes, that's exactly what I mean. Those computed values will not be saved back to db. — eko
– eko, Commented Mar 15, 2017 at 12:16

Sergey Berezovskiy · Accepted Answer · 2017-03-15 12:17:41Z

1

Just use SQL query which will check if IsPremiumUser field not null and return result of creation date check if user does not have this flag set:

var sql =
    @"SELECT 
        Id,
        Name,
        CASE
            WHEN IsPremiumUser IS NOT NULL
                THEN IsPremiumUser
                ELSE CAST(CASE WHEN CreationDate < '2000-01-01' THEN 1 ELSE 0 END AS BIT)
        END AS IsPremiumUser
        FROM Users";
var users = conn.Query<User>(sql);

Other option will be using dynamic query with manual mapping results to user class:

var sql = @"SELECT Id, Name, CreationDate, IsPremiumUser FROM Users";
var millenium = new DateTime(2000, 1, 1);

var users = conn.Query(sql).Select(row => new User {
       Id = row.Id,
       Name = row.Name,
       IsPremiumUser = row.IsPremiumUser == null
           ? row.CreationDate < millenium
           : row.IsPremiumUser
    });

edited Mar 15, 2017 at 12:17

answered Mar 15, 2017 at 12:04

Sergey Berezovskiy

237k44 gold badges441 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

eko Over a year ago

Thanks, I ended up using this approach, although it's not exactly what I had in mind. What if I have some more complex mapping logic that is not convenient to do on the db side?

Sergey Berezovskiy Over a year ago

@eko please refresh page :) Keep in mind that Dapper will return IsPremiumUser flag either as null or as boolean. It will not return nullable boolean

Void Ray · Accepted Answer · 2017-03-15 15:04:18Z

0

Another option is to make a factory and let it assemble the user:

var userRecords = _connection.Query<UserRecord>("select * from users");   
var users = UserFactory.Build(userRecords);

UserRecord class is only used to query the data
Factory will handle property mapping and dynamic properties

Pros: Keep SQL queries free of logic. Factory is more flexible over time.

Cons: More code

answered Mar 15, 2017 at 15:04

Void Ray

10.3k4 gold badges35 silver badges55 bronze badges

Collectives™ on Stack Overflow

.NET Dapper - Mapping computed properties

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related