Effects database changes have on Linq to SQL

In this post I’m going to discuss the effects of changing the underlying database table has on Linq to SQL and to make people aware of the potential issues as quite a few people seem to be asking about this.  Changes to the underlying database structure is a problem with any application and data access, be it ADO.net and Datasets, SubSonic, Linq or any other approach.  If the data structure is changed without your application being updated or knowing about the change then you will run into problems.

In this application, I will be working with the following entity.

Table

Writing queries against this isn’t a problem.   For example, if we wanted to write everything out to the console we could do this:

var query = from p in db.Persons
                   select p;

foreach (var person in query)
{

    Console.WriteLine(“{0} | {1} | {2} | {3}”,
                                 person.id, person.Name, person.PostCode, person.Phone);
}

This could result in:

1 | Bob | AAA | 123
2 | Jack | BBB | 0
3 | Gill | MB | 12346

Adding additional columns

If we added an additional column, say email,  onto our table then the above could would still work fine however we would never be able to access that additional column until we regenerated our DataContext (see below).

If we inserted a new person then this also wouldn’t stop our application from working, unless the new column was set to be NOT NULL, however we wouldn’t be able to fill in that information.

Person newP = new Person();
newP.Name = “Test”;
newP.PostCode = “TTTT”;
newP.Phone = new Random().Next();
db.Persons.Add(newP);
db.SubmitChanges();

Changing column data types

Changing the types in our database could cause our application to stop functioning. If we change the int to a bigint in the table design then our query would not longer run because Linq couldn’t convert a long to an int.  If we did something like change a nvarchar(50) to a nvarchar(MAX) then this wouldn’t cause a problem as Linq treats them both as a string.

Removing columns

Removing columns is when most problems will occur. If I removed the phone column from the table and execute the query then I would receive a SqlException – Invalid column name ‘Phone’.  If our query was like below, then it wouldn’t cause an exception as Phone would never be called.

var query = from p in db.Persons
                        select new { p.id, p.Name, p.PostCode };

However, when inserting data we will always get a SqlException even if the column was not populated as Linq will try and insert null into the column.

Regenerating our DataContext

So, if you are changing the underlying table, updating the DataContext in your system is also required.  In Visual Studio 2008 there are only two ways to update a DataContext.  Either, regenerate the entire datacontext using SQLMetal, or remove the table from the datacontext using the designer and then insert it again.  Shame there isn’t a button called refresh on the designer.

Thankfully, all the objects are partial, so you can store all your custom logic in a separate class which will not be affected by this.  However, if you go against this and write your logic in the actual DataContext.designer.cs class then when you recreate it, you will lose these changes.

Technorati Tags: ,

5 thoughts on “Effects database changes have on Linq to SQL”

  1. Shame there isn’t a button called refresh on the designer.

    Actually, I remember a while back on one of the Microsoft blogs a person mentioned that the refresh feature was planned. Even though I use the partial class feature a lot it still would be easier to refresh the code and not have to remove the table and then drag it back on to get the changes 🙁

  2. Regarding refreshing the datacontext/using a partial class… would using a partial class provide you with a means of setting column attributes such as UpdateCheck? That’s my problem – I need to manipulate the default column attributes for an application – but I have to remember what they are so that if I regen the DataContext I can reapply the changes.. I would be very interested in learning more about the partial class approach you mention – because I’m not aware of a way to programmatically set Attributes..
    feels like technology has taken a step backwards, doesn’t it?

  3. I had the same problem, so I’ve written my own utility to do this. It uses the command line SQLmetal.exe tool to build new. complete dbml from the database and then compares this with the current model, and adjusts the tables/views/procs as required.

  4. Whenever I make changes in LINQ, I don’t delete the table from the LINQ designer. I drag the table onto the LINQ designer surface. Then you can copy just the columns you need from the table object you just dragged on and paste those into the original table. Then delete the old one. That way if you setup relationships inside of the LINQ designer but not in the database, you preserve those.

Leave a Reply

Your email address will not be published. Required fields are marked *