Category Archives: Entity Framework

Pagination with Entity Framework Core and Microsoft MVVM in WinUI 3

In this article we present a LINQ-based pagination solution for list controls as well as a pager user interface, all in a WinUI 3 application. We use SQLite as data provider and a Windows Community Toolkit DataGrid as host control, but the solution applies to any Entity Framework Core (EF Core) store and any WinUI 3 ItemsControl. For the implementation of the MVVM pattern, we chose the usual suspect: Microsoft MVVM Toolkit.

Here’s how the sample page looks like:

This is a new XAML page that we added to the sample app for our previous article on using the DataGrid in WinUI 3.

The heart of our pagination infrastructure is PaginatedList<T> – a subclass of List<T>.

It holds a specific page from a query result as a list of items can be bound to any ItemsControl as ItemsSource. A PaginatedList<T> instance not only embeds the (partial) result of the query but it also exposes the current page number, and the total number of pages for the whole query (quite convenient for a pager UI). The page size (number of records wanted) is not a property but is provided to the constructor.

Here’s how the class definition looks like:

public class PaginatedList<T> : List<T>
{
    public int PageIndex { get; private set; }

    public int PageCount { get; private set; }

    private PaginatedList(List<T> items, int count, int pageIndex, int pageSize)
    {
        PageIndex = pageIndex;
        PageCount = (int)Math.Ceiling(count / (double)pageSize);
        AddRange(items);
    }

    public static async Task<PaginatedList<T>> CreateAsync(
	IQueryable<T> source, 
	int pageIndex, 
	int pageSize)
    {
        int count = await source.CountAsync();
        List<T> items = await source
	.Skip((pageIndex - 1) * pageSize)
	.Take(pageSize)
	.ToListAsync();

        return new PaginatedList<T>(items, count, pageIndex, pageSize);
    }
}

We’re using some of the asynchronous EF Core IQueryable Extensions here, but feel free to add a synchronous version if that better fits your use case.

The PaginatedList is used inside the XAML page’s ViewModel (a Microsoft MVVM Toolkit’s ObservableObject) to produce a List<Mountain> to which the DataGrid is bound. The ViewModel also exposes the current page number and the total number of pages. These are used by the pager on top. Here’s the ViewModel’s code:

public class PaginationPageViewModel : ObservableObject
{
    private int _pageSize = 10;
    private int _pageNumber;
    private int _pageCount;
    private List<Mountain> _mountains;

    public int PageNumber
    {
        get => _pageNumber;
        private set => SetProperty(ref _pageNumber, value);
    }

    public int PageCount
    {
        get => _pageCount;
        private set => SetProperty(ref _pageCount, value);
    }

    public List<Mountain> Mountains
    {
        get => _mountains;
        private set => SetProperty(ref _mountains, value);
    }

    private async Task GetMountains(int pageIndex, int pageSize)
    {
        using MountainDbContext dbContext = new();
        PaginatedList<Mountain> pagedMountains = await PaginatedList<Mountain>.CreateAsync(
            dbContext.Mountains
                .OrderBy(m => m.Rank),
            pageIndex,
            pageSize);
        PageNumber = pagedMountains.PageIndex;
        PageCount = pagedMountains.PageCount;
        Mountains = pagedMountains;
    }
}

Here’s how the ‘current page of Mountains’ is bound to the DataGrid:

<ctWinUI:DataGrid x:Name="DataGrid"
                    ItemsSource="{x:Bind ViewModel.Mountains, Mode=OneWay}"
                    AutoGenerateColumns="False"
                    CanUserSortColumns="False"
                    SelectionMode="Single"
                    IsReadOnly="True"
                    RowDetailsVisibilityMode="Collapsed">
    <ctWinUI:DataGrid.Columns>
        <ctWinUI:DataGridTextColumn Header="Rank"
                                    Binding="{Binding Rank}" />
        <ctWinUI:DataGridComboBoxColumn Header="Mountain"
                                        Binding="{Binding Name}" />
        <!-- More columns -->
    </ctWinUI:DataGrid.Columns>
</ctWinUI:DataGrid>

The ViewModel also exposes 4 commands -implementors of IAsyncRelayCommand– to allow refreshing the DataGrid with a new logical page:

public IAsyncRelayCommand FirstAsyncCommand { get; }

public IAsyncRelayCommand PreviousAsyncCommand { get; }

public IAsyncRelayCommand NextAsyncCommand { get; }

