Written by 3:43 pm Programming • 2 Comments

Converting from Linq-to-SQL to Entity Framework Model First

Recently I switched hosting providers and had to update an existing project to work against a MySQL database instead of SQL Server. After successfully creating the MySQL database and porting over all the data (which turned out to be a major chore and worthy of a post in itself…), I went to update my website to target the MySQL database only to find out (having forgotten completely) that I’d used Linq-to-SQL (L2S) as the ERM and, of course, L2S only supports SQL Server (thanks Microsoft!). So began what I thought would be a painful process of migrating to Entity Framework…

Which actually turned out to be rather easy! If you’re needing to do the same thing, here’s the process I followed and some pointers/things to remember:

Before you start: if you’re planning to use MySQL like I did, you’ll need to download the MySQL .NET Connector first otherwise Entity Framework won’t let you use a MySQL database as the source.

  1. In the project containing your old L2S .DBML file, right-click and add a new item ADO.NET Entity Data Model. Give your EDMX file a sensible name and click Add to continue.
  2. On the next page, choose Generate from database and hit Next. You’ll probably need to set up a new connection to your MySQL database, so hit New Connection and then choose MySQL Database as the Data source. Click OK and proceed to enter the relevant server and authentication details, then choose the right database from the drop-down list.
  3. With the connection created, click next and check the Tables box to select all tables in the target database (or choose individual tables if you only want your model to contain a subset of the entire database).
  4. Enter a namespace for the model. Generally I use the same namespace as the project the EDMX is going to live in, but you can use anything you like.
  5. Once the model is created, the app.config file within the project will be updated with a new connection string used by your entity data context. Copy this connection string into your Web.config of the website project.

With the new context created, all that remains is to update any existing code to point to the new context instead of the old L2S data context. This is made much easier if you give your EDMX model the same name as the DBML file as it should create a context with the same name.

If you’ve used a sensible design pattern for your website, there shouldn’t be too many places where new data contexts are created, so it should just be a case of swapping these to reference your new EF context. Any existing partial classes should just work, and the only other thing to remember is that with L2S you call .SubmitChanges() to persist object changes back to the database but with EF the method is called .SaveChanges() instead. (A careful Find-and-Replace is your friend here…)

Good luck and don’t forget to back up your project first, or preferably use a source control system such as Subversion or Git to allow you to roll back any changes if you hit trouble!

(Visited 2,894 times, 3 visits today)
Tags: , , , , Last modified: 16/02/2020