Generated (Ugly) SQL Problem

.Include(DealConfirmation.EntityPropertyNames.DealConfContacts) .Include(DealConfirmation.EntityPropertyNames.DealConfContacts + "." + DealConfContact.EntityPropertyNames.Contact)

   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:

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

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:

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:

"UnionAll3".C71 AS C70,

"UnionAll3".C1 AS C71,

vs.

"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

Edited by skingaby - 23-Feb-2010 at 8:16am