public IAsyncRelayCommand LastAsyncCommand { get; }

We would have loved to use one of the new WinUI Pager controls for user interface, but tody these are available in WinUI 2 only. Therefor we brewed our own CommandBar-based pagination control with the canonical navigation buttons to the first, previous, next, and last pages:

<CommandBar DefaultLabelPosition="Right">
    <AppBarButton ToolTipService.ToolTip="First"
                    Icon="Previous"
                    Command="{x:Bind ViewModel.FirstAsyncCommand, Mode=OneWay}" />
    <AppBarButton ToolTipService.ToolTip="Previous"
                    Icon="Back"
                    Command="{x:Bind ViewModel.PreviousAsyncCommand, Mode=OneWay}" />
    <AppBarElementContainer>
        <TextBlock Text="Page" />
    </AppBarElementContainer>
    <AppBarElementContainer>
        <TextBlock Text="{x:Bind ViewModel.PageNumber, Mode=OneWay}" />
    </AppBarElementContainer>
    <! -- And so on ... -->

Here’s how the AsyncRelayCommands are initialized in the ViewModel with their respective Execute and CanExecute logic:

FirstAsyncCommand = new AsyncRelayCommand(
    async () => await GetMountains(1, _pageSize),
    () => _pageNumber != 1
);
PreviousAsyncCommand = new AsyncRelayCommand(
    async () => await GetMountains(_pageNumber - 1, _pageSize),
    () => _pageNumber > 1
);
NextAsyncCommand = new AsyncRelayCommand(
    async () => await GetMountains(_pageNumber + 1, _pageSize),
    () => _pageNumber < _pageCount
);
LastAsyncCommand = new AsyncRelayCommand(
    async () => await GetMountains(_pageCount, _pageSize),
    () => _pageNumber != _pageCount
);

Each time a new page is fetched and displayed, we need to tell the commands that their CanExecute property was updated so that the corresponding buttons in the pager will enable or disable themselves:

FirstAsyncCommand.NotifyCanExecuteChanged();
PreviousAsyncCommand.NotifyCanExecuteChanged();
// And so on ...

To improve the user experience, we enhanced PaginatedList<T> to implement some edge cases. When the result of the full query is empty (page count is zero), we set the current page number to zero:

int count = await source.CountAsync();
if (count == 0)
{
   // No results -> return page 0.
   return new PaginatedList<T>(new List<T>(), 0, 0, pageSize);
}

This is how the pager then looks like:

When the requested page is out of range (has no records) then PaginatedList<T> returns the last page:

List<T> items = await source.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync();
if (items.Count == 0)
{
    // Requested page is out of range -> return last page.
    pageIndex = (int)Math.Ceiling(count / (double)pageSize);
    items = await source.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync();
}

return new PaginatedList<T>(items, count, pageIndex, pageSize);

Here’s how the pager looks like when we try to navigate to page 1000:

Last but not least, we allow the user to change the page size. We first added the property and a list of possible values to the ViewModel:

public List<int> PageSizes => new() { 5, 10, 20, 50, 100 };

public int PageSize
{
    get => _pageSize;
    set
    {
        SetProperty(ref _pageSize, value);
        Refresh();
    }
}

Then we added a ComboBox and a TextBlock to the AppBar, via AppBarElementContainer instances:

<AppBarSeparator />
<AppBarElementContainer VerticalContentAlignment="Center">
    <ComboBox ItemsSource="{x:Bind ViewModel.PageSizes}"
                SelectedItem="{x:Bind ViewModel.PageSize, Mode=TwoWay}" />
</AppBarElementContainer>
<AppBarElementContainer VerticalContentAlignment="Center">
    <TextBlock Text="rows per page"
                Margin="8 0" />
</AppBarElementContainer>

Here’s how that part of the UI looks like:

paginationpagesize

Finally we implemented the Refresh() method – triggered by a change of the PageSize property. We simply navigate to the first page. Before that we make sure that the command can execute by setting the page number to zero:

private void Refresh()
{
    _pageNumber = 0;
    FirstAsyncCommand.Execute(null);
}

With a handful lines of code and a little help from our friends Microsoft MVVM Toolkit and EF Core we just implemented a WinUI 3 pagination use case. Our sample app lives here on GitHub.

Enjoy!

Using the Windows Community Toolkit DataGrid with WinUI 3 and Entity Framework Core

