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) 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?
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 with the .edml file:
-
Go to the
%Program Files%\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\Templates\Includes\EF.Utility.CS.ttinclude
-
Change
if (extension.Equals(".edmx", StringComparison.InvariantCultureIgnoreCase))
to
if(extension.Equals(".edmx", StringComparison.InvariantCultureIgnoreCase)
||
extension.Equals(".edml", StringComparison.InvariantCultureIgnoreCase))
-
Save the file
In case you are using VB.NET, you should perform these operations with the
%Program Files%\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\Templates\Includes\EF.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 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:
- 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)
- Change the type of the PL/SQL BOOLEAN parameters to bool
- 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 function
Here 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.