New Features of Entity Framework Support in dotConnect Providers

• DML Options
  • Batch Updates
    • Batch Updates Limitations
    • Batch Updates Settings
  • Other DML Options
• SQL Formatting Settings
• Workarounds
• Database Script Generation Settings
  • DeleteDatabaseBehaviour
  • DBMS-Specific Properties
    • Oracle
    • MySQL
    • PostgreSQL
• Provider-Specific LIKE Implementation
• Oracle-Specific Improvements
  • Multiple Includes Support Improvement in Oracle
  • NumberMappings Support in EDM Wizard
  • Improved StoreGeneratedPattern Attribute Handling

In the new version of Devart dotConnect ADO.NET providers for Oracle, MySQL, PostgreSQL and SQLite we have significantly extended functional capabilities of Entity Framework. We maximally fulfilled requests of our users, including those received via our new UserVoice. Our users mainly requested new functional features, more flexibility in behavior and configuration as well as better performance.

New Entity Framework support features are configured in the <ProviderName>EntityProviderConfig class. This class is available in the Devart.Data.<ProviderName>.Entity assembly. For example, this code is used to enable the batch updates feature in dotConnect for Oracle:

      OracleEntityProviderConfig config = OracleEntityProviderConfig.Instance;
      config.DmlOptions.BatchUpdates.Enabled = true;

Don’t forget to add this Devart.Data.<ProviderName>.Entity.dll assembly as Reference for the corresponding Entity Framework version (support for the new functionality is available for EF v1 and v4.x).

DML Options

Please note that DML options influence only the INSERT/UPDATE/DELETE statements and stored procedure calls. SELECT statements are not affected by these options.

Batch Updates

We keep receiving complaints on low performance when a large number of CUD operations are executed in Entity Framework projects. We offered a Batch Update feature that should increase CUD performance, at our UserVoice forum, and it has become one of the most demanded features. Since opinions of our customers are always important for us, this feature had been given top priority in our plans. With its implementation, the CUD performance increased 2-3 times.

Note: In real-world applications the performance gain can vary from 20-30% to 200-300%. Please take into account that performance should be evaluated without using monitoring tools like dbMonitor because monitoring tools significantly reduce performance. When using SQLite, please remember that this is a local embedded database; that is why, the use of Batch Updates for SQLite does not always guarantee an increase in performance. In some cases, it might actually result in a performance decrease.

By default Entity Framework updates data one statement at a time. We have enabled our providers to group several INSERT/UPDATE/DELETE statements into one SQL block and merge their parameters. Now, instead of sending each DML statement separately, the SaveChanges method generates and executes statement batches.

Here is an example:

BatchUpdates disabled

