OQL query with implicit DISTINCT

6 posts, 0 answers
  1. Peter Szintai
    Peter Szintai avatar
    28 posts
    Member since:
    Jul 2009

    Posted 07 Mar 2013 Link to this post

    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
  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 08 Mar 2013 Link to this post

    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
    OpenAccess ORM Q1 2013 is out featuring Multi-Diagrams, Persistent Data Stream Support and much more. Check out all of the latest highlights.
  3. DevCraft banner
  4. Peter Szintai
    Peter Szintai avatar
    28 posts
    Member since:
    Jul 2009

    Posted 13 Mar 2013 Link to this post

    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
  5. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 18 Mar 2013 Link to this post

    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
    OpenAccess ORM Q1 2013 is out featuring Multi-Diagrams, Persistent Data Stream Support and much more. Check out all of the latest highlights.
  6. Peter Szintai
    Peter Szintai avatar
    28 posts
    Member since:
    Jul 2009

    Posted 19 Mar 2013 Link to this post

    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
  7. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 22 Mar 2013 Link to this post

    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
    Free Webinar: OpenAccess Integration in Sitefinity. SIGN UP NOW.
Back to Top
DevCraft banner