Using .NET interactive notebooks in Visual Studio Code

05/12/21
moon moon

Since I have not been able to find much comprehensive information on this, I figured I would write a bit about it to help anyone else that may want to make use of this amazing feature. For those that are unfamiliar, Jupyter notebooks have been integrated into Azure Data Studio and (kind of) Visual Studio Code. The Azure Data Studio notebooks are pretty much standard jupyter notebooks, but with an additional SQL kernel. In VSCode, the story is a little different.

Notebooks in VSCode are, as I understand it, primarily for .NET interactive, and are added by the .NET Interactive Notebooks extension. Once you have this extension, you can create or open an existing notebook. These notebooks are stored in the .dib file format to differentiate them from the .ipynb jupyter format, though you can open the latter as well. This gives you a new kernel: the .NET Interactive Kernel. This kernel allows you to create script blocks using any .NET language and run them individually.

Entity Framework Core and Database Contexts

Entity Framework Core is .NET Core's ORM for MS SQL Server. Lucky you, EF Core is available in interactive notebooks, it just needs a couple specific things.

You can use any SQL Server, but in the spirit of small local notebooks I use LocalDB. LocalDB is a feature of SQL Server Express that comes installed with Visual Studio 2016 or later, and is perfect for local development and testing databases.

Let's set up a LocalDB instance and connect to it. Assuming you have LocalDB installed, whether through Visual Studio or SQL Server Express, you can run SqlLocalDb create <instance_name> where <instance_name> is whatever name you choose to create a new instance. By default, LocalDB instances are created in a stopped state, and you can see that if you run SqlLocalDb info <instance_name>. You'll get an output similar to this:

Name:               <instance_name>
Version:            13.1.4001.0
Shared name:
Owner:              <domain>\<username>
Auto-create:        No
State:              Stopped
Last start time:    5/12/2021 2:20:10 PM
Instance pipe name:

You can then start your LocalDB instance by running SqlLocalDb start <instance_name>.

Before we can connect to LocalDB in .NET, we need the Microsoft.DotNet.Interactive.SqlServer NuGet package to be installed. Fortunately, we can install those temporarily to the active kernel with the #r preprocessor directive. This directive tells the kernel to install the package with the specified version, which in this case is the latest.

#r "nuget:Microsoft.DotNet.Interactive.SqlServer,*-*"

Once you have the SqlServer package installed, you can stick this Jupyter directive in a code block to scaffold a database context.

#!connect mssql --create-dbcontext --kernel-name <dbcontext_object> <connection_string>

For a LocalDB instance, your connection string will likely look something like this: "Data Source=(LocalDb)\<localdb_instance>;Database=<database_name>;Integrated Security=SSPI;".

This requires a database to have already been created in your LocalDB instance. To create a database, I used an Azure Data Studio notebook with a SQL kernel. I believe there is a SQL kernel available in VSCode's notebook so I will likely update this once I have played with it.

Backlinks