Devart Blog

Entity Framework Tips and Tricks, Part 1

Posted by on July 9th, 2010

This article will open a 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.

Our first post consists of the following users’ questions concerning Entity Framework:

1. How to provide the current datetime for record filtering?

Issue:

I’m trying to filter results in the LINQ to Entities query and show all orders which are older than today. I have built two queries and each of them returns different record sets. For example,

This query sometimes returns incorrect results:

var query = from c in db.Orders where c.OrderDate <= DateTime.Now select c;

The corresponding SQL-statement is the following:

SELECT * FROM Orders WHERE OrderDate <= CURRENT_DATE

This query always works fine:

DateTime date = DateTime.Now;
var query = from c in db.Orders where c.OrderDate <= date select c;

The corresponding SQL-statement is the following:

SELECT * FROM Orders WHERE OrderDate <= :p0

The :p0 parameter is bound with the current datetime value, for example ‘6/10/2010′.

Could you please explain the reasons of this behaviour? Am I doing something in wrong way?

Response:

If you have a difference between server and local machine dates, it is better to use local parameter in your LINQ to Entities queries. It will guarantee that you will use values from your local machine.

Note: This situation isn’t a problem or bug. If DateTime.Now is used as a LINQ to Entities query parameter it will be mapped to the function that returns value of current date and time in server format. When you pass a local variable as a parameter of a LINQ to Entities query, the current date and time will be transformed to the local machine format.

2. How can I use XML-types from Oracle tables in the Entity Framework model?

Issue:

I’m using Entity Framework and trying to access an Oracle table which contains a field of XMLTYPE data type. When reading data from the table I get the following exception: “ORA-03115 unsupported network datatype or representation”. Can I use XMLTYPE in Entity Framework with dotConnect for Oracle?

Solution:

You can use XML type directly only in the OCI mode because Direct mode has a set of limitations. Please see the Using Direct mode topic of our documentation for detailed information about the Direct mode limitations.
As a workaround, you can use Oracle CLOBs. To do this, you will need to make the following changes in your .edmx (or .edml) file:

1. SSDL. Here you should add DefiningQuery for your table:

<EntitySet Name="XML_TYPEs" EntityType= "DataSourceModel1.Store.XML_TYPE"
store:Type =  "Views">
  <DefiningQuery>
    SELECT
      "Extent1".ID AS ID,
      "Extent1".XMLFIELD.GetClobVal() AS XMLFIELD
      FROM SCOTT.XML_TYPE "Extent1"
  </DefiningQuery>
</EntitySet>

Add definition of the properties of your table as well:

<EntityType Name="XML_TYPE">
   <Key>
       <PropertyRef Name="ID" />
   </Key>
   <Property Name="ID" Type="decimal" Nullable="false" />
   <Property Name="XMLFIELD" Type="XMLTYPE" />
</EntityType>

2. CSDL part. Here it’s enough to declare the class and its properties:

<EntitySet Name="XmlTypes" EntityType="DataSourceModel1.XmlType" />
    <EntityType Name="XmlType">
       <Key>
         <PropertyRef Name=&Quot;Id" />
       </Key>
       <Property Name="Id" Type="Decimal" Nullable="false" />
       <Property Name="Xmlfield" Type="String" />
    </EntityType>
</EntitySet>

3. MSL part. It’s enough to just specify the mapping:

<EntitySetMapping Name="XmlTypes">
   <EntityTypeMapping TypeName="DataSourceModel1.XmlType">
      <MappingFragment StoreEntitySet="XML_TYPEs">
        <ScalarProperty Name="Id" ColumnName="ID" />
        <ScalarProperty Name="Xmlfield" ColumnName="XMLFIELD" />
      </MappingFragment>
   </EntityTypeMapping>
</EntitySetMapping>

These operations let you read the XMLTYPE columns to your code. To save XMLTYPE objects to database you’ll have to write a set of stored procedures (for inserting, updating, and deleting) and map these procedures as an Insert/Update/Delete actions in the Configure Behaviour form.

3. Is ORA_ROWSCN type useful for concurrency handling in Entity Framework?

Issue:

Can I use Oracle ORA_ROWSCN column for concurrency control purposes in Entity Framework?

Solution:

In fact, ORA_ROWSCN column is not very helpful in this situation. This column value is refreshed only after the transaction commit, taking place in the end of all SaveChanges(), is performed.

In case the first user performs an insert (ORA_ROWSCN will be null after this operation), then refreshes the object from database (ObjectContext.Refresh()) and performs an update, there will be no problem. But in case the first user wants to save changes, then to update the values inserted by himself (not refreshing them explicitly), there will arise a possibly unnecessary concurrency error. The same problem will arise in case when this user wants to perform two consecutive updates separated by a SaveChanges call. Please note that after the first update the old ORA_ROWSCN value will be read.

In case, when the second user performs an update of the record that was just inserted by the first user, everything will go smoothly. So, the only possible way to refresh the value for this column is to refresh the entire object (fetch it from the database), what seems to be an overkill.

4. What is “outer apply” statement and why isn’t it supported in the Entity Framework statements?

Issue:

I am using FirstOrDefault with a condition for the first time.
I have tried a few permutations. Always with the same result – failure.

I have the following tables:

  • Jobs (list of Jobs)
  • Names (list of names able to do the jobs)
  • JobNames (list of name(s) that are associated with a specific job

All Jobs have at least one name associated with it, so for every row in Jobs there is at least one row in JobNames.

For every Job there is one Main name (person responsible). Jobnames has the columns:

  • Surname
  • Forename
  • IsResponsible

For every job there is one row in Jobnames that has the flag IsResponsible set to true.

There is one row in jobs and one row in JobNames and the flag IsResponsible is set to true.

Devart for PostgreSQL was used to build an entity model for queries.

I want a list of Jobs to include the surname of the person responsible for the job.

The code in a query:

Surname = Jobs.JobNames.FirstOrDefault().Names.Surname;

It works but I cannot guarantee that the Surname returned is that of the person responsible. Looking at examples on the web for the syntax I presumed:

Surname = Jobs.JobNames.FirstOrDefault(n => n.IsResponsible == true).Names.Surname;

Sometimes it fails with the error:

base {System.Data.EntityException} 
  = {"An error occurred while preparing the command definition. 
     See the inner exception for details."}

InnerException = {“OUTER APPLY is not supported by PostgreSQL”}

Does this mean that the issue is with Postgres and I can’t do it?
Is the problem with the Devart interface and if so will there be a fix?
Is there a workround to the issue?

Solution:

The OUTER APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. It is supported only in Microsot SQL Server 2005 and higher but the Entity Framework query in C# code is transformed to Expression Tree by Microsoft code, and only after this transformation we generate provider-specific SQL query. Sometimes OUTER APPLY statement is included to your SQL query even when you use other database servers. It is the reason of the error described in this issue. We have added NotSupportedException in case of Outer Apply occurrence in the expression tree.

This material will be enhanced when the new requests are resolved by our support team. We hope that this article will be useful for you and helps you in the Entity Framework usage.

One Response to “Entity Framework Tips and Tricks, Part 1”

  1. Monducci Marco Says:

    Hi, I’m writting about “3. Is ORA_ROWSCN type useful for concurrency handling in Entity Framework?”.

    I got your answer but now I would like to know if you have any suggestion….

    Thanks in advance,
    Monducci Marco