In this article we demonstrate the Windows Community Toolkit DataGrid in a desktop application powered by the Windows App SDK and a Sqlite relational database. We’ll cover

  • populating the DataGrid,
  • sorting rows via a click on a column header,
  • filtering rows on predefined criteria,
  • grouping rows,
  • searching for rows,
  • instant theme switching,

Here’s how our sample app looks like on Windows 10:

 DatabaseSearch

Recently we started migrating some UWP apps to WinUI 3. Some of these apps have 3rd party DataGrid controls, and we wanted to know whether Community Toolkit DataGrid is a decent candidate to replace these – after all: it’s free. We had no idea how this DataGrid control would behave in a WinUI 3 desktop app, since there are no official samples yet: 

WinUIControlsGallery

On top of that the Community Toolkit DataGrid control is still in its first version which was a port from Silverlight to UWP. When you look at its source you’ll notice that a lot of the code is already 3 years old. Would it run on the brand new Windows App SDK?

For all these reasons we were a bit reluctant to immediately start using this DataGrid in a production WinUI 3 desktop application. So we decided to test drive it first in a more representative setting. This test drive became the sample app that we’re describing in this article.

Migrating the Community Toolkit Sample app

We started our adventure by migrating the UWP DataGrid sample page from the Community Toolkit Sample app to WinUI 3. It has all the functionality we need (filtering, grouping, sorting, theming) and more (editing), and it’s Open Sourced. Here’s how the original looks like:

CommunityToolkitSampleApp

The migration was easier than expected and basically boiled down to updating the “windows.ui.” namespace references to “microsoft.ui.” all over the place. We then applied some cosmetic changes, like adding transparency to the column header background and adding elegance to the CommandBar on top. We also couldn’t resist modernizing the C# syntax here and there.

The Home page of our sample app is the result of the migration from an UWP/WinRT XAML page to WinUI 3/.NET 5:

HomeSort

All features of the original Toolkit demo were successfully ported to our own sample app. You can for example apply a filter to the displayed records:

HomeFilter

You can group records:

HomeGroup

You can sort the records by clicking on a column header:

HomeSearch

The icon in the top right corner op the page allows you to immediately switch between light and dark theme. As long as you stick to ‘lightweight styling’ (i.e. just overriding theme color resources) the following one-liner will do the trick:

Root.RequestedTheme = Root.RequestedTheme == ElementTheme.Light 
	? ElementTheme.Dark 
	: ElementTheme.Light;

From the moment you start to programmatically modify resources, or retemplate (parts of) a control, this instant theme-switching becomes problematic. Not only the control’s XAML but also the defined animations refer to expected colors, opacities, and more – and it’s super hard to override all of these. But let’s focus on DataGrid’s core features.

It takes some development and design effort to implement sorting, filtering, and especially grouping with the Community Toolkit DataGrid. In third party control toolkits such as the ones from DevExpress, Syncfusion, or Telerik [no ranking, just alphabetic order] such features can be configured declaratively and they come with a built-in UI. Nevertheless we were happy with the result of this first page, and decided to build a more modern/representative version of it by bringing some new components to the equation, like

  • Microsoft MVVM Toolkit to replace the custom change propagation code in Models and ViewModels,
  • a Sqlite relational database to replace the CSV file, and
  • Entity Framework Core as an Object-Relational Mapper to run LINQ queries against the data.

Here’s an overview of the NuGet packages – ignore the version numbers, they were all upped multiple times since we made the screenshot:

NuGetPackages

We skipped editing and validation in our sample app, but still all Models and ViewModels were renamed and became children of MVVM Toolkit’s ObservableObject class:

public class Mountain : ObservableObject
{
    private uint _rank;
    private string _name;
    // more fields ...

    // Key

    [Key]
    public int Id { get; set; }

    // Fields

    public uint Rank
    {
        get => _rank;
        set => SetProperty(ref _rank, value);
    }

    // more properties ...

}

For more details on Microsoft MVVM Toolkit, check this introduction. In the rest of this article we’ll focus on Entity Framework (EF) Core, since that is most probably new to UWP developers who are migrating to Windows App SDK.

Configuring Entity Framework Core

For the enterprise sample we replaced the .csv file with a relational Sqlite database. An Entity Framework DbContext was defined to host a table with Mountain entities – a DbSet<Mountain>. Everything is persisted in the app’s local folder:

