Mobile Corner

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).

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.

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).

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.

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.

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.

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).

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

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.

<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>

I've finished setting up the solution structure, so now I can get on with building the application. I'll start by removing the template class, Class1.cs, from the Meetings.Core project. In its place I'll add a Model folder and within the folder create two entity classes, Meeting and Person (see Listing 2).

public class Meeting
{
  public int Id { get; set; }
  public string Subject { get; set; }

  public DateTimeOffset MeetingTime { get; set; } = DateTimeOffset.Now;

  public string Notes { get; set; }

  public ObservableCollection<Person> Attendees { get; set; } = 
    new ObservableCollection<Person>();
}

public class Person
{
  public int Id { get; set; }
  public string Name { get; set; }
  public string Position { get; set; }

  public int MeetingId { get; set; }
   public Meeting Meeting { get; set; }
}

In the Model folder I'll also create a MeetingsContext class, which will be used to wrap the interactions with the SQLite database (the OnConfiguring method defines the name of the SQLite database file to use):

public class MeetingsContext : DbContext
{
  public DbSet<Meeting> Meetings { get; set; }
  public DbSet<Person> People { get; set; }

  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
  {
    optionsBuilder.UseSqlite("Filename=Meetings.db");
  }
}

The MeetingsContext entity will be used to manage instances of the Meeting and Person classes that are stored and retrieved from the SQLite database. 

So far I’ve defined the entity structure that will be populated when working with the SQLite database. However, I need to define what the underlying database schema will be. For this I will use the Entity Framework tools to generate migration code that will define and create the schema for the database. In the Package Manager Console window, I select the Meetings.Core as the Default Project and then invoke the following to generate the migration code:

Add-Migration 'Initial Database Creation'

This will create a Migrations folder and add two files that define the database schema.

It's not a good practice to use the same entity for interacting with the database as well as data binding. Instead, for each page within the application I'll create a corresponding viewmodel. I'll add a ViewModels folder, and subsequently a MainViewModel class, which will hold an instance of the MeetingsContext used to load Meetings from the database. You can see that in Listing 3.

public class MainViewModel:INotifyPropertyChanged
{
  private MeetingsContext Context { get; } = new MeetingsContext();

  public ObservableCollection<Meeting> Meetings { get; } = 
    new ObservableCollection<Meeting>();

  private Meeting selectedMeeting = new Meeting();

  public Meeting SelectedMeeting
  {
    get { return selectedMeeting; }
    set
    {
      if (value == null)
      {
        value = new Meeting();
      }
      selectedMeeting = value;
      OnPropertyChanged();
    }
  }
  public async Task Init()
  {
    await Context.Database.MigrateAsync();
  }

  public async Task Load()
  {
    Meetings.Clear();
    await Context.Meetings.Include(meeting => meeting.Attendees).ForEachAsync(Meetings.Add);
    SelectedMeeting=new Meeting();
  }

  public async Task SaveMeeting()
  {
    if (SelectedMeeting.Id == 0)
    {
      Context.Meetings.Add(SelectedMeeting);
    }
    await Context.SaveChangesAsync();
    await Load();
  }

  public async Task CancelMeeting()
  {
    RejectChanges();
    await Load();
  }


  public void RejectChanges()
  {
    foreach (var entry in Context.ChangeTracker.Entries())
    {
      switch (entry.State)
      {
        case EntityState.Modified:
        case EntityState.Deleted:
          entry.State = EntityState.Modified; //Revert changes made to deleted entity.
          entry.State = EntityState.Unchanged;
          break;
        case EntityState.Added:
          entry.State = EntityState.Detached;
          break;
      }
    }
  }


  public event PropertyChangedEventHandler PropertyChanged;

  protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null)
  {
    PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
  }
}

• Get Code Download