Dapper.Contrib
Dapper.Contrib copied to clipboard
DapperLib
Dapper.Contrib Update ignores mapping
Hi,
some fields in my table have underscores like full_name so i did
DefaultTypeMap.MatchNamesWithUnderscores = true;
which works just fine when i retrieve entity with Get<> but throws "Invalid column name 'FullName'."
when i update
my model
public class SmUser{
public int Id { get; set; }
public string Name { get; set; }
public string FullName { get; set; }
public string Email { get; set; }
}
Whet i run Get it works just fine, the property FullName is populated with data
var user = cn.Get<SmUser>(id);
but when i update the entity
user.Email = user.Email + "z";
cn.Update(user);
i get
An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code
"Invalid column name 'FullName'."
and the sql query
update smuser set [Name] = @Name, [FullName] = @FullName, [Email] = @Email where [Id] = @Id
"FullName" is the entity property, "full_name" is the db field using dapper 1.42
This feature is not supported . In Dapper.Contrib.SqlMapperExtensions.cs are fetched only the properties from the entity type and constructs directly the update statement.
DefaultTypeMap.MatchNamesWithUnderscores or any implementation of SqlMapper.ITypeMap is not taken in account.
I ll give it a try myself Thanks
I've created a pull request that implements the mapping for Insert and Update the last week, but I don't know if the maintainer have seen it yet.
Wow, I was really counting on this working. The db schema I'm using still has some old tables in it with underscored columns and the classes all utilize CamelCase property names. I went to test some updates and that didn't go so well.
My take on this to update a Class with lots of properties:
var query = $@"UPDATE {tableName} SET ";
foreach (var prop in objDb.GetType().GetProperties())
{
if (prop.Name == "Id" || prop.Name == "CreatedAt" || prop.Name == "Application" || prop.Name == "Platform")
continue; // The properties that I don't want to update
query = query + $@"{prop.Name.ToSnakeCase()} = @{prop.Name}, ";
}
query = query.Remove(query.Length - 2, 2); // Removing last comma and space
query = query + $@" WHERE id = @Id";
await _connection.ExecuteAsync(query, objDb);
This logic can be made into a reusable function with parameters as necessary.
Example: UpdateInDB(connection, objDb, tableName)
To snake_case from PascalCase converter as:
public static string ToSnakeCase(this string str)
{
Regex pattern = new Regex(@"[A-Z]{2,}(?=[A-Z][a-z]+[0-9]*|\b)|[A-Z]?[a-z]+[0-9]*|[A-Z]|[0-9]+");
return string.Join("_", pattern.Matches(str)).ToLower();
}
Wow, I was really counting on this working. The db schema I'm using still has some old tables in it with underscored columns and the classes all utilize CamelCase property names. I went to test some updates and that didn't go so well.
This is almost obligatory to have underscores in Postgres - given the way it handles case and quotes. Hence I don't quite understand how anyone using Postgres can use Dapper.Contrib... I can't I guess we can map every field to every underscored column and then use it... but surely the MatchNamesWithUnderscores value should be respected?
For anyone looking for an alternative, I went with FastCrud after years of using Dapper.Contrib. Luckily it was a "drop in" replacement that didn't require any code changes (for me at least) and supported the [Column] attribute that I had in place for my schema migrations. Hopefully Dapper.Contrib can add support for this in the future.