public class MountainDbContext : DbContext
{
    public DbSet<Mountain> Mountains { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        string path = Path.Combine(
	ApplicationData.Current.LocalFolder.Path, 
	"mountains.db");

        string connectionStringBuilder = new
            SqliteConnectionStringBuilder()
        {
            DataSource = path
        }
            .ToString();

        SqliteConnection connection = new SqliteConnection(connectionStringBuilder);
        optionsBuilder.UseSqlite(connection);
    }
}

Before accessing it, the ViewModel ensures that the database is created:

dbContext.Database.EnsureCreated();

This checks whether the database exists -it may of may not have been deployed with your app- and if not it creates an empty one. Our sample app then reads the original .CSV file and populates the Mountains table with it. Now we can write LINQ queries against the data, such as this one to get all of the Mountains:

public async Task<IEnumerable<Mountain>> AllMountainsAsync()
{
    using (MountainDbContext dbContext = new())
    {
        return await dbContext.Mountains
	.OrderBy(m => m.Rank)
	.AsNoTracking()
	.ToListAsync();
    }
}

Observe that EF Core comes with an asynchronous version of ToList(). When you’re not modifying the content, then it’s a good idea to disable EF change tracking, that’s what the AsNoTracking() does.

Making a read-only DataGrid

The DataGrid definition is similar to the one in the Home page, except that we made the grid read-only since we’re not huge fans of editable DataGrids:

<ctWinUI:DataGrid x:Name="DataGrid"
                    AutoGenerateColumns="False"
                    CanUserSortColumns="True"
                    Sorting="DataGrid_Sorting"
                    LoadingRowGroup="DataGrid_LoadingRowGroup"
                    SelectionMode="Single"
                    IsReadOnly="True">
    <ctWinUI:DataGrid.Columns>
        <ctWinUI:DataGridTextColumn Header="Rank"
                                    Binding="{Binding Rank}"
                                    Tag="Rank" />
        <ctWinUI:DataGridComboBoxColumn Header="Mountain"
                                        Binding="{Binding Name}"
                                        Tag="Name" />
        <!-- More columns ... -->
    </ctWinUI:DataGrid.Columns>
</ctWinUI:DataGrid>

It does not make sense to allow selecting individual cells, so we implemented “row-selection mode” by turning the focus brushes for individually selected cells transparent:

<SolidColorBrush x:Key="DataGridCellFocusVisualPrimaryBrush">Transparent</SolidColorBrush>
<SolidColorBrush x:Key="DataGridCellFocusVisualSecondaryBrush">Transparent</SolidColorBrush>

Be aware that Community Toolkit DataGrid does not support the {x:Bind} syntax for column property bindings, even when you provide bindings for DataContext and ItemsSource:

xBind

Drawing a Template Column

The apps that we’re migrating use templated columns, so we looked for an excuse to define a DataGridTemplateColumn in this sample app. We decided to display the height of the Mountain as a Slider. Here’s the template, observe that the use of {x:Bind} is supported, at least when you declare the DataType :

<ctWinUI:DataGridTemplateColumn Header="Height"
                                Tag="Height">
    <ctWinUI:DataGridTemplateColumn.CellTemplate>
        <DataTemplate x:DataType="models:Mountain">
            <Grid Background="Transparent"
                    ToolTipService.ToolTip="{x:Bind HeightDescription}">
                <Slider Minimum="7200"
                        Maximum="8848"
                        Value="{x:Bind Height}"
                        IsHitTestVisible="False"
                        IsTabStop="False" />
            </Grid>
        </DataTemplate>
    </ctWinUI:DataGridTemplateColumn.CellTemplate>
</ctWinUI:DataGridTemplateColumn>

Here’s how the page now looks like:

DatabaseTemplate

We kept IsEnabled to true to allow the Slider to keep its accent color instead of being grayed out, and set both IsHitTestVisible and IsTabStop to false to prevent user interaction.

Applying a Filter

Here are the LINQ queries that correspond to the filter options:

case FilterOptions.Rank_High:
    return await dbContext.Mountains
	.Where(m => m.Rank > 50)
	.OrderBy(m => m.Rank)
	.AsNoTracking()
	.ToListAsync();

case FilterOptions.Height_High:
    return await dbContext.Mountains
	.Where(m => m.Height > 8000)
	.OrderBy(m => m.Rank)
	.AsNoTracking()
	.ToListAsync();

As already mentioned, third party libraries have built-in UI to allow your end user to define and apply his own filters. Check this article for an example. Here’s how a filtered dataset looks like in our sample app – with less rows, and an extra indicator on the command button to notify the user that a filter is applied:

DatabaseFilter

Sorting by clicking a column header

