Object-relational mapping in computer software is a programming technique for converting data between incompatible type systems in object-oriented programming languages. This creates, in effect, a "virtual object database" that can be used from within the programming language...
The .NET Framework is a software framework that runs primarily on Microsoft Windows. It includes a large library and supports several programming languages which allows language interoperability...
.
Overview
ADO.NET Entity Framework abstracts the relational (logical) schema of the data that is stored in a database
Database
A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...
A conceptual schema or conceptual data model is a map of concepts and their relationships. This describes the semantics of an organization and represents a series of assertions about its nature...
The object-relational impedance mismatch is a set of conceptual and technical difficulties that are often encountered when a relational database management system is being used by a program written in an object-oriented programming language or style; particularly when objects or class definitions...
that is otherwise common in conventional database-oriented programs. The run-time overhead of mapping between the conceptual schema and the underlying relational schema is still a factor to be considered.
For example, in a conventional database-oriented system, entries about a customer and their information can be stored in a Customer table, their orders in an Order table and their contact information in yet another Contact table. The application that deals with this database must "know" which information is in which table, i.e., the relational schema of the data is hard-coded into the application.
The disadvantage of this approach is that if this schema is changed the application is not shielded from the change. Also, the application has to perform SQL joins to traverse the relationships of the data elements in order to find related data. For example, to find the orders of a certain customer, the customer needs to be selected from the Customer table, the Customer table needs to be joined with the Order table, and the joined tables need to be queried for the orders that are linked to the customer.
This model of traversing relationships between items is very different from the model used in object-oriented programming languages, where the relationships of an object's features are exposed as Properties of the object and accessing the property traverses the relationship.
History
The first version of Entity Framework (EFv1) was included with .NET Framework 3.5 Service Pack 1 and Visual Studio 2008
Microsoft Visual Studio
Microsoft Visual Studio is an integrated development environment from Microsoft. It is used to develop console and graphical user interface applications along with Windows Forms applications, web sites, web applications, and web services in both native code together with managed code for all...
Service Pack 1, released on 11 August 2008. This version has been widely criticized, even attracting a 'vote of no confidence' signed by approximately one thousand developers.
The second version of Entity Framework, named Entity Framework 4.0 (EFv4), was released as part of .NET 4.0 on 12 April 2010 and has addressed many of the criticisms made of version 1.
A third version of Entity Framework, version 4.1, was released on April 12, 2011.
A refresh of version 4.1 named Entity Framework 4.1 Update 1, was released on July 25, 2011. It includes bug fixes and new supported types.
Architecture
The architecture of the ADO.NET Entity Framework, from the bottom up, consists of the following:
Data source specific providers, which abstracts the ADO.NET interfaces to connect to the database when programming against the conceptual schema.
Map provider, a database-specific provider that translates the Entity SQL command tree into a query in the native SQL flavor of the database. It includes the Store specific bridge, which is the component that is responsible for translating the generic command tree into a store-specific command tree.
EDM parser and view mapping, which takes the SDL specification of the data model and how it maps onto the underlying relational model and enables programming against the conceptual model. From the relational schema, it creates views of the data corresponding to the conceptual model. It aggregates information from multiple tables in order to aggregate them into an entity, and splits an update to an entity into multiple updates to whichever table contributed to that entity.
Query and update pipeline, processes queries, filters and update-requests to convert them into canonical command trees which are then converted into store-specific queries by the map provider.
Metadata services, which handle all metadata related to entities, relationships and mappings.
Transactions, to integrate with transactional capabilities of the underlying store. If the underlying store does not support transactions, support for it needs to be implemented at this layer.
Conceptual layer API, the runtime that exposes the programming model for coding against the conceptual schema. It follows the ADO.NET pattern of using Connection objects to refer to the map provider, using Command objects to send the query, and returning EntityResultSets or EntitySets containing the result.
Disconnected components, which locally caches datasets and entity sets for using the ADO.NET Entity Framework in an occasionally connected environment.
Embedded database: ADO.NET Entity Framework includes a lightweight embedded database for client-side caching and querying of relational data.
Design tools, such as Mapping Designer are also included with ADO.NET Entity Framework which simplifies the job on mapping a conceptual schema to the relational schema and specifying which properties of an entity type correspond to which table in the database.
Programming layers, which exposes the EDM as programming constructs which can be consumed by programming languages.
Object services, automatically generate code for CLR classes that expose the same properties as an entity, thus enabling instantiation of entities as .NET objects.
Web services, which expose entities as web services.
High level services, such as reporting services which work on entities rather than relational data.
Entity Data Model
The Entity data model (EDM) specifies the conceptual model (CSDL) of the data via the Entity-Relationship data model, which deals primarily with Entities
Entity
An entity is something that has a distinct, separate existence, although it need not be a material existence. In particular, abstractions and legal fictions are usually regarded as entities. In general, there is also no presumption that an entity is animate.An entity could be viewed as a set...
and the Associations they participate in. The EDM schema is expressed in the Schema Definition Language (SDL), which is an application of XML. In addition, the mapping (MSL) of the elements of the conceptual schema (CSDL) to the storage schema (SSDL) must also be specified. The mapping specification is also expressed in XML.
Visual Studio also provides Entity Designer, for visual creation of the EDM and the mapping specification. The output of the tool is the XML file (*.edmx) specifying the schema and the mapping. Edmx file contains EF metadata artifacts (CSDL/MSL/SSDL content). These 3 files (csdl, msl, ssdl) can also be created or edited by hand.
Mapping
Entity Data Model Wizard in Visual Studio initially generates a 1:1 (one to one) mapping between the database schema and the conceptual schema in most of the cases. In the relational schema, the elements are composed of the tables, with the primary and foreign keys gluing the related tables together. In contrast, the Entity Types define the conceptual schema of the data.
The entity types are an aggregation of multiple typed fields – each field maps to a certain column in the database - and can contain information from multiple physical tables. The entity types can be related to each other, independent of the relationships in the physical schema. Related entities are also exposed similarly – via a field whose name denotes the relation they are participating in and accessing which, instead of retrieving the value from some column in the database, traverses the relationship and returns the entity (or a collection of entities) it is related with.
Entity Types form the class of objects entities conform to, with the Entities being instances of the entity types. Entities represent individual objects which form a part of the problem being solved by the application and are indexed by a key. For example, converting the physical schema described above, we will have two entity types:
CustomerEntity, which contains the customer's name from the Customers table, and the customer's address from the Contacts table.
OrderEntity, which encapsulates the orders of a certain customer, retrieving it from the Orders table.
The logical schema and its mapping with the physical schema is represented as an Entity Data Model (EDM), specified as an XML file. ADO.NET Entity Framework uses the EDM to actually perform the mapping letting the application work with the entities, while internally abstracting the use of ADO.NET constructs like DataSet and RecordSet. ADO.NET Entity Framework performs the joins necessary to have entity reference information from multiple tables, or when a relationship is traversed. When an entity is updated, it traces back which table the information came from and issues SQL update statements to update the tables in which some data has been updated. ADO.NET Entity Framework uses eSQL, a derivative of SQL, to perform queries, set-theoretic operations, and updates on entities and their relationships. Queries in eSQL, if required, are then translated to the native SQL flavor of the underlying database.
Entity types and entity sets just form the logical EDM schema, and can be exposed as anything. ADO.NET Entity Framework includes Object Service that presents these entities as Objects with the elements and relationships exposed as properties. Thus Entity objects are just front-end to the instances of the EDM entity types, which lets Object Oriented languages access and use them. Similarly, other front-ends can be created, which expose the entities via web services (e.g., Astoria) or XML which is used when entities are serialized for persistence storage or over-the-wire transfer.
Entities
Entities are instances of EntityTypes; they represent the individual instances of the objects (such as customer, orders) to which the information pertains. The identity of an entity is defined by the entity type it is an instance of; in that sense an entity type defines the class an entity belongs to and also defines what properties an entity will have. Properties describe some aspect of the entity by giving it a name and a type. The properties of an entity type in ADO.NET Entity Framework are fully typed, and are fully compatible with the type system used in a DBMS system, as well as the Common Type System of the .NET Framework. A property can be SimpleType, or ComplexType, and can be multi valued as well. All EntityTypes belong to some namespace, and have an EntityKey property which uniquely identifies each instance of the entity type. The different property types are distinguished as follows:
SimpleType, corresponds to primitive data types such as Integer, Characters and Floating Point numbers.
ComplexType, is an aggregate of multiple properties of type SimpleType, or ComplexType. Unlike EntityTypes, however, ComplexTypes cannot have an EntityKey. In Entity Framework v1 ComplexTypes cannot be inherited.
All entity instances are housed in EntityContainers, which are per-project containers for entities. Each project has one or more named EntityContainers, which can reference entities across multiple namespaces and entity types. Multiple instances of one entity type can be stored in collections called EntitySets. One entity type can have multiple EntitySets.
EDM primitive types (simple types):
EDM type
CLR type mapping
Edm.Binary
Byte[]
Edm.Boolean
Boolean
Edm.Byte
Byte
Edm.DateTime
DateTime
Edm.DateTimeOffset
DateTimeOffset
Edm.Decimal
Decimal
Edm.Double
Double
Edm.Guid
Guid
Edm.Int16
Int16
Edm.Int32
Int32
Edm.Int64
Int64
Edm.SByte
SByte
Edm.Single
Single
Edm.String
String
Edm.Time
TimeSpan
Relationships
Any two entity types can be related, by either an Association relation or a Containment relation. For example, a shipment is billed to a customer is an association whereas an order contains order details is a containment relation. A containment relation can also be used to model inheritance between entities. The relation between two entity types is specified by a Relationship Type, instances of which, called Relationships, relate entity instances. In future releases, other kinds of relationship types such as Composition, or Identification, may be introduced.
Relationship types are characterized by their degree (arity) or the count of entity types they relate and their multiplicity. However, in the initial release of ADO.NET Entity Framework, relationships are limited to a binary (of degree two) bi-directional relationship. Multiplicity defines how many entity instances can be related together. Based on multiplicity, relationships can be either one-to-one, one-to-many, or many-to-many. Relationships between entities are named; the name is called a Role. It defines the purpose of the relationship.
A relationship type can also have an Operation or Action associated with it, which allows some action to be performed on an entity in the event of an action being performed on a related entity. A relationship can be specified to take an Action when some Operation is done on a related entity. For example, on deleting an entity that forms the part of a relation (the OnDelete operation) the actions that can be taken are:
Cascade, which instructs to delete the relationship instance and all associated entity instances.
None.
For association relationships, which can have different semantics at either ends, different actions can be specified for either end.
Schema definition language
ADO.NET Entity Framework uses an XML based Data Definition Language called Schema Definition Language (SDL) to define the EDM Schema. The SDL defines the SimpleTypes similar to the CTS primitive types, including String, Int32, Double, Decimal, Guid, and DateTime, among others. An Enumeration, which defines a map of primitive values and names, is also considered a simple type. Enumerations are unsupported in the current version of the framework. ComplexTypes are created from an aggregation of other types. A collection of properties of these types define an Entity Type. This definition can be written in EBNF grammar as:
EntityType ::= ENTITYTYPE entityTypeName [BASE entityTypeName]
[ABSTRACT true|false] KEY propertyName [, propertyName]*
{(propertyName PropertyType [PropertyFacet]*) +}
PropertyType ::= ((PrimitiveType [PrimitiveTypeFacets]*)
| (complexTypeName) | RowType
PropertyFacet ::= ( [NULLABLE true | false] |
[DEFAULT defaultVal] | [MULTIPLICITY [ 1|*] ] )
PropertyTypeFacet ::= MAXLENGTH | PRECISION | SCALE | UNICODE | FIXEDLENGTH | COLLATION
| DATETIMEKIND | PRESERVESECONDS
PrimitiveType ::= BINARY | STRING | BOOLEAN
| SINGLE | DOUBLE | DECIMAL | GUID
| BYTE | SBYTE | INT16 | INT32 | INT64
| DATETIME | DATETIMEOFFSET | TIME ) Facets are used to describe metadata of a property, such as whether it is nullable or has a default value, as also the cardinality of the property, i.e., whether the property is single valued or multi valued. A multiplicity of “1” denotes a single valued property; a “*” means it is a multi-valued property. As an example, an entity can be denoted in SDL as:
A relationship type is defined as specifying the end points and their multiplicities. For example, a one-to-many relationship between Customer and Orders can be defined as
Database First
Entity Framework supports several approaches for creating Entity Data Models. Database First approach was historically the first one. It appeared in Entity Framework v1, and its support was implemented in Visual Studio 2008 SP1. This approach considers that an existing (legacy) database is used, or the new database is created first, and then Entity Data Model is generated from this database with Entity Data Model Wizard..
All needed model changes in its conceptual (CSDL) and mapping (MSL) part are performed with Entity Data Model Designer.. If the storage part needs changing, the database must be modified first, and then Entity Data Model is updated with Update Model Wizard .
Database First approach is supported in Visual Studio 2008/2010 for MS SQL Server only. However, there are third party solutions that provide Database First support in Visual Studio for other database servers: DB2, EffiProz, Firebird, Informix, MySQL, Oracle, PostgreSQL, SQLite, Sybase, and VistaDB.. Besides, there are third party tools that extend or completely replace standard Entity Data Model Wizard, Entity Data Model Designer and Update Model Wizard:
A new Model First approach was supported in Visual Studio 2010, which was released together with the second Entity Framework version (Entity Framework v4). In Model First approach the development starts from scratch. At first, the conceptual model is created with Entity Data Model Designer, entities and relations are added to the model, but mapping is not created. After this Generate Database Wizard is used to generate storage (SSDL) and mapping (MSL) parts from the conceptual part of the model and save them to the edmx file. Then the wizard generates DDL script for creating database (tables and foreign keys)
If the model was modified, the Generate Database Wizard should be used again to keep the model and the database consistent. In such case, the generated DDL script contains DROP statements for tables, corresponding to old SSDL from the .edmx file, and CREATE statements for tables, corresponding to new SSDL, generated by the wizard from the conceptual part. In Model First approach developer should not edit storage part or customize mapping, because they will be re-generated each time when Generate Database Wizard is launched.
Model First in Visual Studio 2010 is supported only for MS SQL Server.. However there are third-party solutions that provide support for Oracle, MySQL, and PostgreSQL. Besides, there are third party tools for complete replacement of Entity Data Model Designer and Generate Database Wizard in the context of Model First approach.
Code First is the most recent approach included in RC. The model is defined via classes and configuration written by the developer and via conventions included in the framework itself.
SQL is a programming language designed for managing data in relational database management systems ....
, named Entity SQL, which is aimed at writing declarative queries and updates over entities and entity relationships – at the conceptual level. It differs from SQL in that it does not have explicit constructs for joins because the EDM is designed to abstract partitioning data across tables.
Querying against the conceptual model is facilitated by EntityClient classes, which accepts an Entity SQL query. The query pipeline parses the Entity SQL query into a command tree, segregating the query across multiple tables, which is handed over to the EntityClient provider. Like ADO.NET data providers, an EntityClient provider is also initialized using a Connection object, which in addition to the usual parameters of data store and authentication info, requires the SDL schema and the mapping information. The EntityClient provider in turn then turns the Entity SQL command tree into an SQL query in the native flavor of the database. The execution of the query then returns an Entity SQL ResultSet, which is not limited to a tabular structure, unlike ADO.NET ResultSets.
Entity SQL enhances SQL by adding intrinsic support for:
Types, as ADO.NET entities are fully typed.
EntitySets, which are treated as collections of entities.
Composability, which removes restrictions on where subqueries can be used.
Entity SQL Canonical Functions
Canonical functions are supported by all Entity Framework compliant data providers. They can be used in an Entity SQL query. Also, most of the extension methods in LINQ to Entities are translated to canonical functions. They are independent of any specific database. When ADO.NET data provider receives a function, it translates it to the desired SQL statement.
But not all DBMSs have equivalent functionality and a set of standard embedded functions. There are also differences in the accuracy of calculations. Therefore, not all canonical functions are supported for all databases, and not all canonical functions return the same results.
Group
Canonical functions
Aggregate functions
Avg, BigCount, Count, Max, Min, StDev, StDevP, Sum, Var, VarP
Linq is a word-based card game from Endless Games, introduced at the American International Toy Fair in 2005.Game play requires at least four players, two of whom are dealt cards with the same word, while the others receive blanks. The goal is to gain points by correctly naming the players with...
to be used to query various RDBMS data sources. Several database server specific providers with Entity Framework support are available.
Native SQL
In the Entity Framework v4 new methods ExecuteStoreQuery and ExecuteStoreCommand were added to the class ObjectContext.
Visualizers
Visual Studio has a feature called Visualizer. A Linq query written in Visual Studio can be viewed as Native SQL using a Visualizer during debug session. A Visualizer for Linq to Entities (Object Query) targeting all RDBMS is available via VisualStudioGallery.
The IBM DB2 Enterprise Server Edition is a relational model database server developed by IBM. It primarily runs on Unix , Linux, IBM i , z/OS and Windows servers. DB2 also powers the different IBM InfoSphere Warehouse editions...
International Business Machines Corporation or IBM is an American multinational technology and consulting corporation headquartered in Armonk, New York, United States. IBM manufactures and sells computer hardware and software, and it offers infrastructure, hosting and consulting services in areas...
InterSystems Corporation is a privately held vendor of software for high-performance database management, rapid application development, integration, and healthcare information systems...
An embedded database system is a database management system which is tightly integrated with an application software that requires access to stored data, such that the database system is “hidden” from the application’s end-user and requires little or no ongoing maintenance...
Firebird is an open source SQL relational database management system that runs on Linux, Windows, and a variety of Unix. The database forked from Borland's open source edition of InterBase in 2000, but since Firebird 1.5 the code has been largely rewritten ....
IBM Informix is a family of relational database management system developed by IBM. It is positioned as IBM's flagship data server for online transaction processing as well as integrated solutions...
International Business Machines Corporation or IBM is an American multinational technology and consulting corporation headquartered in Armonk, New York, United States. IBM manufactures and sells computer hardware and software, and it offers infrastructure, hosting and consulting services in areas...
Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
MySQL officially, but also commonly "My Sequel") is a relational database management system that runs as a server providing multi-user access to a number of databases. It is named after developer Michael Widenius' daughter, My...
MySQL officially, but also commonly "My Sequel") is a relational database management system that runs as a server providing multi-user access to a number of databases. It is named after developer Michael Widenius' daughter, My...
PostgreSQL, often simply Postgres, is an object-relational database management system available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software...
SQLite is an ACID-compliant embedded relational database management system contained in a relatively small C programming library. The source code for SQLite is in the public domain and implements most of the SQL standard...
Sybase, an SAP company, is an enterprise software and services company offering software to manage, analyze, and mobilize information, using relational databases, analytics and data warehousing solutions and mobile applications development platforms....
Virtuoso Universal Server is a middleware and database engine hybrid that combines the functionality of a traditional RDBMS, ORDBMS, virtual database, RDF, XML, free-text, web application server and file server functionality in a single system...
An embedded database system is a database management system which is tightly integrated with an application software that requires access to stored data, such that the database system is “hidden” from the application’s end-user and requires little or no ongoing maintenance...
ADO.NET is a set of computer software components that programmers can use to access data and data services. It is a part of the base class library that is included with the Microsoft .NET Framework. It is commonly used by programmers to access and modify data stored in relational database systems,...
Object-relational mapping in computer software is a programming technique for converting data between incompatible type systems in object-oriented programming languages. This creates, in effect, a "virtual object database" that can be used from within the programming language...
The architecture of the ADO.NET Entity Framework, from the bottom up, consists of the following: