Build Data Applications for the Universal Windows Platform Using Entity Framework and SQLite -- Visual Studio Magazine

Build Data Applications for the Universal Windows Platform Using Entity Framework and SQLite

Saving and retrieving data is a fundamental part of all but the simplest of applications. In this article, Nick walks through getting started using Entity Framework with SQLite to persist application data.

By Nick Randolph
12/15/2016

In all but the simplest of applications, it's necessary to be able to save and retrieve data. This might be just settings or it might be data that users have entered. The Universal Windows Platform (UWP) has support for working directly with files, as well as support for storing data in a SQLite database. However, to save and retrieve data easily, developers working with SQLite often have to rely on third-party NuGet packages.

In parallel, Microsoft has released many versions of Entity Framework for connecting to server-side databases, such as SQL Server. Entity Framework is an ORM, providing the translation between the object world of .NET and the underlying data store. By releasing Entity Framework for SQLite, Microsoft has significantly reduced the effort required for developers to work with data within UWP apps.

It's worth noting that Entity Framework is compatible with .NET Standard 1.3, meaning that it can be used across a wide range of platforms, such as Xamarin, making it great for cross-platform applications.

In this article I'll put together a simple application that will record meeting notes and attendees. I'll start by creating a new UWP app, Meetings, by selecting the Blank App (Universal Windows) template in the New Project dialog (shown in Figure 1).

**[Click on image for larger view.]** *Figure 1.* Creating a New Project

Since the original release of Windows 10 (Build 10240) there have been two version updates, Build 10586 and Build 14393. When creating a new UWP app I'm prompted to select the Target and Minimum versions (Figure 2). In this case, to ensure the maximum reach of the application, I've selected Build 10240 as the Minimum Version.

**[Click on image for larger view.]** *Figure 2.* Target and Minimum Versions

Even though in this scenario I'm only building a UWP app, it's important that I separate the UI (that is, the UWP app itself) from the application logic. This allows for the application logic to be used if I target another platform, but it also helps ensure a clear separation of concerns and aids in making the application logic easier to test. To this end I'm going to add a new project, Meetings.Core, based on the Class Library (Portable) template (Figure 3).

**[Click on image for larger view.]** *Figure 3.* Adding a Class Library

When creating a portable class library (PCL) I'm prompted to select which platforms I want to target. Figure 4 illustrates that I've only selected the Windows Universal 10.0 platform. In fact, what I select at this point is irrelevant, as I'll show in the next step.

**[Click on image for larger view.]** *Figure 4.* Selecting a Target Platform

After creating the library, I need to change the Targeting from being based on a PCL Profile (that is, by selecting target platforms) to being based on .NET Standard. Double-clicking the Properties node under the Meetings.Core node in the Solution Explorer window opens the Meetings.Core project properties, shown in Figure 5.

**[Click on image for larger view.]** *Figure 5.* Targeting a PCL Profile

Figure 5 illustrates that the Meetings.Core library currently targets the Windows platform. Clicking the Target .NET Platform Standard link at the bottom of the Targeting section of the Library tab allows me to configure the Meetings.Core library to conform to one of the .NET Standards. Figure 6 shows how the Targeting section now appears -- note that I've selected .NETStandard 1.3, which is the minimum level required to support Entity Framework for SQLite.

**[Click on image for larger view.]** *Figure 6.* Targeting a .NET Standard

To complete the process of adding the Meetings.Core library I need to add it as a reference to the UWP project itself. The Reference Manager can be opened by right-clicking on the UWP project, Meetings, in Solution Explorer and selecting Add | Reference.

To get started with Entity Framework for SQLite I need to add a reference to the appropriate NuGet packages. Before doing this I recommend upgrading all existing NuGet packages for both the UWP app and the library to the latest stable version. As a general rule of thumb it's a good idea to upgrade the NuGet packages an application references to take advantage of improvements, bug fixes and new features that are added to NuGet packages. Doing this periodically avoids having to make major changes in the future when multiple NuGet packages change, or if you're forced to upgrade for some reason (for example, if you add a new NuGet package that requires a newer version of a package your application is already referencing). In the case of the Meetings application it's both the .NET Standard Library and Microsoft.NETCore.UniversalWindowsPlatform NuGet packages that requires updating (the latter is required before adding a reference to the Entity Framework SQLite package).

**[Click on image for larger view.]** *Figure 7.* Adding a Reference

The main NuGet package required for Entity Framework for SQLite is Microsoft.EntityFrameworkCore.Sqlite, which can be added either by going to Manage Packages for the Solution (or Project), or via the Package Manager Console. To open the management interface for NuGet, right-click on the solution node in Solution Explorer and select Manage NuGet Packages for the Solution. Alternatively, the Package Manager Console can be opened from the Other Windows item on the View menu.

At the time of this writing the Microsoft.EntityFrameworkCore.Sqlite package wasn't appearing when searching the Microsoft and .NET package source -- if you're using the Manage Packages UI to search for the package, make sure you set the Package source to nuget.org.

I'm going to add a reference to Microsoft.EntityFrameworkCore.Sqlite via the Package Manager Console, shown in Figure 8:

Install-Package Microsoft.EntityFrameworkCore.Sqlite

**[Click on image for larger view.]** *Figure 8.* Installing Entity Framework for SQLite

Note that I've set the Default project to Meetings.Core, rather than the UWP project, Meetings.

Entity Framework has a number of associated tools that can be invoked from the Package Manager Console that assist with creating and updating the database schema for the application. To use these tools, they first need to be installed into the UWP project. In the Package Manager Console I need to set the Default project back to Meetings and then install the Microsoft.EntityFrameworkCore.Tools package. This package is still in preview, so I need to append the -Pre flag:

Install-Package Microsoft.EntityFrameworkCore.Tools -Pre

Alternatively in the Manage Package user interface, you need to check the "Include prerelease" option to see the Tools package in the search results.

At the time of writing the prerelease version of the Tools has a known issue, preventing the tools from working without assembly binding redirects specified in an app.config file. I need to add the app.config file in Listing 1 to the UWP project, Meetings.

Listing 1: Configuration File for Meetings App

<configuration>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="System.IO.FileSystem.Primitives" 
          publicKeyToken="b03f5f7f11d50a3a" culture="neutral" />
        <bindingRedirect oldVersion="4.0.0.0" newVersion="4.0.1.0"/>
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Threading.Overlapped" 
          publicKeyToken="b03f5f7f11d50a3a" culture="neutral" />
        <bindingRedirect oldVersion="4.0.0.0" newVersion="4.0.1.0"/>
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.EntityFrameworkCore" 
          publicKeyToken="adb9793829ddae60" culture="neutral" />
        <bindingRedirect oldVersion="1.0.0.0" newVersion="1.0.1.0"/>
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.EntityFrameworkCore.Relational" 
          publicKeyToken="adb9793829ddae60" culture="neutral" />
        <bindingRedirect oldVersion="1.0.0.0" newVersion="1.0.1.0"/>
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>