C# Entity Framework: Connecting Data with Your Applications

If you’re an C# MVC developer, you’ve probably run across Entity Framework or EF. EF removes all that clunky database code and represents your data tables using classes. EF is an object relationship mapper (ORM) that lets you create CRUD (create, read, update and delete) operations. Instead of writing stored procedures and flipping between the database and the presentation GUI, you can use EF to work with CRUD operations and leave database processes to your database administrator.

New to C# and Entity Framework? Take a class at Udemy.com.

First, let’s take a look at old methods. Before, you needed to manage your database connections. You first set your connection, open the connection, name your stored procedure or dynamic SQL, pass parameters and then close your connection. Of course, in between opening and closing the connection, you get your data set. Most developers used the ADO.NET provider, which is Microsoft’s system for connecting to databases such as MSSQL.

With EF, you still use ADO.NET, but there is an added layer in between your application and ADO.NET. Entity Framework maps database objects to classes. OOP programmers will recognize these classes quickly. If you’re new to OOP, you might have a learning curve. But, it’s well worth the learning experience if you work with large data applications.

Creating an EF Data Model

In your project, it’s probably best you create a folder for your data model. If you add a folder, you’ll need to add the folder to the “using” statements. Visual Studio creates a new namespace by default when you create a new folder, so you can name it something such as “Data” to identify your data folder from the rest of the application.

Right-click the folder and select “New.” Select “ADO.NET Entity Data Model” in the “Data” section of Visual Studio.

A wizard appears that guides you through the process. The easiest way to create a data model is first set it up in your database. For instance, use SQL Server Management Studio to create all of your tables and define table columns. The data modeling tool in Visual Studio is a bit clunky, so it can take more time to create a data model and import it into your database. Plus, all of the Visual Studio tools don’t necessarily map properly to MSSQL tables. If it doesn’t work, you wind up creating your database and its tables twice, which is frustrating.

Learn how to work with Microsoft MVC.NET and Entity Framework at Udemy.com.

If you’ve already created your database, you can import the tables using the Visual Studio data tool. Choose “Generate from Database” and follow the prompts. You’ll be asked to create a connection string, which includes your database server name, database name and your user name and password. You’re also prompted to create a connection string name and store it in the web.config. Choose “Yes” for this option. You’ll need the connection in the future.

After you work through the wizard, it takes a few seconds for Visual Studio to import and create classes. These classes point to tables in your database. For instance, in the above image, a “User” table is shown. A class named “Users” (notice the multiple case is added) is created. Each property for the class is a table column.

After you create your data model, you need to instantiate the database context. This is the name you gave the model when you used the Visual Studio data model creator. For instance, if you named your model “Userdb,” the following could applies:

Userdb db = Userdb();

At this point, you can use any CRUD procedure on the tables. For instance, you can update your user database after retrieve data on a web page. For instance, the following code inserts a new user with a first name and last name from some text boxes on the web application:

Userdb db = Userdb();
UserAccounts ua = new UserAccounts();
ua.first_name = Textbox1.Text;
ua.last_name = Textbox2.Text;
db.UserAccounts.Add(ua);
db.SaveChanges();

In the first line of code, the database context object is created. You’ll need this object whenever you want to retrieve, update or delete records. You no longer need to write any of these statements as EF does it for you. You can use LINQ to retrieve certain records, but that is beyond the scope of this article. EF and LINQ are good tools for all your data manipulation.

The next line of code instantiates a table object named “UserAccounts.” It’s assumed from this code that the UserAccounts table stores user information.

The next two lines of code retrieve the input from the user in text boxes named “Textbox1” and “Textbox2.” Finally, the next two lines of code add the new record to the database table. Notice that no SQL is written in these blocks of code. EF and the MVC.NET framework handle the complex database language.

Because you’re adding an entire object and not just specifying a record, you don’t need to add an auto-incrementing ID or any other code. Of course, your database should have some kind of automation to identify a unique record from the rest of the UserAccounts records. In this example, it’s assumed some database trigger or auto-incrementing column exists.

The “SaveChanges()” function saves the changes. You call this method last. This method does the final save transaction. It should be noted that you can perform several database changes before you call the SaveChanges method. For instance, you can add two records to the database and then save the changes. This is actually preferred over calling the method multiple times. You do this to improve performance on your web application and to reduce the load on your database server.

You can do the same types of procedures on your tables including read, delete and update. The difference is that you first need to retrieve a record ID. You retrieve one record using LINQ and store it in a model object.

While EF seems a bit more complicated at first, using tables as objects is easier to read in the code. You also can eliminate complex SQL statements. You might still need to write procedures, but LINQ replaces many of the common database functionalities you’re used to doing in older ADO.NET code.