Convert existing LinqToSQL code to be compatible with other databases

Question

I have a working application that communicates with an SQL Server database. This application makes use of LinqToSQL.

For a number of reasons, I want to convert this application to using Microsoft Access instead. (It's a small set of data, concurrent usage is possible but rare, I want to deploy on machines that don't have access to SQL Server, and I want to be able to send the entire database around as one file to be used by colleagues who only have Microsoft Access) .

How should I go about converting the application? As LinqToSQL is only provided for SQL Server, I suspect I'm facing a problem occasionally encountered by other developers; for example to ship an SQL Server application to a new site that uses Oracle.

The only strategy I can think of is to search the code for every use of LinqToSQL and change it to using ADO.NET. Obviously, this will be a time consuming and error-prone task.

Is there a better way, or should I just grit my teeth and get on with it?

A snippet of code is below, just in case an example helps the conversation:

        var existing =
           from ph in _dataContext.pub_hols
           where ph.pub_hol_def_id == this._publicHolidayDefId
              &&
           ph.pub_hol_date.Year == year
           select ph;
        _dataContext.pub_hols.DeleteAllOnSubmit(existing);
        _dataContext.pub_hols.InsertAllOnSubmit(GetPublicHolidays(cellsForYear));
        _dataContext.SubmitChanges();

Answer 1

Quickly googling turned up this article. Maybe it helps.

Answer 2

I'd recommend switching to a more generic DAL via something like nHibernate. That way next time you need to switch databases again you won't have to change anything. Depending on how complicated your LINQ queries are you may even be able to use them with the nHibernate LINQ module. That module is still in beta and I can't speak for it myself.

Answer 3

ADO.NET Entity Framework is cross-database, and should be a loy closer to your current code.