Devart Blog

Entity Framework: Tips and Tricks, part 4

Posted by on February 4th, 2011

We continue post series about Entity Framework usage. We consider the following questions in this article:


1. MS SQL Server and Oracle – how to use both in one EF project?

Issue:

I am using Entity Framework with SQL Server and I need to migrate to Oracle database. How can I do this? And, is it possible to have both Oracle and SQL Server databases in production simultaneously without duplicating the code of the project?

Solution:

To solve this situation perform the following steps:
  • Create a copy of your .edmx file, set a valid Oracle connection for it, and run the Generate Database from Model wizard. As a result you will obtain a new valid EF Oracle model
  • Change the Metadata Artifact Processing property to CopyToOutputDirectory for both models
  • Change the connection strings accordingly (pointing to the same .csdl. .msl. and different .ssdl resources)

Basically, that’s all.

As for the users’ feedback, Paul Reynolds, one of our users, has created series of posts based on his experience in a similar task:

Preparing for Multiple Databases

SSDL Adjustments

Database Agnostic LINQ to Entities

If you are interested in Model First, take a look at this post by Vagif Abilov. Don’t miss our comment.

2. DDL is generated with wrong keywords.

Issue:

I have tried to generate Oracle-specific DDL from a conceptual model (initially generated from SQL Server), have set the DDL Generation template to “Devart SSDLToOracle.tt” but the script contains some not supported by Oracle types (like “varchar(max)” or “datetime”). Is it a bug?

Solution:

Please comment out the context connection string in the application configuration file (App.Config/Web.Config) and run the Generate Database from Model wizard again. Provide a correct connection on the first step of the wizard, and the DDL script will be generated successfully.

3. What is the difference between .edml and .edmx models?

Issue:

Are there any significant differences between Devart Entity models and Microsoft Entity models? In particular, I failed to add an Oracle stored procedure having reference cursor as an output parameter in .edmx model.

Solution:

The structure of Microsoft and Devart entity models is almost identical. There are some additional mapping elements that enable additional functionality like TPC and TPT inheritance, File per Class code generation, View Pregeneration and stored procedures handling, for example.

As for Oracle cursors, read our blog-article – Working with Oracle cursors and stored procedures in Entity Framework.

There is no possibility to handle this situation in design time using Entity Data Model designer.

4. Why setting the StoreGeneratedPattern attribute to “Identity” or “Computed” in Visual Studio 2010 does not work as expected?

Issue:

I have a field with a default value specified in database. I have set the StoreGeneratedPattern to “Identity” for the corresponding property in Entity Data Model designer, but it does not have any effect – Oracle gives an error stating that null is inserted into non-nullable field. What is the reason of the problem?

Solution:

Please make sure that you have changed the StoredGeneratedPattern attribute in the Store part of the model. It is a known issue with Visual Studio Entity Data Model Designer, it does not make any change to the StoreGeneratedPattern attribute if it is changed in design time (only the annotation:StoreGeneratedPattern conceptual attribute is modified). The solution is to open the model using any XML Editor and add StoreGeneratedPattern=“Identity” to the necessary property in the SSDL part of the model.

5. How can I avoid loading an entire child collection?

Issue:

I am trying to get a subset of the child entity properties in the query like the following:
var parents = from p in context.Parents select p;
foreach (var parent in parents)
{
  var children = from c in parent.Children
                 select new {
                   c.ID,
                   c.Name
                 };
}

The issue is the fact that the generated SQL I can see in OracleMonitor is too agressive. It loads the complete child entity, and only after that the LINQ to Objects filtering occurs. Is it a bug or do I miss something?

Solution:

When you are calling parent.Children, the EF Lazy Loading mechanism loads the entire collection, and the select new clause is only a LINQ to Objects query, and is executed in memory. As a workaround you can use, for example, the following syntax:

var parents = from p in context.Parents select p;
foreach (var parent in parents)
{
    var children = from c in context.Children
               where c.ParentID == parent.ParentID
               select new {
                 c.Id,
                 c.Name
               };
}

This statement will produce the query you expect.

 


We hope this material will be useful for you. Please provide your feedbacks and suggestions in comments to article.

One Response to “Entity Framework: Tips and Tricks, part 4”

  1. Saman Pirooz Says:

    Tanx for the identity bit in SSDL
    Really helped alot