For the column sort feature, we wanted to avoid a huge switch statement with a different LINQ expression for each column – as in the original sample. We went for a solution that takes the name of the column and the sort direction as parameter:

public async Task<IEnumerable<Mountain>> SortedMountainsAsync(
	string sortBy, 
	bool ascending)
{
    using (MountainDbContext dbContext = new())
    {
        return await dbContext.Mountains
	.OrderBy(sortBy, !ascending)
	.AsNoTracking()
	.ToListAsync();
    }
}

The OrderBy() in the previous code is an -admittedly cryptic- extension method that builds the appropriate LINQ expression:

public static IOrderedQueryable<TEntity> OrderBy<TEntity>(
    this IQueryable<TEntity> source,
    string orderByProperty,
    bool desc)
{
    string command = desc ? "OrderByDescending" : "OrderBy";
    Type type = typeof(TEntity);
    PropertyInfo property = type.GetProperty(orderByProperty);
    ParameterExpression parameter = Expression.Parameter(
            type,
            "p");
    MemberExpression propertyAccess = Expression.MakeMemberAccess(
            parameter,
            property);
    LambdaExpression orderByExpression = Expression.Lambda(
            propertyAccess,
            parameter);
    MethodCallExpression resultExpression = Expression.Call(
            typeof(Queryable),
            command,
            new Type[] { type, property.PropertyType },
            source.Expression, Expression.Quote(orderByExpression));
    return (IOrderedQueryable<TEntity>)source
            .Provider
            .CreateQuery<TEntity>(resultExpression);
}

When working with EF or EF Core, it’s always good to have IQueryable extensions like this hanging around.

Implementing the Mode

Clicking on a column header (or on one of the command bar buttons) changes the ItemsSource of the DataGrid but also changes its UI (arrow indicators in the column header, and command bar decorators above the grid). You have to make sure that all ‘old’ arrow indicators are removed when you apply a filter -something that was overlooked in the official Toolkit Sample App.

We centralized the UI logic behind a “mode switch” in a PropertyChangedCallback for ItemsSourceProperty:

protected override void OnNavigatedTo(NavigationEventArgs e)
{
    _token = DataGrid.RegisterPropertyChangedCallback(
	ctWinUI.DataGrid.ItemsSourceProperty,
	DataGridItemsSourceChangedCallback);
    base.OnNavigatedTo(e);
}

protected override void OnNavigatedFrom(NavigationEventArgs e)
{
    DataGrid.UnregisterPropertyChangedCallback(
	ctWinUI.DataGrid.ItemsSourceProperty, 
	_token);
    base.OnNavigatedFrom(e);
}

private void DataGridItemsSourceChangedCallback(DependencyObject sender, DependencyProperty dp)
{
    // Remove Sort Indicators.
    if (dp == ctWinUI.DataGrid.ItemsSourceProperty)
    {
        foreach (var column in (sender as ctWinUI.DataGrid).Columns)
        {
            column.SortDirection = null;
        }
    }

    // Other display mode dependent UI logic
    // ...
}

Here’s how we implemented the button decorators in the command bar to indicate the active mode – as an Icon in an AppBarElementContainer that has a negative margin:

<CommandBar DefaultLabelPosition="Right"
            Background="Transparent"
            VerticalAlignment="Center">
    <AppBarButton Icon="Filter"
                    Label="Filter"
                    Width="80">
        <AppBarButton.Flyout>
            <MenuFlyout>
                <!-- Menu items -->
            </MenuFlyout>
        </AppBarButton.Flyout>
    </AppBarButton>
    <AppBarElementContainer x:Name="FilterIndicator"
                            Visibility="Collapsed"
                            Margin="-16 0 0 0">
        <FontIcon Glyph=""
                    FontSize="12"
                    Foreground="Coral"
                    VerticalAlignment="Top" />
    </AppBarElementContainer>

    <!-- More buttons and indicators -->

</CommandBar>

In the future InfoBadge might be a nice alternative but that control is not yet pushed to WinUI 3.

Searching for records

Here’s our initial LINQ query behind the search feature:

return await dbContext.Mountains
            .Where(m => m.Name.Contains(queryText))
            .OrderBy(m => m.Rank)
            .AsNoTracking()
            .ToListAsync();

At first sight no surprises here, except that … it’s case sensitive:

DatabaseSearchWithContains

The LINQ Provider for Sqlite (in contrast to the one for SQL Server) translates Contains() to a case sensitive comparison. The following modification to the query only makes things worse:

