Generated (Ugly) SQL Problem

Generated (Ugly) SQL Problem

Postby skingaby » Tue 23 Feb 2010 16:15

Attachment: posting.txt

We had to pull the plug on a production deployment yesterday because of an anomaly discovered in the 11th hour in our Staging (pre-production) environment.
An Entity Query that works perfectly in our Dev and Test environments, was not returning all the expected records in the Staging environment.

We are using IdeaBlade DevForce for Silverlight (v5.2.4), Devart dotConnect for Oracle (v5.35.62), and the MS Entity Framework (.Net 3.5).

I have an entity query like so:

Code: Select all
public IEntityQuery ConfirmationsFilteredQuery(object[] parameters)
{
   var filter = (BasicFilter)parameters[0];
   var confirmationsQuery = this.ConfirmationsBaseQuery(null);

   return confirmationsQuery
      .Where(c => c.DealSnapShot.FlowDateEnd >= filter.FlowDateStart)
      .Where(c => c.DealSnapShot.FlowDateStart <= filter.FlowDateEnd)
      .Where(c => c.ConfStatusId == (long)filter.SelectedStatus)
      .With(QueryStrategy.DataSourceOnly);
}

private IEntityQuery ConfirmationsBaseQuery(object[] parameters)
{
   var em = (ConfirmationEntityManager)this.PersistenceContext.EntityManager;
   return em.DealConfirmations
      .Include(DealConfirmation.EntityPropertyNames.DealConfContacts)
      .Include(DealConfirmation.EntityPropertyNames.DealConfContacts + "." + DealConfContact.EntityPropertyNames.Contact)
      .Include(DealConfirmation.EntityPropertyNames.DealSnapShot + "." + DealSnapShot.EntityPropertyNames.DealPricingSnapShots)
      .Include(DealConfirmation.EntityPropertyNames.DealSnapShot + "." + DealSnapShot.EntityPropertyNames.DealTrxPricingSnapShots)
      .Include(DealConfirmation.EntityPropertyNames.DealSnapShot + "." + DealSnapShot.EntityPropertyNames.DealTrxSnapShots);
}


When I execute this query, the generated SQL looks like (hold your nose) this:

Code: Select all
SELECT "UnionAll3".C2 AS C1,
   "UnionAll3".C3 AS C2,
   ...See attached file...


The problem is that this query only returns some of the records that should be returned. And only in one of our database environments. We have done the obvious things and all of the tables are the same. There are some differences in indexes and constraints between the database where the query works and where it doesn't.
Also, if we comment out the one of the date range where clause, i.e.:
//.Where(c => c.DealSnapShot.FlowDateStart <= filter.FlowDateEnd)
The resulting query does return all the expected records in all database environments. Here is what the working query looks like:

Code: Select all
SELECT "UnionAll3".C2 AS C1,
   "UnionAll3".C3 AS C2,
   ...See attached file...


A diff. on these two SQL files shows some obvious differences re: the where clause, and this one weird difference:

Code: Select all
"UnionAll3".C71 AS C70,
"UnionAll3".C1 AS C71,


vs.

Code: Select all
"UnionAll3".C1 AS C70,
"UnionAll3".C71 AS C71,


So, my questions are as follows:
1) Which tool actually creates this SQL statement?
2) Is it possible that differences in Indexes on the tables could alter the way the SQL Generator generates the SQL?
3) Is this fixed in a newer release?
4) How can I fix/debug this?

Thanks,
Simon
skingaby
 
Posts: 32
Joined: Thu 14 May 2009 16:17

Postby AndreyR » Wed 24 Feb 2010 17:14

1. This query is generated by Entity Framework runtime using the code generated from the model. So, if you have not changed your model, there should be the same code generated and the same SQL sent to the database.
2. No.
3. We are unable to reproduce this issue, so I can't reply to this question.
4. Try to play around the LINQ query - change the expression tree, this may change query generation in the proper way.
One more advice - try EF v4, if possible. There are some changes in code generation that may help.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle