locked
Adding a new row to a possibly empty table with a PK RRS feed

  • Question

  • Hi, I am trying to add a new row to a table which may or may not be empty. I need to determine if the table is empty in order to know whether to add a new id or imcrement a max id. I need to do one of two things:

    Either work out if the table is empty, or somehow have the id incremented based on the most recent id & set it to 0 if not.

    Any ideas how this is done? Below is the code. Many thanks :)

    using (TestEntities testDB = new TestEntities())
                {
                    var result = testDB.TestTables.Where(t => t.Col1 == 1);
                  
     // how to check if this value is null??
                    var nextId = testDB.TestTables.Max(c => c.Col1);
                    // Insert a new row
                    var newRow = new TestTable();
                    newRow.Col1 = ++nextId;
                    newRow.Col2 = "This is a test";
                    newRow.Col3 = "It worked!";
                    testDB.AddToTestTables(newRow);
                    testDB.SaveChanges();
                }


    @nt

    Wednesday, April 18, 2012 5:02 AM

Answers

  • If your database is sql, you can mention Auto increment seed.

    Open the table in design mode, go to column properties, navigate to Identity specification and set the flag to true.

    If you set this falg to true, you don't need to pass the ID value. It will be automatically incremented.

    • Marked as answer by Allen_MSDN Monday, April 23, 2012 3:27 AM
    Wednesday, April 18, 2012 5:37 AM
  • Hi A n t,

    Welcome to MSDN Forum.

    Commonly, we set the primary key as identity in the database, then when we do insert operation in EF, we needn't to provide a primary key explicitly, database will auto-generate for the new record.

    Based on the issue, if you want to provide the primary key manually, you have to query out the last record's primary key everytime, it isn't a sensible solution. But if you have some special scenario to force you to do like that, you can write the code as below.

    var test = (from t in testDB.TestTables select t).LastOrDefault();
    var nextId = test ? test.Id+1 : 1;

    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Allen_MSDN Monday, April 23, 2012 3:28 AM
    Thursday, April 19, 2012 2:40 AM

All replies

  • If your database is sql, you can mention Auto increment seed.

    Open the table in design mode, go to column properties, navigate to Identity specification and set the flag to true.

    If you set this falg to true, you don't need to pass the ID value. It will be automatically incremented.

    • Marked as answer by Allen_MSDN Monday, April 23, 2012 3:27 AM
    Wednesday, April 18, 2012 5:37 AM
  • Hi A n t,

    Welcome to MSDN Forum.

    Commonly, we set the primary key as identity in the database, then when we do insert operation in EF, we needn't to provide a primary key explicitly, database will auto-generate for the new record.

    Based on the issue, if you want to provide the primary key manually, you have to query out the last record's primary key everytime, it isn't a sensible solution. But if you have some special scenario to force you to do like that, you can write the code as below.

    var test = (from t in testDB.TestTables select t).LastOrDefault();
    var nextId = test ? test.Id+1 : 1;

    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Allen_MSDN Monday, April 23, 2012 3:28 AM
    Thursday, April 19, 2012 2:40 AM