Dapper.Contrib character case bug

[moabtools]

Hi! I have table in postgres created with field names in quotes (i.e. case sensitive):

create table "buttons" (
"Id" SERIAL PRIMARY KEY,
"UserId"  INTEGER,
"Phone"  VARCHAR,
"NumberType" INTEGER,
...

and c# class also with case sensitive properties:

    public class Button
    {
        public Int32 Id { get; set; }
        public Int32 UserId { get; set; }
        public String Phone { get; set; }
        public NumberType NumberType { get; set; }
        ...

When I try to insert record with InsertAsync, it throws an error

42703: column "id" does not exist

as you can see, column "id" is written here in lower case, because the function InsertAsync skips id-column during query creation.

UpdateAsync works perfectly because it uses "Id" in where clause.

But when I change the name of column in database to "id" - InsertAsync works good, but UpdateAsync fails by the same reason.

Please help.

[rclarke2050]

I found a way around this field name problem for the time being.

in my Repository Constructor i used:

DapperExtensions.DapperExtensions.SqlDialect = new DapperExtensions.Sql.PostgreSqlDialect();

and in the Insert Method: db.Insert(item); and in the Updaet Method: db.Update(item);

i did not use the SqlMapper approach, nor did I use the SqlMapperExtensions.Insert()

Ive tried on .Net Core 2.1 Lambda, and AWS Aurora 10.7.

Dapper v2.0.30 Dapper.Contrib 2.0.30 DapperExtensions.DotnetCore 1.0.1

I hope this helps others for now....in the interim.

[goforgold]

Hi @rclarke2050

Does it mean that we have to include DapperExtenssions library to achieve this?

DapperExtenssions and DapperContrib seem to be doing same thing but different way. I was thinking to choose between both of them.

[rclarke2050]

Hi @rclarke2050

Does it mean that we have to include DapperExtenssions library to achieve this?

DapperExtenssions and DapperContrib seem to be doing same thing but different way. I was thinking to choose between both of them.

try either, and you'll know which one works for your scenarios. In terms of how i managed it, setting the SQLDialect did the trick. Also, take a look at this: https://github.com/tmsmith/Dapper-Extensions/issues/205

[goforgold]

Hi @rclarke2050

Actually, both didn't work for me really. Each had its own limitations. I had to write my own solution to generate Insert and Update queries using Reflection and Humanizer with some if/else. It seems to work just fine and really isn't too much. It is very compact than I thought it could possibly be.

Here is the gist. You could customize it to suit your own specific needs but it works for me throughout my project.

[sigpop]

@rclarke2050

DapperExtensions.DapperExtensions.SqlDialect = new DapperExtensions.Sql.PostgreSqlDialect();

I'm a little confused. Why would Dapper.Contrib have anything to do with Dapper-Extensions? Dapper.Contrib doesn't use the "dialect" approach to determine platform. Rather it tries to check the IDbConnection and then formats some of the strings accordingly.

private static ISqlAdapter GetFormatter(IDbConnection connection)

[dogac00]

Adding ExplicitKey attribute on my Id worked for me. But important point is you have to set "Id" key yourself.

Edit: Adding Computed attribute with changing it from Id to something like ItemId or ProductId solves the problem for sequential primary keys.

[dani-fmena]

@dogac00, are you going to re-submit you PR (this one) in the new Dapper.Contrib repo for traying to solve this issue ?

[jakobadam]

Just to follow up. Dapper.Contrib is useless with postgres. There are several casing bugs:

For instance in InsertAsync, in https://github.com/DapperLib/Dapper.Contrib/blob/main/src/Dapper.Contrib/SqlMapperExtensions.Async.cs#L496

Here sb.Append(property.Name); adds the pk without taking casing into considerations.