@bobnoordam

EF Framework core for ling2sql users

Posted on | By bobnoordam

Linq2Sql had a few extremely practical tools such as sqlmetal enabling you to generate code form an existing database with a single command, replace a single file and be done with it. EF Core seems to align with the trends lately to make everything more cumbersome, but you can stil use a workflow much like it.

Creating a data DLL

1) Create a new empty net core dll

2) Install nuget packages for EF Core:

Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.Tools

3) Create an empty Models directory in the project to contain the target files

3) Scaffold the existing database

The tools package contains the scaffold-dbcontext command which we need to create a dbcontext file from the database. Since it is part of the nuget package, we need to access it from the nuget package manager console.

  • In VS: Tools->Nuget package manager->Package manager console
  • Set the Default project to your data project in the top bar
  • Set the Startup project to your data project

Generating the files

Adjust the below command for your sqlserver location and database name

Scaffold-DbContext “Server=localhost;Database=Toybox;Trusted_Connection=True;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

  • Optional: Parameter -Force to overwrite existing files (regenerating after database changes)
  • Optional: Parameter -Context to specify output datacontext name
  • Optional: Parameter -Project to specify the target project to create the models in
  • Optional: Paramete -StartupProject to specify the project that contrains the tooling

The last 2 parameters are very usefull when working with seperate projects for application and data access.

We use the generic name BaseContext here, because to make the connection string easily configurable we need an overriding class, which will get a more descriptive name.

4) Your models directory now contains the files for the classes, and the datacontext. (in this case: ToyboxContext.cvs). The files have been added to the solution.

Connecting to data

Linq2Sql offered a simple and elegant solution by offering an overload that accepts a single parameter as a connection string. EF Core lacks this option, and needs additional work to make our dbcontext configurable.

To archieve this, create a class that derives of the context class that you just created, and set the connection parameters through the new class. That way you can safely regenerate the models and context, and keep your own modification ready to use.
Your class may, or may not look something like this. AsterixEfContext is the generated class, while EduDataContext is the derived class that will be used from code.

using EduPlannerCoreData.Models;
using Microsoft.EntityFrameworkCore;

namespace EduPlannerCoreData
{
    public class EduDataContext : AsterixEfContext
    {
        private readonly string _database;

        public EduDataContext(string database) {
            _database = database;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
            if (!optionsBuilder.IsConfigured) {
                optionsBuilder.UseSqlServer(_database);
            }
            base.OnConfiguring(optionsBuilder);
        }
    }
}

You now have easy access to your EF Core DataContext and can use a constructor accepting a connection string.

eg: x = new EdutDataContext(conString)

Regenerating your context

If you use the -Force parameter to create a new version of your datacontext you will find that new and altered tables are added to your project. Tables that you dropped from your database leave the model files behind however, so you need to manually clean up after that. SqlMetal did not suffer from that because it generated one massive file.

Category: C#