Monday, January 21, 2019

Weirdness with EF Core 2 (SQLite), .NET Standard, and .NET Framework

I've run into some issues referencing .NET Standard projects from a .NET Framework project -- specifically in regard to Entity Framework Core 2 and SQLite. I encountered this when moving some of my demo code to newer projects.

Here's the error that I get:

This is a missing DLL. I've managed to come up with 2 workarounds -- neither of which I like -- but they get the projects working.

Let's take a look at the project.

This article is technically *not* part of the More DI series of articles. However, the issue is present in the code described in that series. The code is available on GitHub:

The Projects
The solution consists of 12 projects which are a mix of project types. Here are the full-framework projects:

The PeopleViewer project is a WPF application using .NET Framework 4.7.2.

The test projects are also .NET Framework due to current requirements of NUnit 3.x.

All of the "library" projects are .NET Standard 2.0:

The .NET Standard projects include the Presentation (which includes the view model), the Readers (including the CSV data reader, the SQL data reader, the Service data reader, and all of the decorators), and the Shared projects.

There is one .NET Core project:

This is the WebAPI service that provides data for the Service data reader.

Project References
Project references can get a bit weird when using .NET Standard with .NET Framework. When I was first having issues, I came across Scott Hanselman's article: Referencing .NET Standard Assemblies from both .NET Core and .NET Framework, and this helped. Let's see how.

Here are the dependencies from the "PersonReader.SQL" project (listed in the PersonReader.SQL.csproj file):

We can see the references to Entity Framework Core and SQLite.

The "PeopleViewer" project does not reference either of these NuGet packages directly:

Based on the advice provided in Scott Hanselman's article, I manually edited the "PeopleViewer.csproj" file to add a "RestoreProjectStyle" value of "PackageReference":

This took care of some of my issues: the ServiceReader works. But the SQLReader is still broken.

The Service Data Reader Works
The Service data reader project has a reference to NewtonSoft.Json (which is probably not a surprise):

By using the "PackageReference" setting, the correct version of the Newtonsoft.Json DLLs made it to the output folder of the "PeopleViewer" project.

You can look at the article "Adding Retry with the Decorator Pattern" for more information on running this code.

Be sure to read Scott Hanselman's article for the details on this setting and why it's needed.

The SQL Data Reader is Broken
But even with this setting, the SQL Data Reader is still broken. Let's take a closer look.

First, we'll update the "ComposeObjects" method to use the SQLReader object. This is in the "PeopleViewer" project, App.xaml.cs file -- although the code in the GitHub project is more complex than what we have here since it uses all of the decorators.

This builds a MainWindow that uses a PeopleReaderViewModel that uses a SQLReader.

When we run the application and click the "Refresh People" button, we get the error:

This indicates that we're missing a DLL that's needed for SQLite functionality.

If we check the output folder, we see that we have many Entity Framework DLLs, including one that references SQLite:

But "e_sqlite3.dll" is nowhere to be found.

As a side note, I tried this with the most recent version of EF Core/SQLite (version 2.2.1 at the time of writing) and had the same results.

I managed to work around this in 2 different ways. If there's a better solution, *PLEASE* let me know about it in the comments. I do not like either of these options.

Workaround #1: Include the NuGet Package in the .NET Framework Project
The first workaround I found was to include the Entity Framework Core / SQLite NuGet packages in the "PeopleViewer" project (the WPF application).

This works because the application now has all of the DLLs that it needs:

The "e_sqlite3.dll" is in the "x64" folder (and the "x86" folder as well).

Why I Don't Like This
I don't like this solution because it creates a dependency on a NuGet package that the .NET Framework project (the WPF application) does not explicitly need. The .NET Standard library (the SQL data reader) is the project that needs the package in order to work. This solution seems to mess up the dependency graph.

It can get a bit worse. If the version of the NuGet package referenced by the .NET Framework project is different from the version referenced in the .NET Standard project, there will be runtime errors. (I've run into this with Newtonsoft.Json references.)

Workaround #2: Copying the Needed Files to the Output Folder
The second workaround I came up with is to copy the missing files into the output folder. Since the bulk of the DLLs are making it to the output folder, I figured that just including the "e_sqlite3.dll" that's missing might be the best way to go.

To do this, I grabbed the "x64" and "x86" folders above, and put them into an "AdditionalFiles" folder at the root of the solution.

This folder already exists: it has the text file used by the CSV data reader (People.txt) as well as the SQLite database used by the SQL data reader (People.db). So adding a couple more files doesn't seem like too much of a problem.

The files are copied to the output folder using a post-build event on the "PeopleViewer" project:

This copies the contents of the "AdditionalFiles" folder to the output folder. For more information on Visual Studio build events, refer to "Using Build Events in Visual Studio to Make Life Easier".

The other thing I had to do was explicitly add these files to the Git repository. The .gitignore file doesn't usually include DLLs, so I had to add these two files manually. You can see them in the GitHub repository:

This has the same effect as Workaround #1: the files are added to the output folder:

Why I Don't Like This
The reason I don't like this workaround is the same as above: There's a good chance that the versions will get out of sync in the future. In addition, I don't like the idea of adding executable files to the Git repository.

There is a bug somewhere. I'm not sure if it's a bug in the Entity Framework Core packages (specifically the SQLite package). I'm not sure if it's a conflict between the package management schemes in the .NET Standard project vs. the .NET Framework project. And I have to admit that this is a pretty big frustration for me.

The frustration is 2-fold -- first for myself. I've been coding in the .NET framework for close to 15 years. I really would not expect to have these types of difficulties, even with things that are relatively new. But as a developer, I deal with this type of issue all the time.

The bigger frustration is when I think about other developers. Many developers in the .NET space do not have the type of experience that I have. If I am having difficulty, then that means that there are a large number of other developers who will also have difficulty.

This is even a bigger concern since the bulk of my effort is spent helping developers learn more about C# and .NET -- in particular, I work with the "dark matter developers" described by Scott Hanselman . I have a fear that if we make things too complex in the environment, we will have difficulty getting new developers on board.

This is just one example of what is causing me concerns. The other items are a topic for another day.

Wrap Up
If you have any advice on handling this issue, please let me know. I would file a bug report, but as noted, I'm not exactly sure where the bug is.

If you have run into this same issue, understand that I do not like either of these workarounds. They add concerns about versioning. But they do get the code working, and that's always the first step.

Happy Coding!


  1. Thanks for sharing...i also ran into error that I can't wrap my head around. I am using VS2017 community... Coding in c#, when I want to bind data using EF6 in Winforms by using data grid view combo box cell I get an exception error. That the value is not valid... Please help

  2. Install this package


    1. Thanks for the reference. When I tried it, I ran into the same issue as the other workarounds. The SQLitePCLRaw.bundle_e_sqlite3 package needs to be added to the .NET Framework (WPF) project. On further digging, the Microsoft.EntityFrameworkCore.Sqlite package has a dependency on SQLitePCLRaw.bundle_green (which also contains the e_sqlite3 DLL). It looks like this is the package that does not make it through the dependency process. It could make sense to include the "bundle_green" package in the WPF application, but that doesn't change the versioning concerns.

      Thank you for giving another point of reference. It has helped me narrow down where the bug may lie.

  3. I first encountered that in an aspnet core application with Rlrctron.Net .And that became me defacto solution which has worked so far.

    Anyway, I add the EnsureDbCreated function is called in startup.