UPDATE "Company" SET "CompanyName"=:p0,"Web"=:p1 WHERE "CompanyID"=:p2 
UPDATE "Company" SET "CompanyName"=:p0,"Web"=:p1 WHERE "CompanyID"=:p2 
UPDATE "Company" SET "CompanyName"=:p0,"Web"=:p1 WHERE "CompanyID"=:p2 
UPDATE "Company" SET "CompanyName"=:p0,"Web"=:p1 WHERE "CompanyID"=:p2 
UPDATE "Company" SET "CompanyName"=:p0,"Web"=:p1 WHERE "CompanyID"=:p2 
UPDATE "Product" SET "ProductName"=:p0 WHERE "ProductID"=:p1 
UPDATE "Product" SET "ProductName"=:p0,"Price"=:p1 WHERE "ProductID"=:p2 
UPDATE "Product" SET "ProductName"=:p0,"Price"=:p1 WHERE "ProductID"=:p2 
UPDATE "Product" SET "ProductName"=:p0,"Price"=:p1 WHERE "ProductID"=:p2 
UPDATE "Product" SET "ProductName"=:p0,"Price"=:p1 WHERE "ProductID"=:p2 
UPDATE "ProductCategory" SET "CategoryName"=:p0 WHERE "CategoryID"=:p1 
UPDATE "ProductCategory" SET "CategoryName"=:p0 WHERE "CategoryID"=:p1 
UPDATE "ProductCategory" SET "CategoryName"=:p0 WHERE "CategoryID"=:p1 
UPDATE "ProductCategory" SET "CategoryName"=:p0 WHERE "CategoryID"=:p1 
UPDATE "ProductCategory" SET "CategoryName"=:p0 WHERE "CategoryID"=:p1 
DELETE FROM "Company" WHERE "CompanyID"=:p0 
DELETE FROM "Company" WHERE "CompanyID"=:p0 
DELETE FROM "Company" WHERE "CompanyID"=:p0 
DELETE FROM "Company" WHERE "CompanyID"=:p0 
DELETE FROM "Company" WHERE "CompanyID"=:p0 
DELETE FROM "Product" WHERE "ProductID"=:p0 AND "Category_CategoryID"=:p1 
DELETE FROM "Product" WHERE "ProductID"=:p0 AND "Category_CategoryID"=:p1 
DELETE FROM "Product" WHERE "ProductID"=:p0 AND "Category_CategoryID"=:p1 
DELETE FROM "Product" WHERE "ProductID"=:p0 AND "Category_CategoryID"=:p1 
DELETE FROM "Product" WHERE "ProductID"=:p0 AND "Category_CategoryID"=:p1 
DELETE FROM "ProductCategory" WHERE "CategoryID"=:p0 AND "ParentCategory_CategoryID"=:p1   
DELETE FROM "ProductCategory" WHERE "CategoryID"=:p0 AND "ParentCategory_CategoryID"=:p1 
DELETE FROM "ProductCategory" WHERE "CategoryID"=:p0 AND "ParentCategory_CategoryID"=:p1   
DELETE FROM "ProductCategory" WHERE "CategoryID"=:p0 AND "ParentCategory_CategoryID"=:p1 
DELETE FROM "ProductCategory" WHERE "CategoryID"=:p0 AND "ParentCategory_CategoryID"=:p1 
INSERT INTO "Company"("CompanyID","CompanyName","Web","Email","Address_AddressTitle","Address_Address","Address_City","Address_Region","Address_PostalCode","Address_Country","Address_Phone","Address_Fax","PrimaryContact_ContactID","PersonContact_ContactID")VALUES(:p0,:p1,:p2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
INSERT INTO "Company"("CompanyID","CompanyName","Web","Email","Address_AddressTitle","Address_Address","Address_City","Address_Region","Address_PostalCode","Address_Country","Address_Phone","Address_Fax","PrimaryContact_ContactID","PersonContact_ContactID")VALUES(:p0,:p1,:p2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL) 
INSERT INTO "Company"("CompanyID","CompanyName","Web","Email","Address_AddressTitle","Address_Address","Address_City","Address_Region","Address_PostalCode","Address_Country","Address_Phone","Address_Fax","PrimaryContact_ContactID","PersonContact_ContactID")VALUES(:p0,:p1,:p2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL) 
INSERT INTO "Company"("CompanyID","CompanyName","Web","Email","Address_AddressTitle","Address_Address","Address_City","Address_Region","Address_PostalCode","Address_Country","Address_Phone","Address_Fax","PrimaryContact_ContactID","PersonContact_ContactID")VALUES(:p0,:p1,:p2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL) 
INSERT INTO "Company"("CompanyID","CompanyName","Web","Email","Address_AddressTitle","Address_Address","Address_City","Address_Region","Address_PostalCode","Address_Country","Address_Phone","Address_Fax","PrimaryContact_ContactID","PersonContact_ContactID")VALUES(:p0,:p1,:p2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL) 
INSERT INTO "Product"("ProductID","ProductName","UnitName","UnitScale","InStock","Price","DiscontinuedPrice","Category_CategoryID")VALUES(:p0,:p1,NULL,:p2,:p3,:p4,:p5,NULL) 
INSERT INTO "Product"("ProductID","ProductName","UnitName","UnitScale","InStock","Price","DiscontinuedPrice","Category_CategoryID")VALUES(:p0,:p1,NULL,:p2,:p3,:p4,:p5,NULL) 
INSERT INTO "Product"("ProductID","ProductName","UnitName","UnitScale","InStock","Price","DiscontinuedPrice","Category_CategoryID")VALUES(:p0,:p1,NULL,:p2,:p3,:p4,:p5,NULL) 
INSERT INTO "Product"("ProductID","ProductName","UnitName","UnitScale","InStock","Price","DiscontinuedPrice","Category_CategoryID")VALUES(:p0,:p1,NULL,:p2,:p3,:p4,:p5,NULL) 
INSERT INTO "Product"("ProductID","ProductName","UnitName","UnitScale","InStock","Price","DiscontinuedPrice","Category_CategoryID")VALUES(:p0,:p1,NULL,:p2,:p3,:p4,:p5,NULL) 
INSERT INTO "ProductCategory"("CategoryID","CategoryName","ParentCategory_CategoryID")VALUES(:p0,:p1,NULL) 
INSERT INTO "ProductCategory"("CategoryID","CategoryName","ParentCategory_CategoryID")VALUES(:p0,:p1,NULL) 
INSERT INTO "ProductCategory"("CategoryID","CategoryName","ParentCategory_CategoryID")VALUES(:p0,:p1,NULL) 
INSERT INTO "ProductCategory"("CategoryID","CategoryName","ParentCategory_CategoryID")VALUES(:p0,:p1,NULL) 
INSERT INTO "ProductCategory"("CategoryID","CategoryName","ParentCategory_CategoryID")VALUES(:p0,:p1,NULL)

BatchUpdates enabled

BEGIN
UPDATE "Company" SET "CompanyName"=:p0,"Web"=:p1 WHERE "CompanyID"=:p2;
UPDATE "Company" SET "CompanyName"=:p3,"Web"=:p4 WHERE "CompanyID"=:p5;
UPDATE "Company" SET "CompanyName"=:p6,"Web"=:p7 WHERE "CompanyID"=:p8;
UPDATE "Company" SET "CompanyName"=:p9,"Web"=:p10 WHERE "CompanyID"=:p11;
UPDATE "Company" SET "CompanyName"=:p12,"Web"=:p13 WHERE "CompanyID"=:p14;
UPDATE "Product" SET "ProductName"=:p15 WHERE "ProductID"=:p2;
UPDATE "Product" SET "ProductName"=:p16,"Price"=:p17 WHERE "ProductID"=:p5;
UPDATE "Product" SET "ProductName"=:p18,"Price"=:p19 WHERE "ProductID"=:p8;
UPDATE "Product" SET "ProductName"=:p20,"Price"=:p21 WHERE "ProductID"=:p11;
UPDATE "Product" SET "ProductName"=:p22,"Price"=:p23 WHERE "ProductID"=:p14;
UPDATE "ProductCategory" SET "CategoryName"=:p24 WHERE "CategoryID"=:p2;
UPDATE "ProductCategory" SET "CategoryName"=:p25 WHERE "CategoryID"=:p5;
UPDATE "ProductCategory" SET "CategoryName"=:p26 WHERE "CategoryID"=:p8;
UPDATE "ProductCategory" SET "CategoryName"=:p27 WHERE "CategoryID"=:p11;
UPDATE "ProductCategory" SET "CategoryName"=:p28 WHERE "CategoryID"=:p14;
DELETE FROM "Company" WHERE "CompanyID"=:p29 OR "CompanyID"=:p30 OR "CompanyID"=:p31 OR "CompanyID"=:p32 OR "CompanyID"=:p33;
DELETE FROM "Product" WHERE ("ProductID"=:p29 AND "Category_CategoryID"=:p2) OR ("ProductID"=:p30 AND "Category_CategoryID"=:p2) OR ("ProductID"=:p31 AND "Category_CategoryID"=:p2) OR ("ProductID"=:p32 AND "Category_CategoryID"=:p2) OR ("ProductID"=:p33 AND "Category_CategoryID"=:p2);
DELETE FROM "ProductCategory" WHERE ("CategoryID"=:p29 AND "ParentCategory_CategoryID"=:p2) OR ("CategoryID"=:p30 AND "ParentCategory_CategoryID"=:p2) OR ("CategoryID"=:p31 AND "ParentCategory_CategoryID"=:p2) OR ("CategoryID"=:p32 AND "ParentCategory_CategoryID"=:p2) OR ("CategoryID"=:p33 AND "ParentCategory_CategoryID"=:p2);
END;

BEGIN
INSERT INTO "Company"("CompanyID","CompanyName","Web","Email","Address_AddressTitle","Address_Address","Address_City","Address_Region","Address_PostalCode","Address_Country","Address_Phone","Address_Fax","PrimaryContact_ContactID","PersonContact_ContactID")VALUES(:p0,:p1,:p2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "Company"("CompanyID","CompanyName","Web","Email","Address_AddressTitle","Address_Address","Address_City","Address_Region","Address_PostalCode","Address_Country","Address_Phone","Address_Fax","PrimaryContact_ContactID","PersonContact_ContactID")VALUES(:p3,:p4,:p5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "Company"("CompanyID","CompanyName","Web","Email","Address_AddressTitle","Address_Address","Address_City","Address_Region","Address_PostalCode","Address_Country","Address_Phone","Address_Fax","PrimaryContact_ContactID","PersonContact_ContactID")VALUES(:p6,:p7,:p8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "Company"("CompanyID","CompanyName","Web","Email","Address_AddressTitle","Address_Address","Address_City","Address_Region","Address_PostalCode","Address_Country","Address_Phone","Address_Fax","PrimaryContact_ContactID","PersonContact_ContactID")VALUES(:p9,:p10,:p11,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "Company"("CompanyID","CompanyName","Web","Email","Address_AddressTitle","Address_Address","Address_City","Address_Region","Address_PostalCode","Address_Country","Address_Phone","Address_Fax","PrimaryContact_ContactID","PersonContact_ContactID")VALUES(:p12,:p13,:p14,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "Product"("ProductID","ProductName","UnitName","UnitScale","InStock","Price","DiscontinuedPrice","Category_CategoryID")VALUES(:p0,:p15,NULL,:p16,:p17,:p18,:p18,NULL);
INSERT INTO "Product"("ProductID","ProductName","UnitName","UnitScale","InStock","Price","DiscontinuedPrice","Category_CategoryID")VALUES(:p3,:p19,NULL,:p16,:p17,:p20,:p18,NULL);
INSERT INTO "Product"("ProductID","ProductName","UnitName","UnitScale","InStock","Price","DiscontinuedPrice","Category_CategoryID")VALUES(:p6,:p21,NULL,:p16,:p17,:p22,:p18,NULL);
INSERT INTO "Product"("ProductID","ProductName","UnitName","UnitScale","InStock","Price","DiscontinuedPrice","Category_CategoryID")VALUES(:p9,:p23,NULL,:p16,:p17,:p24,:p18,NULL);
INSERT INTO "Product"("ProductID","ProductName","UnitName","UnitScale","InStock","Price","DiscontinuedPrice","Category_CategoryID")VALUES(:p12,:p25,NULL,:p16,:p17,:p26,:p18,NULL);
INSERT INTO "ProductCategory"("CategoryID","CategoryName","ParentCategory_CategoryID")VALUES(:p0,:p27,NULL);
INSERT INTO "ProductCategory"("CategoryID","CategoryName","ParentCategory_CategoryID")VALUES(:p3,:p28,NULL);
INSERT INTO "ProductCategory"("CategoryID","CategoryName","ParentCategory_CategoryID")VALUES(:p6,:p29,NULL);
INSERT INTO "ProductCategory"("CategoryID","CategoryName","ParentCategory_CategoryID")VALUES(:p9,:p30,NULL);
INSERT INTO "ProductCategory"("CategoryID","CategoryName","ParentCategory_CategoryID")VALUES(:p12,:p31,NULL);
END;

Note that in situations when other applications significantly load the database server, you will get even more performance gain with batch updates, because they reduce the number of database server calls.

Batch Updates Limitations

TransactionScope cannot be used with batch updates. When using batch updates, concurrency check is not performed for PostgreSQL and SQLite, and it is disabled by default for MySQL and Oracle. You may still enable concurrency check for Oracle and MySQL databases as it is described below.

Some statements cannot be put into batches. When such a statement is encountered, the batch with previously added commands is executed immediately, then this statement is executed separately.

The following statements cannot be put into batches:

• Statements with the RETURNING clause, for example, statements that update or insert entities with columns having the StoreGeneratedPattern attribute value set to Identity or Computed.
• Calls of stored procedures or functions that have non-empty CommandText.

Batch Updates Settings

Please note that the Batch Updates functionality is available in run time, and can be turned on and off even in one method.

Batch Updates are configured with a number of parameters:

• Enabled – enables batch updates. If set to false (the default value), other Batch Updates settings are ignored.
• BatchSize – specifies the maximal number of commands in a batch. Optimal batch size is different for each specific case and DBMS, however, the default value 30 provides good enough results in most cases. If this property is set to 0, all commands will be encapsulated in one batch. In this case, the performance goes down, and DBMS will probably fail with an error because of the too large command size.
• AsynchronousBatch – determines whether batches are executed asynchronously (false by default), or a new batch will be created and executed only after the successful execution of the previous batch. It is recommended to enable asynchronous batch execution when the batch size is large. If the batch size is small, the performance gain is compensated by synchronization costs. However, if the batch size is too large, the performance gain will not be significant. To conclude – BatchSize should be large enough to take some time to execute, and small enough not to make the main thread wait for the background thread for too long.Activating asynchronous batch execution makes sense only if your application will be run on multiple-core processors. On single-core processors asynchronous batch execution provides even less performance than synchronous one.
• ConcurrencyCheck – enables concurrency check for Oracle and MySQL. By default, concurrency check is disabled when using batch updates. Then the concurrency is checked by comparing the number of affected rows to the batch size. Note that enabling concurrency check for Oracle database may cause some performance loss because of the additional parameter and calculations. It should be remembered that concurrency check in this case is performed for batches, not for individual rows. One command can influence 0 entries, and another can influence 2 entries, this situation will not be treated as an exception if these commands are in one batch. Of course, such situations are very uncommon.
  When concurrency check is enabled, the StateEntries property of the generated OptimisticConcurrencyException, if any, is not initialized because batch updates are managed by provider at ADO.NET level, and the provider does not have the Entity Framework level information about states of entities in the context’s collection.

Other DML Options

Here are some more DML Options (not dependent on Batch Updates) available in the EntityProviderConfig class:

• ReuseParameters – enables reusing existing parameters instead of creating new ones. For example, if the batch contains thirty INSERT statements, inserting the number 1024, one :p1 parameter will be used instead of creating thirty :p1 – :p30 parameters. Set this property to true only if a large number of the same values is sent to the database. Otherwise, it may even cause performance loss, if there are many parameters in a batch.
• ParametersAsLiterals – determines if parameter values are inserted into SQL statements as literals instead of parameters. This behavior can improve performance a bit in some rare cases.
• InsertNullBehaviour – determines how NULL values are inserted. NULL values can be inserted in different ways. We have implemented this configuration property that allows the developer to determine the behavior suitable for his particular application. Here is the list of possible alternatives:
  • InsertNull. In this case NULLs are inserted explicitly (for each column that has no non-NULL value specified) like in the following example:

     INSERT INTO "Company"
      ("CompanyID","CompanyName","Web","Email","Address_AddressTitle","Address_Address",
      "Address_City","Address_Region","Address_PostalCode","Address_Country",
      "Address_Phone","Address_Fax","PrimaryContact_ContactID","PersonContact_ContactID")
    VALUES
      (:p0,:p1,:p2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)

  • Omit. In this case provider simply omits all columns that do not have non-NULL values like in the following example:

     INSERT INTO "Company"("CompanyID","CompanyName","Web")VALUES(:p0,:p1,:p2)

  • InsertDefaultOrNull. In this case provider determines if there is a default value specified in the SSDL part of the model, and if it is available, this value is persisted to the database. If there is no default value specified, the behaviour is identical to the InsertNull one.
  • InsertDefaultOrOmit. In this case provider determines if there is a default value specified in the SSDL part of the model as well. However, if there is no default value specified for a column, it is omitted from the INSERT command completely.

Please note that the last two options work with both common DefaultValue SSDL attribute and the new custom devart:DefaultValue SSDL attribute. The latter attribute does not trigger the type consistency check, so you can use a wider range of default values, like CURRENT_TIMESTAMP or MY_SEQUENCE.NEXTVAL.

Here is an example of setting this attribute in SSDL:

The generated SQL command looks like the following:

INSERT INTO "Product"
  ("ProductID","ProductName","UnitScale","InStock","Price","DiscontinuedPrice")
  VALUES(MY_SEQUENCE.NEXTVAL,:p0,:p1,:p2,:p3,:p4)

If the InsertNullBehaviour is set to InsertNullBehaviour.InsertDefaultOrNull or InsertNullBehaviour.InsertDefaultOrOmit, and the column has DefaultValue, then the following rules are applied when executing INSERT:

• For Primary Key columns, DefaultValue is always inserted.
• For a column with StoreGeneratedPattern, DefaultValue is always inserted too.
• For a nullable column without StoreGeneratedPattern, if the value is not NULL, then this value is inserted, otherwise, the DefaultValue is inserted.
• For a not null column of .NET reference type (string, byte[]) without StoreGeneratedPattern, if the value is not NULL, then this value is inserted, otherwise, the DefaultValue is inserted.
• For a not null column of value type (Int32, DateTime, etc) without StoreGeneratedPattern, the value cannot be NULL, so this value is inserted.

For the use of Batch Updates, we recommend to enable the ReuseParameters options and set InsertNullBehaviour to the Omit or InsertDefaultOrOmit values; in most cases, that leads to performance increase. Here is an example of how to set these DML options for Oracle:

var config = OracleEntityProviderConfig.Instance;
config.DmlOptions.ReuseParameters = true;
config.DmlOptions.InsertNullBehaviour = InsertNullBehaviour.Omit;

SQL Formatting Settings

Additionally we have implemented some settings for disabling code formatting. This reduces the size of generated SQL statements a bit. The formatting can be completely enabled or disabled using the
<ProviderName>EntityProviderConfig.SqlFormatting.Enable()
or
<ProviderName>EntityProviderConfig.SqlFormatting.Disable()
methods respectively.

Here is an example of how to disable SQL formatting for Oracle:

var config = OracleEntityProviderConfig.Instance;
config.SqlFormatting.Disable();

More specific settings are available as well:

• ColumnListStacking – disables inserting new lines in the SELECT list. If set to true, the list looks like the following:

   SELECT 
  "Extent1"."CompanyID",
  "Extent1"."CompanyName",
  "Extent1"."Web",
  "Extent1"."Email",
  "Extent1"."Address_AddressTitle",
  "Extent1"."Address_Address",
  "Extent1"."Address_City",
  ...

  instead of

   SELECT "Extent1"."CompanyID", "Extent1"."CompanyName", "Extent1"."Web", "Extent1"."Email", "Extent1"."Address_AddressTitle", "Extent1"."Address_Address", "Extent1"."Address_City", ...

• ExtraSpaces – determines whether to insert spaces between operators and operands and after commas.
• Indenting – determines whether to indent keywords in multi-line queries.
• MultilineStatements – determines whether to split statements to several lines.

Workarounds

One of the most sophisticated issues with supporting Entity Framework is to accommodate the peculiarities of different DBMS, because Entity Framework is the easiest to use with Microsoft SQL Server. Properties from the Workarounds class are used to resolve some conflicts:

• IgnoreSchemaName. When set to true, this property makes provider remove the schema (database) name from all commands and queries, no matter whether Schema is specified in the model or not. A command will use the schema from the connection. Default value is false.This workaround is very useful in EF 4.1 Code First, since the “dbo” schema is set by default (like in SQL Server), and writing either attribute-based or fluent mapping is rather a time-consuming task. This functionality is useful in case of the Model or Database First approach as well. If the production and testing schemas have identical structure, it is enough to use this workaround, and no manual Schema replacing or removing is necessary in this case.
• DisableQuoting. This property allows the user to disable quoting all identifiers in all queries and commands. Default value is false.

Here is an example of how to disable schema name generation and quoting for dotConnect for Oracle:

var config = OracleEntityProviderConfig.Instance;
config.Workarounds.IgnoreSchemaName = true;
config.Workarounds.DisableQuoting = true;

Database Script Generation Settings

We have added some customization for creating and deleting database objects in the CreateDatabase() and DeleteDatabase() methods. These customizations are rather useful for the Code First approach. Here are the settings which can be accessed via the DatabaseScript.Schema property:

DeleteDatabaseBehaviour

This enumeration determines the behavior of the DeleteDatabase() function.

• ModelObjectsOnly — only the tables and sequences (in Oracle) that model objects are mapped to are deleted. This is the default value.
• AllSchemaObjects — all tables (and corresponding sequences used for auto-increment columns in Oracle) will be deleted from schemas or databases that model objects were mapped to.
• Schema – entire schema (database) is deleted. If the model contains objects from other schemas, these schemas (databases) will be deleted as well.
  Note: There is no possibility to delete the user that is currently connected in Oracle. Thus, to call the DeleteDatabase() method, the user must have grants to delete users and must not have any database objects that model objects are mapped to.

Here is an example of how to set DeleteDatabaseBehaviour for dotConnect for Oracle:

var config = OracleEntityProviderConfig.Instance;
config.DatabaseScript.Schema.DeleteDatabaseBehaviour =
DeleteDatabaseBehaviour.AllSchemaObjects;

DBMS-Specific Properties:

These properties affect the CreateDatabase() behavior if the DeleteDatabaseBehaviour option is set to DeleteDatabaseBehaviour.Schema

Oracle

• DefaultTablespace – set the name of default tablespace for the created schema
• Grants – pass the list of grants for the CREATE USER statement
• Profile – set the existing profile to the created user
• TemporaryTablespace – set the name of temporary tablespace for the created schema

MySQL

• CharacterSet – set the charset for the created database
• Collate – set the specific collation option for the created database

PostgreSQL

• AuthorizationUsername – set the authorization user name

Provider-Specific LIKE Implementation

dotConnect data providers introduced the full-featured database-specific LIKE function instead of using three different methods StartsWith(), Contains(), and EndsWith(). We have added the Like implementation to the OracleFunctions, MySqlFunctions, PgSqlFunctions and SQLiteFunctions classes (these classes contain the implementations of DB-specific functions).

Please note that this function can be used in LINQ-to-Entities, and the call will be translated into a LIKE operator with the corresponding parameters, and the escape-parameters will be included into the ESCAPE clause.

Oracle-Specific Improvements

Multiple Includes Support Improvement in Oracle

We got numerous user requests concerning the ORA-12704 error (“character set mismatch”). The reason of this error was a large number of Includes in the user code, and these Includes, in their turn, resulted in a query with a large number of UNION’s. We added a couple of workaround properties (TypedNulls and StringCastFormat) to deal with this error, which now are obsolete. We have found a possibility to fix this problem without these properties, and these queries are built correctly at the moment.

NumberMappings Support in EDM Wizard

The Number Mappings setting allows one to change the mapping of the Oracle NUMBER data type to .NET types, so that the properties of your entities, created by Entity Data Model Wizard, will have the type you need. For example, most of our users need to map the NUMBER(1, 0) field to Boolean, so it is a default behaviour. However, we have some users that need to map NUMBER(1, 0) to Byte or Int16.

Number Mappings solve the problem. We adjust the Number Mappings while creating the connection – and both the model properties and the values of these properties will have the needed type. You can map NUMBER to such .NET-types as Boolean, SByte, Byte, Int16, Int32, Int64, Single, Double and Decimal.

Here is the table of the default mappings:

Improved StoreGeneratedPattern Attribute Handling

It is a well-known fact that Oracle does not contain any built-in auto-increment support. The common solution to this problem is the use of a trigger and a sequence. However, Microsoft Entity Data Model Wizard is unable to set the StoreGeneratedPattern attribute for such columns automatically. In the previous builds of dotConnect for Oracle the manually set values of this attribute were discarded after each Update Model from Database wizard call (this problem was partially fixed in Visual Studio 2010 Service Pack 1). The new build of dotConnect for Oracle contains functionality that preserves the StoreGeneratedPattern settings for Visual Studio 2008 SP 1, Visual Studio 2010, Visual Studio 2010 SP 1 in all cases.