Problem with generated SQL

[Levente Mihaly]

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

[Thomas]

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

[Levente Mihaly]

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

[Thomas]

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