Entity Framework Tips and Tricks, Part 1

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:

• How to provide the current datetime for record filtering?
• How can I use XML-types from Oracle tables in the Entity Framework model?
• Is ORA_ROWSCN type useful for concurrency handling in Entity Framework?
• What is “outer apply” statement and why isn’t it supported in the Entity Framework statements?

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

Issue:

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:

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

Issue:

Solution:

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:

Solution:

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 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:

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.