0

I am trying to use Dapper and Dapper.Contrib to insert a whole object into a table in the databse, while ignoring (because the table has a default value for that column) the properties that have a value set to null.

This is the class of the object I want to insert:

[Table("table_in_database")]
    public class Example
    {
        public int ex_id { get; set; }
        public string? ex_desc { get; set; }
    }

Assume that the column names in the table (called "table_in_database") match the names of the properties (so there is a column called ex_id and ex_desc). Also assume that ex_desc is nullable and has default value set to "test" in the database.

Now, assume that I create an object of class Example like so:

Example example = new Example()
{
    ex_id = 1
};

And then do the following to insert it into the database:

using (DbConnection conn = GetConnection())
{
    conn.Insert(example); 
   //the Insert method comes from Dapper.Contrib.Extensions
}

My problem is that this code inserts NULL in the ex_desc column instead of "test".

The only other post I found that was similar to mine is this, but I don't want to do what is proposed in the accepted answer. I want the database to handle default values (so option 1 and 2 are out), and I don't want to always ignore the ex_desc property since I want the data in the property to be inserted if it's not set to NULL (so option 3 is out).

How could I go about doing this?

Librapulpfiction
  • 37
  • 4
  • What is the generated SQL? Have you tried executing that SQL from management studio? – Amit Joshi Aug 18 '23 at 07:33
  • I'm not sure how to see the generated SQL, but based on the output it should be something like this: INSERT INTO table_in_database (ex_id, ex_desc) VALUES (1, NULL); – Librapulpfiction Aug 18 '23 at 07:40
  • Please refer to this post to log the generated SQL: https://stackoverflow.com/a/50875558/5779732 – Amit Joshi Aug 18 '23 at 07:47
  • It seems Dapper.Contrib doesn't have support for this. You can apply the `Write(false)` attribute but then you can't update the column either. Maybe make a pull request on GitHub to do that. – Charlieface Aug 18 '23 at 11:46
  • @Charlieface: Not sure how any ORM will implement this; not sure if ANY ORM have implemented this. Dapper.Contrib includes the column name in INSERT query as it is part of Entity being inserted. NULL is allowed for the column. So, ORM simply tries to insert NULL for that column and it goes all right. If column is set to NOT NULL, I guess it will throw an error; it will still not use DEFAULT value. As far I can understand, there is no way for any ORM to handle this. The only way I can imagine is to create a different Entity without the property that you want to use DEFAULT. – Amit Joshi Aug 18 '23 at 13:25
  • @AmitJoshi I can't fathom why it would be impossible to simply add a `HasDefault` attribute, which means it can ignore that column for inserts but not for updates. – Charlieface Aug 18 '23 at 17:29
  • @Charlieface: Yes; that looks possible with your way. "which means it can ignore that column for inserts **if its value is null**". Otherwise, it will be included while insert. If one is ready to add one more configuration, the even easier way is to set the default value of property in constructor of Entity. But OP wants to do this on DB side, so the constructor way may not help OP. – Amit Joshi Aug 19 '23 at 07:35

0 Answers0