The ADO.NET Entity Framework Data Provider
The ADO.NET Entity Framework is an object-relational mapping (ORM) framework for the .NET Framework. Developers can use it to create data access applications by programming against a conceptual application model instead of directly against a relational storage schema. This model allows developers to decrease the amount of code to be written and maintained in data-centric applications.
The PSQL ADO.NET Entity Framework data provider (formerly Pervasive ADO.NET Entity Framework data provider) can be used with applications that use the ADO.NET Entity Framework.
The PSQL ADO.NET Entity Framework data provider is compatible with versions 5.0, 6.0, 6.1., 6.1.1, and 6.1.2 of the Microsoft ADO.NET Entity Framework. It supports the following programming features:
•Applications targeting the following .NET Framework versions:
•4.5.x (.NET Framework 4.5 and its point releases, 4.5.1, and 4.5.2)
•4.6.x (.NET Framework 4.6 and its point releases, 4.6.1 and 4.6.2)
•Database First, Code First, and Model First workflows
•Enumerated type support in all workflows
•Code First migrations
•"Plain-old" CLR objects (POCO) entities
•DbContext class
The PSQL ADO.NET Entity Framework data provider also supports the following features specific to Microsoft ADO.NET Entity Framework 6.1, 6.1.1, and 6.1.2:
•Multiple DBContext classes
•Code First mapping to Insert, Update, and Delete stored procedures
•Configurable migration history
•Connection resiliency
•Index Attribute for Code First Migrations
•Disable Transactions for Function Imports
•Enum.HasFlag Support
•Allow Migrations commands to use context from reference instead of project
•Interceptors in web/app.config and DatabaseLogger
•Support for identifiers starting with '_'
•Select concatenated string and numeric property
The ADO.NET Entity Framework data provider uses the ADO.NET data provider to communicate with the ADO.NET database server. This means that the functionality defined by the ADO.NET data provider applies to the ADO.NET Entity Framework data provider unless otherwise noted here. Similarly, any performance configurations made to the ADO.NET data provider are realized by the ADO.NET Entity Framework data provider.
Visual Studio 2012 or later is required when developing applications for the PSQL ADO.NET Entity Framework. If you have configured Microsoft ADO.NET Entity Framework 6.1 (EF 6.1) to use Visual Studio 2012, you must install Entity Framework Tools 6.1.3 for Visual Studio 2012. However, once you install it, all your Visual Studio 2012 applications that previously used Microsoft ADO.NET Entity Framework 5.0 (EF5) must be upgraded to EF 6.1, after which you cannot revert to EF5.
About the ADO.NET Entity Framework Data Provider
The ADO.NET Entity Framework data provider is built with 100% managed code; it can run and connect to the database entirely within the common language runtime (CLR).
Code that runs in the native operating system, such as client libraries and COM components, is called unmanaged code. You can mix managed and unmanaged code within a single application. However, unmanaged code reaches outside the CLR, which means that it effectively raises complexity, reduces performance, and opens possible security risks.
Namespace
The namespace for the ADO.NET Entity Framework data provider is Pervasive.Data.SqlClient.Entity.
Note: The Pervasive.Data.SqlClient.Entity namespace is common for Microsoft ADO.NET Entity Framework Versions 5.0 (EF 5) and 6.1 (EF 6.1).
Assembly Names
The PSQL ADO.NET Entity Framework data provider uses two different assembly versions:
•Pervasive.Data.SqlClient.Entity.dll v4.3.0.0 while referring to EF5
•Pervasive.Data.SqlClient.Entity.dll v4.3.0.6 while referring to EF6
However, the assembly name is common: Pervasive.Data.SqlClient.Entity.dll.
During installation, the assemblies are placed in the Global Assembly Cache (GAC) in two separate subfolders: v4.3.0.0 and v4.3.0.6.
To refer to EF 5, select:
%windir%\Microsoft.NET\assembly\GAC_MSIL\Pervasive.Data.SqlClient.Entity\v4.0_4.3.0.0__c84cd5c63851e072
To refer to EF 6.1, select:
%windir%\Microsoft.NET\assembly\GAC_MSIL\Pervasive.Data.SqlClient.Entity\v4.0_4.3.0.6__c84cd5c63851e072
Configuring Entity Framework 6.1
The PSQL ADO.NET Entity Framework data provider supports the Microsoft ADO.NET Entity Framework versions 5.0 (EF5) and 6.1 (EF 6.1).
To use EF 6.1, you must first register it using one of the following methods:
Note: To register EF 6.1 while testing your applications locally, you can perform a code-based registration during development. However, when you deploy your project, you must perform a configuration file registration.
Configuration File Registration
►To configure EF 6.1 by updating the configuration file
1 Install the EntityFramework 6.1.2 NuGet package.
An app.config file is created.
2 Remove the defaultConnectionFactory registration section from the app.config file and replace it with the following code:
<providers>
<provider invariantName="Pervasive.Data.SqlClient"
type="Pervasive.Data.SqlClient.Entity.PsqlProviderServices, Pervasive.Data.SqlClient.Entity, Version=4.3.0.6, Culture=neutral, PublicKeyToken=c84cd5c63851e072" />
</providers>
The EF 6.1 provider registration is added to Entity Framework section of the app.config file.
Code-Based Registration
►To configure EF 6.1 through a code-based registration
1 Add the following new DbConfiguration class to your test application:
public class MyConfiguration : DbConfiguration
{
public MyConfiguration()
{
SetProviderServices("PsqlProviderServices.ProviderInvariantName, new PsqlProviderServices());
}
}
2 Add the following annotation on top of the DBContext class:
[DbConfigurationType(typeof(MyConfiguration))]
Using Multiple Entity Framework Versions Against the Same Database
A single database can use multiple versions of the Microsoft ADO.NET Entity Framework: 5.0 (EF5) and 6.1 (EF 6.1). However, when you switch between EF5 and EF6 applications against the same database, you will receive an error when you try saving to the database.
The error occurs due to the difference between the structure of the "__MigrationHistory" table used by EF5 and EF6.
To use EF5 and EF 6.1 applications against the same database without any errors, run the following command in the database:
drop table "__MigrationHistory"
Using Connection Strings with the PSQL ADO.NET Entity Framework Data Provider
The PSQL ADO.NET Entity Framework uses information contained in connection strings to connect to the underlying ADO.NET data provider that supports the Entity Framework. The connection strings also contain information about the required model and mapping files.
The data provider uses the connection string when accessing a model and mapping metadata and connecting to the data source.
You can specify an existing connection in the Entity Framework Wizard, or can define a new connection. Connection string options can be defined directly in a connection string, or set in the Advanced Properties dialog box in Visual Studio (see
Adding Connections in Server Explorer).
Defining Connection String Values in Server Explorer
See
Adding Connections in Server Explorer for detailed information about using Visual Studio to add and modify connections.
See Table
27 for a description of the supported connection string options.
Changes in Default Values for Connection String Options
Most default values of the connection string options used by the ADO.NET Entity Framework data provider are the same as those used by the PSQL ADO.NET data provider (see Table
27 for more information). Table
9 lists the connection string options that have a different default value when used with an ADO.NET Entity Framework application.
Table 9 Default Values of Connection String Options Used in an Application
Connection String Option | Default Value in ADO.NET Entity Framework Application |
Parameter Mode | Not supported. |
Statement Cache Mode | ExplicitOnly is the only supported value. |
Code First and Model First Support
Entity Framework 4.1 and later provide support for the Model First and Code First features. Implementing support for these features requires changes to the data provider, such as the way that long identifier names are handled. However, these changes do not require changes to your application.
Code First and Model First implementations require type mapping changes. See
Mapping Data Types and Functions for more information.
Handling Long Identifier Names
Most PSQL identifiers have a maximum length of 20 bytes. The identifier name can exceed this size because the names of the objects to be created on the server are taken from the class and property names. In addition, constraint names are often created by concatenating several object names. In these cases, the chances of exceeding the maximum identifier length are even greater.
The data provider shortens identifiers to database-allowed maximum identifier length, replacing the end of the identifier with an integer hash-code, for example, the string ColumnMoreThanTwentyCharacters is shortened to ColumnMor_2873286151. If you access or view the DB object using a DB tool, the names of the created tables may differ from what you might expect based on the Plain Old CLR Object (POCO) class names and property names (Code First), or the entity names and entity property names (Model First).
Note that when two identifiers that have the same leading characters are shortened, the difference between the identifiers is less obvious to a visual inspection. For example, assume that a table has two supporting sequences, ColumnMoreThanTwentyCharacters and ColumnMoreThanTwenty1Characters. When these sequences are shortened, they are renamed ColumnMor_2873286151 and ColumnMor_672399971.
Using Code First Migrations with the ADO.NET Entity Framework
Entity Framework 4.3 and later support Code First Migrations, which enables you to update your database schema to reflect POCO classes without having to drop and recreate them.
Migrations enable you to incrementally evolve your database schema as your model changes. Each set of changes to the database is expressed in a code file, known as a migration. The migrations are ordered, typically using a timestamp, and a table in the database keeps track of which migrations are applied to the database.
Code First Migrations implementation requires type mapping changes. See
Mapping Data Types and Functions for more information,
To implement Code First Migrations using Progress DataDirect Connect for PSQL ADO.NET Data Provider, you must perform the following additional settings:
1 Add references to the Pervasive.Data.SQLClient.Entity assembly in the project.
2 Inherit the Configuration Class changes and register the SQL Generator in the constructor of the Configuration Class. Do the following:
•Inherit the Configuration Class from PervasiveDbMigrationsConfiguration <TContext>. For example:
internal sealed class Configuration: PervasiveDbMigrationsConfiguration<%Context Name%>
•Register the Class Generator.
After you enable migrations using Package Manager Console, specify the Connection String either in the app.config or configuration.cs file along with additional settings in the configuration.cs file. However, if Connection String is specified in the app.config file, then ensure that the Connection String and the context have the same name.
If the Connection String is specified in the app.config file, use the following syntax to register SQL Generator in the app.config file:
<providers>
<provider invariantName="Pervasive.Data.SqlClient" type=
"Pervasive.Data.SqlClient.Entity.PsqlProviderServices,
Pervasive.Data.SqlClient.Entity, Version=4.3.0.6, Culture=neutral,
PublicKeyToken=c84cd5c63851e072" />
</providers>
To register SQL Generator in configuration.cs, use the following syntax:
SetSqlGenerator(PervasiveConnectionInfo.InvariantName, new PervasiveEntityMigrationSqlGenerator());
Using Enumerations with the ADO.NET Entity Framework
The enum keyword is used to declare an enumeration, a distinct type consisting of a set of named constants called the enumerator list. Every enumeration type has an underlying type. By default, every underlying type of the enumeration element is mapped to type int32. By default, the first enumerator has the value 0, and the value of each consecutive enumerator is incremented by 1. For example, you would specify a days-of-the-week enum type as:
enum Days {MON, TUE, WED, THU, FRI, SAT, SUN};
In this enumeration, MON would be 0, TUE 1, WED 2, and so forth. Enumerators can have initializers to override the default values. For example:
enum Days {MON=1, TUE, WED, THU, FRI, SAT, SUN};
In this enumeration, the sequence is forced to start at 1 instead of 0. The names of an enum type's fields are in uppercase letters., by convention, bacause they are constants.
Microsoft ADO.NET Entity Framework 5.0 and later support Enumerations. To use the enumeration feature, you must target .NET Framework 4.5 or later. Visual Studio 2012 targets .NET Framework 4.5 by default. Enumerations are supported in all three workflows, namely, Model First, Code First, and Database First.
In Entity Framework, an enumeration can have the following underlying types:
•Byte
•Int16
•Int32
•Int64
•SByte
By default, the enumeration is of type Int32. Another integral numeric type can be specified using a colon.
enum Days : byte{MON=1, TUE, WED, THU, FRI, SAT, SUN};
The underlying type specifies how much storage is allocated for each enumerator. However, an explicit cast is needed to convert from enum type to an integral type. Enum implementations also support type mapping changes. See
Mapping Data Types and Functions for more information.
As part of Entity Framework, Entity Developer fully supports enum types by providing a new Enum node in its Model Explorer window. You can use the Enum property just like any other scalar property, such as in LINQ queries and updates.
Mapping Data Types and Functions
Developers can use the ADO.NET Entity Framework to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema.
Type Mapping for Database First
In a Database First model, the data provider uses a store-centric type mapping scheme, in which the PSQL (store) type influences the EDM type used when the model is generated.
Mapping PSQL Types to EDM Types shows PSQL types are mapped to primitive types used in a Database First model. Some PSQL data types can map to several different EDM types; the default values are shown in italics.
The columns are defined as follows:
•The PSQL Type column refers to the native type name.
•The Store (SSDL) Type column refers to data types used by the store schema definition language (SSDL) file. The storage metadata schema is a formal description of the database that persists data for an application built on the EDM.
•The PrimitiveTypeKind column refers to the common data primitives used to specify the valid content of properties of entities used in defining EDM applications.
Table 10 Mapping PSQL Types to EDM Types
PSQL Type | Store (SSDL) Type | PrimitiveTypeKind |
BFLOAT4 | BFloat4 | Single |
BFLOAT8 | BFloat8 | Double |
BIGIDENTITY | Bigint | Int64 |
BIGINT | Bigint | Int64 |
BINARY | binary | Byte[] |
BIT | Bit | Boolean |
CHAR | Char | String |
CURRENCY | Currency | Decimal |
DATE | Date | DateTime |
DECIMAL | Decimal | Decimal |
DOUBLE | Double | Double |
FLOAT | Float | Float |
IDENTITY | Identity | Int32 |
INTEGER | Integer | Int32 |
LONGVARBINARY | LongVarBinary | Byte[] |
LONGVARCHAR | LongVarChar | String |
MONEY | Money | Decimal |
NCHAR | NChar | String |
NLONGVARCHAR | NLongVarChar | String |
NUMERIC | Decimal | Decimal |
NUMERICSA | DecimalSA | Decimal |
NUMERICSTS | DecimalSTS | Decimal |
NVARCHAR | NVarChar | String |
REAL | Real | Single |
SMALLIDENTITY | SmallIdentity | Int16 |
ROWID | Rowid | Binary |
SMALLINT | Smallint | Int16 |
TIME | Time | Time |
DATETIME | DateTime | DateTime |
TINYINT | TinyInt | SByte |
UBIGINT | UBigInt | UInt64 |
UNIQUE_IDENTIFIER | Guid | Guid |
UINTEGER | UInteger | UInt32 |
USMALLINT | USmallInt | UInt16 |
UTINYINT | UTinyInt | Byte |
VARCHAR | Varchar | String |
Type Mapping for Model First
Mapping EDM Types to PSQL Types shows the model-centric type mapping, where the EDM Simple Types influences the PSQL (store) type used when the database is created. The columns are defined as follows:
•The PrimitiveTypeKind column refers to the common data primitives used to specify the valid content of properties of entities used in defining EDM applications.
•The Property Values Affecting Type Mapping identifies any property values that can affect type mapping.
•The Store (SSDL) column refers to data types used by the store schema definition language (SSDL) file. The storage metadata schema is a formal description of the database that persists data for an application built on the EDM.
•The PSQL Type column refers to the native type name.
Table 11 Mapping EDM Types to PSQL Types
PrimitiveTypeKind | Property Values That Affect Type Mapping | Store (SSDL) Type | PSQL Type |
Binary | Fixed Length: TRUE Fixed Length: FALSE | Binary LongVarBinary | Binary(n) LongVarBinary |
Boolean | | Boolean | Bit |
Byte | | Tinylint_as_byte | TinyInt |
DateTime | | DateTime | DateTime |
Decimal | | Decimal | Decimal |
Double | | Double | Double |
Guid | | Guid | Guid |
Single | | Float | Float |
SByte | | Smallint_as_Sbyte | Smallint |
Int16 | | SmallInt | Smallint |
Int32 | | Integer | Integer |
Int64 | | Bigint | BigInt |
String | MaxLength= (1<=n<=8000) Fixed Length=True Unicode=False | Char | Char(n) |
MaxLength= (1<=n<=8000) Fixed Length=False Unicode=False | Varchar | Varchar(n) |
MaxLength= (>8000) Fixed Length=False Unicode=False | LongVarChar | LongVarchar |
MaxLength= (1<=n<=4000) Fixed Length=True Unicode=True | NChar | NChar(n) |
MaxLength= (1<=n<=4000) Fixed Length=False Unicode=True | NVarChar | NVarChar(n) |
MaxLength= (>4000) Fixed Length=False Unicode=True | NLongVarChar | NLongVarChar |
Time | | Time | Time |
DateTimeOffset | | DateTime | DateTime |
Type Mapping for Code First
Mapping CLR Types to PSQL Data Types in a Code First Model shows the model-centric type mapping, where the CLR type influences the PSQL (store) type used when the database is created. Some CLR types can map to several different PSQL types; the default values are shown in italics. ).
The columns are defined as follows:
•The CLR Type column refers to the common language runtime type name.
•The PSQL Type column refers to the native type name.
Table 12 Mapping CLR Types to PSQL Data Types in a Code First Model
CLR Type | PSQL Data Type |
Byte | BINARY |
Boolean | BIT |
Byte | TINYINT |
DateTime | DATETIME |
Decimal | DECIMAL |
Double | DOUBLE |
Guid | UNIQUEIDENTIFIER BINARY |
Single | FLOAT |
Sbyte | SMALLINT |
Int16 | SMALLINT |
Int32 | INTEGER |
Int64 | BIGINT |
String1 | NCHAR NVARCHAR NLONGVARCHAR |
TimeSpan | TIME |
DateTimeOffset | DateTime |
1 In the Code First workflow, if the length of the string field in an entity is not specified, the data provider sets the default length to 2048 and 4096 bytes for unicode and non-unicode types respectively. However, if the length of the string field is set to a maximum allowed limit, that is 4000 bytes for unicode types and 8000 bytes for non-unicode types, the data provider resets it to 2048 bytes and 4096 bytes respectively. For all the other scenarios where the length of the string field is specified, the data provider uses the specified length. |
Mapping EDM Canonical Functions to PSQL Functions
The ADO.NET Entity Framework translates the Entity Data Model (EDM) canonical functions to the corresponding data source functionality for the ADO.NET Entity Framework Data Provider for PSQL. The function invocations are expressed in a common form across data sources.
Because these canonical functions are independent of data sources, argument and return types of canonical functions are defined in terms of types in the EDM. When an Entity SQL query uses canonical functions, the appropriate function is called at the data source.
Both null-input behavior and error conditions are explicitly specified for all canonical functions. However, the ADO.NET Entity Framework does not enforce this behavior. Further details are available at:
http://msdn.microsoft.com/en-us/library/bb738626.aspx Aggregate Canonical Functions
Table
13 describes the mapping of EDM aggregate canonical functions to PSQL functions.
Table 13 Mapping Aggregate Canonical Functions
Aggregate Canonical Function | PSQL functions |
Avg(expression) | avg(expression) |
BigCount(expression) | count(expression) |
Count(expression) | count(expression) |
Max(expression) | max(expression) |
Min(expression) | min(expression) |
StDev(expression) | stdev(expression) |
StDevP(expression) | stdevp(expression) |
Sum(expression) | sum(expression) |
Var(expression) | var(expression) |
VarP(expression) | varp(expression) |
Math Canonical Functions
Table
14 describes the mapping of EDM math canonical functions to PSQL functions used to process columns that contain only decimal and integer values.
For more information, refer to the
Numeric Functions.
Table 14 Mapping Math Canonical Functions
Math Canonical Function | PSQL Function |
Abs(value) | abs(value) |
Ceiling(value) | ceiling(value) |
Floor(value) | floor(value) |
Power(value, exponent) | power(value, exponent) |
Round(value) | round(numeric_expression1, integer_expression2) |
Round(value, digits) | round(value, digits) |
Truncate(value, digits) | truncate(value, digits) |
Date and Time Canonical Functions
Table
15 describes the mapping of EDM date and time canonical functions to PSQL functions that generate, process, and manipulate data that consists of data types such as DATE and TIME.
Table 15 Mapping Date and Time Canonical Functions
Date and Time Canonical Function | PSQL Functions |
AddNanoseconds(expression,number) | dateadd(millisecond,number/1000000) |
AddMicroseconds(expression,number) | dateadd(millisecond,number/1000) |
AddMilliseconds(expression,number) | dateadd(millisecond,number) |
AddSeconds(expression,number) | dateadd(second,number) |
AddMinutes(expression,number) | dateadd(minute,number) |
AddHours(expression,number) | dateadd(hour,number) |
AddDays(expression,number) | dateadd(day,number) |
AddMonths(expression,number) | dateadd(month,number) |
AddYears(expression, number) | dateadd(year,number) |
CreateDateTime(year,month,day,hour,minute,second) | datetimefromparts(year,month,day,hour,minute,second,0) |
CreateDateTimeOffset(year,month,day, hour,minute,second,tzoffset)1 | datetimeoffsetfromparts(year,month,day,hour, minute,second,tzoffset) |
CreateTime(hour,minute,second)1 | timefromparts(hour,minute,second,0,0) |
CurrentDateTime() | now() |
CurrentDateTimeOffset() | sysdatetimeoffset() |
CurrentUtcDateTime() | current_timestamp() |
Day(expression) | datepart(day,expression) |
DayOfYear(startexpression,endexpression) | dayofyear(expression) |
DiffNanoSeconds(startexpression,endexpression) | datediff(millisecond,startexpression,endexpression)*1000000 |
DiffMilliSeconds(startexpression,endexpression) | datediff(millisecond,startexpression,endexpression) |
DiffMicroSeconds(startexpression,endexpression) | datediff(millisecond,startexpression,endexpression)*1000 |
DiffSeconds(startexpression,endexpression) | datediff(second,startexpression,endexpression) |
DiffMinutes(startexpression,endexpression) | datediff(minute,startexpression,endexpression) |
DiffHours(startexpression,endexpression) | datediff(hour, startexpression,endexpression) |
DiffDays(startexpression,endexpression) | datediff(day, startexpression, endexpression) |
DiffMonths(startexpression,endexpression) | datediff(month,startexpression,endexpression) |
DiffYears(startexpression,endexpression) | datediff(year,startexpression,endexpression) |
GetTotalOffsetMinutes(DateTime Offset) | datepart(tzoffset,expression) |
Year(expression) | datepart(year,expression) |
Month(expression) | datepart(month,expression) |
Day(expression) | datepart(day,expression) |
Hour(expression) | datepart(hour,expression) |
Minute(expression) | datepart(minute,expression) |
Second(expression) | datepart(second,expression) |
Millisecond(expression) | datepart(millisecond,expression) |
TruncateTime(expression) | convert(expression, SQL_DATE) |
1 Requires PSQL v11.30 Update 4 (May 2013) |
Bitwise Canonical Functions
Table
16 describes the mapping of EDM bitwise canonical functions to PSQL functions.
Table 16 Mapping Bitwise Canonical Functions
Bitwise Canonical Function | PSQL Functions |
BitWiseAnd (value1, value2) | bit_and (value1, value2) |
BitWiseNot (value) | bit_compliment |
BitWiseOr (value1, value2) | bit_or |
BitWiseXor (value1, value2) | bit_xor |
String Canonical Functions
Table
17 describes the mapping of EDM string canonical functions to PSQL functions.
Table 17 Mapping String Canonical Functions
String Canonical Function | PSQL Function |
Concat(string1, string2) | concat(string1, string2) |
Contains(string, target) | contains(string, target) |
EndsWith(string, target) | endswith(string, target) |
IndexOf(target, string2) | instr(target, string2) |
Left(string1, length) | left(string1, length) |
Length(string) | length(string) |
LTrim(string) | ltrim(string) |
Trim(string) | trim (BOTH FROM string) |
Replace(string1, string2, string3) | replace(string1, string2, string3) |
Reverse(string) | reverse(string) |
RTrim(string) | rtrim(string) |
StartsWith(string, target) | startswith(string, target) |
Substring(string, start, length) | INCOMPLETE regexpr_substr(…) |
ToLower(string) | lower(string) |
ToUpper(string) | upper(string) |
Other Canonical Functions
Table
18 describes the mapping of other canonical functions to PSQL functions.
Table 18 Mapping Other Canonical Functions
Other Canonical Function | PSQL Function |
NewGuid() | newid() |
Extending Entity Framework Functionality
The ADO.NET Entity Framework offers powerful productivity gains by masking many ADO.NET features, simplifying application development. The PSQL ADO.NET Data Provider includes functionality designed to optimize performance.
Applications that use the standard Logging Application Block (LAB) from the Microsoft Enterprise Library 6.0 and the related design patterns can quickly display the SQL generated as part of the ADO.NET Entity Framework data providers.
See
Logging Application Blocks for more information.
Enhancing Entity Framework Performance
Although the Entity Framework offers powerful productivity gains, some developers believe that the Entity Framework takes too much control of the features they need to optimize performance in their applications.
Limiting the Size of XML Schema Files
Building large models with the Entity Data Model (EDM) can be very inefficient. For optimal results, consider breaking up a model when it has reached 50 to 100 entities.
The size of the XML schema files is to some extent proportional to the number of tables, views, or stored procedures in the database from which you generated the model. As the size of the schema files increase, additional time is needed to parse and create an in-memory model for the metadata. This is a one-time performance cost that is incurred for each ObjectContext instance.
This metadata is cached per application domain, based on the EntityConnection String. This means that if you use the same EntityConnection string in multiple ObjectContext instances in a single application domain, the application incurs the cost of loading metadata only once. However, the performance cost could still be significant if the size of the model becomes large and the application is not a long-running one.
Using Stored Procedures with the ADO.NET Entity Framework
Using stored procedures with the ADO.NET Entity Framework requires mapping functions. Calling these stored procedures is complex and requires some coding.
Providing Functionality
The Connection object includes properties and methods that provide enhanced statistics functionality that are standard in the ADO.NET data provider, but are not available at the ADO.NET Entity Framework layer. Instead, you expose the same functionality through "pseudo" stored procedures.
This approach uses the Entity Data Model (EDM) to achieve results that correspond to the ADO.NET results. This in effect provides entities and functions backed by pseudo stored procedures.
Table
19 lists the mapping of the data provider’s Connection properties to the corresponding pseudo stored procedure.
Table 19 Mapping to Pseudo Stored Procedure
Connection Property | Pseudo Stored Procedure |
StatisticsEnabled | Psql_Connection_EnableStatistics Psql_Connection_DisableStatistics |
Connection Method | Pseudo Stored Procedure |
ResetStatistics | Psql_Connection_ResetStatistics |
RetrieveStatistics | Psql_Connection_RetrieveStatistics |
Applications must use the ObjectContext to create a stored procedure command as shown in the following C# code fragment:
using (MyContext context = new MyContext())
{
EntityConnection entityConnection = (EntityConnection)context.Connection;
// The EntityConnection exposes the underlying store connection
DbConnection storeConnection = entityConnection.StoreConnection;
DbCommand command = storeConnection.CreateCommand();
command.CommandText = "Psql_Connection_EnableStatistics";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new PsqlParameter("cid", 1));
}
//
bool openingConnection = command.Connection.State == ConnectionState.Closed;
if (openingConnection) { command.Connection.Open(); }
int result;
try
{
result = command.ExecuteNonQuery();
}
finally
{
if (openingConnection && command.Connection.State == ConnectionState.Open) { command.Connection.Close(); }
}
Using Overloaded Stored Procedures
If you have multiple overloaded stored procedures, the PSQL Entity Framework data provider appends an identifier to each stored procedure name so you can distinguish between them in the SSDL. The data provider removes the appended identifier before calling the stored procedure for your application.
Using .NET Objects
The ADO.NET Entity Framework data provider supports the .NET public objects, exposing them as sealed objects.
For more information, see
.NET Objects Supported.
The ADO.NET Entity Framework programming contexts inherently eliminate the need to use some ADO.NET methods and properties. These properties and methods remain useful for standard ADO.NET applications. The online help, which is integrated into Visual Studio, describes the public methods and properties of each class.
Table
20 lists the properties and methods that are not required or are implemented differently when using the data provider with an ADO.NET Entity application.
Table 20 Properties and Methods Differences with the ADO.NET Entity Data Provider
Property or Method | Behavior |
PsqlCommand |
AddRowID | Not supported. The ADO.NET Entity Framework does not process the additional data that is returned. |
ArrayBindCount | Not supported. The application cannot influence this bind count on top of the ADO.NET Entity Framework. |
ArrayBindStatus | Not supported. The application cannot influence this bind count on top of the ADO.NET Entity Framework. |
BindByName | Not supported. Instead, the data provider uses the ADO.NET Entity Framework programming contexts. |
CommandTImeout | Not supported. Instead, the data provider uses the ADO.NET Entity Framework programming contexts. |
UpdatedRowSource
| Not supported. Instead, the data provider uses the ADO.NET Entity Framework programming contexts. |
PsqlCommandBuilder |
DeriveParameters | Not supported. Instead, the data provider uses the ADO.NET Entity Framework programming contexts.
|
PsqlConnection |
ConnectionTimeout | Supported only in a connection string. |
StatisticsEnabled | Use the StatisticsEnabled or StatisticsDisabled stored procedure. See
Using Stored Procedures with the ADO.NET Entity Framework for information on using this functionality in an ADO.NET Entity Framework application. |
DataAdapter |
UpdateBatchSize | Not supported. Instead, the data provider uses the ADO.NET Entity Framework programming contexts. |
Error |
ErrorPosition | Not supported. Instead, the data provider uses the ADO.NET Entity Framework programming contexts. |
SQLState | Not supported. Instead, the data provider uses the ADO.NET Entity Framework programming contexts. |
Creating a Model
The Entity Framework creates a model of your data in Visual Studio.
Note: Developing with the Microsoft ADO.NET Entity Framework data provider requires that you use Microsoft .NET Framework Version 4.5.x or 4.6.x and Visual Studio 2012 or later with version 4.3 of the PSQL ADO.NET Entity Framework data provider.
To create a model of your data in Visual Studio using the Entity Framework, you must first ensure that you already have the database schema available.
►To use the Entity Framework for creating a model of your data in Visual Studio
1 Create a new .NET application, such as Windows Console, Windows Forms, in Visual Studio.
2 In the Solution Explorer, right-click the project and select Add > New Item.
3 Select
ADO.NET Entity Data Model, then click
Add.
4 The Entity Data Model Wizard appears. Based on whether you have configured Microsoft ADO.NET Entity Framework 6.1 (EF 6.1), do one of the following:
•If you
have not configured EF 6.1, select
Generate from database and click
Next.
•If you
have configured EF 6.1, select
EF Designer from database and click
Next.
5 On the Choose your Data Connection page, click
New Connection to create a new connection. If you have an established connection, you can select it from the drop-down list.
6 The Connection Properties window appears. Provide the necessary connection information and click
OK.
7 The Wizard creates an Entity connection string.
a. If the radio buttons are selectable, select Yes, include the sensitive data in the connection string to include the sensitive data in the connection string.
b. In the Save entity connection settings field, enter a name for the name of the main data access class or accept the default.
c. Click Next.
8 Based on the configured Entity Framework version, do one of the following:
•If you have configured EF5 for the current project, on the Choose Your Version page, proceed with the default
Entity Framework 5.0 by clicking
Next.
Note: To use the EF 6.1 with your current project, exit the wizard, configure EF 6.1, and then rebuild the project. When you rebuild the project after configuring EF 6.1, the wizard does not display the Choose Your Version page and you can directly proceed to the next step.
•If you have configured EF 6.1 for the current project, proceed to the next step.
9 Select the database objects that will be used in the model.
10 Click Finish. The model is generated and opened in the Model Browser.
For More Information
Refer to the following sources for additional information about the ADO.NET and the Entity Framework:
•
Connection Strings (Entity Framework) describes how connection strings are used by the Entity Framework. The connection strings contain information used to connect to the underlying ADO.NET data provider as well as information about the required Entity Data Model mapping and metadata.
•
Entity Data Model Tools describes the tools that help you to build applications graphically with the EDM: the Entity Data Model Wizard, the ADO.NET Entity Data Model Designer (Entity Designer), and the Update Model Wizard. These tools work together to help you generate, edit, and update an Entity Data Model.
•
LINQ to Entities enables developers to write queries against the database from the same language used to build the business logic.
