The InsertAll operation does not respect [Default] attributes on the poco properties, unlike the Insert operation which does. The InsertAll operation generates SQL that inserts the property value for all properties in the poco. Insert, on the other hand, generates SQL that ignores all properties that have a [Default] attribute and lets the database insert the default value. This behavior is the same in current master and in 4.5.0. Is this intentional or a bug? If this is a bug, I’ll submit a pull request with a test that demonstrates it.
InsertAll uses an optimization that only prepares the parameterized Insert query once then replaces the parameter values for each row whereas individual Insert will create parameterized Insert query for each row. Eventually we want to update InsertAll so it leverages the most optimal way in each RDBMS to insert bulk rows and relying on individual parameterized queries for each row will likely inhibit future optimizations like this.
So I’d recommend if you need default values to use a foreach over a single Insert() to force using individual parameterized queries for each Insert.
I have no problem using a loop with single Inserts.
However, none of what you say in your first paragraph justifies InsertAll ignoring default attributes on the model poco. Those default values apply uniformly to all rows inserted, and with a bulk insert optimization would again apply. In both cases the generated SQL should not set values for the columns with default values.
I understand better now. Insert looks at the values in the poco provided and generates SQL to insert, omitting a property with a [Default] attribute in the model only if the poco to insert has the default value for that property, otherwise it includes that column in the SQL. What I’m suggesting is that InsertAll ought to use the [Default] attributes unconditionally for properties that have them. In other words, always omit those columns from the generated SQL.
Then you wouldn’t be able to insert values that were populated on the POCO, which wouldn’t be expected behavior. I’ve added a new db.InsertUsingDefaults() API which should have this behavior in this commit. Feel free to update its impl to suit the behavior you prefer.
Thanks! InsertUsingDefaults does exactly what I need. I still think that the InsertAll API method should behave like that as well, but keeping it unchanged maintains backward compatibility which is probably more important.