none
How to prevent "Invalid Column Name" when extending a Model?

    Question

  • Hi,

    I am currently developing a Windows Phone App with an annotated underlying data model that will be extended during upcoming releases with high probability.

    When I update my model with a new property I get an "invalid column name" exception as soon as I am trying to query the database for the existing objects. That's what I expected.

    I have searched for this on MSDN and StackOverflow but haven't found the silver bullet for this problem yet. My questions are:

    • How do I update a DB schema with new columns in L2S (It would be okay for me if those columns were set to null) before actually reading objects?
    • If the previous point is not possible: How do I pull the "old" objects into my model during runtime so that I can wipe and recreate the schema?

    If this question has been answered somewhere else before I'd also be glad if you could point me towards the solution or provide me with the right terms to search for :)

    Cheers,

    Fred

    Monday, July 22, 2013 1:53 AM

Answers

All replies

  • Hi Fred,

    It seems that there isn't much support for us to update database schema through O/R designer. I don't know if there are any API to read the dbml file so we can recreate the schema either.

    There are some tools introduced in this thread. Some of them might be useful to generate the DDL for example.

    Best regards,


    Chester Hong
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Tuesday, July 23, 2013 4:48 PM
    Moderator
  • Hi Chester,

    thanks for the reply. The problem is the life cycle of the database. For Windows Phone projects you are very, _very_ limited regarding L2S. You don't even have deep object loading. :)

    So you also don't have a O/R-Designer and no dbml file you can push or pull. All you have is a simple SDF file in the IsoStorage which is created via 

    _db = new DBContext("isostore:/dbname.sdf");

    The context is a DataContext (http://msdn.microsoft.com/de-de/library/system.data.linq.datacontext.aspx) and you can create and delete the database but not update it.

    So what happens is:

    1. Add a property to the model class
    2. Compile
    3. Try read records
    4. DB schema is not updated -> won't find the property -> invalid column exception
    5. App exits.

    And I would wish for:

    1. Add a property to the model class
    2. Compile
    3. Try read records
    4. DB schema is not updated -> all new fields are simply nulled as there is no data
    5. Now I have a backup so that I can delete the schema
    6. Recreate
    7. Commit the new objects

    It all comes down to the fact that if you update your model with new properties there is no easy method to get the existing data. Do you know any best practices?

    Thank you, cheers,

    Fred

    Tuesday, July 23, 2013 6:47 PM
  • Hi Fred,

    Thanks for the information. I'm not very familiar with L2S for the Windows Phone application. I'll try to involve some other senior engineers in this thread.

    It will take some time to get the response. Your patience will be appreciated.

    Thanks for your understanding.

    Best regards,


    Chester Hong
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Wednesday, July 24, 2013 6:17 AM
    Moderator
  • Hi Chester,

    I asked the question in our Student Partner network in the meantime. Luckily someone came up with the buzzword: DatabaseSchemaUpdater.

    In fact that is exactly the word to search the MSDN documentation for.  If anyone else has the problem:

    http://msdn.microsoft.com/en-us/library/windowsphone/develop/hh202860(v=vs.105).aspx#BKMK_ChangingtheDatabaseSchema

    http://msdn.microsoft.com/en-us/library/windowsphone/develop/hh394022(v=vs.105).aspx

    But really..Sadly it is quite hard to find these pages if you are not searching for the right term. Plus: The schema updater only reveals itself when adding an extra reference to the code. So there is absolutely no chance of discovering it by method browsing.

    Cheers,

    Fred

    • Marked as answer by Freddixx Friday, July 26, 2013 12:16 AM
    Friday, July 26, 2013 12:16 AM
  • Hi Fred,

    Thanks for your sharing. It will be beneficial to other community members who have similar problems.

    Have a nice day!

    Best regards,


    Chester Hong
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Friday, July 26, 2013 7:22 AM
    Moderator