return await dbContext.Mountains
            .Where(m => m.Name.Contains(queryText, StringComparison.InvariantCulture)) // Crashes at runtime
            .OrderBy(m => m.Rank)
            .AsNoTracking()
            .ToListAsync();

It will crash at runtime since the LINQ Provider does not know how to translate this C# to its SQL syntax. Again you’ll find enough extension methods to solve this … but in this case EF Core itself has one: EF.Functions.Like(). Here’s how it’s used to generate a SQL LIKE:

return await dbContext.Mountains
            .Where(m => EF.Functions.Like(m.Name, $"%{queryText}%"))
            .OrderBy(m => m.Rank)
            .AsNoTracking()
            .ToListAsync();

Here’s how the result of the non-case-sensitive search looks like in the sample app:

DatabaseSearch

Grouping rows

This is the first version of our LINQ query for the grouping feature:

IEnumerable<GroupInfoCollection<string, Mountain>> query = dbContext.Mountains
                .OrderBy(m => m.Range)
                .ThenBy(m => m.Rank)
                .AsNoTracking()
                .GroupBy(m => m.Range, (key, list) => new GroupInfoCollection<string, Mountain>(key, list));

Bad luck: the LINQ Provider for Sqlite does not know how to translate this C# into SQL syntax, and crashes at runtime:

MissingGroupByLinq

We opted to bail out of the Sqlite Provider with a ToList() in the middle of the expression. The GroupBy() is then not applied to the query (an IQueryable) but to the result of the query (an IEnumerable). The responsibility was passed to the native .NET 5 LINQ provider, who knows how to handle this:

IEnumerable<GroupInfoCollection<string, Mountain>> query = dbContext.Mountains
                .OrderBy(m => m.Range)
                .ThenBy(m => m.Rank)
                .AsNoTracking()
                .ToList()
                .GroupBy(m => m.Range, (key, list) => new GroupInfoCollection<string, Mountain>(key, list));

Here’s how grouping looks like in our sample app:

DatabaseGroup

Showing Row Details

The command bar has a button to reveal (or hide) the details of the selected row:

DatabaseDetails

We prefer this pattern over the ‘every-click-opens-details-row’ that’s built into the DataGrid. Firstly because you cannot unselect a row – clicking on the selected row does not unselect it. The only way to close the details row is selecting another row – which then opens a new details row. Secondly we believe that in most cases it’s better to display the details not inside the DataGrid but in a separate place (navigate to another page, open a dialog, …).

Stability

Running V1 of a ported Silverlight control in an application ecosystem that’s only in v0.8: what could possibly go wrong? Smile 

When browsing the open DataGrid related issues on GitHub, you encounter some stability issues. When we started building the sample app, we ran into some of these. When rapidly scrolling, switching mode, and clicking column headers, the DataGrid control gave up rather quickly. For the sake of completeness: with the same behavior we also managed to crash all of the 3rd party sample apps.

We were relieved to observe that most if not all of the performance and stability issues were solved with the release of Windows App SDK 0.8.2! From that release on the DataGrid is behaving as expected.

Conclusion

As far as we are concerned, Community Toolkit DataGrid on WinUI 3 has passed the tests, and is ready for prime time. Some of the features -like filtering and grouping- require more design and development effort than you would have with third party components. This is a challenge, but it’s also an opportunity for you to come up with a friendly non-technical user experience for these scenarios. Here are a couple of examples (NOT included in the sample app) of custom filtering and grouping that would even be hard with third party grids.

  • Removing the Lagers from a list of beer styles. This is a filter on a substring in a hidden column:

Sample

  • Grouping a list of hops by origin. This is a grouping on an n-to-n relation:

Sample2

It’s great for the end user to see the most relevant filtering and groupings of the data and just select one from a menu.

Our sample app lives here on GitHub.

Enjoy!

Getting started with SQLite and Entity Framework on UWP

In this article we’ll show how to use Entity Framework Core and SQLite in a UWP app. We’ll use the code-first approach to define and generate a local SQLite database with one table, and and then perform all CRUD operations against that table. For the data definition as well as the data manipulation, we’ll be using only the Entity Framework API – no native SQL involved.

I created a more or less representative sample XAML and C# UWP app that

  • configures a SQLite database,
  • creates an entity class with properties of different data types (including a date and an image) using the code-first approach,
  • supports evolution of that data model,
  • demonstrates selects, inserts, updates, and deletes against a table, and
  • does all of that in a MVVM architecture.

