Zen ADO.NET Entity Framework Data Providers
Zen 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.
Zen ADO.NET Entity Framework data providers (formerly Pervasive ADO.NET Entity Framework data providers) can be used with applications that use the ADO.NET Entity Framework.
Zen ADO.NET Entity Framework data providers are compatible with versions 6.1, 6.1.1, and 6.1.2 of the Microsoft ADO.NET Entity Framework. They support the following programming features:
• Applications targeting the .NET Framework versions listed under Zen ADO.NET Data Providers Available with SDK Download.
• 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
• 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
Zen ADO.NET Entity Framework data providers use ADO.NET data providers to communicate with the ADO.NET database server. This means that the functionality defined by Zen ADO.NET data providers applies to Zen ADO.NET Entity Framework data providers unless otherwise noted here. Similarly, any performance configurations made to Zen ADO.NET data providers are realized by Zen ADO.NET Entity Framework data providers.
Visual Studio 2017 or later is required when developing applications for the Zen ADO.NET Entity Framework. If you have configured Microsoft ADO.NET Entity Framework 6.1 (EF 6.1) to use Visual Studio 2017, you must install Entity Framework Tools 6.1.3 for Visual Studio 2017. However, once you install it, all your Visual Studio 2017 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 Zen ADO.NET Entity Framework Data Providers
Zen ADO.NET Entity Framework data providers are built with 100% managed code, so they 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 Zen ADO.NET Entity Framework data providers is Pervasive.Data.SqlClient.Entity.
Note: The Pervasive.Data.SqlClient.Entity namespace is common to Microsoft ADO.NET Entity Framework versions 5.0 (EF 5) and 6.1 (EF 6.1).
Assembly Names
Zen ADO.NET Entity Framework data providers use the assembly name Pervasive.Data.SqlClient.Entity.dll.
To refer to EF 6.1 using the 4.4 version of the provider, select:
%windir%\Microsoft.NET\assembly\GAC_MSIL\Pervasive.Data.SqlClient.Entity\v4.0_4.4.0.6__c84cd5c63851e072
To refer to EF 6.1 using the 4.5 version of the provider, select:
%windir%\Microsoft.NET\assembly\GAC_MSIL\Pervasive.Data.SqlClient.Entity\v4.0_4.5.0.6__c84cd5c63851e072
Configuring Entity Framework 6.1
The Zen 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 according to the version of the provider you are using.
• For the 4.4 version:
<providers>
<provider invariantName="Pervasive.Data.SqlClient"
type="Pervasive.Data.SqlClient.Entity.PsqlProviderServices, Pervasive.Data.SqlClient.Entity, Version=4.4.0.6, Culture=neutral, PublicKeyToken=c84cd5c63851e072" />
</providers>
• For the 4.5 version:
<providers>
<provider invariantName="Pervasive.Data.SqlClient"
type="Pervasive.Data.SqlClient.Entity.PsqlProviderServices, Pervasive.Data.SqlClient.Entity, Version=4.5.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 Zen ADO.NET Entity Framework Data Provider
The Zen 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 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 Zen ADO.NET data provider. The following table lists the connection string options that have a different default value when used with an ADO.NET Entity Framework 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 Zen 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 database changes is expressed in a code file, known as a migration. The migrations are ordered, typically using a time stamp, and a table in the database keeps track of which migrations are applied.
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 Zen 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%>
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 according to the version of the provider you use:
• For the 4.4 version:
<providers>
<provider invariantName="Pervasive.Data.SqlClient"
type="Pervasive.Data.SqlClient.Entity.PsqlProviderServices, Pervasive.Data.SqlClient.Entity, Version=4.4.0.6, Culture=neutral, PublicKeyToken=c84cd5c63851e072" />
</providers>
• For the 4.5 version:
<providers>
<provider invariantName="Pervasive.Data.SqlClient"
type="Pervasive.Data.SqlClient.Entity.PsqlProviderServices, Pervasive.Data.SqlClient.Entity, Version=4.5.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. 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 Zen (store) type influences the EDM type used when the model is generated.
Type Mapping for Database First
The following table shows Zen types are mapped to primitive types used in a Database First model. Some Zen data types can map to several different EDM types; the default values are shown in italics.
The columns are defined as follows:
• The Zen 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.
Zen Type | Store (SSDL) Type | PrimitiveTypeKind |
|---|---|---|
AUTOTIMESTAMP | DateTime | DateTime |
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 |
DATETIME | DateTime | 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 |
ROWID | Rowid | Binary |
SMALLIDENTITY | SmallIdentity | Int16 |
SMALLINT | Smallint | Int16 |
TIME | Time | Time |
TIMESTAMP, TIMESTAMP2 | 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
The following table shows the model-centric type mapping where the EDM Simple Types influence the Zen (store) type used to create the database. 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.
• Property Values Affecting Type Mapping identifies any property value 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 Zen Type column refers to the native type name.
PrimitiveTypeKind | Property Values That Affect Type Mapping | Store (SSDL) Type | Zen 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
The following table shows the model-centric type mapping, where the CLR type influences the Zen (store) type used when the database is created. Some CLR types can map to several different Zen 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 Zen Type column refers to the native type name.
CLR Type | Zen 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 Zen 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 Zen. 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
The following table describes the mapping of EDM aggregate canonical functions to Zen functions.
Aggregate Canonical Function | Zen 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
The following table describes the mapping of EDM math canonical functions to Zen functions used to process columns that contain only decimal and integer values.
For more information, refer to the Numeric Functions.
Math Canonical Function | Zen 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
The following table describes the mapping of EDM date and time canonical functions to Zen functions that generate, process, and manipulate data that consists of data types such as DATE and TIME.
Date and Time Canonical Function | Zen 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 Zen v11.30 Update 4 (May 2013) | |
Bitwise Canonical Functions
The following table describes the mapping of EDM bitwise canonical functions to Zen functions.
Bitwise Canonical Function | Zen 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
The following table describes the mapping of EDM string canonical functions to Zen functions.
String Canonical Function | Zen 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
The following table describes the mapping of other canonical functions to Zen functions.
Other Canonical Function | Zen 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 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.
Mapping to Pseudo Stored Procedure
The following table lists the mapping of the data provider’s Connection properties to the corresponding 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 Zen 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.
Properties and Methods Differences with the ADO.NET Entity Data Provider
The following table lists the properties and methods that are not required or are implemented differently when using the data provider with an ADO.NET Entity application.
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 ADO.NET Entity Framework requires that you use Microsoft .NET Framework Version 4.5.x, 4.6.x, 4.7.x, or 4.8 and Visual Studio 2017 or later with the 4.4 or 4.5 version of the Actian Zen 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. 
Upgrading Entity Framework 5 Applications to Entity Framework 6.1
You can use Entity Framework Power Tools to regenerate Mapping Views with EF 6.1. For Entity Framework Power Tools to work with Zen ADO.NET Entity Framework Provider, you must add a Provider registration entry in the .NET Framework 4.0 machine.config file.
The steps given here upgrade an EF 5 application created using Zen ADO.NET Entity Framework provider to an EF 6.1 application. This procedure is needed only to upgrade an EF 5 application to EF 6.1. After upgrading, we recommend undoing machine.config file edits to restore their previous content.
Note: If the target database already contains objects created by the Entity Framework 5 code first application, then you need to drop these objects before running the migrated Entity Framework 6.1 application. Entity Framework 5.0 generates the foreign key constraint name differently from Entity Framework 6.1, which causes the application to fail with error "Table or view already exists."
To edit machine.config files
1. Close all Visual Studio windows before continuing with these steps.
2. Open the machine.config file for .Net Framework 4 in the following location, and also in the second one if you are using a 64-bit system:
• %windir%\Microsoft.NET\Framework\v4.0.30319\Config\machine.config
• %windir%\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config
3. Under the <configSections></configSections> node, add the following entry:
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
4. Under the <configuration> </configuration> node after the </configSections> closure tag, add the following entry:
<entityFramework>
<providers>
<provider invariantName="Pervasive.Data.SqlClient" type="Pervasive.Data.SqlClient.Entity.PsqlProviderServices, Pervasive.Data.SqlClient.Entity, Version=4.5.0.6, Culture=neutral, PublicKeyToken=c84cd5c63851e072" />
</providers>
</entityFramework>
<providers>
<provider invariantName="Pervasive.Data.SqlClient" type="Pervasive.Data.SqlClient.Entity.PsqlProviderServices, Pervasive.Data.SqlClient.Entity, Version=4.5.0.6, Culture=neutral, PublicKeyToken=c84cd5c63851e072" />
</providers>
</entityFramework>
Note: This version 4.5 example uses Version=4.5.0.6. For version 4.4, use Version=4.4.0.6.
5. Save and close the files you changed.
6. You must install EF version 6.1.2 to the corresponding project. To do so, you need to upgrade your project to the new EF 6.1.x runtime. You can do this by one of two methods:
Method 1
a. Right-click your project and select Manage NuGet Packages.
b. In the Online tab, select EntityFramework and click Install.
Any previous version of the EntityFramework NuGet package is upgraded to EF 6.1.x.
Method 2
Run the following command from Package Manager Console for the corresponding project to install EF 6.1.2 for that project:
Install-Package EntityFramework -Version 6.1.2
7. After using one of the methods, confirm removal of assembly references to System.Data.Entity.dll.
Installing the EF6 NuGet package should automatically remove any references to System.Data.Entity from your project.
8. Change any Enterprise Framework Designer (EDMX) models to use EF 6.x code generation. If you have any models created with the EF Designer, you will need to update the code generation templates to generate EF6-compatible code.
Note: For Visual Studio 2012 and later, only EF 6.x DbContext Generator templates are available.
a. Delete existing code-generation templates:
These files are typically named <edmx_file_name>.tt and <edmx_file_name>.Context.tt and are nested under your .edmx file in Solution Explorer. You can select the templates in Solution Explorer and use the delete key to remove them.
These files are typically named <edmx_file_name>.tt and <edmx_file_name>.Context.tt and are nested under your .edmx file in Solution Explorer. You can select the templates in Solution Explorer and use the delete key to remove them.
Note: In website projects, the templates are not nested under your .edmx file, but alongside it in Solution Explorer. In VB.NET projects you need to enable Show All Files to see the nested template files.
b. Add the appropriate EF 6.x code generation template:
Open your model in the EF Designer, right-click the design surface, and select Add Code Generation Item.
Open your model in the EF Designer, right-click the design surface, and select Add Code Generation Item.
• If you are using the DbContext API (recommended), then EF 6.x DbContext Generator is available under the Data tab.
Note that if you are using Visual Studio 2012, you need to install the EF 6 Tools to have this template. For details, see Get Entity Framework at Microsoft, https://msdn.microsoft.com/en-us/library/ee712906(v=vs.113).aspx.
Note that if you are using Visual Studio 2012, you need to install the EF 6 Tools to have this template. For details, see Get Entity Framework at Microsoft, https://msdn.microsoft.com/en-us/library/ee712906(v=vs.113).aspx.
• If you are using the ObjectContext API, then you need to select the Online tab and search for EF 6.x EntityObject Generator.
c. If you applied any customizations to the code generation templates, you will need to reapply them to the updated templates.
9. Update name spaces for any core EF types you are using.
The name spaces for DbContext and Code First types have not changed. This means that for many applications that use EF 4.1 or later you will not need to change anything.
Types such as ObjectContext that were previously in System.Data.Entity.dll have been moved to new name spaces. This means you may need to update your using or import directives to build against EF6.
The general rule for namespace changes is that any type in System.Data.* is moved to System.Data.Entity.Core.*. In other words, just insert Entity.Core. after System.Data. For example:
• System.Data.EntityException => System.Data.Entity.Core.EntityException
• System.Data.Objects.ObjectContext => System.Data.Entity.Core.Objects.ObjectContext
• System.Data.Objects.DataClasses.RelationshipManager => System.Data.Entity.Core.Objects.DataClasses.RelationshipManager
These types are in the Core name spaces because they are not used directly for most DbContext-based applications. Some types that were part of System.Data.Entity.dll are still used commonly and directly for DbContext-based applications and so have not been moved into the Core name spaces. These are:
• System.Data.EntityState => System.Data.Entity.EntityState
• System.Data.Objects.DataClasses.EdmFunctionAttribute => System.Data.Entity.DbFunctionAttribute
Note: This class has been renamed. A class with the old name still exists and works but is now marked as obsolete.
Note: This class has been renamed. A class with the old name still exists and works but is now marked as obsolete.
• System.Data.Objects.EntityFunctions => System.Data.Entity.DbFunctions
Note: This class has been renamed. A class with the old name still exists and works but is now marked as obsolete.
Note: This class has been renamed. A class with the old name still exists and works but is now marked as obsolete.
10. Regenerate Mapping Views
If you have generated Mapping Views previously, delete the files and regenerate the Mapping View. For more information about Mapping Views, see https://msdn.microsoft.com/en-us/data/dn469601.
The EF 5 application built with the Zen ADO.NET data provider is now upgraded to EF 6.1.2.
Note: Once all of your EF 5 applications are upgraded to EF 6.1.2, we recommend that you undo the changes to Machine.config file and restore your earlier Machine.config file.
Entity Framework Power Tools provides an easier way to migrate EF 5.0 applications which use model Views to EF 6.1 applications. During our testing we found the Entity Framework Power Tools working fine with Zen Entity Framework Provider.
Note: Entity Framework Power Tools is not a certified or supported tool with Zen ADO.NET Entity Framework data providers.
For More Information
See the following sources for additional information about ADO.NET and the Entity Framework:
• Programming Entity Framework by Julie Lerman provides a comprehensive discussion of using the ADO.NET Entity Framework.
• ADO.NET Entity Framework introduces the Entity Framework and provides links to numerous detailed articles.
• 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.
Last modified date: 10/31/2023