Motivation for moving to Entity Framework Core (C#)
Introduction
For years, many developers relied on interacting with databases using DataSet and DataTable containers populated using a TableAdapter, DataAdapter or using a connection and command object coupled with events for editing, deleting, filtering and adding new data. Although these methods are still viable the intent here is to present compelling reasons to use Entity Framework Core in Windows, form projects which when done properly can transfer to other project types like WPF and web based solutions.
The database will be SQL-Server although the same techniques presented will work for the majority of data providers offered for Entity Framework Core, main differences are how each database handles specifics as not all databases perform exactly the same way.
EF Core 7: This release has many features to make life easier along with performance enhancements, see code samples.
Working with conventional non Entity Framework data access
There is guesswork from the start for those just starting out with working with databases.
- How to design a database (the same is true for Entity Framework)?
- How to create a connection (Entity Framework Core does this for you)?
- How to interact with the data (Entity Framework Core once working with it for a while is easier)?
For designing a database, the first step is to know exactly what will be stored then figure out tables and columns which can be done first with pencil and paper keeping in mind normalization. A simple example, there is a need to store contact information, if a contact table were to be created in a new database with a primary key, first and last name followed by work, home and cell phone then later there is a need for a secondary work phone? Many novice developers will add a second work phone while the proper way is to have a contact device table with a foreign key back to the contact table so if a secondary work phone is needed simply add a row in the device table. One step farther would be have a table for device type in the event there is a new type other than work, home or cell. Similarly for a contact there is a contact type e.g. owner, sales representative, what happens if one of the titles changed? Each row in the contact table needs to be updated, instead having a contact type table with a foreign key back to the contact table means one change for all contacts.
The downside if time is not spent with a normalized database writing queries in SSMS (SQL-Server Management Studio) but instead writing queries in code it can turn into frustration and lost time while taking time to test the database design in SSMS can validate or invalidate a design and those queries can be copied and pasted into code.
For a good resource for database designs can be found on a site named Database Answers.
When a database is poorly designed this will be reflected in code be it with DataSets, Entity Framework or Entity Framework Core.
Code sample database
The database used for examples is a modified Microsoft NorthWind database which works fine yet can be improved slightly e.g. a table named Order Details could had been named OrderDetails, there is an problem with an index which in the next part of this series will be remedied with a deeper dive into Entity Framework Core, for now each code sample works.
Database connection
Exploration for connecting to a database.
Connecting to a database with conventional methods
When working with a data provider e.g. SqlClient, a connection string is required to connect to the database. The following site ConnectionStrings has all connection strings.
Example, connect to a local SQL-Server database the connection string in this case is privately scoped to a data class. As shown below the connection string is created by hand.
/// <summary>
/// Connection string for database and the catalog to work with
/// </summary>
private static string ConnectionString =
"Data Source=.\\SQLEXPRESS;" +
"Initial Catalog=NorthWindAzureForInserts;" +
"Integrated Security=True";
Which is then used in a connection object
public static DataTable CountryTable()
{
var dt = new DataTable();
using (var cn = new SqlConnection(ConnectionString))
{
using (var cmd = new SqlCommand() {Connection = cn})
{
cmd.CommandText = "SELECT CountryIdentifier, Name FROM dbo.Countries;";
cn.Open();
dt.Load(cmd.ExecuteReader());
}
}
return dt;
}
Connecting to a database with Entity Framework Core
A connection string is created using the following Visual Studio extension which is free and used by thousands of developers.
- Create a new windows form or class project.
- Install Entity Framework Core SQL-Server using NuGet. (this is great for novice to expert developers but can also be done by hand coding yet not advisable for new to coding or new to Entity Framework Core)
- Right click on the project
- Select EF Power tools
- Select Reverse engineer.
- Follow the prompts which starts with selecting a database followed by which tables to create classes for. There are several options which are self-explanatory such as creating folders for models (classes representing tables), data annotations, custom configurations (gone over in the next part of the series) and more.
- Once done a DbContext class is generated with the connection string done for us (along with a comment to move the connection string, also gone over in the next part of this series but for now works).
optionsBuilder.UseSqlServer(
"Data Source=.\\SQLEXPRESS;Initial Catalog=NorthWindAzureForInserts;Integrated Security=True");
Now to get the same data for contact types as done above. Note there is no open method, this is handled for you by Entity Framework Core.
public static List<ContactType> ContactTypes()
{
using (var context = new NorthWindContext())
{
return context.ContactType.AsNoTracking().ToList();
}
}
In both cases, conventional and Entity Framework Core data is read without anyway to directly update the data. With the conventional method a decent amount of code is needed to supplement for saving changes while with Entity Framework Core the NorthWindContext can be scoped to the class level and now saves are done by calling SaveChanges or SaveChangesAsync, In some cases a BindingList coupled with implementing INotifyPropertyChanged interface may be needed as shown in the following code sample.
Important: When viewing the provided code samples don't get discourage by the additional amount of code there is for Entity Framework Core, more code in this case means greater control over every aspect of interacting with a database. Many developers think less code should be needed yet those with this mentality soon learn less is not always better.
Reading data 
Note: Both conventional and EF Core versions have the exact same functionality.
When dealing with very simple non relational data SQL SELECT statements are simple while this is rare except for support read operations such as reading a list of countries. In real life the following query is normal, three tables joined together using conventional data access.
public static async Task<DataTable> GetCustomersAsync()
{
var dataTable = new DataTable();
await Task.Run(async () =>
{
using (var cn = new SqlConnection(ConnectionString))
{
using (var cmd = new SqlCommand() { Connection = cn })
{
cmd.CommandText =
"SELECT cust.CustomerIdentifier, cust.CompanyName, cust.ContactId, Contacts.FirstName, " +
"Contacts.LastName, ct.ContactTitle, cust.ContactTypeIdentifier, cust.CountryIdentifier, " +
"Countries.Name AS CountryName FROM Customers AS cust " +
"INNER JOIN Contacts ON cust.ContactId = Contacts.ContactId " +
"INNER JOIN ContactType AS ct ON cust.ContactTypeIdentifier = ct.ContactTypeIdentifier " +
"INNER JOIN Countries ON cust.CountryIdentifier = Countries.CountryIdentifier " +
"ORDER BY cust.CompanyName";
await cn.OpenAsync();
dataTable.Load(await cmd.ExecuteReaderAsync());
}
}
});
return dataTable;
}
The SELECT statement above should be written in SSMS as hand coding this in C# code unless an SQL expert will turn out wrong, even using SSMS can prove to be a challenge for those just starting out with SQL in regards to the JOIN aspects.
Now let's look at Entity Framework Core version. Still looks complex to the beginner yet here are the advantages
- Each aspect of the query has Intellisense as each class Customers, Contact, ContactType and Country are all classes in the project created using EF Power Tools
- JOINs are replaced with two extension methods (which also have Intellisense), .Include and .ThenInclude. Note that .ThenInclude is new to EF Core 3.1, Entity Framework 6 does not have it, the method for EF 6 is query.Include(x => x.Collection.Select(y => y.Property)) or to using LINQ or lambda with joins similar to the above SQL SELECT.
public static async Task<List<CustomerItem>> GetCustomersAsync()
{
return await Task.Run(async () =>
{
using (var context = new NorthwindContext())
{
return await context.Customers.AsNoTracking()
.Include(customer => customer.Contact)
.ThenInclude(contact => contact.ContactDevices)
.ThenInclude(contactDevices => contactDevices.PhoneTypeIdentifierNavigation)
.Include(customer => customer.ContactTypeIdentifierNavigation)
.Include(customer => customer.CountryIdentifierNavigation)
.Select(customer => new CustomerItem()
{
CustomerIdentifier = customer.CustomerIdentifier,
CompanyName = customer.CompanyName,
ContactId = customer.Contact.ContactId,
Street = customer.Street,
City = customer.City,
PostalCode = customer.PostalCode,
CountryIdentifier = customer.CountryIdentifier,
Phone = customer.Phone,
ContactTypeIdentifier = customer.ContactTypeIdentifier,
Country = customer.CountryIdentifierNavigation.Name,
FirstName = customer.Contact.FirstName,
LastName = customer.Contact.LastName,
ContactTitle = customer.ContactTypeIdentifierNavigation.ContactTitle,
OfficePhoneNumber = customer.Contact.ContactDevices.FirstOrDefault(contactDevices => contactDevices.PhoneTypeIdentifier == 3).PhoneNumber
}).ToListAsync();
}
});
}
Presentation in Windows Forms
Below there is an example for conventional loading of data into a DataGridView and one for Entity Framework loading data into a DataGridView.
First conventional loading
using System;
using System.Data;
using System.Windows.Forms;
using DataGridViewLibrary;
using DataOperationsConventional;
namespace SimpleReadConventional
{
public partial class Form1 : Form
{
private readonly BindingSource _customersBindingSource = new BindingSource();
public Form1()
{
InitializeComponent();
dataGridView1.AutoGenerateColumns = false;
Shown += Form1_Shown;
}
private async void Form1_Shown(object sender, EventArgs e)
{
_customersBindingSource.DataSource = await Operations.GetCustomersAsync();
CountryColumn.DisplayMember = "Name";
CountryColumn.ValueMember = "CountryIdentifier";
CountryColumn.DataPropertyName = "CountryIdentifier";
CountryColumn.DataSource = Operations.CountryTable();
CountryColumn.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;
ContactTitleColumn.DisplayMember = "ContactTitle";
ContactTitleColumn.ValueMember = "ContactTypeIdentifier";
ContactTitleColumn.DataPropertyName = "ContactTypeIdentifier";
ContactTitleColumn.DataSource = Operations.ContactTypeTable();
ContactTitleColumn.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;
dataGridView1.DataSource = _customersBindingSource;
dataGridView1.ExpandColumns();
CountryNamesComboBox.DataSource = Operations.CountryNameList();
CountryNamesComboBox.SelectedIndex = 1;
}
private void CurrentCustomerButton_Click(object sender, EventArgs e)
{
if (_customersBindingSource.DataSource != null && _customersBindingSource.Current != null)
{
var customer = ((DataRowView) _customersBindingSource.Current).Row;
MessageBox.Show($"Id: {customer.Field<int>("CustomerIdentifier")}" +
$"\nContact Id: {customer.Field<int>("ContactId")}");
}
}
private void FilterButton_Click(object sender, EventArgs e)
{
if (CountryNamesComboBox.DataSource == null) return;
if (CountryNamesComboBox.Text == @"Remove filter")
{
_customersBindingSource.Filter = "";
}
else
{
_customersBindingSource.Filter = $"CountryName = '{CountryNamesComboBox.Text}'";
}
}
}
}
Now using Entity Framework Core
using System;
using System.Windows.Forms;
using DataGridViewLibrary;
using DataOperationsEntityFrameworkCore.Classes;
using DataOperationsEntityFrameworkCore.Projections;
using Equin.ApplicationFramework;
namespace SimpleReadEntityFrameworkCore
{
public partial class Form1 : Form
{
private BindingListView<CustomerItem> _customerView;
private readonly BindingSource _customersBindingSource = new BindingSource();
public Form1()
{
InitializeComponent();
dataGridView1.AutoGenerateColumns = false;
Shown += Form1_Shown;
}
private async void Form1_Shown(object sender, EventArgs e)
{
_customerView = new BindingListView<CustomerItem>(await Operations.GetCustomersAsync());
_customersBindingSource.DataSource = _customerView;
CountryColumn.DisplayMember = "Name";
CountryColumn.ValueMember = "CountryIdentifier";
CountryColumn.DataPropertyName = "CountryIdentifier";
CountryColumn.DataSource = Operations.Countries();
CountryColumn.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;
ContactTitleColumn.DisplayMember = "ContactTitle";
ContactTitleColumn.ValueMember = "ContactTypeIdentifier";
ContactTitleColumn.DataPropertyName = "ContactTypeIdentifier";
ContactTitleColumn.DataSource = Operations.ContactTypes();
ContactTitleColumn.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;
dataGridView1.DataSource = _customersBindingSource;
dataGridView1.ExpandColumns();
CountryNamesComboBox.DataSource = Operations.CountryNameList();
CountryNamesComboBox.SelectedIndex = 1;
}
private void CurrentCustomerButton_Click(object sender, EventArgs e)
{
if (_customersBindingSource.DataSource != null && _customersBindingSource.Current != null)
{
var customer = _customerView[_customersBindingSource.Position].Object;
MessageBox.Show($"Id: {customer.CustomerIdentifier}\nContact Id: {customer.ContactId}");
}
}
private void FilterButton_Click(object sender, EventArgs e)
{
if (CountryNamesComboBox.DataSource == null) return;
if (CountryNamesComboBox.Text == @"Remove filter")
{
_customerView.RemoveFilter();
}
else
{
_customerView.ApplyFilter(customer => customer.CountryName == CountryNamesComboBox.Text);
}
}
}
}
Between the two EF Core has two less lines which is not all that important, what is important is even though both are close in lines of code that they are extremely similar.
CRUD operations
Although there are no code samples for these here are the concepts between conventional and EF Core.
Conventional CRUD
When working with a TableAdapter, CRUD operations are coded for the developer and difficult to alter. With a DataAdapter CRUD operations in many cases the DataAdapter handles the CRUD operations with options to override the automatic configuration. With a connection and command object there is a good deal more code involve with many different ways to handle changes ranging from subscribing to DataTable events to DataTable events coupled with various forms of data binding and data binding events coupled with events of a DataGridView.
Entity Framework CRUD
For Entity Framework, implementing INotificationChanged interface with a BindingList is suffice in most cases. Couple with with a) the DbContext is active using SaveChanges will save changes back to the database b) when the DbContext is not active EF Core has methods to attach a detached changed entity back to the database.
Validating data
Checking if data will be accepted by the database bases on things like length of a string, a column is required etc.
Conventional validation
Code is written directly in a form or a backing data class, if rules change in the database it's harder to update rules in code.
Entity Framework validation
Classes which represent table in a database can be setup with validation requirements.
In the following class
- CompanyName is required denoted by [Required] annotation, [StringLength(40)] indicates 40 characters is the max string length.
- For more on data annotations.
- Data annotations along is not enough to check for violations, the following classes provide the base to validate data which will be talked about in the next part in this series. To see a working example, see the following TechNet article on unit testing with uses validating classes.
namespace NorthWithFoldersAnnotations.Models
{
public partial class Customer
{
public Customer()
{
Orders = new HashSet<Order>();
}
[Key]
/// <summary>
/// Id
/// </summary>
public int CustomerIdentifier { get; set; }
[Required]
[StringLength(40)]
/// <summary>
/// Company
/// </summary>
public string CompanyName { get; set; }
/// <summary>
/// ContactId
/// </summary>
public int? ContactId { get; set; }
[StringLength(60)]
/// <summary>
/// Street
/// </summary>
public string Street { get; set; }
[StringLength(15)]
/// <summary>
/// City
/// </summary>
public string City { get; set; }
[StringLength(15)]
/// <summary>
/// Region
/// </summary>
public string Region { get; set; }
[StringLength(10)]
/// <summary>
/// Postal Code
/// </summary>
public string PostalCode { get; set; }
/// <summary>
/// CountryIdentifier
/// </summary>
public int? CountryIdentifier { get; set; }
[StringLength(24)]
/// <summary>
/// Phone
/// </summary>
public string Phone { get; set; }
[StringLength(24)]
/// <summary>
/// Fax
/// </summary>
public string Fax { get; set; }
/// <summary>
/// ContactTypeIdentifier
/// </summary>
public int? ContactTypeIdentifier { get; set; }
/// <summary>
/// Modified Date
/// </summary>
public DateTime? ModifiedDate { get; set; }
[ForeignKey(nameof(ContactId))]
[InverseProperty("Customers")]
public virtual Contact Contact { get; set; }
[ForeignKey(nameof(ContactTypeIdentifier))]
[InverseProperty(nameof(ContactType.Customers))]
public virtual ContactType ContactTypeIdentifierNavigation { get; set; }
[ForeignKey(nameof(CountryIdentifier))]
[InverseProperty(nameof(Country.Customers))]
public virtual Country CountryIdentifierNavigation { get; set; }
[InverseProperty(nameof(Order.CustomerIdentifierNavigation))]
public virtual ICollection<Order> Orders { get; set; }
}
}
Summary
Basic examples have been presented to show how to get started with Entity Framework Core 3 coming from a background of working with conventional data access operations e.g. TableAdapter, DataAdapter etc. in an effort to show its not much different between the two different methods and that keeping with basics Entity Framework is actually easier to work with.
There is always the option to stay with conventional data operations using TableAdapter, DataAdapter, connection and command object while taking time to learn Entity Framework Core provides a pathway to moving current projects to WPF and web solutions or to understand EF Core for starting new projects for WPF or the web while there are other differences the core to access data remains the same.
In the next part of the series a deeper dive into Entity Framework Core will be done without discussing conventional data access methods.
Important: Code presented is not intended to represent a well working CRUD project but instead showing the very basics for differences between conventional and Entity Framework Core data access.
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#)
Source code
- Download or clone the following GitHub repository directly in Visual Studio.
- Run the following database script.
- In Visual Studio, right click on the top node in solution explorer, select restore NuGet packages.
- Build each project and run them.
- Go back and review the code between the conventional data access project and the Entity Framework Core project