Query a Database with Entity Framework

Created: Use Entity Framework and LINQ to Connect to and Query an Existing Database

Updated: 03 September 2023

Info derived from this post on LearnEntityFrameworkCore

Sometimes you want to use a database but don’t want to deal with the difficulties created by SQL

Setting Up

Create Project

First, you will need a database to connect to, this can be done by any method, but I will use the Northwind database for this. The SQL Setup Script can be found here

Once that’s done you can create a new project for running or queries

Terminal window
1
dotnet new console -n DBPlayground

Add Packages

You will also need some packages installed on your application to make this all work

Terminal window
1
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
2
dotnet add package Microsoft.EntityFrameworkCore.Design

Add EF Global

And you’ll also need to install the ef tool if you don’t already have it:

Terminal window
1
dotnet tool install --global dotnet-ef

Scaffold Models

Now that you have everything installed, you can go ahead and run the following to scaffold your code models. I am connecting to SQLEXPRESS on the Northwind database but you can use any connection string that works for your purpose

Terminal window
1
dotnet ef dbcontext scaffold "Server=localhost\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -o Model

The above will output generated models that align to the data in your database in the Models folder

Using the Connection

When we ran the above it will have generated a DBContext file. In our case NorthwindContext which is the class for our Database. We can use this as follows:

1
using System;
2
using System.Linq;
3
using DBPlayground.Model;
4


5
namespace DBPlayground
6
{
7
    class Program
8
    {
9
        static void Main(string[] args)
10
        {
11
            var db = new NorthwindContext();
12
        }
13
    }
14
}

We can thereafter use the db instance to do database things using EF

1
db.Customers
2
    .Take(5)
3
    .ToList()
4
    .ForEach(c => Console.WriteLine($"{c.ContactName}\t{c.Country}"));