Introduction to ADO.NET Entity Data Model

ado.net entity data model

Data is undoubtedly considered the most important asset of any organization. For this reason, organizations invest time and money on devising means to improve data storage and manipulation services. The same rule applies to the software industry where rigorous research has been underway since computing entered the commercial industry in the early 60’s. By the 70’s, relational databases were beginning to make a mark in the IT industry for advantages such as non-redundancy and integrity.

Interested in learning more about databases? Look at this Udemy.com course

For this purpose, the entity-relationship data model was introduced, which handled the aforementioned problem. To manage and manipulate databases developed on different servers such Oracle, MySql and SQLServer, different platforms provide different solution. The Microsoft’s solution for creating, managing and manipulating data is called ADO.NET.

What is the ADO.NET Entity Data Model?

ADO.NET is an abbreviation for ActiveX Data Object.NET, which is part of Microsoft’s larger .NET framework. The entity data model is an integral component of Microsoft’s Entity Framework technology which is Microsoft’s implementation of ORM (Object Relational Model). Previously, all database interaction in .NET was done through data providers, data sets and data adapters. However, that data adapter technique is clumsy and tedious.

Entity Framework has revolutionized the way data is accessed and manipulated in the .NET framework. Entity Framework eases development of data-centric applications by providing a model where each table in the database is mapped to a corresponding object class. To add, insert, update or delete data in the database, these entities or objects are used. This streamlines the database access logic and provides a more realistic and logical approach towards data manipulation.

The question is where does the Entity Data Model fit in with all of this. The model that allows developers to bind entities with tables or vice versa is the Entity Data Model.

In the following section, the ADO.NET Entity Data Model that interacts with a database is explained with the help of an example.

To learn more about C#, see this Udemy.com course

Basic Example of ADO.NET Entity Data Model

To get started, follow these steps to create a console application that uses ADO.NET EF Data model.

Step1:

First create a simple database on any database server. For this tutorial, the database name should be Bookstore. This article uses Visual Studio 2012.

Step2:

Add two tables to the database: The first table should be named Authors and the Second Table should be named Books. The database schema of the tables and the data they contain is mentioned in the following figure.

 

Author Table:

Example1

 

 

Book Table:

example2

There is a one-to-many relation between the Author table and the Book table and it is explained later how Entity Data Model handles this relationship.

Step3:

Create a simple console application using Visual Studio (VS 2012 in this case).  Right-click on the project and choose Add = > New Item in the templates that appear on the left of the window. Choose data and then from the Data templates chose ADO.NET Entity Data Model. Name this model BookstoreModel. See the following figure for details.

example3

Step4:

Click on the Add button in the last figure. The wizard gives two options to create an entity data mode: Generate from Database and Empty model. Choose Generate from Database option and click the next button.

Step5:

The new window shows the database connection options that are used to specify the database from which the model is generated. Choose the database named Bookstore created in step 1. Change the name of connection stored in entity connection string to BookstoreContext and click next button. The following figure demonstrates this step.

example4

 

Step6:

In the window that appears, choose all the tables. This window basically allows developers to specify which tables to include in the entity model. The following figure demonstrates this concept:

example5png

Getting Records from Each Table

Now you can retrieve and display information from the Author table. Use the following code snippet as an example:

 

BookstoreContext bookstoredb = newBookstoreContext();

IQueryable<Author> authors = bookstoredb.Authors.Select(n => n); 

foreach (Author author in authors)

{

Console.WriteLine(author.ID + "  "+ author.Name + "  " +  author.Country);

}

In the above code, the object “BookstoreContext” is instantiated by calling its constructor. This BookstoreContext is actually an in-memory representation of the Bookstore database created earlier in this tutorial. Next, a LINQ query is executed on this bookstore database, which retrieves all the author records from the bookstore database. These author records are stored in an IQueryable interface type object.  Next, a foreach loop is used to iterate over all the records in the Author table. Inside the loop, the columns or the fields associated with these records are displayed.

Next, you might want to get a list of books. The following code is an example of how you can get a list of books using your EF data model:

IQueryable<Book> books = bookstoredb.Books.Select(n => n);

foreach (Book book in books)

{

Console.WriteLine(book.ID + "  " + book.Name + "  " + book.Author.ID);

}

Managing Associations

The Author and Book table has a one-to-many relationship. This means that one author can have many books. This relationship is managed automatically by the EF data model. For instance, if all books associated with the author named Alin is retrieved, the following code snippet can be used:

BookstoreContext bookstoredb = newBookstoreContext();

Author author = bookstoredb.Authors.Single(n => n.Name == "Alin");

foreach (Book book in author.Books)

{

Console.WriteLine(book.ID + " " + book.Name);

}

The relationship between the Author and Book is managed by providing a collection of Books entitywith each Author. Therefore, by retreiving the record of the Author, the record of all the corresponding books can be retreived with ease.

This example demonstrates the simplicity and power that comes with using ADO.NET Entity Data Model.

For more interesting ADO.NET tutorials, look at Udemy.com courses