Here’s how the sample app looks like in ‘browse’ mode, the uses can scroll through a list of Person entities and select one to Edit or Delete, or hit a New button:

SQLiteEF_1

And here’s the app ‘edit’ mode, where the user can update an entity or create a new one and Save it– with the option to Cancel the operation:

SQLiteEF_2

Entity Framework

A long time ago, in a .NET galaxy not far away, the Entity Framework (EF) became the de-facto standard object-relational mapper. EF enables developers to work with relational data by using domain-specific objects. Its most recent version –EF Core- is not an evolution from its previous versions, but a complete rewrite from scratch. The new EF Core not only runs on the full fledged .NET stack – where so you can use it in Windows Forms, ASP.NET, WPF, and/or WCF. EF Core also runs on the ‘lighter’ .NET versions: .NET Core, ASP.NET Core, and the Universal Windows Platform (UWP). The list of databases that you can connect to with EF Core includes all SQL Server editions, SQLite, PostgreSQL, MySQL, IBM Data Servers (DB2 and IDC) and even in-memory data (for testing). All of these totally different platforms can be accessed using the same C# code. For more info on Entity Framework, start reading here. The EF Core specific documentation is here, its source code lives here.

SQLite

SQLite is a free public-domain transactional SQL database engine. The SQLite engine runs embedded in your apps – there is no separate server or background process. A SQLite database can host multiple tables, indexes, triggers, and views, and is contained in one single disk file with a cross-platform format. On the Unified windows Platform, SQLite is the de-facto standard for local single-user databases. If you want to know more about SQLite, start reading here.

Enough introduction, let’s dive into Visual Studio.

Prerequisites

If you want to use SQLite and EF Core in a UWP app, you need to install the following NuGet packages:

  • EntityFramework.SQLite, and
  • EntityFramework.Commands

EFSqLiteDependencies

Both packages are currently in prerelease, so don’t forget to select the Include Prerelease checkbox, or use the –Pre flag if you prefer to type the commands manually:

  • Install-Package Microsoft.EntityFrameworkCore.SQLite –Pre
  • Install-Package Microsoft.EntityFrameworkCore.Tools –Pre

Building the Model

In the code-first EF approach, you start by building the model. The framework will create the code that generates the database schema at runtime. The model consists of a set of regular C# class definitions. It is possible to use attributes on top of class and property definitions to specify the name of the tables and columns that will be generated, and to specify primary keys, maximum lengths, foreign key relations, and even inheritance relations. All of these are documented here. On top of that, EF uses some conventions. An example: if you use Id as a property name, the corresponding column will be defined as primary key.

I personally prefer to keep the entity classes as simple as possible, hence without a dependency to the EF libraries. That makes them more reusable (e.g. as Data Transfer Object or as instance variable inside a ViewModel). So the Person entity from the sample app is nothing more than a Plain Old CLR Object:

/// <summary>
/// Represents a person.
/// </summary>
internal class Person
{
    /// <summary>
    /// Gets or sets the identifier.
    /// </summary>
    public int Id { get; set; }

    /// <summary>
    /// Gets or sets the name.
    /// </summary>
    public string Name { get; set; }

    /// <summary>
    /// Gets or sets the description.
    /// </summary>
    public string Description { get; set; }

    /// <summary>
    /// Gets or sets the day of birth.
    /// </summary>
    public DateTime DayOfBirth { get; set; }

    /// <summary>
    /// Gets or sets the picture.
    /// </summary>
    /// <remarks>Is a blob in the database.</remarks>
    public byte[] Picture { get; set; }
}

Creating the DbContext

When you defined the model, you need to specify which of these classes will end up in the database schema. All entities of which you create a DbSet inside a DbContext will be picked up by EF and added to the schema.

Here are some DbContext methods that you may need or want to override:

  • OnConfiguring allows you to specify the provider and its options, and
  • OnModelCreating allows you to refine the physical data model through a fluent API (which I personally prefer over using data annotations in the model classes). The documentation on this is spread over all articles here (look in each article for the paragraph with the title  ‘Fluent API’).

The following code snippet from the sample app specifies that we will use the SQLite engine, and makes the Id and Name columns NOT NULL allowed:

/// <summary>
/// The Entity Framework Database Context.
/// </summary>
public class PersonContext: DbContext
{
    internal DbSet<Person> People { get; set; }

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

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Make Id required.
        modelBuilder.Entity<Person>()
            .Property(p => p.Id)
            .IsRequired();

        // Make Name required.
        modelBuilder.Entity<Person>()
            .Property(p => p.Name)
            .IsRequired();
    }
}

[Making the Id property required is an obsolete action, since it will be defined through convention as a required auto-incrementing primary key.]

Code Generation and Database Migrations

At this stage, the physical database is still not created yet. We still need to generate the code for that. The database file is not part of the package that you deploy, but it is created by running the app. EF provides two approaches for this: a call to EnsureCreated (sorry: I found no URL to a description of this method) or using Migrations. It’s fairly safe to assume that in most apps the data model will evolve during the lifecycle. The preferred technique in this scenario is to rely on the Code First Migrations feature. Here’s how to enable and use this feature.

Make sure that you added the “EntityFramework.Commands” NuGet package to your solution, and click “Tools –> NuGet Package Manager –> Package Manager Console” to open the console. Run “Add-Migration MyFirstMigration” to generate the classes that will be used to create the initial set of tables for your model:

EFSqLiteFirstMigration

If you want to know more about it, here’s a tutorial on EF Core migration commands.

The Add-migration command does not create the database. It creates a snapshot of you current model:

[DbContext(typeof(PersonContext))]
partial class PersonContextModelSnapshot : ModelSnapshot
{
    protected override void BuildModel(ModelBuilder modelBuilder)
    {
        modelBuilder
            .HasAnnotation("ProductVersion", "7.0.0-rc1-16348");

        modelBuilder.Entity(
	"XamlBrewer.Uwp.SqLiteEntityFrameworkSample.Models.Person", 
	b =>
            {
                b.Property<int>("Id")
                    .ValueGeneratedOnAdd();

                b.Property<DateTime>("DayOfBirth");

                b.Property<string>("Description");

                b.Property<string>("Name")
                    .IsRequired();

                b.Property<byte[]>("Picture");

                b.HasKey("Id");
            });
    }
}

It also generates (but does not execute!) the code that upgrades the model to a newer version, or downgrades it to a previous version:

public partial class MyFirstMigration : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Person",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("Sqlite:Autoincrement", true),
                DayOfBirth = table.Column<DateTime>(nullable: false),
                Description = table.Column<string>(nullable: true),
                Name = table.Column<string>(nullable: false),
                Picture = table.Column<byte[]>(nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Person", x => x.Id);
            });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable("Person");
    }
}

You want to make sure that your end users generate their first version of their local database when they run the app for the first time. You also want to make sure that they migrate to the new data model after they installed an upgrade of your app. I’m pretty sure that you don’t want your end users to run install and upgrade scripts, so your app needs to automagically take care of the migration.

All you need to do to install or upgrade the database, is call DataBase.Migrate() e.g. when the app starts up. I put that code in the static constructor of the DbContext class, so it will be called the first time that the user tries to touch the database:

static PersonContext()
{
    using (var database = new PersonContext())
    {
        database.Database.Migrate();
    }
}

The database and the tables now exist, let’s now play with the content.

CRUD Operations

Every entity in the data model is represented in the DbContext by a DBSet<TEntity> class. You can use LINQ or SQL to get data from these collections. Here are some typical SELECT operations, from the sample app:

internal static List<Person> GetAllPersons()
{
    using (var db = new PersonContext())
    {
        return db.People.ToList();
    }
}

internal static Person GetPersonById(int id)
{
    using (var db = new PersonContext())
    {
        return (from p in db.People
                where p.Id.Equals(id)
                select p).FirstOrDefault();
    }
}

For INSERT and UPDATE operations, it suffices to bring the entities in the context and call the SaveChanges() method:

internal static void SavePerson(Person model)
{
    using (var db = new PersonContext())
    {
        if (model.Id > 0)
        {
            db.Attach(model);
            db.Update(model);
        }
        else
        {
            db.Add(model);
        }

        db.SaveChanges();
    }
}

There’s more info on EF Core entity change tracking and persistence right here.

When you want to DELETE an entity, just call Remove():

internal static void DeletePerson(Person model)
{
    using (var db = new PersonContext())
    {
        db.Remove(model);
        db.SaveChanges();
    }
}

Code and Credits

The sample app lives here on GitHub. It contains some useful code that was not discussed in this article, like how to transform the profile picture to and from a byte array, and how to two-way bind the birthday to a DatePicker control.

The awesome Game of Thrones caricatures are made by Paul Moyse.

Enjoy!