locked
Primary Keys RRS feed

  • Question

  • User-1902356349 posted
    Hi

    I just tried creating my first dynamic data site. I created my LINQ to SQL classes and turned on enableTemplates in the web.config.

    ran the site, no problem. everything looked good.

    tried to add an record to a table, which worked, but adding a second one failed with:

    Violation of PRIMARY KEY constraint 'PK_Table'. Cannot insert duplicate key in object 'dbo.Table'. The statement has been terminated.

    I poked around the LINQ to SQL designer and found "Auto Generated Value", set that to True but then it failed with

    Cannot insert the value NULL into column 'Id', table 'Database.dbo.Table'; column does not allow nulls. INSERT fails. The statement has been terminated.  

    I guess that option inferred the database would do the auto generation?

     

    What I want to do set Id to MAX(Id) + 1

    In another case, where i have a compound key, I would want to set the Id to MAX(Id) + 1 WHERE Type = @Type (where Type is the second part of the Primary key)

    Apologies for the pseudo sql, but I hope you get my meaning.

     

    Before LINQ to SQL i was using transactions and executing the SELECT MAX manually. I did this to avoid the database incompatibilities of Identity fields and the @@IDENTITY etc. This way I have full control over the Id fields and it's database independant.

     

    Is there any way to hook into the LINQ to SQL classes to accomplish this?

    TIA
     

    Tuesday, January 15, 2008 3:54 AM

All replies

  • User-1725998994 posted

    cant you let the database to set it and use -1 in your logic, i think this should be possible.

    Tuesday, January 15, 2008 9:14 AM
  • User1641955678 posted

    One thing you may be able to do is override SubmitChanges in your DataContext class.  e.g. something like this:

    public partial class NWDataContext {
        public override void SubmitChanges(System.Data.Linq.ConflictMode failureMode) {
            ChangeSet changeset = GetChangeSet();
            foreach (object newItem in changeset.Inserts) {
                // Set the fields you need
            }

            base.SubmitChanges(failureMode);
        }
    }

    David

    Tuesday, January 15, 2008 12:02 PM
  • User-1902356349 posted

    @roni

    As soon as there's a generic LINQ to "DB" which has a universal method of retrieving the identity field, then I'll use that. however, at the moment, LINQ to SQL (very misleadingly named) only works with SQL Server and so i need to use a generic method for handling identity fields, and this is what i've been using up to now. If there's a better way of doing this I'm all ears.

    @david

    That looks like it will allow me to do what i need to. But i'm a little annoyed that this isn't handled by the framework. What's the best way to do this?

    Ideally i should be able to set my key field to an identity (or autonumber) and have the framework handle it the same no matter what database i'm using.

     

    Tuesday, January 15, 2008 12:41 PM
  • User1641955678 posted

    This is probably more of a generic Linq question than something specific to Dynamic Data, so it might make sense to repost in a forum that's more about Linq, as you're more likely to hit more knowledgeable folks there.

    David

    Tuesday, January 15, 2008 1:28 PM
  • User949140326 posted

    Can you please tell me how you fixed this problem. Now i am facing the same issue.  my code in data context class is

    [

    Column(Storage = "_BookOrderID", AutoSync = AutoSync.OnInsert, DbType = "UniqueIdentifier NOT NULL", IsPrimaryKey = true, IsDbGenerated = true)]

     

    public System.Guid BookOrderID

    {

     

    get

    {

     

    return this._BookOrderID;

    }

     

    set

    {

     

    if ((this._BookOrderID != value))

    {

     

    this.OnBookOrderIDChanging(value);

     

    this.SendPropertyChanging();

     

    this._BookOrderID = value;

     

    this.SendPropertyChanged("BookOrderID");

     

    this.OnBookOrderIDChanged();

    }

    }

    }

     what else do i need to do?

    Thursday, April 7, 2011 12:55 PM