How to make LINQ to SQL backward compatible

Question

Hi,

I am new to LINQ to SQL.... I need to add a new column to an existing table and I updated the dbml to include this new field. Then in my C# code, I query the database and accessing this new field. Everything is fine with the new database; however, if I load back a previous database without this new field, my program would crash when it's accessing the database (obviously because of this new field). How do I make it, either the database or my C# code to support backward compatibility?

Here's my code snip

I added a field email to Customer table and also add it to the DataContext.dbml, below is the c# code

DataContext ctx = new DataConext ();
var cusList = ctx.Customer;
foreach (var c in cusList)
{
.
.
.
//access the new field
if (c.email != null)
   displayEmail (email);

.
.
.
}

When I ran through debugger, it's crashing at the very first foreach loop if I am using an older version database without the new email field.

Thanks.

Answer 1

Make sure you upgrade old database. That's what updates are made for.

I don't think there's a better option. But i might be wrong.

Answer 2

Should be a code-land fix. Make your code check for the existance of the column, and use different queries on each case.

Answer 3

I agree with Arnis L.: Upgrade your old database. LTS is going to want to look for that column called email in your table, and will complain if it can't find it. I could suggest a workaround that would entail using a Stored Procedure, but you'd need to update your old database to use this stored proc, so it's not a very helpful suggestion :-)

How to update your old database? This is the old-school way:

ALTER TABLE Customers
ADD Email VARCHAR(130) NULL

You could execute this manually against the older database through Query Analyzer, for example. See here for full docs on ALTER TABLE: http://msdn.microsoft.com/en-us/library/aa275462%28SQL.80%29.aspx

If you are working on a team with very strict procedures for deployment from development to production systems, you would already be writing your "change scripts" to the database in this same fashion. However, if you are developing through Enterprise Manager, it might seem counter-productive to have to do the same work, a second time, just to keep old database schemas in sync with the latest schema.

For a friendlier, more "gooey" approach to this latter style of development, I myself can't recommend enough the usage of something like the very excellent Red Gate SQL Compare tools to help you keep multiple SQL Server databases in sync. (There are other 3rd party utilities out there that supposedly can roughly the same thing, and that might even be a little cheaper, but I haven't looked much further into them.)

Best of luck!
-Mike