Entity Framework Tips and Tricks, Part 2

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:

• How can I execute native SQL using Entity Framework?
• How can I use Self-Tracking Entities (STE) or POCO in the Devart Entity Model?
• How can I use Oracle stored procedures with a boolean parameter in Entity Framework?
• How to work with UNION in Entity Framework

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

Issue:

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

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

Solution:

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:

Solution:

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:

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

Solution:

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.