Introduction 

Windows desktop application developer for years worked with databases using ADO.NET which exposes data access services using data providers specific to specific databases e.g. System.Data.SqlClient for SQL-Server, OleDb for MS-Access while for databases such as Oracle provided a namespace for access their database, Oracle.ManagedDataAccess.Client.  The main container for working with data storage is a DataSet which depends on a TableAdapter or DataAdapter or using a connection and command objects to read, update, add, delete and insert daata on a single table or one to many relational tables.

Microsoft has provided Entity Framework, with inbuilt features in .NET framework for data connectivity between application and database. EF provides you a bridge between your application and the database, so that your application can send and receive appropriate data to and from the database.

Part 2 Windows forms Entity Framework Code first from database 


Objective 

In this article, information presented to provide information to make an informed decision for moving from ADO.NET data providers to using Entity Framework for interactive with databases. 

Many developers prefer working with code samples to form an opinion on “is Entity Framework right for me” then look at one or a half dozen code samples were lies the danger for developers with little knowledge of interacting with databases to select the “easy” method similar to selecting the “easy” method with conventional data access via TableAdapters.


First steps: TableAdapter

New developers tend to seek out the easiest method for interacting with data in Window Forms projects. The path taken tends to be using TableAdapter components which begin by selecting the “Data Source” window in Visual Studio while a project is present. Clicking on “Add New DataSource” triggers a wizard which once completed creates strong typed classes for the tables, stored procedures selected during the wizard process. Once this has been completed a developer drags objects from the data window listing objects selected on to a form. A BindingNavigator component provides buttons to traverse and alter data. At this point build and run the project to work with data.

For some quick starts with things, developers tend to get frustrated with see the following code sample.

Frustrations

Since not only did the developer select an easy path by using TableAdapter components they tend not to read Microsoft documentation on TableAdapter components. Instead of pressing the delete button in the BindingNavigator where it’s BindingSource property has been created and set during the wizard process to link to a DataSet the developer must now a) read through Microsoft documentation or a web search or ask in a developer forum how to override the delete button to prompt users to delete the current record or not. 

What does this mean? They become dependent to reading documentation in a rush and/or asking a question in a developer forum, either way lost time.

Another area is setting up master details views on forms, by reading the documentation this would be easy to setup the views.

Frustrations are not only with TableAdapter method for interacting with data but instead with all forms of writing code yet since TableAdapter components seems like the best path they get a bad rap.

TableAdapters have their place but this is segmented to small personal application which should signal those who have selected TableAdapter path to understand there must be a better way to work with data. 


First steps: DataAdapter

Can be an excellent path to work with data, especially when there are relational data containers e.g. tables (DataTable) in a DataSet with relationships setup, database tables have proper primary keys.

Downside for DataAdapter, similar to a TableAdapter they have code brought along that in many cases is not needed but there because they “may be needed”. Also like a TableAdapter the DataAdapter is heavy unlike using concrete classes using a data connection and command object to worth with data and with this path limited usage of DataSet/DataTable done right is fine.


First steps: Connection/Command

Working with a connection and command objects which use the same containers as TableAdapter and DataAdapter provides better control over the latter methods yet with more control comes more lines of code to write which some see as writing a decent amount of code cumbersome compared to Adapter components which are the complete opposite, they take care of must common operations but when they constrain a developer. If an application is medium to complex in nature the Adapter components will eventually outlive their first appeal of easy to troublesome and frustration. This is why using connections and commands are better fit for medium to complex type of applications. Downside, the developer is required to have a decent understanding of SQL because connection and command objects do not write SQL, the developer must write the SQL statements usually best done in the native database or dedicated tool. For MS-Access, creating SQL within the database, for SQL-Server SSMS (SQL-Server Management Studio), free, for Oracle, Toad (must purchase) or SQL-Developer (free).

Entity Framework

Entity Framework is an object-relational mapper. This means that it can return the data in your database as an object (e.g. a Person object with the properties Id, Name, etc.) or a collection of objects.

Instead of working with heavy containers such as DataSet/DataTable EF relies on lightweight classes, which interact with database tables from a special class DbContext.

To start working with Entity Framework there is a decision to make which is to select one of four options.

Option 1: “EF Designer from database”

