The ADO.NET Entity Framework Data Provider

Developer Reference : Data Access Methods : Data Provider for .NET : 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 Zen 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 Zen 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 Zen 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 Zen 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 Zen ADO.NET Entity Framework data provider uses three 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

•Pervasive.Data.SqlClient.Entity.dll v4.4.0.6 while referring to EF6

As shown here, the assembly name Pervasive.Data.SqlClient.Entity.dll is the same in all three cases.

During installation, the assemblies are placed in the Global Assembly Cache (GAC) in three separate subfolders: v4.3.0.0, v4.3.0.6, and v4.4.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

%windir%\Microsoft.NET\assembly\GAC_MSIL\Pervasive.Data.SqlClient.Entity\v4.0_4.4.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:

•Configuration File Registration

•Code-Based Registration

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:

<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>

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 35 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 (see Table 35 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 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 changes to the database 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 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 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%>

•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:

<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>

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 Zen (store) type influences the EDM type used when the model is generated.

Mapping Zen Types to EDM Types 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.

Table 10 Mapping Zen Types to EDM Types
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

Mapping EDM Types to Zen Types 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.

Table 11 Mapping EDM Types to Zen Types
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

Mapping CLR Types to Zen Data Types in a Code First Model 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.

Table 12 Mapping CLR Types to Zen Data Types in a Code First Model
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

Table 13 describes the mapping of EDM aggregate canonical functions to Zen functions.

Table 13 Mapping Aggregate Canonical 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

Table 14 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.

Table 14 Mapping Math Canonical 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

Table 15 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.

Table 15 Mapping Date and Time Canonical Functions
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

Table 16 describes the mapping of EDM bitwise canonical functions to Zen functions.

Table 16 Mapping Bitwise Canonical 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

Table 17 describes the mapping of EDM string canonical functions to Zen functions.

Table 17 Mapping String Canonical 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

Table 18 describes the mapping of other canonical functions to Zen functions.

Table 18 Mapping Other Canonical 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.

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 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.

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 Zen ADO.NET Entity Framework data provider and VS 2017 or later with version 4.4.

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:

•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.