Windows forms Entity Framework Code first from database
Introduction
The intended audience is for those developers that have been writing data centric solution in Visual Studio using SQL-Server with TableAdapter with a desire to use Entity Framework rather than former methods for working with databases, DataAdapter or using SqlClient data provider. Each of these options have advantages and disadvantages were many times the selection was made based on one or more factors such as “this option is easy for me not being a regular developer”, they used the first option found while the educated developer chooses smartly.
For details on differences between conventional methods of accessing data see part one of this series Moving from ADO.NET to Entity Framework.
Time has brought new options for working with data, namely Entity Framework 6 classic or Entity Framework Core. The Entity Framework class families provide more power, streamline coding with more features than conventional methods of working with data in Windows Form solutions.
Even with Entity Framework providing better methods of working with data, developers working with Windows Forms tend to not a) look at Entity Framework, they are satisfied with their current methods of coding b) are unsure how to use Entity Framework in a Windows Form solution as most code samples on the Internet for Entity Framework are generally web based.
For those developers who are unsure about Entity Framework in Windows Forms solution this article will walk through reading, filtering, sorting, editing, deleting and adding data from a traditional Windows Form solution.
The database model below is a modified version of Microsoft’s North-Wind database. Originally North Wind tables were not the best design e.g. Customer table had a string for the primary key, contact information embedded in the customer table rather than broken out into child tables. For experienced developers they will see opportunities to farther refine the database model, that is fine but the point here is to work with a simple schema.
As this series progresses the database schema for Customer table will have more child tables which will use the same methods to work with data as the start of this series, just more complex in nature for proper normalization of data.
The following screenshot is how data is presented. Note there are two ComboBox columns utilized in the DataGridView were all rows show these ComboBoxes. In the code when running the sample application those ComboBox controls will not show as showing them simply clutters the screen. There are comments indicating to comment out two lines of code in form shown event of the main form to present the two ComboBoxes as shown below.
As progress is made through this series the form will change to accommodate new functionality.
Building the sample
Before building the Visual Studio, solution use the script under the folder data scripts to create a database, tables along with populating the tables. Once the database has been completed note that there are cascading rules for delete operations between customer and contact tables so that when removing a customer the contact is removed. If there is no rule for cascade deletes attempting a delete would cause a runtime error. Note that in some cases the contact should not be removed, for these cases contacts may have a active field which is set to false but available, then used the active flag toggles to true.
When building the solution note there are several NuGet packages used, if they are not found then right-click on Solution Explorer and select restore NuGet packages then build the solution.
Description
When using TableAdapters a developer selects "Add New Data Source" when a project is selected and the Data Source window is active. Clicking the link on the Data Source window will perform a step-by-step walkthrough to select a database which can be any database which is supported while for this, the database will be a SQL-Server database from SQL-Express (local/attached database) or SQL-Server (server based database). Once a database and tables have been selected a visual representation is created in a .xsd file. To the developer they see the tables but not all the code which the data wizard generated. When changes are made to the selected database and the generated code is refreshed any changes to the file will be overwritten. Making changes in the generated files is not how changes should be made, instead partial classes are used. No matter, the point is, there is more code than needed in many cases.
Moving to Entity Framework
Moving over to Entity Framework, one of the choices is "EF Designer from database", this is very similar to TableAdapter methodology in that tables selected are shown in a canvas with a file extension of .edmx. Changes can be made to the database, refreshed from the .edmx and if a developer had made changes to the data classes they are wiped out. As with TableAdapters partial classes provide a way to customize the generated classes. For example, implement an Interface on each class. The following Interface indicates each class will have a Identifier property.
public interface IBaseEntity
{
int Identifier { get; }
}
Where the following is how the Interface is implemented in a partial class for Entity Framework which can be done in any flavor of Entity Framework.
public partial class Customer : IBaseEntity
{
public int Identifier
{
get
{
return id;
}
}
}
Another class
public partial class GenderType : IBaseEntity
{
public int Identifier
{
get
{
return GenderIdentifier;
}
}
}
Then when unit testing with live data rather than mocked data a method such as shown below can work against a common identifier (see the Find method below). The following method can be enhanced to include more information and also log information to a file.
public bool AnnihilateData(List<object> mAnnihilateList)
{
bool mAnnihilateDataSuccessful = false;
using (var destroyContext = new PersonEntities())
{
for (int i = mAnnihilateList.Count - 1; i >= 0; i--)
{
try
{
var currentObject = mAnnihilateList[i];
var existingItem = destroyContext
.Set(currentObject.GetType())
.Find(((IBaseEntity)currentObject).Identifier);
if (existingItem != null)
{
try
{
var attachedEntry = destroyContext.Entry(existingItem);
attachedEntry.CurrentValues.SetValues(currentObject);
destroyContext.Set(existingItem.GetType()).Attach(existingItem);
destroyContext.Set(existingItem.GetType()).Remove(existingItem);
}
catch (Exception)
{
// ignore nothing do to as the object was not added in properly.
}
}
else
{
var item = currentObject.GetType();
}
}
catch (Exception)
{
//catch and continue save what we can
}
}
try
{
var resultCount = destroyContext.SaveChanges();
var annihlationCount = mAnnihilateList.Count;
mAnnihilateDataSuccessful = (resultCount == annihlationCount);
}
catch (Exception)
{
// keep on going
}
finally
{
destroyContext.Dispose();
}
}
return mAnnihilateDataSuccessful;
}
While working with either a TableAdapter or with Entity Framework "EF Designer from database" appears an easy path to use there are pitfalls with both.
Here is a short list, with TableAdapter, modifiy the database (rename a field used on a Windows Form, refresh the data source in your project the field on the form is not refreshed so this must be handled manually. Also, creating partial classes and taping into events has never been explained well in the Microsoft documentation, the namespace for the TableAdapter parent classes is in a completely different namespace than the project's namespace.
With "EF Designer from database" when there is a large array of tables e.g. 30 plus the designer is easily corrupted. There is a work-around, create multiple designers and make sure that related tables are not split between designers as this can cause other issues. Another downside is unlike code first method for working with databases using "EF Designer from database" it's nearly impossible to do custom configuration with the Entity model.
The connection string for a model is in the DbContext class where "name=NorthWindModel" points to a connection string in the project's app configuration file.
public partial class NorthWindAzureContext : DbContext
{
public NorthWindAzureContext()
: base("name=NorthWindModel")
{
}
With code first, the connection string can be setup as follows. In the example below the connection string is hard to code but there may be programmer's logic that changes the connection string e.g. the server may be Express or full server, using SMO (SQL-Server Management Objects) or TSQL a determination can be made if either or exists then set the connection string accordingly. Another reason for allowing a conditional connection string is to change from development to test to production environments.
public NorthWindAzureContext()
: base("data source=.\\SQLEXPRESS;initial catalog=NorthWindAzureForInserts;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework")
{
}
By selecting "Code first from database" a developer can have full control over working with data and keep things light weight e.g. DataSet/DataTable method of working with data may endup being heavy in that there is functionality and events that may weigh down a large application and all developers want their solution to be lightening fast.
To populate a ComboBox with country names and a ValueMember which is needed to write to a parent record e.g. country identifier for a customer the average developer will create a connection and command object, create a select statement and read returning data into a DataTable followed by setting DisplayMember and ValueMember.
With Entity Framework the first class generated by Visual Studio for countries.
{
[System.Diagnostics.CodeAnalysis.SuppressMessage(
"Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
public Country()
{
Customers = new HashSet<Customer>();
}
[Key]
public int CountryIdentifier { get; set; }
public string Name { get; set; }
[System.Diagnostics.CodeAnalysis.SuppressMessage(
"Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<Customer> Customers { get; set; }
}
Here DisplayMember and ValueMember are needed while using a class created for displaying countries in a ComboBox with no need to set DisplayMember or ValueMember. In the Data Annotation section below an alternate idea is used, a read-only property marked as [NotMapped].
public class CountryItem
{
public int? CountryIdentifier { get; set; }
public string CountyName { get; set; }
public override string ToString()
{
return CountyName;
}
}
To read data from the database the following performs the read. An important note is that by default when reading the Country Table related entities are also loaded which is not needed so by setting LazyLoadingEnabled = false will not cause related tables to be loaded (more on this later).
public List<CountryItem> GetCountries()
{
using (var context = new NorthWindAzureContext())
{
// indicates not to load Customers
context.Configuration.LazyLoadingEnabled = false;
return context.Countries.Select(country => new CountryItem()
{
CountryIdentifier = country.CountryIdentifier,
CountyName = country.Name
}).ToList();
}
}
To load a ComboBox along with as the first item have a "Select".
var countries = _operations.GetCountries();
countries.Insert(0, new CountryItem() {CountryIdentifier = 0, CountyName = "Select"});
Either Data annotations (discussed later in this article) or standard assertion may be used to validate a country has been selected. The main take-away is control on how data is loaded. Going back to the method GetCountries, the ToList could be removed and return IEnumerable then use .ToList in the form, running .ToList is when data is retrieved.
Creating a Windows Forms solution
Conventional wisdom for those new to development is to create a new Windows Forms project and write data operations directly in one or more forms.
Long time developers will split logic into classes in the same project while other long time developers will not only create classes in the same project but for reusable code will be placed into one or more class projects.
In the code presented here, code for data operations will reside in a form along with classes residing in other class projects.
The first task after creating a new Visual Studio solution is to create a class project (NorthWindDataLibrary) in this example which will contain methods to get data while editing, removing and adding data will be in the main form. Note this code will later transition to have ninety percent of edit, remove and adding into the backend data class in the (in this case NorthWindDataLibrary).
Creating our Entity Framework classes.
- Right click on the project in Solution Explorer.
- Select Add -> New Item.
- Select Data, if the item ADO.NET Entity Data Model is not selected, select this item.
- Leave Name: Model alone for now.
- Press the Add button.
- In the next screen select "Code First from database" and press the Next button.
- On the next screen create a new connection to the database or select a pre-existing connection and press Next button.
- Select "Entity Framework 6.x" and press the Next button.
- On the first node for tables navigate to the dbo node, expand to see all tables.
- The following will be needed, Contacts, ContactType, Countries and Customers followed by pressing the Finish button.
- After a few seconds the Solution Explorer is active. Noe there are classes for each table selected and Model1.cs
- Right click on Model1.cs and rename to NorthWindAzureContext.cs
- Select the contents of app.config.
- Create a new Windows form project.
- Open it's app.config, paste in what was just copied, the connection string is needed to access data with Entity Framework.
- Right click on Solution Explorer, select "Manage NuGet packages for the solution"
- Make Entity Framework the current item.
- On the right click the check box for the class project just created and finish with pressing install.
- Add a reference for the class project to the new windows form project.
- Build the solution to verify the code so far is good.
- Add the solution to source control e.g. Dev Ops (Visual Studio online) or GitHub so that if something breaks there are backups to revert too.
Next several classes are needed for providing choices when editing customer records in a DataGridView where several DataGridViewComboBox columns will be needed for contact type and country (see first screenshot).
The following class will permit assigning a list of the following class to a DataGridViewComobox and in other cases ListBox controls without setting DisplayMember as ToString is used when DisplayMember is not set.
public class ContactListBox
{
public int? ContactId { get; set; }
public string Name { get; set; }
public override string ToString()
{
return Name;
}
}
Another class is needed to pull table data together.
namespace NorthWindDataLibrary.Classes
{
/// <summary>
/// Container for displaying customers in a DataGridView
/// The Notification change via Bindable will be discussed
/// in a future article to this article.
/// </summary>
public class CustomerEntity : Bindable
{
public int CustomerIdentifier { get; set; }
public string CompanyName
{
get => Get<string>();
set => Set(value);
}
public int? ContactIdentifier { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public int ContactTypeIdentifier { get; set; }
public string ContactTitle { get; set; }
public string Address { get; set; }
public string City { get; set; }
public string PostalCode { get; set; }
public int? CountryIdentifier { get; set; }
public string CountyName { get; set; }
}
}
Next, a class for reading data from the SQL-Server database. Note the class level private variable, _context, this is the object responsible for reading data where when data is requested a connection is opened, data is retrieved then Entity Framework closes the connection.
The object _context is of type NorthWindAzureContext which inherits from DbContext where DbContext provides functionality to Write and execute queries, Materialize query results as entity objects, Track changes that are made to those objects, Persist object changes back on the database and Bind objects in memory to UI controls.
Rules for DbContext in regards to Windows forms, one DbContext per form while in web applications operations should perform operations and dispose of the DbContext immediately.
If the context instance is created by a dependency injection container, it is usually the responsibility of the container to dispose the context.
using System.Collections.Generic;
using System.Linq;
namespace NorthWindDataLibrary.Classes
{
public class Operations
{
/// <summary>
/// Context setup in AllCustomers method
/// </summary>
private NorthWindAzureContext _context;
/// <summary>
/// Get all customers into a custom class suitable for viewing
/// in a DataGridView which in the form requires special attention
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public List<CustomerEntity> AllCustomers(NorthWindAzureContext context)
{
_context = context;
var customerData = (
from customer in context.Customers
join contactType in context.ContactTypes on customer.ContactTypeIdentifier
equals contactType.ContactTypeIdentifier
join contact in context.Contacts on customer.ContactId equals contact.ContactId
select new CustomerEntity
{
CustomerIdentifier = customer.CustomerIdentifier,
CompanyName = customer.CompanyName,
ContactIdentifier = customer.ContactId,
FirstName = contact.FirstName,
LastName = contact.LastName,
ContactTypeIdentifier = contactType.ContactTypeIdentifier,
ContactTitle = contactType.ContactTitle,
Address = customer.Address,
City = customer.City,
PostalCode = customer.PostalCode,
CountryIdentifier = customer.CountryIdentifier,
CountyName = customer.Country.Name
}).ToList();
return customerData;
}
/// <summary>
/// Get all countries
/// </summary>
/// <returns></returns>
public List<CountryItem> GetCountries()
{
using (var context = new NorthWindAzureContext())
{
// indicates not to load Customers
context.Configuration.LazyLoadingEnabled = false;
return context.Countries.Select(country => new CountryItem()
{
CountryIdentifier = country.CountryIdentifier,
CountyName = country.Name
}).ToList();
}
}
/// <summary>
/// Get all contact types
/// </summary>
/// <returns></returns>
public List<ContactType> GetContactTypes()
{
using (var context = new NorthWindAzureContext())
{
context.Configuration.LazyLoadingEnabled = false;
return context.ContactTypes.ToList();
}
}
/// <summary>
/// Get all known contacts
/// </summary>
/// <returns></returns>
public List<Contact> GetContacts()
{
using (var context = new NorthWindAzureContext())
{
// indicates not to load Customers
context.Configuration.LazyLoadingEnabled = false;
return context.Contacts.ToList();
}
}
/// <summary>
/// Remove customer by primary key
/// </summary>
/// <param name="pCustomerIdentifier"></param>
/// <returns></returns>
/// <remarks>
/// Discuss cascading delete rules
/// - how to check for child table usage
/// </remarks>
public bool RemoveCustomer(int pCustomerIdentifier)
{
return true;
}
}
}
Note It's not hard to notice everything above and to come is void of DataSet/DataTable method of coding. This should mean those not very familiar working with classes and are eager to use Entity Framework, to get the most out of working with Entity Framework now is a good time to get familiar working with classes.
Caveat on the method AllCustomers, to see how a conventional SELECT statement would look like see the SELECT below. In AllCustomers method there are only two joins as we can refer to country in the customer entity as lazy loading is enabled and country is a related table (also called associations) populated when requesting customer data.
SELECT Cust.CustomerIdentifier,
Cust.CompanyName,
Cust.ContactId,
Contacts.FirstName,
Contacts.LastName,
Cust.ContactTypeIdentifier,
CT.ContactTitle,
Cust.Address AS Street,
Cust.City,
Cust.PostalCode,
Cust.CountryIdentifier,
Countries.Name AS CountryName,
Cust.ModifiedDate
FROM Customers AS Cust
INNER JOIN ContactType AS CT ON Cust.ContactTypeIdentifier = CT.ContactTypeIdentifier
INNER JOIN Contacts ON Cust.ContactId = Contacts.ContactId
INNER JOIN Countries ON Cust.CountryIdentifier = Countries.CountryIdentifier
In Entity Framework, an entity can be related to other entities through an association or relationship. Each relationship contains two ends that describe the entity type and the multiplicity of the type (one, zero-or-one, or many) for the two entities in that relationship. The relationship may be governed by a referential constraint, which describes which end in the relationship is a principal role and which is a dependent role. See also, relationships, navigation properties and foreign keys.
Writing form level code
Before beginning to write code objectives need to be known, in this case data needs to be presented in a DataGridView control where several properties (country and contact details) will need a way to allow a user to change the current values, in this case a DataGridViewComboBox. Also, contact first and last name come from a child related table so this must be considered.
Another factor, provide the ability to sort/order columns in the DataGridView as simply assigning data to the DataGridView will not provide the ability to sort/order columns. In this case rather than rolling a in-house component to handle sorting a third party free component (with source code) will be utilize.
For editing properties that are not of type DataGridViewComboBox columns the following code is used. In EditingControlShowing event of the DataGridView DataGridViewComboBox logic is handled so a check is made at the start of the event to detect if the current cell is a DataGridViewComboBox by determining if the cell type is DataGridViewComboBoxEditingControl using a language extension method included in the source code. If not of type DataGridViewComboBoxEditingControl then exit the event, otherwise first determine which of the two DataGridViewComboBox is current then subscribe to one of two events,
In these two events the proper country identifier or proper ContactType identifier is set to the foreign key in the current customer's record, This must be done as when loading data there are joined tables that Entity Framework does not or can not track, Manually setting these identifier will mark the customer as dirty.
private void DataGridView1_EditingControlShowing(
object sender, DataGridViewEditingControlShowingEventArgs e)
{
if (!gridView.CurrentCell.IsComboBoxCell()) return;
var comboBox = e.Control as ComboBox;
// ReSharper disable once PossibleNullReferenceException
comboBox.SelectionChangeCommitted -= SelectionChangeCommittedForCountryColumn;
comboBox.SelectionChangeCommitted -= SelectionChangeCommittedForContactTitleColumn;
if (gridView.Columns[gridView.CurrentCell.ColumnIndex].Name == "CountyNameColumn")
{
comboBox.SelectionChangeCommitted += SelectionChangeCommittedForCountryColumn;
}
else if (gridView.Columns[gridView.CurrentCell.ColumnIndex].Name == "ContactTitleColumn")
{
comboBox.SelectionChangeCommitted += SelectionChangeCommittedForContactTitleColumn;
}
}
/// <summary>
/// Set backing customer field foreign key for Country for current customer record
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void SelectionChangeCommittedForCountryColumn(object sender, EventArgs e)
{
var customer = _customersBindingSource.CurrentCustomerEntity();
var identifier = ((CountryItem)(((DataGridViewComboBoxEditingControl)sender).SelectedItem))
.CountryIdentifier;
customer.CountryIdentifier = identifier;
// ReSharper disable once PossibleNullReferenceException
context.Customers
.FirstOrDefault(cust => cust.CustomerIdentifier == customer.CustomerIdentifier)
.CountryIdentifier = identifier;
}
/// <summary>
/// Set backing customer field foreign key for contact title type for current customer record
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void SelectionChangeCommittedForContactTitleColumn(object sender, EventArgs e)
{
var customer = _customersBindingSource.CurrentCustomerEntity();
var identifier = ((ContactType)(((DataGridViewComboBoxEditingControl)sender).SelectedItem))
.ContactTypeIdentifier;
customer.ContactTypeIdentifier = identifier;
// ReSharper disable once PossibleNullReferenceException
context.Customers
.FirstOrDefault(cust => cust.CustomerIdentifier == customer.CustomerIdentifier)
.ContactTypeIdentifier = identifier;
}
In the above code several language extensions were used to keep the code clean as possible.
Handling changes to properties other than country, contact type and contact first and last name is done in the DataGridView event CurrentCellDirtyStateChanges.
To get the column name the following line ask the column name via OwningColumn.
var currentColumnName = gridView.CurrentCell.OwningColumn.Name;
Next line is used to get the current row's customer identifier which is then used to get the associated customer object in the DbContext.
var customerEntity = _customersBindingSource.CurrentCustomerEntity();
var customer = context.Customers
.FirstOrDefault(cust =>
cust.CustomerIdentifier == customerEntity.CustomerIdentifier);
This is followed by a if statement to properly handle setting the property from the item in the DataGridView back to the actual Customer object which is done using CurrentValue of the proper Customer object.
context.Entry(customer).Property(gridView.CurrentCell.OwningColumn.DataPropertyName)
.CurrentValue = gridView.EditingControl.Text;
To delete the current row from the DataGridView the following line gains access to the object for obtaining the primary key. This also provides access to the company/customer name used in a MessageBox asking if the user wants to remove the record or not. If they decide to remove the customer the customer state is marked as Deleted and then physically removed from the BindingSource. To permanently remove the record the save button needs to be used for the SaveChanges to propagate back to the backend SQL-Server database.
private void bindingNavigatorDeleteCustomer_Click(object sender, EventArgs e)
{
var currentCustomer = _customersBindingSource.CurrentCustomerEntity();
if (Question($"Delete '{currentCustomer.CompanyName}'"))
{
var customer = context.Customers
.FirstOrDefault(cust =>
cust.CustomerIdentifier == currentCustomer.CustomerIdentifier);
context.Entry(customer).State = EntityState.Deleted;
_customersBindingSource.RemoveCurrent();
}
}
Saving Data
Clicking the save all button will save all changes back to the database then for this sample will show the count of changes to the IDE output window. Of course a Console.WriteLine would never be in a real application, this is here to show that SaveChanges returns how many items were saved.
Console.WriteLine(context.SaveChanges());
One important feature which is needed is to stop the form from closing if there are any uncommitted changes which in this case is handled in the form closing event by inspecting HasChanges property of the change tracker for the active DbContext.
private void MainForm_Closing(object sender, CancelEventArgs e)
{
if (context.ChangeTracker.HasChanges())
{
if (!Question("There are pending changes, abort?"))
{
e.Cancel = true;
}
}
}
Async query and save information
Use asynchronous methods such as ToListAsync extension method will allow applications be remain responsive when dealing with large sets of data. Using asynchronous methods in a windows forms requires checking for cross thread violations.
In the following method ToListAsync is used.
public async Task<List<CustomerEntity>> AllCustomersAsync(NorthWindAzureContext context)
{
_context = context;
var customerData = await (
from customer in context.Customers
join contactType in context.ContactTypes on customer.ContactTypeIdentifier
equals contactType.ContactTypeIdentifier
join contact in context.Contacts on customer.ContactId equals contact.ContactId
select new CustomerEntity
{
CustomerIdentifier = customer.CustomerIdentifier,
CompanyName = customer.CompanyName,
ContactIdentifier = customer.ContactId,
FirstName = contact.FirstName,
LastName = contact.LastName,
ContactTypeIdentifier = contactType.ContactTypeIdentifier,
ContactTitle = contactType.ContactTitle,
Address = customer.Address,
City = customer.City,
PostalCode = customer.PostalCode,
CountryIdentifier = customer.CountryIdentifier,
CountyName = customer.Country.Name
}).ToListAsync();
return customerData;
}
In the form's shown event the signature needs to include async.
private async void Form1_Shown(object sender, EventArgs e)
In form shown the following code is responsible for loading customer data via the method above.
var result = await _operations.AllCustomersAsync(context)
.ConfigureAwait(false);
_customersView =
new BindingListView<CustomerEntity>(result);
Loading data into the DataGridView and setting the BindingNavigator's BindingSource property.
_customersBindingSource.DataSource = _customersView;
gridView.Invoke(new Action(() =>
gridView.DataSource = _customersBindingSource));
gridView.Invoke(new Action(() =>
gridView.ExpandColumns()));
bindingNavigator1.Invoke(new Action(() =>
bindingNavigator1.BindingSource =
_customersBindingSource));
With the above code changes an application will be responsive while loading data. For the average application asynchronous programming is overkill so only use asynchronous techniques when the user interface is not being responsive.
Saving changes synchronously, simply call SaveChanges on the DbContext.
private void SaveAllChangesButton_Click(object sender, EventArgs e)
{
Console.WriteLine(context.SaveChanges());
}
For saving asynchronously the button click event needs async as per below to call SaveChangesAysnc which as with SaveChanges returns a integer of number of changes.
private async void SaveAllChangesButtonAsync_Click(object sender, EventArgs e)
{
Console.WriteLine(await context.SaveChangesAsync());
}
Changes to the backend database
It’s commonplace for a database schema to change as requirements change for a project. For example, using the database for the code sample contact information is stored in a contact table and a contact type table. Now an additional requirement is to store the contact’s phone information and since a contact may have multiple phones (home, cell, work for instance) tables will need to be added to the database schema, device type e.g. desk or cell phone along if the phone number is active.
For those just starting out, add these tables to the database and run queries to ensure everything works as expected before moving on. The next step is to either create a new temporary model and perform the same steps as initially done to create the original model, once created copy those tables and DbSet definitions to the original model. For others there are two additional options, use code migrations or simply code the changes.
Custom BindingList Sort/Filter
The standard DataGridView does not know how to function in regards to sorting and filtering data with Entity Framework so a custom component is used. Refer to source code to see how this component is implemented and note that to get to data several cast are needed. Current source code demonstrates filtering and will be discussed in depth in the next article in this series.
Common cheat sheet
Accessing data
(this is similar to creating a SqlClient connection object), once the using statement has exited the connection is closed. When using a DbContext as a private form level variable after accessing data the connection is closed, each time data is needed a connection is opened then closed when done.
using (var context = new NorthWindAzureContext())
{
// perform work
}
Create operation
using (var context = new NorthWindAzureContext())
{
context.Customers.Add(newCustomer);
context.SaveChanges();
}
Alternate
using (var context = new NorthWindAzureContext())
{
context.Entry(newCustomer).State = EntityState.Added;
context.SaveChanges();
}
Update operation
using (var context = new NorthWindAzureContext())
{
context.Entry(modifiedCustomer).State = EntityState.Modified;
context.SaveChanges();
}
Delete operation
using (var context = new NorthWindAzureContext())
{
context.Entry(customer).State = EntityState.Deleted;
context.SaveChanges();
}
Entity State
using (var context = new NorthWindAzureContext())
{
var currentState = context.Entry(customer).State;
}
Find a entity by primary key
using (var context = new NorthWindAzureContext())
{
customer = context.Customers.Find(2);
}
Disable lazy loading
context.Configuration.LazyLoadingEnabled = false;
Disable validation
context.Configuration.ValidateOnSaveEnabled = false;
Deferred Execution
Deferred execution means that the evaluation of an expression is delayed until its realized value is required. It greatly improves performance by avoiding unnecessary execution.
There may be times when a query takes longer than expected to return which may be tolerable in a windows form project yet for a web application would be unacceptable. For this reason it’s important to understand deferred execution.
In the following example which may appear fine yet there is a problem. The problem is that using .ToList will immediately return all records in the Customer table which in this case has 50,000 records where the two where conditions are expected to return three records. It will return three off the 50,000 which is the problem, returning 50,000 records then filtering down to three.
using (var context = new NorthWindAzureContext())
{
var results = context.Customers.ToList()
.Where(cust => cust.CountryIdentifier == 9)
.Where(cust => cust.ContactTypeIdentifier == 12);
}
(and this would be the same as above)
using (var context = new NorthWindAzureContext())
{
var results = context.Customers.ToList()
.Where(cust => cust.CountryIdentifier == 9 &&
cust.ContactTypeIdentifier == 12);
}
The following would perform the query without returning all 50,000 records.
using (var context = new NorthWindAzureContext())
{
var results = context.Customers.AsQueryable();
results = results.Where(cust => cust.CountryIdentifier == 9);
results = results.Where(cust => cust.ContactTypeIdentifier == 12);
}
Nothing is returned until results.ToList() is executed. The following does the same as the first query above, return 50,000 records then filters down to the first row.
public Customer GetFirstCustomer()
{
using (var context = new NorthWindAzureContext())
{
return context.Customers.ToList().FirstOrDefault();
}
}
While the following returns one record.
public Customer GetFirstCustomer()
{
using (var context = new NorthWindAzureContext())
{
return context.Customers.FirstOrDefault();
}
}
Reading with selective navigation properties
When LazyLoadingEnabled is enabled (which is default) selecting an entity such as Customer all navigation properties (child tables) are also loaded which may not be what a developer needs.
In the following example, all child tables are loaded.
using (var context = new NorthWindAzureContext())
{
var example = context.Customers.ToList();
}
Now with LazyLoadindEnabled = false
using (var context = new NorthWindAzureContext())
{
context.Configuration.LazyLoadingEnabled = false;
var example = context.Customers.ToList();
}
In the following example ContactType is excluded as only Country and Contact have been included using the Include extension method.
using (var context = new NorthWindAzureContext())
{
context.Configuration.LazyLoadingEnabled = false;
var example = context.Customers
.Include(cust => cust.Country)
.Include(cust => cust.Contact)
.ToList();
}
Note that Contact in the above will still have Customer navigation property.
To remove Customer navigation property use the following code.
using (var context = new NorthWindAzureContext())
{
context.Configuration.LazyLoadingEnabled = false;
var example = context.Customers
.Include(cust => cust.Country)
.Include(cust => cust.Contact)
.ToList();
foreach (var customer in example)
{
customer.Country.Customers = null;
}
}
Logging Entity Framework
There may be times a developer needs to see the SQL which Entity Framework generated, for this simply use the following in development environment. All output is sent to the IDE Output window.
context.Database.Log = Console.Write;
If there is a requirement to save logging use a class to capture information to a text file while in development or test environment.
using System;
using System.IO;
namespace ReadEditCustomerWithSpecialClass
{
public class FileLogger
{
public static void Log(string logInfo)
{
File.AppendAllText(
Path.Combine(
AppDomain.CurrentDomain.BaseDirectory,"context.log"),
logInfo);
}
}
}
Implementing logging using the class above (note it's the same as Console.Write is done)
context.Database.Log = FileLogger.Log;
Logging can also be done using a logging library such as NLog, see the following example.
Data Annotations
With Data Annotations attributes a developer can configure entity classes and properties ranging from changing a table's known name, marking a property as a primary key using [Key], marking a property as required using [Required] and a good deal more.
Many articles will talk about data annotations with web solutions as one of the primary uses besides declarat'ing a property for describing classes and properties in a class is for user interface notification of failed validation when modifying or adding a new record to the database. In the next article in this series validation will be discussed starting at the model level which will bubble up tp the windows form level.
It’s imperative to understand these attributes especially when working with code first methods for working with data as a developer can add an attribute in many cases rather than write client-side code. There are other benefits like being able to add properties to a class that will not be tracked by Entity Framework e.g. [NotMapped] were a common usage would be to concatenate two fields perhaps first and last name of a person to display in a ComboBox or ListBox.
[NotMapped]
public string FullName => $"{FirstName} {LastName}";
Combining attributes, when classes are generated if a field is found to be required and has a max length code as shown below will be written.
[Required]
[StringLength(40)]
public string CompanyName { get; set; }
These can be combined as followed
[Required, StringLength(40)]
public string CompanyName { get; set; }
Another example, a user must login to an application or create a password for the first time they need to use an application. This can be done by adding a property (which we don't want Entity Framework to track so it's marked as [NotMapped]) and use the Compare attribute as per below.
[Table("Customer")]
public partial class Customer
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string AccountNumber { get; set; }
[Required(ErrorMessage = "{0} is required")]
[StringLength(20, MinimumLength = 6)]
public string UserPassword { get; set; }
[NotMapped]
[Compare("Password", ErrorMessage = "Passwords do not match, please try again")]
[StringLength(20, MinimumLength = 6)]
public string PasswordConfirmation { get; set; }
}
Usually with a password there are rules, must contain at least one digit, one lower and one upper cased character with min and max length. Rather than do this client side an additional attribute can be used.
[Required(ErrorMessage = "{0} is required")]
[StringLength(20, MinimumLength = 6)]
[PasswordCheck(ErrorMessage = "Must include a number and symbol in {0}")]
public string UserPassword { get; set; }
Definition of PasswordCheck which would reside in a class library with classes representing rules for applying to property in the current and or future projects rather than rewriting them.
Note in the variable reason you might want a completely different message, that is up to you to either a) tell them the reason why b) have them reference a company page explaining the password rules.
public class PasswordCheck : ValidationAttribute
{
public override bool IsValid(object value)
{
var validPassword = false;
var reason = string.Empty;
string password = (value == null) ? string.Empty : value.ToString();
if (string.IsNullOrWhiteSpace(password) || password.Length < 6)
{
reason = "new password must be at least 6 characters long. ";
}
else
{
Regex pattern = new Regex("((?=.*\\d)(?=.*[a-z])(?=.*[A-Z])(?=.*[@#$%]).{6,20})");
if (!pattern.IsMatch(password))
{
reason += "Your new password must contain at least 1 symbol character and number.";
}
else
{
validPassword = true;
}
}
if (validPassword)
{
return true;
}
else
{
return false;
}
}
}
The above examples are just a few reasons for learning data annotations.
SQL Injection
LINQ to SQL passes all data to the database via SQL parameters. So, although the SQL query is composed dynamically, the values are substituted server side through parameters safeguarding against the most common cause of SQL injection attacks.
General caveats
In the code samples presented here all data has been bound to a DataGridView, data binding can also be done manually via DataBinding.Add e.g. Firstname.DataBinding.Add or using a wizard to do the binding for you (see the following page) although the better method of the two is to do the databinding in code rather than a wizard.
Working from one project
Although having presented a Visual Studio solution which comprises of a forms and class projects a developer may at first want to build the first Entity Framework application in a single Windows forms project. Going this route it fine if the Entity Framework code will never be used in another project the Entity Framework code may reside in the form but it's even better to write the Entity Framework code in a separate class as this will make code not dependent on a single form.
This project requirements are for a hotel where guest may place orders for breakfast so that in the morning orders may be prepared so that when guest are ready their order will be ready.
The simplified database schema.
The model is generated the same as the first example in the forms project.
To present data a ListBox will be populated with guest names.
To populate this ListBox ToString is overridden with first and last name of the guest.
namespace RestaurantDemo
{
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.Spatial;
public partial class Guest
{
[System.Diagnostics.CodeAnalysis
.SuppressMessage(
"Microsoft.Usage",
"CA2214:DoNotCallOverridableMethodsInConstructors")]
public Guest()
{
BreakfastOrders = new HashSet<BreakfastOrder>();
}
[Key]
public int GuestIdentifier { get; set; }
public int? RoomIdentifier { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
[System.Diagnostics.CodeAnalysis
.SuppressMessage(
"Microsoft.Usage",
"CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<BreakfastOrder> BreakfastOrders { get; set; }
public virtual Room Room { get; set; }
public override string ToString()
{
return $"{FirstName} {LastName}";
}
}
}
Code to populate the ListBox in Operations class
public List<Guest> TodayGuest()
{
using (var context = new RestaurantContext())
{
context.Configuration.LazyLoadingEnabled = false;
return context.Guests.ToList();
}
}
Which is implemented as follows in the form.
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
Shown += Form1_Shown;
}
private void Form1_Shown(object sender, EventArgs e)
{
var ops = new Operations();
listBox1.DataSource = ops.TodayGuest();
}
When the button "Get breakfast order" is clicked the following retrieves the guest breakfast order.
/// <summary>
/// If a guest has a breakfast order return it, if nothing
/// ordered indicate no order was placed.
/// </summary>
/// <param name="guestIdentifier"></param>
/// <returns></returns>
public string GuestBreakfastOrderForToday(int guestIdentifier)
{
var today = Now.Date;
using (var context = new RestaurantContext())
{
var results =
(
from orderItems in context.BreakfastOrderItems
join orderItem in context.BreakfastOrderItems on
orderItems.BreakfastItem.ItemIdentifier
equals orderItem.ItemIdentifier
where orderItem.BreakfastOrder.OrderDate == today &&
orderItem.GuestIdentifier == guestIdentifier
select orderItem
).ToList();
if (results.Count >0)
{
// ReSharper disable once PossibleNullReferenceException
var room = context.Guests
.FirstOrDefault(g => g.GuestIdentifier == guestIdentifier).Room;
return string.Join(Environment.NewLine, results
.Select(item => item.BreakfastItem.Item).ToArray()) +
$"\n\nRoom: {room.RoomDesginator}{room.RoomNumber}";
}
else
{
return "No order placed";
}
}
}
Form code
private void GetBreakfastOrderButton_Click(object sender, EventArgs e)
{
var guest = (Guest) listBox1.SelectedItem;
var ops = new Operations();
var guestOrder = ops.GuestBreakfastOrderForToday(guest.GuestIdentifier);
// ReSharper disable once LocalizableElement
MessageBox.Show($"{guest}\nOrder\n{guestOrder}");
}
Notes on the method Operations.GuestBreakfastOrderForToday, the variable today must be used rather than using
where orderItem.BreakfastOrder.OrderDate == Now.Date
As Entity Framework does not know how to deal with Now.Date. There are Entity Framework functions to explore also. Another point is better understanding how to write the joins is to first write SQL SELECT statement, get it functioning then model your Entity Framework query against the working SQL SELECT statement.
Summary
In this second part of the series moving from ADO.NET to Entity Framework the basic building blocks have been discussed in detail for displaying data in a Windows Form DataGridView along with starting off editing and saving data to the back-end database including simple filtering and sorting of data in a DataGridView. In the next installment of this series adding new records and full editing of data will be taught using modal window forms which will complete what is needed to build a standard solution for one table with reference table so that the next step will be working with master details in windows forms.
References
See also
- Relationship in Entity Framework Using Code First Approach With Fluent API
- Entity Framework: Disconnected Entities
- Entity Framework: Wiki portal
- Entity Framework Introduction using C#, Part I
- Entity Framework Code First -Defining Foreign Keys using Data Annotations and Fluent API
- Entity Framework TechNet
- Moving from ADO.NET To Entity Framework
- Getting Started with Entity Framework Core: Database-First Development
- Entity Framework FAQ: Concurrency
- Entity Framework FAQ: Conceptual Model (EDM)
Source code
Source code resides in a GitHub repository which requires (as indicating in the Building the sample section) creating the database first found in a folder named datascripts. There is source code that has not been discussed at this point and sections of code that are placeholders for code in the next article in this series.