Entity Framework views in Windows Forms (VB.NET)

Introduction

Presenting database information rarely involves one table as a well-defined database will be relational e.g. orders, order details which a DataGridView does not handle which can easily be accomplished by following steps presented. What is not shown, how to update changes back to the database which will be presented in the next article in this series. 

Code sample setup

• Create database and populate using the following script.
• Clone the following repository.
• In app.config change the connection string data source.

The goal is to present data from a customer table, two reference tables, contact and country which when viewed in a DataGridView is sortable by clicking column headers and code to get the current customer primary key, contact foreign key and country foreign key which are needed when editing properties or removing a record.

Main Setup

All operations are performed in a Windows Form project with one NuGet package known as BindingListView which provides the capability to sort a DataGridView which is not possible by setting a list read in using Entity Framework to a DataGridView. The Entity Framework model in this case is code first, database already exists.

After the models are generated the classes are placed into a folder created and named Models, the DbContext exists in a newly created folder Contexts. Operations to read data resides in a folder named classes along with a partial instance of the Customer class which represents data to be displayed in a DataGridView. For those who uses namespaces for clearly separating functionality (or use ReSharper which will prompt to change the namespace) do not alter the namespace for the Customer class under the Classes folder as this will cause both Customer classes to be independent of each which will break things.

DataGridView Setup

A column is created for each property of the view and the DataPropertyName of each column is set to the backing property in CustomerEntity.

Default view

In a database, a view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object. A view presented here is a class which has properties (columns from one or more tables) to present to the front end, in this case a DataGridView.

This is our view.

Public Class  CustomerEntity
    Public Property  CustomerIdentifier() As Integer
    Public Property  CompanyName() As  String
    Public Property  FirstName() As  String
    Public Property  LastName As  String
    Public Property  CountryName() As  String
    Public Property  ContactId As  Integer
    Public Property  CountryIdentifier As  Integer
    ''' <summary>
    ''' State of customer e.g. modified, deleted etc
    ''' </summary>
    ''' <returns></returns>
    Public Property  EntityState() As  EntityState
End Class

A class is needed to read the rows from a database which is used in a lambda statement to query data.

• FirstName and LastName properties are not part of the model so to prevent runtime errors from Entity Framework both properties must be decorated with the attribute NotMapped. 
• For a query to work the Projection property must use an Expression e.g. from the Customer class to the view class Expression(Of Func(Of Customer, CustomerEntity)).

Imports System.ComponentModel.DataAnnotations.Schema
Imports System.Linq.Expressions
Imports WindowsApp2.Classes
 
Partial Public  Class Customer
    <NotMapped>
    Public Property  FirstName As  String
    <NotMapped>
    Public Property  LastName As  String
    Public Shared  ReadOnly Property  Projection() As  Expression(Of Func(Of Customer, CustomerEntity))
 
        Get
            Return Function(customer) New  CustomerEntity() With {
                .CustomerIdentifier = customer.CustomerIdentifier,
                .CompanyName = customer.CompanyName,
                .CountryName = customer.Country.Name,
                .FirstName = customer.Contact.FirstName,
                .LastName = customer.Contact.LastName,
                .ContactId = CInt(customer.ContactId),
                .CountryIdentifier = customer.CountryIdentifier}
        End Get
 
    End Property
 
End Class

The following class represents a container for all operations (more operations to be added in the next part of this series) againsts customers.

• ReadAsync utilizes the partial Customer class above asynchronously, first on the select then on the Task. Granted in this case working with less than 100 records is overkill but when there are thousands of records this will keep the application responsive.
• Since data usually has a default order by an orderby is against company name here.

Imports System.Data.Entity
 
