Devart Blog

Entity Framework Tips and Tricks, Part 2

Posted by on July 30th, 2010

In this article we continue series of publications about non-trivial situations encountered by our users. Some of these situations are the restrictions of the used platforms, and some simply require a nonstandard approach to solving the problem.

We consider the following questions in this article:


1. How can I execute native SQL using Entity Framework?

Issue:

I don’t seem to be able to use CreateQuery. I have the following code:

using (Entities ent = new Entities())
{
        var results = ent.CreateQuery<PRODUCTS>("SELECT * FROM "Products"");

        foreach (var row in results)
        {
             Console.WriteLine("id: " + row.ID.ToString());
        }
}

This results in the error:

“The query syntax is not valid, near term ‘*’, line 1, column 11.”

What should I use to execute the native SQL?

Solution:

The CreateQuery method takes Entity SQL query, not SQL query.

To execute the SQL query you can use the ObjectContext.ExecuteStoreQuery method. Please note that it is available in the Entity Framework v4 only.

There is one more way to execute native SQL both in Entity Framework v1 and Entity Framework v4. You can use the StoreConnection property of the ObjectContext.Connection object. Here is an Oracle-specific sample:

using (OracleConnection connection 
 = (entities.Connection as EntityConnection).StoreConnection as OracleConnection) 
{
      OracleCommand command 
        = new OracleCommand("SELECT * FROM "Products"", connection);

      connection.Open();
      OracleDataReader reader = command.ExecuteReader();

      //Materialize your query results here
}

2. How can I use Self-Tracking Entities (STE) or POCO in the Devart Entity Model?

Issue:

I am using EF 4.0 on top of your provider dotConnect for Oracle. I want to use Self-Tracking Entities. Can I rename edml file to the edmx file and use STE with this file? Can I perform the same in case of using POCO entities?

Solution:

Devart model has structure similar to the one Microsoft model has. But there are some customizations. There should be no troubles with the extension changing (edml -> edmx).

There is one more approach to use STE or POCO with the .edml file:

  1. Go to the

    %Program Files%Microsoft Visual Studio 10.0Common7IDEExtensionsMicrosoftEntity Framework ToolsTemplatesIncludesEF.Utility.CS.ttinclude

  2. Change
    if (extension.Equals(".edmx", StringComparison.InvariantCultureIgnoreCase))

    to

    if(extension.Equals(".edmx", StringComparison.InvariantCultureIgnoreCase)
    ||
    extension.Equals(".edml", StringComparison.InvariantCultureIgnoreCase))
  3. Save the file

In case you are using VB.NET, you should perform these operations with the

%Program Files%Microsoft Visual Studio 10.0Common7IDEExtensionsMicrosoftEntity Framework ToolsTemplatesIncludesEF.Utility.VB.ttinclude file. Change the following line

If  (extension.Equals(".edmx", StringComparison.InvariantCultureIgnoreCase))

to

If (extension.Equals(".edmx", StringComparison.InvariantCultureIgnoreCase)
Or
extension.Equals(".edml", StringComparison.InvariantCultureIgnoreCase))

We plan to add support for the Self-Tracking Entities and POCO in one of the future builds.

3. How can I use Oracle stored procedures with a boolean parameter in Entity Framework?

Issue:

I am trying to execute a stored procedure with a PL/SQL BOOLEAN parameter in Entity Framework but always get a “ORA-06550: PLS-00306: wrong number or types of arguments in call to ‘EF_BOOLEAN_SP'”.

Is it a bug, and is there any workaround for this situation?

Solution:

PL/SQL BOOLEAN parameters are not supported directly in Entity Framework, but there is a workaround for this case. Here are the steps to solve the problem:
  1. Add the procedure to Devart Entity model (you can perform all actions with ADO.NET Entity Data Model also, but these actions should be performed in XML using XML Editor)
  2. Change the type of the PL/SQL BOOLEAN parameters to bool
  3. Edit the CommandText for these procedure. Write a simple PL/SQL block calling the procedure. Don’t forget to pass the parameters, and convert the necessary parameters from NUMBER to PL/SQL BOOLEAN using the SYS.DIUTIL.INT_TO_BOOL functionHere is a simple XML example:

    The text before changes:

    <Function Name=" EF_BOOLEAN_SP" IsComposable="false" 
    BuiltIn="false" Aggregate="false" NiladicFunction="false" ParameterTypeSemantics="AllowImplicitConversion" 
    Schema="TEST" StoreFunctionName="EF_BOOLEAN_SP">
              <Parameter Name="PK" Type="int64" Mode="In" />
              <Parameter Name="FLAG" Type="PL/SQL BOOLEAN" Mode="In" />
    </Function>

    The text after changes:

    <Function Name=" EF_BOOLEAN_SP" IsComposable="false" 
    BuiltIn="false" Aggregate="false" NiladicFunction="false" ParameterTypeSemantics="AllowImplicitConversion">
          <CommandText>
             BEGIN 
                 EF_BOOLEAN_SP(:PK, SYS.DIUTIL.INT_TO_BOOL(:FLAG));
             END;
          </CommandText>
          <Parameter Name="PK" Type="int64" Mode="In" />
          <Parameter Name="FLAG" Type="bool" Mode="In" />
    </Function>

4. How to work with UNION in Entity Framework?

Issue:

I have created the folowing union of two queries:

string productName  = "Spotlight on Britain's economy";
var query1 = context.Products.Where(t => t.Productname == productName);

productName = "Carroll Lewis. Alice'sventures in Wonderland ";
var query2 = context.Products.Where(t => t.Productname == productName);

var resultQuery = query1.Union(query2);
var ResultSets = resultQuery.ToList();

Because of some reason I get only one result – the book of Lewis Carroll. Is this a designed behaviour or bug?

Solution:

This is a designed behaviour. The reason of it is the deferred execution. The actual materialization of query1 and query2 occurs only in the following line:

var ResultSets = resultQuery.ToList();

Values of the parameters are passed in this moment also (productName points to the book of Lewis Carroll), and that’s why the results of query1 and query2 coincide.
The solution is to use different variables for parameters, or to materialize the query just after the parameter value is assigned.

In the first case, change your code in the following way:

string productName  = "Spotlight on Britain's economy";
var query1 = context.Products.Where(t => t.Productname == productName);

string productName2 = "Carroll Lewis. Alice'sventures in Wonderland ";
var query2 = context.Products.Where(t => t.Productname == productName2);

var resultQuery = query1.Union(query2);
var ResultSets = resultQuery.ToList();

In this case UNION statement is translated as a part of LINQ to Entities query.

In the second case both queries are materialized:

string productName  = "Spotlight on Britain's economy";
var query1 = context.Products.Where(t => t.Productname == productName);
query1.ToList();

productName = "Carroll Lewis. Alice'sventures in Wonderland ";
var query2 = context.Products.Where(t => t.Productname == productName);
query2.ToList();

var resultSet = query1.Union(query2);

In this case both lists are materialized before UNION is applied. That is why there is no need to materialize UNION itself, it is performed as a LINQ to Objects operation.


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

Comments are closed.