CREATE TABLE [dbo].[Example](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[CreatedDate] DATETIME NOT NULL DEFAULT GETDATE(),
CONSTRAINT [PK_Example] PRIMARY KEY CLUSTERED ([ID] ASC)
)
-- notice the CreatedDate is NOT NULL, with a default value.
-- So as long as Simple.Data doesn't include [CreatedDate] in inserts, we should be good, right?
My code:
// notice that I'm not mentioning the CreateDate column anywhere in this code.
// single insert, not assigning result: WORKS
db.Example.Insert(new {Name=Guid.NewGuid().ToString()});
// single insert, assigning result: WORKS
var example0 = db.Example.Insert(new {Name=Guid.NewGuid().ToString()});
// multi-insert, not assigning result: WORKS
db.Example.Insert(new[]{
new {Name=Guid.NewGuid().ToString()},
new {Name=Guid.NewGuid().ToString()}
});
// multi-insert, not assigning result: KABOOM!!! (exception details listed below)
var badExample = db.Example.Insert(new[]{
new {Name=Guid.NewGuid().ToString()},
new {Name=Guid.NewGuid().ToString()}
});
The exception thrown:
Simple.Data.Ado.AdoAdapterException: Cannot insert the value NULL into column 'CreatedDate', table 'SimpleDataExperiments.dbo.Example'; column does not allow nulls. INSERT fails.
The statement has been terminated. ---> System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'CreatedDate', table 'SimpleDataExperiments.dbo.Example'; column does not allow nulls. INSERT fails.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at Simple.Data.Ado.DbCommandExtensions.TryExecuteReader(IDbCommand command)
--- End of inner exception stack trace ---
at Simple.Data.Ado.DbCommandExtensions.TryExecuteReader(IDbCommand command)
at Simple.Data.Ado.BulkInserterHelper.TryExecuteSingletonQuery(IDbCommand command)
at Simple.Data.Ado.BulkInserterHelper.InsertRowAndSelect(IDictionary`2 row, IDbCommand command, Func`3 onError)
at Simple.Data.Ado.BulkInserterHelper.<>c__DisplayClass10.<InsertRowsWithCompoundStatement>b__a(IDictionary`2 row)
at System.Linq.Enumerable.WhereSelectListIterator`2.MoveNext()
at System.Linq.Enumerable.WhereEnumerableIterator`1.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at Simple.Data.Ado.BulkInserterHelper.InsertRowsWithCompoundStatement(String insertSql, String selectSql, Func`3 onError)
at Simple.Data.Ado.BulkInserter.InsertRowsAndReturn(AdoAdapter adapter, String identityFunction, BulkInserterHelper helper, String insertSql, Table table, Func`3 onError)
at Simple.Data.Ado.BulkInserter.Insert(AdoAdapter adapter, String tableName, IEnumerable`1 data, IDbTransaction transaction, Func`3 onError, Boolean resultRequired)
at Simple.Data.SqlServer.SqlBulkInserter.Insert(AdoAdapter adapter, String tableName, IEnumerable`1 data, IDbTransaction transaction, Func`3 onError, Boolean resultRequired)
at Simple.Data.Ado.AdoAdapterInserter.InsertMany(String tableName, IEnumerable`1 data, Func`3 onError, Boolean resultRequired)
at Simple.Data.Ado.AdoAdapter.InsertMany(String tableName, IEnumerable`1 data, Func`3 onError, Boolean resultRequired)
at Simple.Data.DatabaseRunner.InsertMany(String tableName, IEnumerable`1 data, ErrorCallback onError, Boolean resultRequired)
at Simple.Data.Commands.InsertCommand.InsertEntity(Object entity, DataStrategy dataStrategy, String tableName, ErrorCallback onError, Boolean resultRequired)
at Simple.Data.Commands.InsertCommand.DoInsert(InvokeMemberBinder binder, Object[] args, DataStrategy dataStrategy, String tableName)
at Simple.Data.Commands.InsertCommand.Execute(DataStrategy dataStrategy, DynamicTable table, InvokeMemberBinder binder, Object[] args)
at Simple.Data.DynamicTable.TryInvokeMember(InvokeMemberBinder binder, Object[] args, Object& result)
at CallSite.Target(Closure , CallSite , Object , Example[] )
at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
at UserQuery.Main() in c:\Users\MY_USER_NAME\AppData\Local\Temp\LINQPad\_vucemqih\query_jaislk.cs:line 66
Exception details:
Property
Value
CommandText
insert into [dbo].[Example] ([Name],[CreatedDate]) values (@p0,@p1); select * from [dbo].[Example] where [ID] = SCOPE_IDENTITY()
Here's how to reproduce the issue:
My table definition:
My code:
The exception thrown:
Exception details:
insert into [dbo].[Example] ([Name],[CreatedDate]) values (@p0,@p1); select * from [dbo].[Example] where [ID] = SCOPE_IDENTITY()
@p0=877d6984-e90c-47c0-a91b-de4a468c4bc9
,@p1=null