Namespace Classes
 
    Public Class  CustomerOperations
        ''' <summary>
        ''' Unlike many examples which will do using someContext we
        ''' need access to the DbContext which will be covered in
        ''' an upcoming next step to save data back to the database.
        ''' </summary>
        ''' <returns></returns>
        Public Property  Context() As  NorthWindAzureContext
        Public Sub  New()
            Context = New  NorthWindAzureContext()
        End Sub
        ''' <summary>
        ''' Read view/projection asynchronously, note the use
        ''' of inner ToListAsync which on larger operations can
        ''' accept a cancellation token yet there are less than
        ''' 90 records here so no need for a token.
        ''' </summary>
        ''' <returns></returns>
        Public Async Function ReadAsync() As Task(Of List(Of CustomerEntity))
 
            Return Await Task.Run(
                Async Function()
                    Dim customerItemsList As List(Of CustomerEntity) =
                            Await Context.Customers.Select(Customer.Projection).ToListAsync()
                    Return customerItemsList.OrderBy(Function(customer) customer.CompanyName).ToList()
                End Function)
 
        End Function
    End Class
End Namespace

Form code

The form has

• A BindingNavigator for ease of traversing rows in the DataGridView.
• A DataGridView set up in read only mode. In a future article the next step is to enable adding, editing and deleting operations within the DataGridView.
  • Current code is already at a basic level for allowing adding, editing and deleting of data.
• A BindingSource to provide access to data without touching rows and cells of the DataGridView
• A BindingListView to support sorting when clicking column headers to sort columns for the underlying data.

Both the BindingListView and BindingSource are required as form level variables as they provide access to data loaded in the DataGridView. For more advance developers the BindingListView may be setup locally in form shown event then later using casting to get at the component.

Shown event

All setup is performed in the Shown event as there are chances if an error is raised in form load the error(s) might not be raised so shown event is best.

In the code below the focus starts at CustomerOperations = New CustomerOperations() which creates a new instance of the DbContext. Next data is read into a list which represents the view to present followed by setting up the BindingListView and BindingSource when then has the BindingSource set to the DataSource property of the DataGridView.

Private Async Sub Form1_Shown(sender As Object, e As  EventArgs) Handles  Me.Shown
    '
    ' DataGridView has columns defined in the IDE with DataPropertyName set
    ' to a property of CustomerEntity.
    '
    DataGridView1.AutoGenerateColumns = False
 
    '
    ' Create new instance of CustomerOperations which creates 
    ' an instance of the DbContext NorthWindAzureContext
    '
    CustomerOperations = New  CustomerOperations()
 
    CustomerOperations.Context.Database.Log = AddressOf  Console.WriteLine
 
    '
    ' Read customers into a view
    '
    Dim customers As List(Of CustomerEntity) = Await CustomerOperations.ReadAsync()
 
    '
    ' Setup BindingListView with customers read in above
    '
    _customerView = New  BindingListView(Of CustomerEntity)(customers)
 
 
    _customerBindingSource = New  BindingSource()
 
    '
    ' Assign BindingListView to BindingSource
    '
    _customerBindingSource.DataSource = _customerView
 
    '
    ' Setup DataGridView DataSource to read in customers in the view
    '
    DataGridView1.DataSource = _customerBindingSource
    DataGridView1.ExpandColumns()
 
    BindingNavigator1.BindingSource = _customerBindingSource
 
    CurrentCustomerButton.Enabled = True
 
End Sub

Current Customer

A critical operation is to get to underlying data stored in the DataGridView which is done using a language extension method which cast the current row to, in this case a CustomerEntity. Since the DataGridView is set to read only there is no need to check if the Current property of the BindingSource is equal to Nothing.

<Extension>
Public Function  CurrentCustomerEntity(ByVal sender As BindingSource) As CustomerEntity
    Return DirectCast(sender.Current, ObjectView(Of CustomerEntity)).Object
End Function

Using the above extension method to get at the current customer keys and name. The keys will be needed to edit and perform delete operations.

Private Sub  CurrentCustomerButton_Click(sender As Object, e As  EventArgs) _
    Handles CurrentCustomerButton.Click
 
    Dim customer = _customerBindingSource.CurrentCustomerEntity()
 
    MessageBox.Show(
        $"Company: {customer.CompanyName}{Environment.NewLine}" &
        $"Primary key: {customer.CustomerIdentifier}{Environment.NewLine}" &
        $"Contact key: {customer.ContactId}{Environment.NewLine}" &
        $"Country key: {customer.CountryIdentifier}")
 
 
End Sub

Summary

This article has focused on how to present a view of data from a relational database to present into a DataGridView which will later demonstrate how to save changed back to the backend database table.

See also

VB.NET Entity Framework: Wiki portal
Entity Framework dynamic Order by (VB.NET)

Source code

Clone or download the following repository and work with WindowsApp2.