Introduction
Being impressed with the new ORM behaviour exhibited by Microsoft's new LINQ technology, I decided to do a development spike before using it in my next project. Although LINQ is definitely the way to go, there would seem to be a few shortfalls that require a bit of working around. Here I write about a fairly nasty behavior where LINQ objects' properties are set with null values causing the database to reject updates and inserts on columns that won't allow null data.
Using the Code
I ran into a problem using the ASP.NET DetailsView control and an underlying LINQ DataSource. The issue: When I load an object using LINQ rather than using the default values for the properties derived from the database, the default value for each property is null. Being a good DBA, I never (without good reason) have nulls lying around my databases, so my tables always have default values assigned to each column. When the LINQ objects however are sent back to the database, the operation fails since the DB is rejecting the LINQ layer trying to insert that null data.
I think this is a bit of a flaw in the design. By default, LINQ objects should be initialized with properties mapping back to the default values used in the underlying data store.
Anyway, better to demonstrate with a simple example of this problem and then show a workaround …
I have a simple data table...
CREATE TABLE [dbo].[Table1](
[Col1] [int] IDENTITY(1,1) NOT NULL,
[Col2] [nvarchar](50) NOT NULL CONSTRAINT [DF_Table1_Col2] DEFAULT (''),
[Col3] [int] NOT NULL CONSTRAINT [DF_Table1_col3] DEFAULT ((0)),
[Col4] [datetime] NOT NULL CONSTRAINT [DF_Table1_col4] DEFAULT (getdate()),
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = _
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
... and a LINQ class sitting on the table:
Next, I add a LinqDataSource to the project's default page (default.aspx) and set both a filtering parameter based on the underlying data's primary key and let the datasource know that I also want to be able to edit the data.
<asp:LinqDataSource ID="LinqDataSource1" runat="server"
ContextTypeName="DataClassesDataContext" EnableUpdate="True"
TableName="Table1s" Where="Col1 == @Col1">
<WhereParameters>
<asp:QueryStringParameter Name="Col1" QueryStringField="Col1" Type="Int32" />
</WhereParameters>
</asp:LinqDataSource>
As a UI for the data, I drag a DetailsView control onto the page and bind it to the LinqDataSource.
<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False"
DataKeyNames="Col1" DataSourceID="LinqDataSource1" Height="50px" Width="125px">
<Fields>
<asp:BoundField DataField="Col1" HeaderText="Col1" InsertVisible="False"
ReadOnly="True" SortExpression="Col1" />
<asp:BoundField DataField="Col2" HeaderText="Col2" SortExpression="Col2" />
<asp:BoundField DataField="Col3" HeaderText="Col3" SortExpression="Col3" />
<asp:BoundField DataField="Col4" HeaderText="Col4" SortExpression="Col4" />
<asp:CommandField ShowEditButton="True" />
</Fields>
</asp:DetailsView>
I manually add a single record to my data table so as to have some data to edit:
Note that Col2 is empty, but by definition it should be an empty string.
[Col2] [nvarchar](50) NOT NULL CONSTRAINT [DF_Table1_Col2] DEFAULT (''),
Let's try and edit the data for this single row (making sure to supply the query string parameter Col1=1)
So, I leave Col2 empty and hit the update link.
Cannot insert the value NULL into column 'Col2', table 'TestDB.dbo.Table1';
column does not allow nulls. UPDATE fails.
The statement has been terminated.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information about
the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException:
Cannot insert the value NULL into column 'Col2', table 'TestDB.dbo.Table1';
column does not allow nulls. UPDATE fails.
The statement has been terminated.
Source Error:
An unhandled exception was generated during the execution of the current web request.
Information regarding the origin and location of the exception can be identified
using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): Cannot insert the value NULL into column 'Col2',
table 'TestDB.dbo.Table1'; column does not allow nulls. UPDATE fails.
The statement has been terminated.]
System.Data.SqlClient.SqlConnection.OnError_
(SqlException exception, Boolean breakConnection) +925466
System.Data.SqlClient.SqlInternalConnection.OnError_
(SqlException exception, Boolean breakConnection) +800118
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning_
(TdsParserStateObject stateObj) +186
System.Data.SqlClient.TdsParser.Run_
(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, _
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932
System.Data.SqlClient.SqlCommand.FinishExecuteReader_
(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds_
(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, _
Boolean async) +1005
System.Data.SqlClient.SqlCommand.RunExecuteReader_
(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, _
String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery_
(DbAsyncResult result, String methodName, Boolean sendToPipe) +149
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
System.Data.Linq.SqlClient.SqlProvider.Execute_
(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, _
Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, _
Object lastResult) +229
System.Data.Linq.SqlClient.SqlProvider.ExecuteAll_
(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, _
Object[] userArguments, ICompiledSubQuery[] subQueries) +129
System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute_
(Expression query) +878
System.Data.Linq.StandardChangeDirector.DynamicUpdate(TrackedObject item) +127
System.Data.Linq.StandardChangeDirector.Update(TrackedObject item) +168
System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode) +433
System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode) +1155
System.Data.Linq.DataContext.SubmitChanges() +82
System.Web.UI.WebControls.LinqToSqlWrapper.SubmitChanges(DataContext dataContext) +9
System.Web.UI.WebControls.LinqDataSourceView.UpdateDataObject_
(Object dataContext, Object table, Object oldDataObject, Object newDataObject) +106
System.Web.UI.WebControls.LinqDataSourceView.ExecuteUpdate_
(IDictionary keys, IDictionary values, IDictionary oldValues) +326
System.Web.UI.DataSourceView.Update_
(IDictionary keys, IDictionary values, IDictionary oldValues, _
DataSourceViewOperationCallback callback) +78
System.Web.UI.WebControls.DetailsView.HandleUpdate_
(String commandArg, Boolean causesValidation) +1152
System.Web.UI.WebControls.DetailsView.HandleEvent_
(EventArgs e, Boolean causesValidation, String validationGroup) +440
System.Web.UI.WebControls.DetailsView.OnBubbleEvent(Object source, EventArgs e) +95
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.DetailsViewRow.OnBubbleEvent_
(Object source, EventArgs e) +109
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +132
System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler._
RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, _
String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +177
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, _
Boolean includeStagesAfterAsyncPoint) +1746
________________________________________
Version Information: Microsoft .NET Framework Version:2.0.50727.1433;
ASP.NET Version:2.0.50727.1433
Annoying or what?
Firstly, this is not a new insert, so when the LINQ object loaded the data for the row, Col2 should have been set to the value obtained from the database in col2, which was an empty string. Second, if it was a new insert, then I want to use the defaults set in the table definition for each column, not null.
So, let's try a new insert. I modify both the DataSource and the DetailsView to support inserts as well as updates:
<asp:LinqDataSource ID="LinqDataSource1" runat="server"
ContextTypeName="DataClassesDataContext" EnableInsert="True"
EnableUpdate="True" TableName="Table1s" Where="Col1 == @Col1">
<WhereParameters>
<asp:QueryStringParameter Name="Col1" QueryStringField="Col1"
Type="Int32" />
</WhereParameters>
</asp:LinqDataSource>
<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False"
DataKeyNames="Col1" DataSourceID="LinqDataSource1"
Height="50px" Width="125px">
<Fields>
<asp:BoundField DataField="Col1" HeaderText="Col1" InsertVisible="False"
ReadOnly="True" SortExpression="Col1" />
<asp:BoundField DataField="Col2" HeaderText="Col2"
SortExpression="Col2" />
<asp:BoundField DataField="Col3" HeaderText="Col3"
SortExpression="Col3" />
<asp:BoundField DataField="Col4" HeaderText="Col4"
SortExpression="Col4" />
<asp:CommandField ShowEditButton="True" ShowInsertButton="True" />
</Fields>
</asp:DetailsView>
Now I try a fresh insert leaving each of the columns on the DetailsView blank:
And click the insert link:
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more information
about the error and where it originated in the code.
Exception Details: System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow.
Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
Source Error:
An unhandled exception was generated during the execution of the current web request.
Information regarding the origin and location of the exception can be identified
using the exception stack trace below.
Stack Trace:
[SqlTypeException: SqlDateTime overflow.
Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.]
System.Data.SqlTypes.SqlDateTime.FromTimeSpan(TimeSpan value) +653497
System.Data.SqlTypes.SqlDateTime.FromDateTime(DateTime value) +150
System.Data.SqlTypes.SqlDateTime..ctor(DateTime value) +13
System.Data.SqlClient.MetaType.FromDateTime(DateTime dateTime, Byte cb) +56
System.Data.SqlClient.TdsParser.WriteValue_
(Object value, MetaType type, Byte scale, Int32 actualLength, _
Int32 encodingByteSize, Int32 offset, _
TdsParserStateObject stateObj) +519
System.Data.SqlClient.TdsParser.TdsExecuteRPC_
(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, _
SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, _
Boolean isCommandProc) +4163
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds_
(CommandBehavior cmdBehavior, RunBehavior runBehavior, _
Boolean returnStream, Boolean async) +1005
System.Data.SqlClient.SqlCommand.RunExecuteReader_
(CommandBehavior cmdBehavior, RunBehavior runBehavior, _
Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.RunExecuteReader_
(CommandBehavior cmdBehavior, RunBehavior runBehavior, _
Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader_
(CommandBehavior behavior, String method) +122
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
System.Data.Common.DbCommand.ExecuteReader() +9
System.Data.Linq.SqlClient.SqlProvider.Execute_
(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, _
Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, _
Object lastResult) +903
System.Data.Linq.SqlClient.SqlProvider.ExecuteAll_
(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, _
Object[] userArguments, ICompiledSubQuery[] subQueries) +129
System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute_
(Expression query) +878
System.Data.Linq.StandardChangeDirector.DynamicInsert(TrackedObject item) +261
System.Data.Linq.StandardChangeDirector.Insert(TrackedObject item) +168
System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode) +250
System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode) +1155
System.Data.Linq.DataContext.SubmitChanges() +82
System.Web.UI.WebControls.LinqToSqlWrapper.SubmitChanges(DataContext dataContext) +9
System.Web.UI.WebControls.LinqDataSourceView.InsertDataObject_
(Object dataContext, Object table, Object newDataObject) +82
System.Web.UI.WebControls.LinqDataSourceView.ExecuteInsert(IDictionary values) +267
System.Web.UI.DataSourceView.Insert_
(IDictionary values, DataSourceViewOperationCallback callback) +72
System.Web.UI.WebControls.DetailsView.HandleInsert_
(String commandArg, Boolean causesValidation) +390
System.Web.UI.WebControls.DetailsView.HandleEvent_
(EventArgs e, Boolean causesValidation, String validationGroup) +602
System.Web.UI.WebControls.DetailsView.OnBubbleEvent(Object source, EventArgs e) +95
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.DetailsViewRow.OnBubbleEvent_
(Object source, EventArgs e) +109
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +132
System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler._
RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent_
(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +177
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, _
Boolean includeStagesAfterAsyncPoint) +1746
________________________________________
Version Information: Microsoft .NET Framework Version:2.0.50727.1433;
ASP.NET Version:2.0.50727.1433
Hmm, I thought I was setting that default value to GetDate() in the table definition.
So I'm thinking there must be some way I can tell my LINQ layer to let the DB handle the defaults and stop messing things up.
If I look in the LINQ designed code behind file, I can see LINQ attributes set above each property indexer relating to the underlying DB, for Col2 in this case:
[Column(Storage="_Col2", DbType="NVarChar(50) NOT NULL", CanBeNull=false)]
public string Col2
{
get
{
return this._Col2;
}
set
{
if ((this._Col2 != value))
{
this.OnCol2Changing(value);
this.SendPropertyChanging();
this._Col2 = value;
this.SendPropertyChanged("Col2");
this.OnCol2Changed();
}
}
}
So it knows that it can't be null, yet its default value for the property is null. Sorry, but that's not very clever. I had a look at other flags I could set that would maybe force LINQ to use the DB defaults. The intellisense gives me the following :
IsDbGenerated looks like a pretty good guess, so I try that:
[Column(Storage="_Col2", DbType="NVarChar(50) NOT NULL", _
CanBeNull=false, IsDbGenerated=true)]
I try editing my data again, but on update I get the following error:
Value of member 'Col2' of an object of type 'Table1' changed.
A member that is computed or generated by the database cannot be changed.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.
Exception Details: System.InvalidOperationException:
Value of member 'Col2' of an object of type 'Table1' changed.
A member that is computed or generated by the database cannot be changed.
Source Error:
An unhandled exception was generated during the execution of the current web request.
Information regarding the origin and location of the exception can be identified
using the exception stack trace below.
Stack Trace:
[InvalidOperationException: Value of member 'Col2' of an object of type 'Table1' changed.
A member that is computed or generated by the database cannot be changed.]
System.Data.Linq.ChangeProcessor.CheckForInvalidChanges(TrackedObject tracked) +291
System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode) +415
System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode) +1155
System.Data.Linq.DataContext.SubmitChanges() +82
System.Web.UI.WebControls.LinqToSqlWrapper.SubmitChanges(DataContext dataContext) +9
System.Web.UI.WebControls.LinqDataSourceView.UpdateDataObject_
(Object dataContext, Object table, Object oldDataObject, _
Object newDataObject) +106
System.Web.UI.WebControls.LinqDataSourceView.ExecuteUpdate_
(IDictionary keys, IDictionary values, IDictionary oldValues) +326
System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, _
IDictionary oldValues, DataSourceViewOperationCallback callback) +78
System.Web.UI.WebControls.DetailsView.HandleUpdate_
(String commandArg, Boolean causesValidation) +1152
System.Web.UI.WebControls.DetailsView.HandleEvent_
(EventArgs e, Boolean causesValidation, String validationGroup) +440
System.Web.UI.WebControls.DetailsView.OnBubbleEvent(Object source, EventArgs e) +95
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.DetailsViewRow.OnBubbleEvent_
(Object source, EventArgs e) +109
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +132
System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler._
RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent_
(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +177
System.Web.UI.Page.ProcessRequestMain_
(Boolean includeStagesBeforeAsyncPoint, _
Boolean includeStagesAfterAsyncPoint) +1746
________________________________________
Version Information: Microsoft .NET Framework Version:2.0.50727.1433;
ASP.NET Version:2.0.50727.1433
Now, I am really beginning to get frustrated. I played with this for hours, and trawled the Web to try and find a solution that doesn't involve me writing code. No luck so far.
So here's my workaround:
What I definitely didn't want to do is write any code that is explicitly bound to any table definition because if the table changes it will definitely become a maintenance issue. I also didn't want to have to write partial methods to check on each of the LINQ properties for nulls as they are being set because that means more maintenance code. So, the best thing I could think of is to hook into the update event from the DetailsView control and make sure the LINQ object is properly initialized before trying to permeate it back to the database. I modified the LinqDataSource control to call a check method when the OnUpdating and OnInserting methods are fired, as below:
<asp:LinqDataSource ID="LinqDataSource1" runat="server"
ContextTypeName="DataClassesDataContext" EnableInsert="True"
EnableUpdate="True" TableName="Table1s" Where="Col1 == _
@Col1" OnUpdating="SetDefaultUpdateValues" _
OnInserting="SetDefaultInsertValues">
<WhereParameters>
<asp:QueryStringParameter Name="Col1" QueryStringField="Col1" _
Type="Int32" />
</WhereParameters>
</asp:LinqDataSource>
The code behind is as follows:
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Reflection;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
private void SetDefaults(object LinqObj)
{
PropertyInfo[] props = LinqObj.GetType().GetProperties();
foreach (PropertyInfo prop in props)
{
try
{
object[] customAttributes = prop.GetCustomAttributes
(typeof(System.Data.Linq.Mapping.ColumnAttribute), false);
if (((System.Data.Linq.Mapping.ColumnAttribute)
(customAttributes[0])).DbType.ToLower().IndexOf("not null") != -1)
{
if (prop.GetValue(LinqObj, null) == null || prop.GetValue(LinqObj,
null).ToString() == (new DateTime(1, 1, 1, 0, 0, 0)).ToString())
{
switch (prop.PropertyType.ToString())
{
case "System.String":
prop.SetValue(LinqObj, String.Empty, null);
break;
case "System.Int32":
case "System.Int64":
case "System.Int16":
prop.SetValue(LinqObj, 0, null);
break;
case "System.DateTime":
prop.SetValue(LinqObj, DateTime.Now, null);
break;
}
}
}
}
catch
{
}
}
}
protected void SetDefaultInsertValues(object sender, LinqDataSourceInsertEventArgs e)
{
SetDefaults(e.NewObject);
}
protected void SetDefaultUpdateValues(object sender, LinqDataSourceUpdateEventArgs e)
{
SetDefaults(e.NewObject);
}
}
Points of Interest
The shortfall of the above method is that the default values set in the code aren't derived from the database. This could be corrected, but it would be expensive. It's a shame the LINQ designer doesn't add the default column value as metadata during the creation of the classes. I assume Microsoft will find a fix for this soon.
