search

tmsmith / Dapper-Extensions

Dapper Extensions is a small library that complements Dapper by adding basic CRUD operations (Get, Insert, Update, Delete) for your POCOs. For more advanced querying scenarios, Dapper Extensions provides a predicate system. The goal of this library is to keep your POCOs pure by not requiring any attributes or base class inheritance.
1.79k stars 586 forks source link

Insert the entity with not null ID. #304

Open alexpantyukhin opened 2 years ago

alexpantyukhin commented 2 years ago

Hello! Thank you for the great lib! I have an issue with Insert function. I have an Entity with 2 not null primary keys:

Entity
{
    OtherEntityId (PK, not null)
    OtherEntityField (PK, not null)
    ...
}

So there is the composite key for OtherEntityId and OtherEntityField.

When I try to put it into the DB:

conn.Insert(new Entity{OtherEntityId = 1, OtherEntityField = 'OtherField'})

I got an error: Cannot insert the value NULL into column 'OtherEntityId', table 'DB.dbo.TicketField'; column does not allow nulls. INSERT fails.

I think that it's because of these lines of code: https://github.com/tmsmith/Dapper-Extensions/blob/master/DapperExtensions/Sql/SqlGenerator.cs#L228 . It skips the primary keys values. It would be good if the lib checks if fields of the DB set up not null value and allow to put values into the insert command.

ghost commented 1 year ago

First, I found that the primary key cannot be inserted. The primary key does not support insertion (it must rely on mysql to automatically process the value. If it is not set, an exception will be thrown if the default value is not set).

Second, there seems to be some problems with data types. MySQL defines the column as int, but C # prompts that I cannot convert the exception of type int64 to int32.

I hope I can get your fix, much appreciated.

alexpantyukhin commented 1 year ago

@kelilife For example: the primary key could be a composite key (2 or more fields), which consists from foreign keys on other tables.

ghost commented 1 year ago

@alexpantyukhin I am now using another ORM framework and basically have no problems. It seems that the primary key is affected to the detriment of inserting data into it, even if it is the primary key for only one field. As for the rest I don't know much about it.