Dapper: Insert/Update don´t know what Id uses

[antoniovendramin]

Hi, I´m using the Dapper.Contrib to Insert and Update my data on the database, but
the Dapper.Contrib does not know how to use the Id, because on the insert it requeres an "id" and
on the Update, it requires "Id".
Insert = Message: 42703: column "id" does not exist
Update = Message: 42703: column "Id" does not exist

I´m using the Dapper.Contrib (1.50.0-beta8)

Thanks

[xmedeko]

Do you have a property annotated by the [Key] attribute?

[antoniovendramin]

No, I clone the Dapper.Contrib and ToLower the properties name.
For me fixed. I don´t know if this is a good idea to suggest.

[NickCraver]

Closing to cleanup, this is under review on many other issues in v2.

[timematcher]

Is this issue solved in the latest Dapper.Contrib on nuget? I am using 1.50.2 and facing the exact same problem.

I am using Postgresql as backend database and its column are case sensitive (when quotes are used). I am making a call to
InsertAsync<Session>(sessionObj);

Dapper.Contrib tries to look for Session_Guid and fails to find it. Session_guid exists in the database.

The problem is, Dapper.Contrib is enclosing each column names in quotes which is being rejected by the database. The query being generated is this:
INSERT INTO Session ("Session_Guid", "AU_Guid", "AU_Id", "Valid_Till", "Created", "Last_Modified", "Is_Enabled") VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING Auth_Id

When this query is manually run against postgresql database, it errors out.

ERROR: column "Session_Guid" of relation "session" does not exist
SQL state: 42703
Character: 22

Is there a way to prevent Dapper/Dapper.Contrib from quoting the columns?

[mikegottlieb]

This is still a problem as far as I can tell and it is driving me crazy

[Alikhll]

same error for me on PostgreSQL
Npgsql.PostgresException: '42703: column "id" does not exist'
I tried Key annotation and raises same error
but work using ExplicitKey which I expected not to generate Id in database but database generate id automatically!

[mikegottlieb]

I ended up switching to NPoco, which seems to support Postgres a bit better. Plus it contains some of useful things that are only found in Dapper extensions which tend to have even less support for Postgres. I still recommend Dapper to people, but usually only when they are using SQL Server.

[Huhra]

I had the same error, using pgsql it seems all fields in table have to be lowercase, now working

[timematcher]

I was able to sort the issue out (and then some) using PregrineDb for insert() and insertAsnc() operations as it handles it natively.
https://github.com/berkeleybross/PeregrineDb/

It also seems to present a feature matrix between various Dapper extensions including FastCrud, SimpleCrud, and itself.

Until v2 for Dapper comes out and these issues are fixed, PregrineDb could be a handy extension for Dapper that works seamlessly with PostgreSQL and takes care of quoting/casing problems.

Hope it helps someone.

[dogac00]

I am having the same problem, Error is: "column "id" does not exist", anyone have a solution for this?

Edit: Adding ExplicitKey attribute solves this problem, but creates another problem which you have to set "Id" yourself.

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