This is a migrated thread and some comments may be shown as answers.

Problem with generated SQL

3 Answers 64 Views
LINQ (LINQ specific questions)
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Levente Mihaly
Top achievements
Rank 1
Levente Mihaly asked on 01 Aug 2011, 02:59 PM
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


3 Answers, 1 is accepted

Sort by
0
Thomas
Telerik team
answered on 02 Aug 2011, 02:55 PM
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 >>

0
Levente Mihaly
Top achievements
Rank 1
answered on 03 Aug 2011, 01:34 PM
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
0
Thomas
Telerik team
answered on 04 Aug 2011, 04:07 PM
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 >>

Tags
LINQ (LINQ specific questions)
Asked by
Levente Mihaly
Top achievements
Rank 1
Answers by
Thomas
Telerik team
Levente Mihaly
Top achievements
Rank 1
Share this question
or