sapiens / SqlFu

Fast and versatile .net core data mapper/micro-orm
Other
229 stars 50 forks source link

Insert with identity column for PK #28

Closed aleq closed 10 years ago

aleq commented 10 years ago

MS SQL Server 2012, SqlFu 2.2.0

I have a simple table with identity as a PK. SqlFu throws an exception when trying to insert a record:

        var data = new Data
        {
            CreatedAt = DateTime.UtcNow,
        };

        using (var db = SqlFuDao.GetConnection())
        {
            try
            {
                db.Insert(data);
            }
            catch(Exception ex)
            {
                string message = ex.Message;
            }
        }

Generated SQL:

exec sp_executesql N'Insert into Data values(@0,@1); Select SCOPE_IDENTITY() as id', N'@0 bigint,@1 datetime,',@0=0, @1='2013-12-13 10:10:19.917'

Error: Cannot insert explicit value for identity column in table 'Data' when IDENTITY_INSERT is set to OFF.

sapiens commented 10 years ago

I have a feeling the Data class isn't decorated with the Table attribute. If the POCO doesn't have [Table] then the Id property is NOT considered autogenerated. You need to decorate the POCO with [Table]

aleq commented 10 years ago

It works fine when I add the [Table] attribute. Once I add an external (=dependency) attribute to a POCO class, it's not a POCO anymore. Isn't there a way to work it out without adding this attribute - the current solution is not acceptable for me.

What about adding idIsIdentity parameter to the first method, similar to the second one?

    public static LastInsertId Insert<T>(this DbConnection db, T data) where T : class;
    public static LastInsertId Insert(this DbConnection db, string table, object data, bool idIsIdentity = true);

Thanks for your quick reply.

sapiens commented 10 years ago

You have 2 ways:

  1. use an anonymous object
  2. Use a buddy class
aleq commented 10 years ago
  1. Is not an option for me.
  2. Workable, though not the perfect solution.

Would prefer to control the auto-generated behaviour only in the DAO classes...