locked
Foreign Keys and StoreGeneratedPattern="Identity" trying to insert NULLs RRS feed

  • Question

  • I seem to have a found a problem with ADO.NET Entity Framework and the auto-generated .edmx. I'm running on VS 2010 RC, and my project is MVC2 on .NET 3.5, connecting to SQL 2008. For the purpose of this error, I have two tables: [Line] and [Wager]. Each table has an ID column that is set to primary key, integer, and identity generated in the SQL database. The Wager table also has a Foreign Key LineID. I auto-generated an edmx, and most of the solution works correctly. However, when I add a Wager with relationship to the Line table and try _db.SaveChanges();, I get the error "Cannot insert the value NULL into column LineID". I've tried several different ways of setting the relationship, such as:
    wagerToAdd.Line = _db.Lines.Where(l => l.LineID == currentLine).First();
    
    wagerToAdd.LineReference.Value = _db.Lines.Where(l => l.LineID == currentLine).First().LineID;
    In each case, when I inspect the "wagerToAdd" object, it has the correct Line reference selected, but SaveChanges() is trying to insert a NULL value. I inspected the ssdl, and found that my definition for the Wager table has this definition for LineID:
    <Property Name="LineID" Type="int" Nullable="false"
                                    StoreGeneratedPattern="Identity"/>
    
    If I remove the StoreGeneratedPattern="Identity", then SaveChanges(); works fine. This isn't really an issue, except that I have to go through the SSDL every time that I re-generate the model to remove these lines. This happens across multiple tables. It looks like when the foreign keys are set to StoreGeneratedPattern="Identity", the model is expecting the DB to generate that key even though it's being passed in with the object. I can't figure out why the model is marking these keys as Identity.

    I also have a asp_net_Users table (auto-generated by the asp.net sql tool for forms authentication), with a PK of userid (column type uniqueidentifier). I have this userID set as a foreign key to another table, but that foreign key was not set as StoreGeneratedPattern="Identity". What gives?
    Friday, February 19, 2010 8:33 PM

Answers

  • Actually, I just tried 3 times and was unable to re-create the issue. Since my original post I have moved from SQL Express to SQL Developer, which may have resolved the issue.
    Saturday, March 6, 2010 10:28 PM

All replies

  • Hi Matthew
    Is the model reverse engineered from an existing database? If so, it sounds like the schema in the database is strange. Is the wizard really saying it's an identity key when it's truly not in the model? That could be a bug but I've built plenty of models with EF4 and using VS2010 RC where I have this typical parent/child PK/FK relationship and have never seen this behavior.

    julie
    Saturday, February 20, 2010 11:03 PM
  • Julie,

    Yes, the model was generated from an existing database. I just revisited this issue and confirmed that my DB schema was correct. Apparently at some point the SQL designer within Visual Studio blew away most of my foreign key definitions. However, even after re-creating them, the generated schema was still marking my foreign keys as StoreGeneratedPattern="Identity". I then tried updating my application to .NET 4.0 (previously I was working with 3.5), and under the new version the generated schema was correct.
    Monday, February 22, 2010 11:09 PM
  • that's odd. I havent' seen this in VS2008 either (built lots of models there).

    I'm sure if someone from the team was to look at your model or db, they'd be able to pinpoint what's causing the problem. Hopefully whatever it is won't surprise you later.

    julie

    Tuesday, February 23, 2010 12:35 AM
  • Hello Matthew,

     

    I agree with Julie, this issue is really strange! 

     

    If it is convenient for you, could you please send me a demo project and db file which can repro this problem?    Thanks a lot!   My mail address is v-micsun@microsoft.com. 

     

    Have a nice day, both!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, February 23, 2010 8:31 AM
  • Hi Matthew,

     

    How is the problem now? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, February 25, 2010 2:10 AM
  • I can confirm what Matthew is saying.  I'm having the same problem.  Thinking of just jumping ship and using LINQ to SQL
    Tuesday, March 2, 2010 2:48 PM
  • I'm also having this issue - is there a fix?
    Friday, March 5, 2010 10:50 AM
  • Hi Lingzhi. I forgot about this topic for a while. I'll send you the requested file today.
    Saturday, March 6, 2010 9:38 PM
  • Actually, I just tried 3 times and was unable to re-create the issue. Since my original post I have moved from SQL Express to SQL Developer, which may have resolved the issue.
    Saturday, March 6, 2010 10:28 PM
  • Hi all,

     

    @Matthew:  I am glad that the problem disappears.   Of course, please feel free to let me know if you need any further assistance. 

    @KickStartCV and @chemass, are you using SQL Server Express as well?   Is it convenient for you to create a demo and send it to me?   Thanks a lot!  My mail address: v-micsun@microsoft.com

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, March 8, 2010 8:51 AM
  • Hi Lingzhi,

    I just went into the xml and deleted the 'storegeneratedpattern="Identity" attributes which solved the problem.
    I am indeed using SQL 2008 express - the production database is going to be on an SQL 2005 enterprise instance, but i'm happy with the manual fix.

    Cheers,

    Ché
    Friday, March 12, 2010 8:49 AM
  • Hi,
    I am having exactly same problem. Using VS2010 FW4 SqlServer 2008 Express, everytime I update db schema these lines are back, really annoying :(
    Saturday, March 13, 2010 12:21 PM
  • Hi,

    Could you try out the hotfix below for your issue? We released this hotfix to fix issues with StoreGeneratedPattern value not propagating correctly.

    MSConnect: http://connect.microsoft.com/VisualStudio/Downloads/DownloadDetails.aspx?DownloadID=37957

    Code Gallery: http://archive.msdn.microsoft.com/KB2561001

    Thank you,
    Sarah McDevitt
    Program Manager, Microsoft


    Sarah McDevitt Program Manager SQL Server Data Tools
    Wednesday, September 28, 2011 11:11 PM