none
Handling an identity field and a computed default field during row insert RRS feed

  • Question

  • I bound a DetailsView control to a LinqDataSource (which, in turn, points to a SQL Server 2008 database).  Details of the associated table are as follows:

    TABLE NAME: AddressType (this table is utilized by other tables in the database as a lookup table)

    TABLE FIELDS:
    AddressTypeID, tinyint, IDENTITY(1,1), NOT NULL, PRIMARY KEY
    AddressTypeName, nvarchar(20), NOT NULL
    ModifiedDate, datetime, NOT NULL, DEFAULT (getdate())

    Although I have activated the Enable Insert and Enable Update operations, I have set the ReadOnly property of AddressTypeID and ModifiedDate to True, and the InsertVisible property to False for these same two fields.  Since AddressTypeID is an IDENTITY field and ModifiedDate is set to default to the current date & time during Insert, the only field in the DetailsView control that can (and should) accept a value is AddressTypeName.  However, during an Insert (i.e. New) operation, when I click Update, I receive the following error message: "The primary key column of type 'TinyInt' cannot be generated by the server."

    Being a novice programmer, I am still not sure how to tell the DetailsView (and/or LinqDataSource) not to attempt to insert Nulls in the AddressTypeID and ModifiedDate fields during the Insert (i.e. New) operation.  Instead, the DetailsView (and/or LinqDataSource) only needs to pass the value of the AddressTypeName field back to SQL Server.

    I think I need to handle the ItemInserting (and/or Inserting) event in my code-behind file, but I don't know how to code it for the situation described above.

    I would greatly appreciate any assistance on how to modify my .cs file (and if necessary, .aspx file) to handle this (http://cid-7e032375c285f136.office.live.com/self.aspx/Public%20Folder/Files.zip).  I am using Windows 7 with VS2010.

    Thanks.

    • Edited by emerald77 Friday, August 13, 2010 3:49 PM clarified the title
    Wednesday, August 11, 2010 7:28 PM

Answers

All replies

  • Nulls in AddressTypeId the field is not your problem - this is normal behaviour when it is an IDENTITY field. Understandably, LINQ cannot know the value in advance so it is right to leave that as null. After the record is inserted into the database, the record's AddressTypeId value is stored in your object.

    A quick search around the 'net suggests that the LINQ does not support tinyint, but does support smallint. That is probably what you should do to rectify the problem. It won't really cause you storage size issues if you are planning to have less than 255 AddressType records anyway.

    If you can't change the database, you might be able to fool LINQ into thinking it is a smallint by changing the field's data type in the source or the dbml file. I haven't tried this and nor would I recommend it, but it might get you past a hurdle.

    Thursday, August 12, 2010 4:32 PM
  • Thanks for your suggestion.  To play it safe, I changed tinyint to int and that caused the error message, "The primary key column of type 'TinyInt' cannot be generated by the server." to no longer appear.  Now when I perform an Insert operation, I receive the error message, "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM."  This error message is obviously related to my ModifiedDate field and the fact that it is set to default to the current date & time during Insert and does not permit nulls.

    After doing some more research on MSDN, I found "Attribute-Based Mapping (LINQ to SQL)" (http://msdn.microsoft.com/en-us/library/bb386971.aspx?appId=Dev10IDEF1&l=EN-US&k=k(NET.ASP.LINQDATASOURCE.ADVANCEDDIALOG);k(TargetFrameworkMoniker-".NETFRAMEWORK&k=VERSION=V4.0");k(DevLang-ASPX)&rd=true).  I discovered that if I manually edit the .designer.cs file and add the "AutoSync=AutoSync.OnInsert" and "IsDbGenerated=true" attributes to the line:
    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_ModifiedDate", DbType="DateTime NOT NULL", AutoSync=AutoSync.OnInsert, IsDbGenerated=true)]
    the Insert operation works perfectly and correctly.

    Now I realize that code generated by the O/R Designer is regenerated when changes are made to the entity classes and other objects on the designer surface, and because of this, any code that I add to the generated code is typically overwritten when the designer regenerates code.  However, the O/R Designer provides the ability to generate partial class files in which I can add code that will not be overwritten (http://msdn.microsoft.com/en-us/library/bb531258.aspx?appId=Dev10IDEF1&l=EN-US&k=k(NET.ASP.LINQDATASOURCE.ADVANCEDDIALOG);k(TargetFrameworkMoniker-".NETFRAMEWORK&k=VERSION=V4.0");k(DevLang-ASPX)&rd=true).

    The part where I am stumped is I don't know where or how to create a partial class (that will add the two aforementioned missing attributes for ModifiedDate) when I click View Code and my cursor lands on the block of code shown below within the .designer.cs file.

    Any help would be greatly appreciated.  Thanks again.

    [global::System.Data.Linq.Mapping.TableAttribute(Name="dbo.AddressType")]
    public partial class AddressType : INotifyPropertyChanging, INotifyPropertyChanged
    {
    	
    	private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);
    	
    	private int _AddressTypeID;
    	
    	private string _AddressTypeName;
    	
    	private System.DateTime _ModifiedDate;
    	
      #region Extensibility Method Definitions
      partial void OnLoaded();
      partial void OnValidate(System.Data.Linq.ChangeAction action);
      partial void OnCreated();
      partial void OnAddressTypeIDChanging(int value);
      partial void OnAddressTypeIDChanged();
      partial void OnAddressTypeNameChanging(string value);
      partial void OnAddressTypeNameChanged();
      partial void OnModifiedDateChanging(System.DateTime value);
      partial void OnModifiedDateChanged();
      #endregion
    	
    	public AddressType()
    	{
    		OnCreated();
    	}
    	
    	[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_AddressTypeID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
    	public int AddressTypeID
    	{
    		get
    		{
    			return this._AddressTypeID;
    		}
    		set
    		{
    			if ((this._AddressTypeID != value))
    			{
    				this.OnAddressTypeIDChanging(value);
    				this.SendPropertyChanging();
    				this._AddressTypeID = value;
    				this.SendPropertyChanged("AddressTypeID");
    				this.OnAddressTypeIDChanged();
    			}
    		}
    	}
    	
    	[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_AddressTypeName", DbType="NVarChar(20) NOT NULL", CanBeNull=false)]
    	public string AddressTypeName
    	{
    		get
    		{
    			return this._AddressTypeName;
    		}
    		set
    		{
    			if ((this._AddressTypeName != value))
    			{
    				this.OnAddressTypeNameChanging(value);
    				this.SendPropertyChanging();
    				this._AddressTypeName = value;
    				this.SendPropertyChanged("AddressTypeName");
    				this.OnAddressTypeNameChanged();
    			}
    		}
    	}
    	
    	[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_ModifiedDate", DbType="DateTime NOT NULL", AutoSync=AutoSync.OnInsert, IsDbGenerated=true)]
    	public System.DateTime ModifiedDate
    	{
    		get
    		{
    			return this._ModifiedDate;
    		}
    		set
    		{
    			if ((this._ModifiedDate != value))
    			{
    				this.OnModifiedDateChanging(value);
    				this.SendPropertyChanging();
    				this._ModifiedDate = value;
    				this.SendPropertyChanged("ModifiedDate");
    				this.OnModifiedDateChanged();
    			}
    		}
    	}
    
    Thursday, August 12, 2010 8:22 PM
  • The part where I am stumped is I don't know where or how to create a partial class (that will add the two aforementioned missing attributes for ModifiedDate) when I click View Code and my cursor lands on the block of code shown below within the .designer.cs file.

    Creating a partial class (or extending an existing one in your case) is easy, but I'm going to have to defer to the experts about whether you will be able to do what you want to by using one.

    You will see the signature of the AddressType class is already partial. What you need to do now is

    1. create an additional file in your project. It must define the same namespace as your designer.cs does.
    2. copy the AddressType class signature (without the [Table(...)] attribute).
    3. Add things into that class.

    However I've just tried doing this (by creating a new version of ModifiedDate property with IsDbGenerated=true in the column attribute) and it failed with "Error The type 'BlahBlah.AddressType' already contains a definition for 'ModificationDate'". So I think (but am not sure) that you will have to fall back to modifying the dbml file in some way using the O/R designer surface. You could: a) modify the column's property or b) remove the column and use the partial class to code the ModifiedDate property. Either way, the changes will be lost whenever you recreate the dbml by dragging that table onto the design surface again.

     

    That said, the experts always seem to be able to come up with a clever way to get round a problem. So over to them ... please.

    Friday, August 13, 2010 8:23 AM
  • You can't do it like that, you can use  partial methods but they can't be defined on both classes.

    A signature in one and the complete method in the other or else you will get the aforementioned exception.

    I have several ideas but I dislike talking without being complete sure it works, I did something similar a while back.

    I'll test it and see if it fits your problem and postback next week if this is not resolved yet.

    Regards

    Friday, August 13, 2010 12:39 PM
  • John - Thanks again for your help.  I really appreciate it.

    Although the Microsoft creators of the O/R Designer accounted for Identity fields by ensuring that the "AutoSync=AutoSync.OnInsert" and "IsDbGenerated=true" attributes were automatically added to the field's definition in the .designer.cs file, I wish they did the same for computed Default fields (i.e., getdate()), or at least allowed a way for developers to easily (and permanently) add to/modify a field's definition.  Doing so would eliminate the need to generate a partial class file for this type of situation.

    My wish notwithstanding, I will keep my fingers crossed that the "experts" can come up with a viable solution.

    Friday, August 13, 2010 3:40 PM
  • Serguey123 - I appreciate your offer to test some ideas.  As I mentioned in my response to John, I will keep my fingers crossed.  Thanks for your help.
    Friday, August 13, 2010 3:46 PM
  • Sorry it took me some time to respond, I was looking for a silver bullet, sadly there is none.

    You have a few choice but all will require some extra work.

    1. Modify the database, so that it will match your current app design
    2. Use external or custom mapping http://msdn.microsoft.com/en-us/library/bb386907.aspx
    3. Create a sepparate dbml file and when the original regenerate you manually sync them both (you can automate the process by creating a sepparate app) http://msdn.microsoft.com/en-us/library/bb882675.aspx

    Both option two and three will require knowlodge of xsd and xml

    Regards

    • Marked as answer by emerald77 Monday, August 16, 2010 5:23 PM
    Monday, August 16, 2010 1:39 PM
  • Serguey123 - No apology necessary.  I sincerely appreciate you taking the time to research the issue.

    I would prefer not to modify the database, so I will pursue options 2 & 3.

    Thanks again for all your help.

    Monday, August 16, 2010 5:27 PM