With this option, the database exists with tables. A wizard will walk through a process to create classes for tables selected in the wizard process. Once the wizard process has completed a new file with the extension of .edmx (represents the data model for tables selected) will appear in your project. Double click on this file in Solution Explorer, which opens a canvas with a graphical representation of the tables selected with lines between related table plus what are called “Navigation objects e.g. a product class may have a navigation property of category (which would in this case be a related table to product table).

Note the following appears when selecting this option.

Creates a model in the EF Designer based on an existing database. You can choose the database connection, settings for the model, and database objects to include in the model. The classes your application will interact with are generated from the model.

Regarding database connection, this can be changed in the project application configuration file, once the wizard has completed the basic options are to refresh items on the .edmx canvas which would be done if there are changes in a table schema e.g. a field was named “first name” is changed to “FirstName” or a field data type changes or fields are removed or added.

Concerning changing model settings, for the average project using EF the limited things which are configurable are suffice but there other choices such as “Code First from database” or “Empty code first model” which will be discussed below.

Advantages

When working with EF Designer from database a developer does not need be concerned with writing code to create a new database tables when the developer has limited knowledge of creating relational databases. For those just starting out the recommendation if to create a database and tables using a tool like SSMS which is dedicated to creating tables and databases while taking this task on in code there are no warnings that the database and tables have been configured properly in the C# code.

Disadvantages

Like with TableAdapter components the EF wizard will generate code for you, in some cases it is not possible to make changes to the generated code because when updating the .edmx file while “Code First” paths allow changes as they have no way to refresh a model (there are tricks to refreshing models which will be discussed later).

As a data model grows with 20 plus tables and many relationships a .edmx file may become corrupt which at this point may be hard to figure out why and time to fix.

Option 2: “Empty EF Designer model”

Identical to option one, except instead of engineering the models from a database they are designed through the interface. This is somewhat cumbersome as the designer can be difficult to work with and changes to the database itself need to be made outside of the designer.

Option 3: “Empty Code First model” 

The idea being write the code that you want to use to interact with your database First. This allows you the flexibility to define how you want to interact with your database. EF generates a supporting database schema for your code.

Advantages

Provides almost full control for interacting with a database except there are still actions that must be done after the database and tables are created and populated e.g. creation of indexes based on real time usage of data that can only come from rigorous testing which all developers should be performing rather let an application go into production and be dog slow because proper indexes were never created.

Disadvantages

With flexibility, control over most aspects of the database flow of read, write, find operations for the novice developer thinking they have a solid schema when they don’t and become frustrated is a reason to not start with this option but instead perhaps start with option 1 then when familiar with the basics move to option 4 below, become familiar with no .edmx then when comfortable move to option 3.

Option 4: “Code first from database”

This is identical to option three; except you are using an existing database reverse engineered into classes.

We are going to focus on option one and option three. These approaches are fundamentally different; the other options are extensions of option one and three.

Using a database project leaves entity framework’s EDMX file as a push button solution for interacting with your database in code, however you cannot customize what that looks like. “Code First” gives you the option to customize the objects you work with in code but sacrifices the ease of a design surface such as SSMS to build the tables.

This option inherently cause some developers to back off as once code is generated via reverse engineering a database if there are changes to the database there is no obvious method to collect changes from the database. 

There are two choices, create a temporary project, add a new item, traverse as done when first creating classes for option 3 and select everything you need, classes are generated, copy and paste into your current classes in your project followed by deleting the temporary project. Do this enough times in an agile project will help a developer consider option 3 or start with option 4 and continue as if option 3 was being used along.


Which path is best

It depends, if this is for a novice developer tinkering around with a small to medium size project option one would be ideal. If the developer is new or has a decent knowledge of coding background option 3 or 4 would be the best fit (maybe). For an experience developer all options are open depending on the project. A smart developer will know how to work with at least options 1, 3 and 4.

Summary

In this article information has been provided to have an idea which path to take if considering using Entity Framework or Entity Framework Core yet though out this article there has been no code presented so not to contaminate the consideration of which path to take.

Important, for best figuring out which path to take avoid shadowing a developer style found in the first to 10 code samples found in a web search, instead take your time to get a handle on what is presented, does it fit your work, does it fit your style, can you adapt to the style and does it feel right.


See also