Accessing Databases Using the Entity Framework

Zia

2 Followers

Follow

May 9, 2024

-

0 sec read

444

0

0

0

Most C# applications persist and retrieve data that might be stored in some data source, such as a relational database, XML file, or spreadsheet. In an application, data is usually represented in the storm of classes and objects. However, in a database, data is stored in the form of tables and views. Therefore, an application to persist and retrieve data first requires to connect with the data store. The application must also ensure that the definitions and relationships of classes or objects are mapped with the database tables and table relationships.

Finally, the application must provide the data access code to persist and retrieve data. All these operations can be achieved using ADO.NET, a set of libraries that allows an application to interact with data sources. However, in enterprise data-centric applications, using ADO.NET results in development complexity, which subsequently increases development time and cost. In addition, as the data access code of an application increases, the application becomes difficult to maintain and often leads to performance overhead.

To address data access requirements of enterprise applications, Object Relationship Mapping (ORM) frameworks have been introduced. An ORIM TrameWork simplifies the process of accessing data from applications. An ORM framework performs the necessary conversions between incompatible type systems in relational databases and object-oriented programming languages.

The Entity Framework is an ORM Framework that .NET applications can use.

1. Entity Data Model

The Entity Framework is an implementation of the Entity Data Model (EDM), which is a conceptual model that describes the entities and the associations they participate in an application. EDM allows a programmer to handle data access logic by programming against entities without having to worry about the structure of the underlying data store and how to connect with it. For example, in an order placing operation of a customer relationship management application, a programmer using the EDM can work with the Customer and order entities object-oriented without writing database connectivity code or SQL-based data access code.

2. Development Approaches

Entity Framework eliminates the necessity to write most of the data-access code that is otherwise required to be written. It uses different approaches to manage data related to an application. These approaches are as follows:

• The database-first approach: The Entity Framework creates the data model containing all the classes and properties corresponding to the existing database objects, such as tables and columns.
  
  
• The model-first approach: The Entity Framework creates database objects based on a programmer's model to represent the entities and their relationships in the application.
  
  
• The code-first approach: The Entity Framework creates database objects based on custom classes that a programmer creates to represent the entities and their relationships in the application.
  
  
  

3. Creating an Entity Data Model

Visual Studio 2019 provides support for creating and using EDM in C# application. Programmers can use the Entity Data Model wizard to create a model in an application. After creating a model, the programmer can add entities to the model and define their relationship using the Entity Framework designer. The information on the model is stored in a .edmx file. Based on the model, the programmer can use Visual Studio 2019 to automatically generate the database objects corresponding to the entities. Finally, the programmer can use LINQ queries against the entities to retrieve and update data in the underlying database.

To create an entity data model and generate the database object, a programmer must perform the following steps:

• Open Visual Studio 2019.
• Create a Console Application project, named EDMDemo.
• Right-click EDMDemo in the Solution Explorer window, and select Add-> New Item. The Add New Item -EDMDemo dialog box is displayed.
• Select Data from the left menu and then select ADO.NET Entity Data Model.
• Click Add. The Entity Data Model Wizard appears.
• Select the Empty model.
• Click Finish. The Entity Data Model Designer is displayed.
• Right-click the Entity Data Model Designer, and select Add New>Entity. The Add Entity dialog box is displayed.
• Enter Customer in the Entity name field and Customerld in the Property name field.
• Click OK. The Entity Data Model Designer displays the new Customer entity.
• Right-click the Customer entity and select Add New>Scalar property.
• Enter the Name as the name of the property.
• Similarly, add an Address property to the Customer entity.
• Add another entity named Order with an OrderId key property.
• Add a Cost property to the Order entity.
  
  
  

4. Defining Relationships

After creating an EDM and adding the entities to the EDM, the relationships between the entities can be defined using the Entity Data Model Designer. As a customer can have multiple orders, the Customer entity will have a one-to-many relationship with the Order entity. To create an association between the Customer and order entities:

• Right-click the Entity Data Model Designer, and select Add New->Association. The Add Association dialog box is displayed.
  
  
• Ensure that the left-hand End section of the relationship points to Customer with a multiplicity of 1 (One) and the right-hand End section points to Post with a multiplicity of * (Many).
  
  
• Click OK. The Entity Data Model Designer displays the entities with the defined relationships.
  
  
  

5. Creating Database Objects

After designing the model of the application, the programmer must generate database objects based on the model. To generate database objects:

• Right-click the Entity Data Model Designer, and select Generate Database from Model. The Generate Database Wizard dialog box appears.
• Click New Connection. The Connection Properties dialog box is displayed.
• Enter (localdb)\v11.0 in the Server name field and EDMDEMO.CRMDB in the Select or enter a database name field.
• Click OK. Visual Studio will prompt whether to create a new database.
• Click Yes.
• Click Next in the Generate Database Wizard window. Visual Studio generates the scripts to create the database objects.
• Click Finish. Visual Studio opens the file containing the scripts to create the database objects.
• Right-click the file and select Execute. The Connect to Server dialog box is displayed.
• Click Connect. Visual Studio creates the database objects.
  
  
  

6. Using the EDM

When a programmer uses Visual Studio to create an EDM with entities and their relationships, Visual Studio automatically creates several classes. The important classes that a programmer will use are as follows:

• Database Context Class: This class extends the DbContext class of the System. Data. Entity namespace to allow a programmer to query and save the data in the database. In the EDMD emo Project, the Model1Container class is present in the Model1.Context.cs file is the database context class.
  
  
• Entity Classes: These classes represent the entities that programmers add and design in the Entity Data Model Designer. In the EDMDemo Project, Customer and Order are the entity classes.
  
  
  

7. Querying Data by Using LINQ Query Expressions

LINQ provides a consistent programming model to create standard query expression syntax to query different types of data sources. However, different data sources accept queries in different formats. To solve this problem, LINQ provides various LINQ providers, such as LINQ to Entities, LINQ to SQL, LINQ to Objects, and LINQ to XML. To create and execute queries against the conceptual model of Entity Framework, programmers can use LINQ for Entities.

In LINQ to Entities, a programmer creates a query that returns a collection of zero or more typed entities. To create a query, the programmer requires a data source against which the query will execute. An instance of the ObjectQuery class represents the data source. In LINQ to entities, a query is stored in a variable. When the query is executed, it is first converted into a command tree representation that is compatible with the Entity Framework. Then, the Entity Framework executes the query against the data Source and returns the result.

• Forming Projections: When using LINQ queries, the programmer might only require to retrieve specific properties of an entity from the data store; for example only the Name property of the Customer entity instances. The programmer can achieve this by forming projections in the select clause.
  
  
• Filtering Data: The where clause in a LINQ query enables filtering data based on a Boolean condition, known as the predicate. The where clause applies the predicate to the range variable that represents the source elements and returns only those elements for which the predicate is true.
  
  
  

8. Querying Data by Using LINQ Method-Based Queries

The LINQ queries used so far are created using query expression syntax. Such queries are compiled into method calls to the standard query operators, such as select, where, and order by. Another way to create LINQ queries is by using method-based queries where programmers can directly make method calls to the standard query operator, passing lambda expressions as the parameters.