Problem with generated SQL

4 posts, 0 answers
  1. Levente Mihaly
    Levente Mihaly avatar
    3 posts
    Member since:
    Feb 2010

    Posted 01 Aug 2011 Link to this post

    Hi,

    I'm trying to migrate an existing application to OpenAccess and ran into a problem:
    Here's my linq query:
    var q = model.DocImages.Where(doc =>               
                    (
                        doc.Category.CGroups.Any(cg =>
                        cg.ACL.Contains("R")
                        && model.GroupMembers.Count
                            (gm => gm.UserName == "xyz"
                             && gm.GroupCode == cg.GroupCode) > 0)
                    )
                    ); 
    And here's the generated SQL:
    SELECT  TOP(??T) a.[DocID] AS COL1 FROM [dbo].[Doc] a
    WHERE EXISTS
        (SELECT 1234567 FROM [dbo].[Category] b
            WHERE a.[CategoryID] = b.[CategoryID]
            AND ((EXISTS
                    (SELECT 1234567 FROM [dbo].[CGroup] c
                        WHERE b.[CategoryID] = c.[DimCategoryID]
                        AND c.[ACL] LIKE '%R%' ESCAPE '\'
                        AND  (SELECT COUNT(1) FROM [dbo].[GroupMember] d
                            WHERE d.[UserName] = 'xyz'
                            AND d.[GroupCode] = d.[GroupCode])  > 0)           
                    ))
        )

    Note the last row: d.[GroupCode] = d.[GroupCode]
    It should be d.[GroupCode] = c.[GroupCode]

    On a side note the GroupCode (nvarchar) field is in fact a foreign key from table GroupMember to table CGroup, but OpenAccess didn't generate any relationships there, that's why the reach out to  model.GroupMembers is there.

    Thanks,
    Levente


  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 02 Aug 2011 Link to this post

    Hello Levente Mihaly,

    thanks for reporting this issue; we are currently working on our LINQ support. We will post you when support for such queries has been improved.

    Best wishes,
    Thomas
    the Telerik team

    Explore the entire Telerik portfolio by downloading the Ultimate Collection trial package. Get now >>

  3. DevCraft banner
  4. Levente Mihaly
    Levente Mihaly avatar
    3 posts
    Member since:
    Feb 2010

    Posted 03 Aug 2011 Link to this post

    Hi Thomas,

    Thanks for the quick response.
    Do you have any suggestions for a workaround in the meantime? I'd like to have a query like this as a basis, and dynamically extend it.
    E.g. var query = GetMemberQuery("xyz"); query.Where(d => d.FileName = "abc"); ..

    Thanks again,
    Levente
  5. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 04 Aug 2011 Link to this post

    Hi Levente Mihaly,

     the composability of the queries is not an issue, but the relativly complex translation of the Any() condition is problematic at the moment.

    Best wishes,
    Thomas
    the Telerik team

    Explore the entire Telerik portfolio by downloading the Ultimate Collection trial package. Get now >>

Back to Top