Using an mdf file database with Entity Framework Core 2 in Visual Studio 2017

Full source code available here.

If you want to play around with Entity Framework it can be a little frustrating to create a complex database with a lot of sample data.
Instead, you could download the Northwind database from Microsoft, it has plenty of tables, a bunch of views and handful of stored procs. More than enough to try out many of the features of EF.

Now, all you have to is figure out how to get access to the db from your application, and it’s not as simple as you might hope.
So here’s how to do it in Visual Studio 2017.

Step 1
Download the Northwind database – you can get it here
Install it, it will create some files in c:\SQL Server 2000 Sample Databases.

Step 2
Copy Northwind.mdf and Northwind.ldf to your user directory, something like c:\users\bryan. The Northwind files have to be in that directory to work with the connection string below.

Step 3
Now in Visual Studio use the following connection string.

"Data Source=(LocalDB)\\MSSQLLocalDB;DataBase=Northwind;Integrated Security=True;Connect Timeout=30"

My sample application is a .NET Core Web Api. In startup.cs, add the following –

public void ConfigureServices(IServiceCollection services)
	services.AddDbContext<NorthwindContext>(options =>
		options.UseSqlServer("Data Source=(LocalDB)\MSSQLLocalDB;DataBase=Northwind;Integrated Security=True;Connect Timeout=30"));

If you want to place the database files elsewhere in your filesystem, add an absolute filepath to the connection string.

Step 4
Add an Order.cs class, this represents the orders table from the Northwind database. You can of course add more classes to represent other tables in the Northwind database.

public class Order
	public int OrderId { get; set; }
	public string CustomerID { get; set; }
	public int EmployeeID { get; set; }
	public DateTime OrderDate { get; set; }
	// etc.

Step 5
Add a NorthwindContext class that inherits from DbContext and add the Order DbSet.

public class NorthwindContext : DbContext
	public NorthwindContext(DbContextOptions options) : base(options) { }
	public DbSet<Order> Orders { get; set; }

Step 6
In the controller, add the NorthwindContext to the constructor.

public OrdersController(NorthwindContext northwindContext)
	_northwindContext = northwindContext;

Step 7
And finally, use the context in the Get method.

public async Task<IActionResult> Get(int orderId)
	var order = await _northwindContext.Orders.Where(o => o.OrderId == orderId).SingleOrDefaultAsync();
	return Ok(order);

Full source code available here.

Performing a WHERE IN with Entity Framework or on a List

WHERE IN is a very useful and commonly used feature of SQL, it looks like this –

SELECT * FROM ORDER WHERE OrderId IN (10248, 10249, 10250, 10251)

The will return up to four rows of data, showing just the orders that have an OrderId in the list you passed to the select statement.

You can do the same with Entity Framework by using the Contains predicate with a Where.

First you need to put the OrderIds you are looking for in some sort of enumerable.

IEnumerable ordersToFind = new List { 10248, 10249, 10250, 10251 };

Then you use Where on the Orders DbSet and check the orderIds against the list you just created.

var orders = _northwindContext.Orders.Where(o => ordersToFind.Contains(o.OrderId));

That’s all there is too it. You don’t have to use this on just Entity Framework, it also works on simple lists, arrays, enumerables, etc.