Entity Framework Core/Windows Forms tips and tricks
Introduction
Entity Framework Core provides data access for Windows, web and mobile projects working as an object-relational mapper (O/RM) which supports many database engines including SQL-Server, Microsoft Access, Oracle, MySQL to name the top supported database engines. Entity Framework Core was born from a mature Entity Framework 6 with a multitude of new features and more ways to control working with data. With increased flexibility for working with data does not always make working with data easy especially with performance. Below there are tips and tricks to assist with efficiently working with Entity Framework Core overall but is not all that is needed to work with Entity Framework Core, consider these tips and tricks at start for getting started with Entity Framework.
Prerequisite
Although Entity Framework is capable of creating a database and populate tables with data for novice developers the best way to learn Entity Framework is to first understand relational databases and how they work as Entity Framework can easily reverse engineer your databases while using Entity Framework Core to create databases and tables means knowing how to create related tables correctly. By allowing Entity Framework Core to work with an existing databases will reduce any possible frustrations as when there are issues one must ask, is it the code to generate the database or the code to interact with the database? Working from existing databases is easier.
- Basics for creating relational databases, most database solutions require more than one table and have more than one table related to another table.
- A tool to create databases, tables and queries, for SQL-Server SSMS (SQL-Server Management Studio), Oracle, SQL Developer, Microsoft Access etc.
- Learn the basics of a good database design which includes primary keys and indexes as this will affect performance of data operations with Entity Framework Core.
- Have a decent understanding of SQL, the following resource provides all the basics needed to assist with learning SQL.
- Basic understanding of LINQ and/or Lambda standard query operators, see Microsoft documentation on Projection operators.
First steps
Once a database with tables and data have been created to interact with them classes must be generated which is known as reverse engineering. Reverse engineering is the process of scaffolding entity type classes and a DbContext class based on a database schema. It can be performed using the Scaffold-DbContext command of the EF Core Package Manager Console (PMC) tools or the dotnet ef dbcontext scaffold command of the .NET Command-line Interface (CLI) tools.
Reverse engineering even with standard tools is not an easy task as the process is done via the Package Manager console within Visual Studio. For a pleasant first time experience use the following tool with source code or the following Visual Studio extension, EFCorePowerTools.
Tip 1 code placement
Place all Entity Framework code into a class project so that if more than one front end project needs to use the code they can have a reference to the class project to access the code.
Tip 2 reverse engineering
When reverse engineering, a class will be created for controlling access to data, see the following for an example. This class will be placed in the root folder of the project which is fine unless there will be more than one database included in the project. If there will be multiple databases create a folder e.g. Context and move the classes to this folder for organization purposes. Optionally change the namespace to reflect the folder name which means each of the classes created in the reverse engineering process mus reference that namespace. Before doing this create a Model folder for each database and move those classes into the folder then add a using statement to reference back to the context class, see the attached source code for examples.
Tip 3 AsNoTracking
By default all EF Core select queries are tracked for changes made, in some cases there is no need for this such as populating a reference table into a ListBox or ComboBox. If there are a good deal of records use AsNoTracking which will not track changes to the data returned from the database. In the following example AsNoTracking speeds up reading data.
public static async Task<List<CustomerLister>> CustomerDisplay(this NorthwindContext context)
{
return await context.Customers
.AsNoTracking()
.Select(customer => new CustomerLister
{
Name = customer.CompanyName,
Id = customer.CustomerIdentifier
}).ToListAsync();
}
Note: .AsNoTracking benefits come when working with over 1,000 records. To get an idea if .AsNoTracking is suitable install BenchmarkDotNet on NuGet and learn how to use this library from the documentation.
Tip 4 common misconceptions
Only returned data needed to perform an operation. In conventional data operations developers tend to use SELECT * FROM some table which means more data than needed is read which can affect performance of the application. For novice developers first starting out with EF Core the same thing is likely to happen, instead create a class which represents the data needed.
Example, a requirement is to show a company name in a ListBox with the primary key to display in the ListBox. The following code is like SELECT *,
public List<Customer> GetCustomersForListBox()
{
using (var context = new NorthwindContext())
{
return context.Customers.ToList();
}
}
And this is what is the definition of the Customer class, more than needed to populate a ListBox. And to show the Company name DisplayMember must be set on the ListBox.
public partial class Customer
{
public Customer()
{
Orders = new HashSet<Order>();
}
public int CustomerIdentifier { get; set; }
[Required]
[StringLength(40)]
public string CompanyName { get; set; }
[StringLength(30)]
public string ContactName { get; set; }
public int? ContactId { get; set; }
[StringLength(60)]
public string Address { get; set; }
[StringLength(15)]
public string City { get; set; }
[StringLength(15)]
public string Region { get; set; }
[StringLength(10)]
public string PostalCode { get; set; }
public int? CountryIdentifier { get; set; }
[StringLength(24)]
public string Phone { get; set; }
[StringLength(24)]
public string Fax { get; set; }
public int? ContactTypeIdentifier { get; set; }
public DateTime? ModifiedDate { get; set; }
[ForeignKey("ContactId")]
[InverseProperty("Customers")]
public virtual Contact Contact { get; set; }
[ForeignKey("ContactTypeIdentifier")]
[InverseProperty("Customers")]
public virtual ContactType ContactTypeIdentifierNavigation { get; set; }
[ForeignKey("CountryIdentifier")]
[InverseProperty("Customers")]
public virtual Country CountryIdentifierNavigation { get; set; }
[InverseProperty("CustomerIdentifierNavigation")]
public virtual ICollection<Order> Orders { get; set; }
}
Using the following class only returns the data needed to populate a ListBox plus by overriding ToString this takes the place of setting DisplayMember for the ListBox.
public class CustomerLister
{
public string Name { get; set; }
public int Id { get; set; }
public override string ToString() => Name;
}
In the form
List<CustomerLister> results = await context.CustomerDisplay();
CustomerListBox.DataSource = results;
Tip 5 understanding LINQ/Lambda
By understanding basics of LINQ/Lambda operators developers can avoid many performance issues. For instance in the following query too much time is taken. The issue is that data is read into via .ToList which returns all records for the table then the .FirstOrDefault is applied against .ToList.
public Customer GetCustomerByIdentifier(int customerIdentfier)
{
using (var context = new NorthwindContext())
{
return context.Customers.ToList()
.FirstOrDefault(customer =>
customer.CustomerIdentifier == customerIdentfier);
}
}
To fix this eliminate the .ToList which then means the FirstOrDefault is going to return only one record or null if the record is not found.
public Customer GetCustomerByIdentifier(int customerIdentfier)
{
using (var context = new NorthwindContext())
{
return context.Customers
.FirstOrDefault(customer =>
customer.CustomerIdentifier == customerIdentfier);
}
}
A very common mistaken which is similar to using .ToList then .FirstOrDefault is to use a predicate in a Where then use FirstOrDefault e.g.
public Customer GetCustomerByIdentifier(int customerIdentfier)
{
using (var context = new NorthwindContext())
{
// ReSharper disable once ReplaceWithSingleCallToFirstOrDefault
return context.Customers
.Where(customer => customer.CustomerIdentifier == customerIdentfier)
.FirstOrDefault();
}
}
There is no need for Where as the FirstOrDefault extension accepts a predicate.
public Customer GetCustomerByIdentifier(int customerIdentfier)
{
using (var context = new NorthwindContext())
{
return context.Customers.ToList()
.FirstOrDefault(customer =>
customer.CustomerIdentifier == customerIdentfier);
}
}
Tip 6 eager loading
Since Entity Framework Core is eager loading related data is not returned by default. To load related data Entity Framework Core has the .Include extension method. This means each time related data is required Include or ThenInclude must be used as shown below.
var customer = await context.Customers
.Include(cust => cust.CountryIdentifierNavigation)
.Include(cust => cust.Contact)
.Include(cust => cust.ContactTypeIdentifierNavigation)
.FirstOrDefaultAsync(cust => cust.CustomerIdentifier == customerIdentifier);
This means code is cluttered up and when used in multiple locations if a requirement changes each instance must be changed. Use an extension method instead as per below.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using NorthWindCoreLibrary.Contexts;
using NorthWindCoreLibrary.HelperClasses;
using NorthWindCoreLibrary.Models;
namespace NorthWindCoreLibrary.Extensions
{
public static class CustomerExtensions
{
public static async Task<Customer> CustomerPartial(
this NorthwindContext context, int customerIdentifier)
{
return await context.Customers
.AsNoTracking()
.Include(customer => customer.CountryIdentifierNavigation)
.Include(customer => customer.Contact)
.Include(customer => customer.ContactTypeIdentifierNavigation)
.FirstOrDefaultAsync(cust => cust.CustomerIdentifier == customerIdentifier);
}
}
}
Code is now reduced to the following.
var customer = await context.CustomerPartial(customerIdentifier);
Some developer do not like to use language extension methods, in this case a static method can be used.
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using NorthWindCoreLibrary.Contexts;
using NorthWindCoreLibrary.Models;
namespace NorthWindCoreLibrary.HelperClasses
{
public class CustomerHelpers
{
public static async Task<Customer> CustomerEntityAsync(
NorthwindContext context, int customerIdentifier)
{
return await context.Customers
.Include(customer => customer.CountryIdentifierNavigation)
.Include(customer => customer.Contact)
.Include(customer => customer.ContactTypeIdentifierNavigation)
.FirstOrDefaultAsync(cust => cust.CustomerIdentifier == customerIdentifier);
}
}
}
Then as with the language extension clutter is gone and easy to maintain.
var customer = await CustomerHelpers.CustomerEntityAsync(context, customerIdentifier);
Tip 7 logging
At one point or another code will not perform as expected or there will be a runtime exception that just can not be figured out. In these cases use a LoggerFactory. To log to Visual Studio output window install Microsoft.Extensions.Logging from NuGet. Next add the following line of code to the DbContext class. See the following example.
public static readonly ILoggerFactory LoggerFactory = new LoggerFactory().AddConsole((_, ___) => true);
Next i OnConfiguring method in the DbContext class add the following property which permits using or not using logging in client code.
public bool Diagnostics { get; set; }
Next configure OnConfiguring as shown here which allows turning logging on/off. In a form with a CheckBox to control logging.
using (var context = new NorthwindContext())
{
context.Diagnostics = LogConsoleCheckBox.Checked;
var customer = await context.CustomerPartial(customerIdentifier);
}
Now when logging is enabled make sure the IDE Output window is open to see diagnostics.
Important: Unless there is a good reason make sure when an application goes to production built in release mode use the following to turn off logging.
#if DEBUG
context.Diagnostics = LogConsoleCheckBox.Checked;
#endif
Lastly, depending on the release of Entity Framework Core used the implementation of logging may change, see the following for specific versions.
Tip 8 asserting connections
In some cases such as a remote client setup via a installation program there is always the possibility of connection issues. Consider using assertion and display a screen while performing the initial operation for Entity Framework. In the screenshot below (include in the attached source code) is a animated gif to indicate something is going on to the user. The first operation uses CanConnectAsync to ensure a connection can be made to the database. CanConnectAsync has an overload which accepts a CancellationToken also.
Code to implement checking a connection following by loading a ListBox with data.
private async void Form1_Shown(object sender, EventArgs e)
{
var waitForm = new ConnectStatusForm() {Top = Top, Left = Left, TopMost = true};
await Task.Delay(20);
waitForm.Show();
await Task.Delay(10);
using (var context = new NorthwindContext())
{
// set to true for logging Entity Framework operations
// for this instance of the DbContext.
// context.Diagnostics = true;
try
{
if (await context.Database.CanConnectAsync())
{
var results = await context.CustomerDisplay();
CustomerListBox.DataSource = results;
IncludeStatementsConventionalButton.Enabled = true;
IncludeStatementsUsingExtensionButton.Enabled = true;
}
else
{
waitForm.Close();
MessageBox.Show("Failed to connection to server, please check the connection string.");
}
}
catch (Exception ex)
{
waitForm.Close();
MessageBox.Show(ex.Message);
}
finally
{
waitForm.Dispose();
}
}
}
Tip 9 asynchronous
If you’re not doing it already, start using async and await to increase performance of your application. Especially on I/O intensive operations like heavier SQL queries this will allow your application to respond to other requests while waiting for the previous operations to complete. For many Windows desktop developers this is a foreign concept which when working with Entity Framework Core is easy as many of the methods have both synchronous and asynchronous counterparts.
public Customer GetCustomerByIdentifier(int customerIdentfier)
{
using (var context = new NorthwindContext())
{
return context.Customers
.FirstOrDefault(customer =>
customer.CustomerIdentifier == customerIdentfier);
}
}
public async Task<Customer> GetCustomerByIdentifierAsync(int customerIdentfier)
{
using (var context = new NorthwindContext())
{
return await context.Customers
.FirstOrDefaultAsync(customer =>
customer.CustomerIdentifier == customerIdentfier);
}
}
Note the minor differences
Tip 10 connection string
Be careful with Connection string as the default is to store connection string in the DbContext class but can be placed into app.config under the ConnectionStrings section which means they are exposed and may be manipulated or allow a user to connect to the database outside of the application. This may also be problematic when there are user names and passwords. Unfortunately Windows Forms does not have the same level of security as ASP.NET projects have vis the secret manager tool so be careful with setting up a connection string.
See the following project included in the attached source for ideas on securing a connection string.
Tip 11 unit testing
Write unit test for all Entity Framework Core queries, by writing unit test this allows a developer to know each query works as expected. When implementing queries into the front end there may be unexpected results which may be the fault of a untested query or how the query is used. By having appropriate unit test methods can assist in diagnosing problems. The only downside is that test methods tak a considerable amount of time to rig up but are well worth time spent later down the road.
Entity Framework Core provides in-memory testing via SqlLite which is not the same as testing against a real database and if considering using in-memory testing this is not foolproof.
Tip 12 must checks
Make sure
- Each and every table has an auto-incrementing primary key as Entity Framework will not function in regards to inserts without this.
- Ensure before reverse engineering that tables have proper indexes which can be done using proper tools such as in the case of SQL-Server via SSMS profiling tools and the ability to get action plans when running conventional SQL statements.
Tip 13 global query filter
Entity Framework Core Fluent API HasQueryFilter method, a global query filter to a specific entity, so that the filter is included in all query statements generated for the entity by EF Core.
One example, an application presents list of products in the front end which are available. In each query there needs to be a condition to filter out discontinued products e.g.
var products =
context
.Products
.Where(product => product.CategoryId == categoryIdentifier && !product.Discontinued)
.ToList();
Implementing HasQueryFilter the query changes to
var products =
context
.Products
.Where(product => product.CategoryId == categoryIdentifier)
.ToList();
HasFilter are implemented in the DbContext class in OnModelCreating. In the following example (included in attached source code) any product.Discontinued is true are filtered out.
modelBuilder.Entity<Product>(entity =>
{
entity.HasOne(d => d.Category)
.WithMany(p => p.Products)
.HasForeignKey(d => d.CategoryId)
.HasConstraintName("FK_Products_Categories");
entity.HasOne(d => d.Supplier)
.WithMany(p => p.Products)
.HasForeignKey(d => d.SupplierId)
.HasConstraintName("FK_Products_Suppliers");
entity.HasQueryFilter(prod => prod.Discontinued == false);
});
To ignore a filter use IgnoreQueryFilters.
var products =
context
.Products.IgnoreQueryFilters()
.Where(product => product.CategoryId == categoryIdentifier)
.ToList();
Important
Before using HasQueryFilter take time experimenting as there are some issues such as chaining multiple filters where in a case of two filters applied the last one is used while the first one is ignored. Also, use caution when using an unusual filter that other developers don't know about along with yourself days, months years later the filter has been forgotten and now wonder why a query does not return expected results.
Take time to review the Microsoft documentation.
Tip 14 What's new
Entity Framework Core is constantly changing, from time to time read the "new features and breaking changes" for EF Core. Developers as a whole tend to stick to code they are comfortable with while there may and many times are better ways to achieve better results while this is not always the case this means just don't implement a new feature and expect it to be better than the current code. This means to take time not only to read about new features and breaking changes but also try them out and when possible using unit test.
Tip 15 Source control
Not one developer is immune from writing code that fails to work and then needs to move back to a prior working version of code. As a developer, progresses with Entity Framework there are parts that can break and these parts are not easy to find. This is where source control can assist by undoing changes and or reviewing current code against prior versions. Take time to implement source control.
Tip 16 Interceptors
Entity Framework Core (EF Core) interceptors enable interception, modification, and/or suppression of EF Core operations. This includes low-level database operations such as executing a command, as well as higher-level operations, such as calls to SaveChanges.
For example, on saving changes a requirement is to disallow a certain value to be set. In this example the interceptor must be registered in the DbContext.
The Interceptor
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using Microsoft.EntityFrameworkCore.Diagnostics;
using NorthWindSqlServerLibrary.Models;
namespace NorthWindSqlServerLibrary.Interceptors
{
/// <summary>
/// Objective here is to assert for Region property equal to specific value
/// and if so reject the save.
/// </summary>
public class LoggingSavingChangesInterceptor : SaveChangesInterceptor
{
public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
DbContextEventData eventData,
InterceptionResult<int> result,
CancellationToken cancellationToken = new())
{
if (InspectCustomerRegion(eventData.Context.ChangeTracker.Entries()))
{
result = InterceptionResult<int>.SuppressWithResult(0);
}
return new ValueTask<InterceptionResult<int>>(result);
}
/// <summary>
/// Provides a way to inspect model/entry values
/// </summary>
/// <param name="entries"></param>
/// <returns></returns>
private static bool InspectCustomerRegion(IEnumerable<EntityEntry> entries)
{
foreach (EntityEntry entry in entries)
{
if (entry.State != EntityState.Modified) continue;
if (entry.Entity is not Customers customer) continue;
if (customer.Region == "Canada")
{
return true; // reject this entities region
}
}
return false;
}
}
}
The following line allows to rejection
InterceptionResult<int>.SuppressWithResult(0);
DbContext setup methods for registering the interceptor and obtain the database connecton string
private static IConfigurationRoot ReadAppsettings(out IConfigurationBuilder builder)
{
builder = new ConfigurationBuilder()
.AddJsonFile("appsettings.json", optional: false, reloadOnChange: true);
IConfigurationRoot config = builder.Build();
return config; // connection string
}
/// <summary>
/// Demo in this case to prevent a Customer Region from being Canada
/// See test method SaveChangesInterceptor
/// </summary>
/// <param name="optionsBuilder"></param>
private static void ConnectionWithSaveInterceptor(DbContextOptionsBuilder optionsBuilder)
{
var config = ReadAppsettings(out var builder);
optionsBuilder
.AddInterceptors(new LoggingSavingChangesInterceptor())
.UseSqlServer(config.GetConnectionString("DatabaseConnection"));
}
Do the actual registration
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
ConnectionWithSaveInterceptor(optionsBuilder);
}
}
Test this with a unit test method
[TestMethod]
public async Task SaveChangesInterceptor()
{
await using var context = new NorthwindContext();
var customer = await context.Customers.FindAsync(1);
customer.Region = "Canada";
int affected = await context.SaveChangesAsync();
Debug.WriteLine(affected);
}
Another version to use for development for inspecting changes which is registered the same as the interceptor above.
using System;
using System.Diagnostics;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using Microsoft.EntityFrameworkCore.Diagnostics;
namespace NorthWindCoreLibrary.Data.Interceptors
{
public class SavedChangesInterceptor : SaveChangesInterceptor
{
public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
DbContextEventData eventData,
InterceptionResult<int> result,
CancellationToken cancellationToken = default)
{
Debug.WriteLine($"Started saving changes.");
return new ValueTask<InterceptionResult<int>>(result);
}
public override ValueTask<int> SavedChangesAsync(
SaveChangesCompletedEventData eventData,
int result,
CancellationToken cancellationToken = default)
{
Debug.WriteLine($"Saved {result} No of changes.");
return new ValueTask<int>(result);
}
public override int SavedChanges(
SaveChangesCompletedEventData eventData,
int result)
{
eventData.Context.ChangeTracker.DetectChanges();
Debug.WriteLine(eventData.Context.ChangeTracker.DebugView.LongView);
foreach (var entry in eventData.Context.ChangeTracker.Entries())
{
var auditMessage = entry.State switch
{
EntityState.Deleted => CreateDeletedMessage(entry),
EntityState.Modified => CreateModifiedMessage(entry),
EntityState.Added => CreateAddedMessage(entry),
_ => null
};
if (auditMessage is not null)
{
Console.WriteLine();
}
}
return base.SavedChanges(eventData, result);
}
string CreateAddedMessage(EntityEntry entry)
=> entry.Properties.Aggregate(
$"Inserting {entry.Metadata.DisplayName()} with ",
(auditString, property) => auditString + $"{property.Metadata.Name}: '{property.CurrentValue}' ");
string CreateModifiedMessage(EntityEntry entry)
=> entry.Properties.Where(property => property.IsModified || property.Metadata.IsPrimaryKey()).Aggregate(
$"Updating {entry.Metadata.DisplayName()} with ",
(auditString, property) => auditString + $"{property.Metadata.Name}: '{property.CurrentValue}' ");
string CreateDeletedMessage(EntityEntry entry)
=> entry.Properties.Where(property => property.Metadata.IsPrimaryKey()).Aggregate(
$"Deleting {entry.Metadata.DisplayName()} with ",
(auditString, property) => auditString + $"{property.Metadata.Name}: '{property.CurrentValue}' ");
}
}
Tip 17 ToQueryString
Generates a string representation of the query used. This string may not be suitable for direct execution is intended only for use in debugging.
private static void ToQueryString()
{
using var context = new NorthwindContext();
var categoryQueryString = context.Categories.ToQueryString();
Debug.WriteLine(categoryQueryString);
Debug.WriteLine("");
int categoryIdentifier = 2;
var productsQueryString = context
.Products
.Include(product => product.OrderDetails)
.Include(product => product.Supplier)
.Include(product => product.Category)
.Where(c => c.CategoryId == categoryIdentifier).ToQueryString();
Debug.WriteLine(productsQueryString);
}
Results (formatted from SSMS)
SELECT c.CategoryID,
c.CategoryName,
c.Description,
c.Picture
FROM Categories AS c;
DECLARE @__categoryIdentifier_0 INT= 2;
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.City, s.CompanyName,
s.ContactName, s.ContactTitle, s.CountryIdentifier,
s.Phone, s.PostalCode, s.Region, s.Street,
c.CategoryID, c.CategoryName, c.Description,
c.Picture, o.OrderID, o.ProductID, o.Discount,
o.Quantity, o.UnitPrice
FROM Products AS p
LEFT JOIN Suppliers AS s ON p.SupplierID = s.SupplierID
LEFT JOIN Categories AS c ON p.CategoryID = c.CategoryID
LEFT JOIN OrderDetails AS o ON p.ProductID = o.ProductID
WHERE p.CategoryID = @__categoryIdentifier_0;
Same with Oracle
SELECT [c].[CategoryID], [c].[CategoryName], [c].[Description], [c].[Picture]
FROM [Categories] AS [c]
DECLARE @__categoryIdentifier_0 int = 1;
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].[City], [s].[CompanyName],
[s].[ContactName], [s].[ContactTitle], [s].[CountryIdentifier], [s].[Fax],
[s].[HomePage], [s].[Phone], [s].[PostalCode], [s].[Region], [s].[Street],
[c].[CategoryID], [c].[CategoryName], [c].[Description], [c].[Picture],
[o].[OrderID], [o].[ProductID], [o].[Discount], [o].[Quantity], [o].[UnitPrice]
FROM [Products] AS [p]
LEFT JOIN [Suppliers] AS [s] ON [p].[SupplierID] = [s].[SupplierID]
LEFT JOIN [Categories] AS [c] ON [p].[CategoryID] = [c].[CategoryID]
LEFT JOIN [OrderDetails] AS [o] ON [p].[ProductID] = [o].[ProductID]
WHERE [p].[CategoryID] = @__categoryIdentifier_0
ORDER BY [p].[ProductID], [s].[SupplierID], [c].[CategoryID], [o].[OrderID], [o].[ProductID]
Exploration
Entity Framework Core provides the ability to override many methods which provides developers the ability to incorporate their business logic ranging from validation injected into save changes, set values that are not in the model or to build in data manipulation that needs to be done outside of regular operations similar to doing a global filter mentioned earlier.
An easy way to explore/find potential overloads is to simply start typing public in the context class and a scrollable list appears to explore. When ToQueryString generates a string representation of the query used not that if a constant value is passed into a predicate such as FirstOrDefault or Where the value is shown in the generated query while using a variable the EF Core creates a parameter for the value.
Take the following, a developer would like to view changed data before performing the save operations. Override SaveChanges in the DbContext class.
public override int SaveChanges()
{
OnBeforeSaving();
return base.SaveChanges();
}
In the following code only modified entities are examined. An important piece of logic is to work only on non-virtual members done with in one of the assertions in OnBeforeSaving.
if (!prop.GetGetMethod().IsVirtual)
Here Console.WriteLine is used to display the primary key for each modified entity along with original and current values for any changed property.
private void OnBeforeSaving()
{
var entries = ChangeTracker.Entries();
/*
* Only interested in modified entities
*/
var modifiedEntities = ChangeTracker
.Entries().Where(_ => _.State == EntityState.Modified);
/*
* Iterate any modified entities
*/
foreach (var change in modifiedEntities)
{
/*
* We are only concerned in this case with customer entities
*/
if (change.Entity is Customer customer)
{
/*
* Show primary key
*/
Console.WriteLine($"Primary key: {GetEntityPrimaryKeyValue(change.Entity)}");
foreach (var prop in change.Entity.GetType().GetTypeInfo().DeclaredProperties)
{
if (!prop.GetGetMethod().IsVirtual)
{
/*
* Show property name, original and current values
*/
Console.WriteLine(
$"Name: {prop.Name} original " +
$"'{change.Property(prop.Name).OriginalValue}' " +
$"current '{change.Property(prop.Name).CurrentValue}'");
}
}
}
}
}
Besides overrides there are times when language extension methods make more sense when they are constructed generically, for instance to reset all unchanged entities or to perform annotation validation in an overridden save changes.
Summary
Tips and tricks have been presented to assist in learning several best practices along with how to avoid some common issues which typically arise when first starting writing code with Entity Framework Core. As progressing with writing code for Entity Framework Core keep in mind that Entity Framework is still evolving which means new features on the horizon along with bug fixes which means it’s prudent to keep abreast of changes to Entity Framework Core over time. Take time to learn the basics rather than forge head first with a complex project, keep things simple until the basics are understood.
See also
Windows Forms: Entity Framework Core Reverse Engineering databases
Entity Framework Core: Projections
Entity Framework Core Find all by primary key (C#)
Entity Framework Core HasValueGenerator (C#)
Getting Started with Entity Framework Core: Database-First Development
Entity Framework Windows Form validation
Entity Framework: Disconnected Entities
Entity Framework database/Code First Enum support
ASP.NET Core Entity Framework Core Code First: CRUD Operations
Visual Studio: structuring an Entity Framework project for Windows forms
.NET Core: Secure your web applications using IdentityServer 4
External resources
Porting from Entity Framework 6 to Entity Framework Core
Entity Framework Core API Reference
Installing Entity Framework Core
EF Core Tools and Extensions
BenchmarkDotNet
Source code
The following GitHub repository contains code which needs the following prior to executing.
- Create the sample database with scripts provided.
- Change the connection string in the following location.