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.

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.