Generated (Ugly) SQL Problem
Posted: 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:
When I execute this query, the generated SQL looks like (hold your nose) this:
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:
A diff. on these two SQL files shows some obvious differences re: the where clause, and this one weird difference:
vs.
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
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 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);
}
Code: Select all
SELECT "UnionAll3".C2 AS C1,
"UnionAll3".C3 AS C2,
...See attached file...
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...
Code: Select all
"UnionAll3".C71 AS C70,
"UnionAll3".C1 AS C71,
Code: Select all
"UnionAll3".C1 AS C70,
"UnionAll3".C71 AS C71,
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