.NET Interactive with SQL!| .NET Notebooks in Visual Studio Code
***Please note this post is outdated. The .NET Interactive Notebooks Extension is now named Polyglot Notebooks. See here to learn more: https://devblogs.microsoft.com/dotnet/dotnet-interactive-notebooks-is-now-polyglot-notebooks/ ***
The .NET Interactive team has collaborated with the Azure Data Studio team to bring SQL integration to .NET Notebooks. SQL notebooks combine the benefits of querying data with the rich visualization of notebooks.
First, you’ll need a Microsoft SQL Server or Azure SQL database. If you already have a SQL database, you can use that. If not, you can follow along with the examples below by installing the following:
- SQL Server Developer edition 2019
- A database – In this blog post, I will be using the Adventure Works 2019 lightweight edition.You’ll also need:
- Visual Studio Code
- The .NET Interactive extension
Creating a new SQL Connection in VS Code
Once you have the requirements listed above installed, you are ready to start using SQL in .NET Interactive Notebooks in VS Code.
To create a new notebook, open the Command Palette(
Ctrl+Shift+P on Windows or
Cmd-Shift-P on macOS), and select
.NET Interactive: Create new blank notebook. You can also create a new notebook with
Ctrl+Shift+Alt+N key combination.
Every notebook has a default language. In our case, it’s
C# (.NET Interactive), and we will need to switch the language to
SQL (.NET Interactive). To change the language in a cell, you can either use a magic command in Jupyter (e.g.
#!sql) or the VS Code notebook language selector. The language selector is in the lower right corner of the cell. Click on it and pick
SQL (.NET Interactive) from the language list.
Test the SQL cell out by writing a simple SQL select statement;
SELECT * FROM table_name and run the cell.
Once the cell execution is complete, a list of instructions appears on connecting to a SQL database.
Connecting to a SQL database
The SQL language support in .NET Interactive is added by installing the
Microsoft.DotNet.Interactive.SqlServer package. We can do this by adding a new
C# (.NET Interactive) cell and running(Click on +Code to add a new cell):
To learn more about connecting to a Microsoft SQL Server Database, execute the help command in a new cell.
#!connect mssql -h
Now that we have all the prerequisites listed above, we can connect to database. To establish a connection, you will need a
--kernel-name and the connection string to the database.
In a new
C# (.NET Interactive) cell, establish a connection and label it
adventureworks and add the connection string. Once this cell is executed the
#!sql-adventureworks sub- kernel is available for use.
We have our database connected so, let’s create a new SQL(.NET Interactive) cell and write a select query that would grab all the Adventure Works database data. Enter this
SELECT * FROM AdventureWorksLT2019 and execute the cell.
After the cell runs, you will notice an info message in the available connections in the output. In this case, we only have the #!sql-adventureworks sub-kernel, but you can connect have multiple databases in a single notebook.
Querying and Visualizing Data
In a new
SQL (.NET Interactive) cell, you can now specify your connection using
#!sql-adventureworks magic command followed by a query against the AdventureWorks database. You will notice in the image below that since we have specified the SQL sub-kernel, we get code completion on the database tables and columns!
Once you have completed writing your SQL query,run the cell. The results of the query are displayed using the nteract Data Explorer,providing a rich interactive filter and visualization experience where the user can explore their data in a number of ways.
- Filter and sort by column – Click on Show Filters
- Using the toolbar, select a preferred visualization:
Entity Framework Core in a SQL Notebook
EF Core is the data access API of choice for .NET developers. Bringing EF Core and SQL together in .NET Interactive Notebooks provides users the quick iteration of notebooks while providing strongly typed APIs, code completion over tables and columns, and Language-Integrated-Query (LINQ). Quickly explore your data’s shape and schema, craft queries with filtering, sorting, aggregations, and projections that seamlessly translate to SQL, and return results for analysis and visualization.
As an example, I am going to demo how you can start leveraging EF Core and quickly. I will demonstrate how you can use
--create-dbcontext option to scaffold a
DbContext that you can access in the C# cell.
Start by adding a new
C#(.NET Interactive) cell and establish a new connection to your database label it
adventureworksEF. In addition to giving a new
--kernel-name, you will need the include the
Your new connection should look like the code snippet below; once you are done, run the cell.
The cell above does the following:
- Scaffolds a
DbContextand initializes an instance of it called
adventureworksEFin the C# kernel.
- Installs the
- Adds a new sub-kernel
In a new
C# (.NET Interactive) cell, you can now start exploring and querying your data using the
adventureworksEF DbContext. You will notice in the image that we continue to get code completion over tables and columns and get visualizations of our data. The visualizations are generated through
dataFrame.Explore() from the nteract data explorer.
Now, since we know the shape of our data, I can write a LINQ to query all the data in my table of a particular order number. For example
var qty = adventureworksEF.SalesOrderDetail.Where(q => q.OrderQty.Equals(4)).ToList();qty.ExploreWithNteract()
And there you have it! A simple demonstration on how you can leverage .NET Interactive notebooks with SQL and EF Core.
- Source code
- .NET Interactive Notebook Extension
- Azure Data Studio
- Introduction to LINQ
Happy interactive programming!
I appreciate all the hard work you guys are putting into .NET Interactive, but I don’t have any use-cases for Jupyter Notebooks. Nor does anyone I know in the industry have any use cases for them. They seem like something great for demos at conferences that have little real-world applications outside (maybe) ad-hoc data science.
What we do use heavily is C# Interactive in Visual Studio. Which was released with little fanfare and then promptly abandoned. We need it ported to .NET Core. We need it integrated with active debug sessions. These two asks are far higher-impact for the vast majority of your customers than anything you could do with Jupyter.
Yup, while I like the progress on things like this… I have to say I’m disappointed on the lack of focus for C# Interactive in Visual Studio. I’ve provided similar feedback as MgSam has suggested many, many times (blogs, UserVoice, VS, GitHub, etc.).
I really hope (https://github.com/dotnet/roslyn/issues/51480) will be completed and added to Visual Studio.
One can hope 🙂
I’d also rather see C# Interactive to work with .NET 5 & .NET Standard 2.0 projects (with option which runtime to use). I need C# Interactive to send its debug into output window in VS and being able to invoke line under debugger (e.g. by pressing F11 Step in, instead of pressing Enter).
Well, that fails at the first hurdle:
Installing package Microsoft.DotNet.Interactive.SqlServer, version –…
PackageManagement Error 3217 Timed out resolving packages, process: ‘C:\Program Files\dotnet\dotnet.exe’ ‘msbuild -v:quiet -restore “C:\Users\richa\AppData\Local\Temp\nuget\21364–e3554cfd-8628-41ad-920b-098be79170a3\Project.fsproj” /nologo /t:InteractivePackageManagement’
And then prevents VS Code from closing:
“The following dirty editors could not be saved to the backup location…”
You can’t close VS Code with a “dirty” interactive notebook open. You have to close the file first, then close VS Code.
r “nuget:Microsoft.DotNet.Interactive.SqlServer, –”
Please give it another try. Some people ran afoul of a preview API change in VS Code notebooks that went out at the same time as our announcement. It should all be working now but please file an issue if you’re still hitting this.
Doesn’t work for me as well. It looks like any non-public NuGet feed in the user’s nuget.config file that requires authentication fails the installation of SQL.
MS team is adding too many features in Azure Data Studio which are only useful to a very small number of developers but not adding a more useful feature like result aggregate to result-pane.
Show excel-like Result Aggregate (Sum/Count/Min/Max/Avg/Nullabe & non-nullable count) on status bar pane. This will be one of the most productive and useful features every SQL developer will like. Think about this.
What about support to postgresql or mysql ?
Adding support for other kinds of databases is trivial for statement execution. You can see our SQLite example here.
Completion support like we’ve enabled for T-SQL takes a bit more work. This preview laid the groundwork for other SQL variants and query languages by integrating with the SQL Tools Service.
Does not install the package. And I see is “SyntaxError: invalid syntax”
I love the idea of this and it works very well so far, the perfect tool to interactively explore the data and iteratively create complex queries. However, do you have any idea when the Microsoft.DotNet.Interactive.SqlServer package will switch from EntityFrameworkCore 3.1.8 to 5.0.4? Just having IQueryable.ToQueryString() available would be very useful, for example.
Glad you are like the experience. Yes, we plan to update EF Core package and are tracking the issue.
hi, please help me in theme Entity Framework of this example:
1) yes work: #!connect mssql –kernel-name mydatabase “Persist Security Info=False; Integrated Security=true; Initial Catalog=AdventureWorks2019; Server=localhost”
2) yes work: #!sql-mydatabase
select * from AdventureWorks2019.Sales.Customer
yes show data
3)#!connect mssql –create-dbcontext –kernel-name mydatabaseEF “Persist Security Info=false; Integrated Security=true; Initial Catalog=AdventureWorks2019; Server=localhost”
yes work: Scaffolding a DBContext….
Installed package Microsoft.EntityFrameworkCore.Design version 3.1.8
Kernel added: #!sql-mydatabaseEF
3) but to use mydatabaseEF.SalesPerson.Explore();
dont show data.
I have Visual Studio Code
Version:1.55.0 (user setup)
Had the same problem: https://github.com/dotnet/interactive/issues/1283
Explore()has a bug but is also obsolete and being removed.
Hi Mr Elon Mallin; thanks a lot, now works.
Thanks for all the effort Maria, but it would be so much more lovely to have a native .NET approach to interactive documentation, using the more recent work of the wider .NET team, rather than attempting to use the .ipynb json serialization format for the notebooks with the C# scripting dialect.
Now that the .NET team is getting the live IL patching implemented (required for the hot reload effort in .NET 6….and even working in the WASM environment in the browser), imagine how amazing a solution would be where:
* Standard .NET 6 projects were used (rather than CSX variants).
* Live IL patching was used to update the running code as things were being edited (which would be many times faster).
* The markdown was simply included (in commented out form) in the source of the project, and a nice web based “notebook” editor allowed you to edit the markdown in a clean way. This should all be possible in .NET 6, even in the browser, with Roslyn, IL patching, etc. It would allow us to use a standard .NET 6 project, and just embed our markdown documentation, but have it appear cleanly, as per the notebook style of UI you are attempting.
My problem is: I just spent 2 hours try to see if .NET Interactive would work for me (as I do for your team’s work about every 6-months), but each time I just hit a wall (even if it a learning wall). I suspect this is what people in the thread say when they comment that it doesn’t work for most .NET developers. Today, the killer for me were things like:
a) Trying to reference an external CS file (into the Interactive .NET CSX variant), so I didn’t instead need to have the entire class appear in the notebook. I spent at least an hour searching around, including looking at the github repos and lots of documentation before giving up.
b) Hitting issues with referencing external packages (I was actually trying to bring in System.Net.Http.Json extensions), and getting annoyed with the .CSX style import syntax and just not being able to ultimately get it working after spending another hour.
We need a more native .NET solution for notebooks. Ultimately, what would win me over is:
a) A standard .NET 5/6 Project running within a notebook type environment.
b) Where edits to code are IL patched live.
c) Where edits to markdown are really just changing embedded code comments.
I disagree wholeheartedly with the “this isn’t useful” sentiment. This is great! Can’t imagine anyone using it?
I have a pile of LINQPad scripts compiled over the years for “doing stuff” and for “troubleshooting” and bug investigations and such. When certain bugs come to my attention, I respond faster by opening the appropriate LINQPad script which already has some of the things I want to “check first.” Things like:
Scripts like these are GREAT. Often, my colleagues are thrilled when I share them, because they not only give a quick-start to the investigation, but they reveal the concepts and things to know, what is involved in this situation, etc. The problem is, LINQPad only has minimal “commenting” capability.
I’m seeing this as a means to take what I’ve done in LINQPad in the past and make the scripts MUCH more user-friendly. Since this works in VSCode, if you combine this with the “CodeTour” extension, I think this is a fantastic way to on-board new developers.
I can use uses for talks at user groups and conferences. Any scenario where you want to communicate both code and “intent.”