Entity Framework Core 3: projections
Introduction
Learn how to read data into a C# Windows Forms application efficiently utilizing projections along with when to use asynchronous read operations rather than perform the equivalent of SELECT * FROM in conventional methods using a connection and command to read data into a DataSet or DataTable.
What is a projection?
A projection is just a way of mapping one set of properties to another. In relation to Entity Framework specifically, it's a way of translating a full entity (database table) into a C# class with a subset of those properties. The values can also be altered/joined/removed. Most databases are relational which means with Entity Framework Core (and Entity Framework 6) tables have relations e.g. Orders have order details. When scaffolding a database without fine tuning the DbContext orders will have a order details navigation which with EF Core will not be populated unless using .Include extension while in another case there is a table named categories which has an image property which is not always needed to populate a ComboBox or ListBox which means the image is included no matter. Also, categories happens to have a navigation property for Products which means products property although empty will be included. Then there is the case products are required in another part of the application so an Include is used and the method to read categories is used for populating a ComboBox/ListBox there is more data then needed.
public partial class Categories
{
public Categories()
{
Products = new HashSet<Products>();
}
public int CategoryId { get; set; }
public string CategoryName { get; set; }
public string Description { get; set; }
public byte[] Picture { get; set; }
public virtual ICollection<Products> Products { get; set; }
}
Conventional read for populating a Windows Form control which includes images, no products.
public static List<Categories> Simple()
{
return Context.Categories.ToList();
}
Suppose products are included not knowing this is being done, lack of understanding EF Core.
public static List<Categories> Simple()
{
return Context.Categories.Include(category => category.Products).ToList();
}
This is the result.
If the categories are for display only as coded the ChangeTracker will watch for changes which means more resources are being used then needed.
Real world projection usage
Keeping with working with categories, take the category model presented above, create a folder in a project named projections followed by creating a class named Category. Copy properties CategoryId and CategoryName from Categories class into the class. Next to create the projection we use a Expression and Func to create a method which will form a Category from Categories.
using System;
using System.Linq.Expressions;
namespace AsyncOperations.Projections
{
public class Category
{
public int CategoryId { get; set; }
public string CategoryName { get; set; }
public override string ToString() => CategoryName;
/// <summary>
/// This projection simplifies a lambda select
/// </summary>
public static Expression<Func<Categories, Category>> Projection =>
category => new Category()
{
CategoryId = category.CategoryId,
CategoryName = category.CategoryName
};
}
}
To use the projection, in a class with access to the DbContext use the project in the .Select as shown below. Note the use of AsNoTracking as the categories are display only.
public static List<Category> GetCategoriesAllNotTrackedProjections()
{
return Context.Categories
.AsNoTracking()
.Select(Category.Projection)
.ToList();
}
When to use projections
In cases when data will not be modified as in the example for categories or when a slightly different scenario where data is read only with options to say place an order, products would be presented per category.
Productions model where specific properties have change notification as they are expected to change at runtime.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Runtime.CompilerServices;
namespace AsyncOperations
{
public partial class Products : INotifyPropertyChanged
{
private string _productName;
private bool _process;
public Products()
{
OrderDetails = new HashSet<OrderDetails>();
}
[NotMapped]
public bool Process
{
get => _process;
set
{
_process = value;
OnPropertyChanged();
}
}
public int ProductId { get; set; }
[Required]
public string ProductName
{
get => _productName;
set
{
_productName = value;
OnPropertyChanged();
}
}
public int? SupplierId { get; set; }
public int? CategoryId { get; set; }
public string QuantityPerUnit { get; set; }
public decimal? UnitPrice { get; set; }
public short? UnitsInStock { get; set; }
public short? UnitsOnOrder { get; set; }
public short? ReorderLevel { get; set; }
public bool Discontinued { get; set; }
public DateTime? DiscontinuedDate { get; set; }
public virtual Categories Category { get; set; }
public virtual Suppliers Supplier { get; set; }
public virtual ICollection<OrderDetails> OrderDetails { get; set; }
public event PropertyChangedEventHandler PropertyChanged;
protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null)
{
PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
}
}
}
While a projection has properties for display and primary keys using the same pattern as with category projection.
using System;
using System.Linq.Expressions;
namespace AsyncOperations.Projections
{
public class Product
{
public int ProductId { get; set; }
public string ProductName { get; set; }
public int? SupplierId { get; set; }
public string SupplierName { get; set; }
public string QuantityPerUnit { get; set; }
public decimal? UnitPrice { get; set; }
public short? UnitsInStock { get; set; }
public short? UnitsOnOrder { get; set; }
public short? ReorderLevel { get; set; }
public override string ToString() => ProductName;
public static Expression<Func<Products, Product>> Projection =>
product => new Product()
{
ProductId = product.ProductId,
ProductName = product.ProductName,
SupplierName = product.Supplier.CompanyName,
SupplierId = product.SupplierId,
QuantityPerUnit = product.QuantityPerUnit,
ReorderLevel = product.ReorderLevel,
UnitPrice = product.UnitPrice,
UnitsInStock = product.UnitsInStock,
UnitsOnOrder = product.UnitsOnOrder
};
}
}
Using asynchronous method
Typically a ComboBox or ListBox will be loaded in the main form where the first time EF Core performs operations will be slow for the first read. To keep the application responsive use an asynchronous read method e.g.
public static async Task<List<Category>> GetCategoriesAllProjectionsAsync()
{
var categoryList = new List<Category>();
await Task.Run(async () =>
{
categoryList = await Context.Categories
.AsNoTracking().Select(Category.Projection)
.ToListAsync();
});
return categoryList;
}
In the main form Shown event, mark the event as following.
private async void Form1_Shown(object sender, EventArgs e)
Load the ComboBox.
var categories = await Operations.GetCategoriesAllProjectionsAsync();
CategoryComboBox.DataSource = categories;
Caveat: Note DisplayMember is not set, this is because in the Category class .ToString is overridden thus the ComboBox uses the property set as the DisplayMember whiile if .ToString is not overridden the object type is shown for each category which is not what is desired.
public override string ToString() => CategoryName;
In the code sample the shown event has another list read and various properties setup. Any buttons that will read data if not set Enabled = false and the user attempts to click a button before the first reads are done it's possible for a runtime exception so buttons are enabled only after data is finished read into controls unlike synchronous reads were the buttons and for that matter the user interface would be unresponsive.
private async void Form1_Shown(object sender, EventArgs e)
{
#if DEBUG
Console.WriteLine("Loading categories");
#endif
var categories = await Operations.GetCategoriesAllProjectionsAsync();
CategoryComboBox.DataSource = categories;
SupplierColumn.DisplayMember = "CompanyName";
SupplierColumn.ValueMember = "SupplierId";
SupplierColumn.DataPropertyName = "SupplierId";
SupplierColumn.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;
#if DEBUG
Console.WriteLine();
Console.WriteLine("Loading Suppliers");
#endif
_supperList = await Operations.GetSupplierAsync();
dataGridView1.CellValueChanged += DataGridView1_CellValueChanged;
ProductsButton.Enabled = true;
SaveChangesButton.Enabled = true;
CheckedProductsButton.Enabled = true;
}
Note: The Console.WriteLine are used for monitoring/logging Entity Framework read operations in the code sample, if there were updates, deletes and additions they are monitored also. Entity Framework Core has several ways of logging as per Microsoft documentation. In the supplied code sample console logging is used.
One way to see how asynchronous might make a difference, a form loads, populates a ComboBox with categories in form shown event.
Create another window purely for testing. Each of the buttons in the left portion of the screen work with slight permutations for loading the ComboBox.
First button on first attempt takes 48ms, third attempt, 5ms, third attempt, 1ms.
public static async Task<List<Category>> GetCategoriesAllProjectionsAsync()
{
var categoryList = new List<Category>();
await Task.Run(async () =>
{
categoryList = await Context.Categories
.AsNoTracking().Select(Category.Projection)
.ToListAsync();
});
return categoryList;
}
Second button down from top, first attempt 55ms, second attempt, 3ms, last attempt, 1ms.
public static async Task<List<Categories>> GetCategoriesAllNoProjectionsAsync()
{
var categoryList = new List<Categories>();
await Task.Run(async () =>
{
categoryList = await Context.Categories
.AsNoTracking()
.ToListAsync();
});
return categoryList;
}
Third button down, completely synchronous, 37ms on first attempt, second 2ms and last 0ms.
public static List<Categories> GetCategoriesAllNotTracked()
{
return Context.Categories
.AsNoTracking()
.ToList();
}
Last button, 40ms on first attempt, second attempt, 2ms and 0ms on the last attempt.
public static List<Category> GetCategoriesAllNotTrackedProjections()
{
return Context.Categories
.AsNoTracking()
.Select(Category.Projection)
.ToList();
}
Between all of the test there are no huge differences between asynchronous, synchronous and with or without projects in this small example. This means in a larger example the added time for an asynchronous operation is not noticeable yet most likely in a larger data set this will have ramifications on memory usage. The only reason not to use asynchronous operation and projections is not knowing how which this article explains enough with ample code samples.
When moving an application to mobile applications resources usually are less than found on a standard new laptop so learning to work with only what is needed regarding data is very important, more so than a desktop solution.
To put things into perspective for window form projects the following project demonstrates putting asynchronous operation and projections together in a simple load data, edit and update in a standard DataGridView.
- Form Shown is asynchronous to load the ComboBox and data source for a DataGridViewComboBoxColumn.
- CellValueChanged event demonstrates accessing the current supplier in the single DataGridViewComboBoxColumn
- A custom BindingList is used to provide sorting the DataGridView by column click,
- The custom BindingList has an event for notification of changes, in this demo it's used to get at the deleted row, changes and added records.
- A DatagridViewCheckBoxColumn provides a method to mark shown products which in this case is by introducing a property into the products model, Process declarative as not mapped so the Entity Framework ChangeTracker knows the property is not part of the model.
Console logging
Console logging writes Entity Framework queries Visual Studio output window, e.g. for when loading categories. Note there are several ways to configure logging, here sensitive option is enabled so parameter values are exposed which can help with debugging an issue.
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (53ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [c].[CategoryID] AS [CategoryId], [c].[CategoryName]
FROM [Categories] AS [c]
When configured properly values for WHERE conditions will be exposed, in this case a category is selected, a button is pressed to get products for the selected category.
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (27ms) [Parameters=[@__categoryIdentifier_0='4' (Nullable = true)],
CommandType='Text', CommandTimeout='30']
SELECT
[p].[ProductID],
[p].[CategoryID],
[p].[Discontinued],
[p].[DiscontinuedDate],
[p].[ProductName],
[p].[QuantityPerUnit],
[p].[ReorderLevel],
[p].[SupplierID],
[p].[UnitPrice],
[p].[UnitsInStock],
[p].[UnitsOnOrder],
[s].[SupplierID],
[s].[Address],
[s].[City],
[s].[CompanyName],
[s].[ContactName],
[s].[ContactTitle],
[s].[Country],
[s].[Fax],
[s].[HomePage],
[s].[Phone],
[s].[PostalCode],
[s].[Region]
FROM [Products] AS [p]
LEFT JOIN [Suppliers] AS [s] ON [p].[SupplierID] = [s].[SupplierID]
WHERE [p].[CategoryID] = @__categoryIdentifier_0
Execution time 27 milliseconds, run the same query again and it's 3 milliseconds is shown and category id is also shown. To use console logging, install the following NuGet package. Open the projects DbContext and add the following factory method. Note builder.AddConsole enclosed in a conditional if means console logging will only work within Visual Studio while debugging the application.
/// <summary>
/// Configure logging for app
/// https://docs.microsoft.com/en-us/ef/core/miscellaneous/logging?tabs=v3
/// </summary>
public static readonly ILoggerFactory ConsoleLoggerFactory = Create(builder =>
{
builder
.AddFilter((category, level) =>
category == DbLoggerCategory.Database.Command.Name && level == LogLevel.Information);
#if DEBUG
builder.AddConsole();
#endif
});
In OnConfiguring method of the DbContext add UseLoggerFactory and EnableSensitiveDataLogging.
optionsBuilder
.UseSqlServer(connectionString)
.UseLoggerFactory(ConsoleLoggerFactory)
.EnableSensitiveDataLogging();
Note that a connection string here is dynamic, to learn more see TechNet: Entity Framework Core 3.x database connection article which shares the same code samples as this article.
Reusable includes
When navigation properties are needed in many places in code it's a smart idea to centralize then for a) maintainability b) keeps code clean. Refer to the following class for reusable includes for the supplied code samples.
Example, a requirement specifies to include contact information for a customer for their orders.
public static async Task<Orders> GetOrders2(int customerIdentifier)
{
Orders order = null;
await Task.Run(async () =>
{
order = await Context.Orders
.IncludeCustomerAndContact()
.FirstOrDefaultAsync(
ord => ord.CustomerIdentifier == customerIdentifier);
});
return order;
}
IncludeCustomerAndContact is a reusable language extension method.
public static IQueryable<Orders> IncludeCustomerAndContact(this IQueryable<Orders> query)
{
return query.Include(orders => orders.CustomerIdentifierNavigation)
.ThenInclude(customer => customer.Contact)
.Include(orders => orders.CustomerIdentifierNavigation.ContactTypeIdentifierNavigation);
}
If in some cases not all the navigation properties are needed the following provides flexibility to include navigation properties or not to include them.
public static IQueryable<Orders> IncludeOptions(
this IQueryable<Orders> query,
bool contact = false,
bool contactType = false)
{
IQueryable<Orders> customerQuery = query
.Include(order => order.CustomerIdentifierNavigation);
if (contact)
{
customerQuery = customerQuery
.Include(order => order.CustomerIdentifierNavigation.Contact);
}
if (contactType)
{
customerQuery = customerQuery.Include(order =>
order.CustomerIdentifierNavigation.ContactTypeIdentifierNavigation);
}
return customerQuery;
}
Summary
With the information presented developers working with Windows Forms projects using C# have learned how to work with smaller payloads when returning data back from a database using Entity Framework Core 3 along with how to keep the user interface responsive.
External resources
Entity Framework Core overview
Entity Framework Core 3 breaking changes
Entity Framework code first DataGridView
See also
Entity Framework Core shadow properties (C#)
Entity Framework Core/Windows Forms tips and tricks
Visual Studio: structuring an Entity Framework project for Windows forms
Entity Framework Windows Forms validation
Entity Framework Core Find all by primary key (C#)
Running code samples
- From SSMS (SQL-Server Management Studio) run the following script to create the database and populate tables.
- Download or clone the GitHub repository.
- Open the solution in Visual Studio
- Right click the top node in solution explorer and select restore NuGet packages
- Build the solution and run.
- Optionally change the start form from Form1 to ExampleForm in program.cs and rebuild/run to test out how load a ComboBox takes to load
Source code
Download or clone the GitHub repository.