DevForce 2009: Generated (Ugly) SQL Problem

Print Page | Close Window

Generated (Ugly) SQL Problem

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2009
Forum Discription: For .NET 3.5
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=1657
Printed Date: 11-Apr-2025 at 4:15am

Topic: Generated (Ugly) SQL Problem

Posted By: skingaby
Subject: Generated (Ugly) SQL Problem
Date Posted: 23-Feb-2010 at 8:08am

Attachment: uploads/308/posting.txt - 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

Replies:

Posted By: ting
Date Posted: 08-Mar-2010 at 5:04pm

Hi Simon,

1) The Entity Framework dynamically generates the SQL based on the LINQ expression tree that is passed to it from DevForce. Depending on the order and structure of the expression tree, the generated SQL may differ slightly.

2) It is possible, but I believe unlikely that the Entity Framework would generate different SQL depending on the indexing.

3) We have not changed anything that would affect SQL generation in DevForce 5.2.5 or 5.2.6.

4) As for debugging this, can you check to see that the full query (the one that includes the date range) generates the same SQL in both environments? If the SQL is the same, you'll want to check your database for bad data (perhaps in that date column?)

Let us know what you find. Thanks!

Posted By: skingaby
Date Posted: 23-Mar-2010 at 6:03am

The original LINQ query was doing a date range filter FromDate and ToDate.
We ended up working around the problem by changing that to pull all entities .Where(c => c.DealSnapShot.FlowDateEnd >= filter.FlowDateStart) [i.e., only one of the range boundaries), and then looping through the results and adding the ones where item.FlowDateStart <= filter.FlowDateEnd to a collection for binding.
So, we didn't fix the problem at all, we simply sidestepped it.

Posted By: midnit
Date Posted: 24-Mar-2010 at 7:04am

I work with skingaby and we have come across other strange "results". We have a table that has no relationships, no indexes, no conditions that should return the full set of results but the table consists of 232 records and once pulled through to Silverlight we get 187. The log does not report the query created (is there a way to make it?) it only says:

source="IdeaBlade.EntityModel.Server.EntityServer:Fetch">Fetch ... value(IdeaBlade.EntityModel.EntityQueryProxy`1[Png.GcsAg.Model.Deal.GmsCustomerComboitem])

Going through Entity Developer and retrieve the data I get all the records.

And in regard to the above issue, it doesn't matter which date condition you leave off, its only when you combine the two that the issue occurs.

We are quickly getting to the point that we cannot trust the application because without meticulous comparison of what IdeaBlade is returning us against whats actually in the database we have no idea if what we are seeing is right.

Posted By: midnit
Date Posted: 24-Mar-2010 at 7:48am

It turned out that the logTraceString was on the wrong datasource so I have now seen the query for the single table and it is as expected. When you run this query on the database you get 232 records, but again we only get 187 when ran through IdeaBlade. We need some direction here.

Posted By: ting
Date Posted: 24-Mar-2010 at 2:05pm

We'll need to get a copy of the database and the query to take a look. I will have someone contact you directly about this.

Posted By: WardBell
Date Posted: 24-Mar-2010 at 5:05pm

Hi Simon - Sorry this is happening. Want to bail you out quickly. We need the EDMX ... probably even more than the database. Need to understand the schema and mappings involved in the query. This is probably more important than the sample database (which we'd like, sure).

Meanwhile, have you tried removing the INCLUDES and turning query inversion OFF? These steps should greatly simplify the SQL generated by EF (no ... we don't generate the SQL). They've been known to "cure" EntityFramework SQL generation errors (whatever their cause). We'll worry about getting the related entities once we resolve the misbehavior.

It is strange that the query works in Dev and Test but not in Stage. I'm sure you're looking into the differences.

Have you tried writing a Console app test of the query ... a way of taking Silverlight out of the equation (not that I think it is to blame).

We will get you through this!

Aside: I understand you use Oracle. I was going to suggest that you use SQL Profiler to look at the generated SQL; it's often as helpful or more helpful than our log. I don't know what the equivalent Oracle tool is.

Aside: although it won't make a difference to your current problem, for the future, I think you will find that you don't need both of the following Includes because the second subsumes the first:

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

In a multi-leg Include, intermediate legs are included automatically:

Posted By: skingaby
Date Posted: 25-Mar-2010 at 6:12am

I am sending you the EDMX via. Private Message.

Note that we have two situations now. We worked around the first, and are now presented with a second. kimj suggested I look at the Key settings in the Model as that might be causing the problem in the new one. I will explore that.

The problems are:
1) A complex query with many includes and several conditions, works fine in Dev and QA but fails in Staging and Production when a Date Range filter (i.e. theDate >= StartDate and theDate <= EndDate is used.
2) An Oracle View is used as an object source in the model and is not returning the correct number of rows.

Posted By: skingaby
Date Posted: 25-Mar-2010 at 6:54am

Whew. We are back to 1 problem. The first one.

The View problem turned out to be the key, as kimj had suggested. We adjusted the Key and the view is returning the correct rows.

To anyone else reading this: When you use a View as a source for an Entity, you MUST ensure that the EF model has the correct Key settings.

Print Page | Close Window