OQL query with implicit DISTINCT

[Peter Szintai]

Hi All!

ORM: 2012.1.214.1
Backend: MS Sql Server 2008 R2 Express
.NET 3.5, Win7 64 Ultimate

Today I tried to run an OQL query (scope.GetOqlQuery(stringSelect).Execute(parameters) form), the result was only 1 row instead of the requeired 2 rows (I checked the same query in MS Sql Server Manag. Studio).

When I checked the BackendQuery property of the GetOqlQuery() method returned IQuery, I found, that the generated SELECT contains an implicit DISTINCT, however the original OQL Select had not, and of course, this caused the loss of the resulted rows.

Can I some how disable this feature, or control, when I want to implicitly include the DISTINCT in the generated select?

Regards,
Peter Szintai

[Thomas]

Hi Peter,

in general there is no outside control over the distinct, but through the query itself. Therefore we would like to see the generated query and the source OQL expression, so that we can check/improve the current situation.

All the best,
Thomas
the Telerik team

[Peter Szintai]

Hi Thomas,

here are the sources, I hope, you can help me!

OQL source:

SELECT
Person.PersId,
Employee.EmplId,
Person.Name,
Person.PersCode,
Employee.HireDate,
Employee.TermDate,
Employee.LastWorkedDate,
EmplHist.DivSelId,
Employee.CalcOrder

FROM
AbPersonExtent as Person,
Person.AbPersComps as PersComp,
Person.AbEmpls as Employee,
Employee.AbEmplHists as EmplHist,
AbDivSelExtent as EmplHistDivSel

WHERE
Person.Deleted = '0' AND
Person.BirthDate >= $1 AND
Person.BirthDate <= $2 AND
PersComp.CompId = $3 AND
Exists PersType IN Person.AbPersTypes : ( (PersType.PersType = 'PersType|Empl') AND
PersType.Deleted = '0' ) AND
Employee.Deleted = '0' AND
EmplHist.Deleted = '0' AND
Employee.HireDate >= $4 AND
Employee.HireDate <= $5 AND
Employee.TermDate >= $6 AND
Employee.TermDate <= $7 AND
Employee.LastWorkedDate >= $8 AND
Employee.LastWorkedDate <= $9 AND
EmplHist.StartDate <= $10 AND
EmplHist.EndDate >= $10 AND
EmplHistDivSel.Deleted = '0' AND
EmplHist.DivSelId = EmplHistDivSel.DivSelId

Generated query:

SELECT DISTINCT
a.[PersId] AS COL1,
c.[EmplId] AS COL2,
a.[Name] AS COL3,
a.[PersCode] AS COL4,
c.[HireDate] AS COL5,
c.[TermDate] AS COL6,
c.[LastWorkedDate] AS COL7,
d.[DivSelId] AS COL8,
c.[CalcOrder] AS COL9

FROM
[AbPerson] a JOIN [AbPersComp] AS b ON (a.[PersId] = b.[PersId])
JOIN [AbEmpl] AS c ON (a.[PersId] = c.[PersId])
JOIN [AbEmplHist] AS d ON (c.[EmplId] = d.[EmplId])
JOIN [AbPersType] AS e ON (a.[PersId] = e.[PersId])
JOIN [AbDivSel] AS f ON (a.[Deleted] = '0' AND
a.[BirthDate] >= ? AND
a.[BirthDate] <= ? AND
b.[CompId] = ? AND
e.[PersType] = 'PersType|Empl' AND
e.[Deleted] = '0' AND
c.[Deleted] = '0' AND
d.[Deleted] = '0' AND
c.[HireDate] >= ? AND
c.[HireDate] <= ? AND
c.[TermDate] >= ? AND
c.[TermDate] <= ? AND
c.[LastWorkedDate] >= ? AND
c.[LastWorkedDate] <= ? AND
d.[StartDate] <= ? AND
d.[EndDate] >= ? AND
f.[Deleted] = '0' AND
d.[DivSelId] = f.[DivSelId])

Regards,

Peter Szintai

[Thomas]

Hi Peter,

It's my fault that I did not request the respective parts of your domain model as well. I thought I would not need it, but it seems not so easy to reproduce your issue. I tried with a Northwind model here on my side, but was not able to do so.
Would you be so kind and give us the definitions of the needed classes for the query? Also, am I correct to assume that you use the ObjectScope API, not the OpenAccessContext API? Do you have a FetchPlan set?

All the best,
Thomas
the Telerik team

[Peter Szintai]

Hi Thomas!

From this link you can download the zipped source.
http://mammutmail.com/hu/mail/download/13471420e68b6c969d37570635b73a15

We use the ObjectScope API, but we have not FetchPlan set.

Regards,

Peter

[Thomas]

Hello Peter,

thanks for the information. It seems, that the fix for this behavior is non-trivial and needs more brain power. We are working on this, but at this time I cannot give you a fix for this. The root cause is the transformation of exists nodes into the joins, and that seems to be insufficiently implemented for nested exists in exists.

Regards,
Thomas
the Telerik team