SQL Generation Issue

[Justin]

I'm using the 2015.1.225.1 version of data access and am passing a filter expression to validate if a group membership exists by examining the group ID which is a guid.  When I pass only one group, this works beautifully, but when I add an or expression, the generated SQL is missing a reference and returning invalid results:

Passing in:

"1 == 1 
&& (DateCreated >= DateTime.Parse(\"5/28/2016 12:00:00 AM\")) 
&& !Labels.Any(Name.Equals(\"Copied\")) 
&& (EventSubject.GroupAssignment.Any(GroupId.Equals(\"f6ee3591-1bfd-4941-8a17-da0c5dad3e23\")) 
    || EventSubject.GroupAssignment.Any(GroupId.Equals(\"881fc249-812d-43a2-bc26-b67ed9134324\")))"

 

Results In the or clause item not identifying the relation properly:

WHERE [ResourceId] = f.[ResourceId] 

should be WHERE d.[ResourceId] = f.[ResourceId] - that change would fix this completely, but I guess there must be something I'm doing that is causing a problem at a certain nesting level or something.  

Full Generated Query:

SELECT a.[EventId] AS COL1, 
       a.[Author] AS COL2, 
  a.[DateCreated] AS COL3, 
  a.[EventDate] AS COL4, 
  a.[EventImpact] AS COL5, 
  a.[EventImpact] AS COL6, 
  a.[EventScope] AS COL7, 
  a.[EventScope] AS COL8, 
  a.[EventType] AS COL9, 
  a.[EventType] AS COL10, 
  a.[EventVersion] AS COL11, 
  a.[LastModified] AS COL12, 
  a.[LastModifiedBy] AS COL13, 
  a.[Published] AS COL14, 
  a.[Author] AS COL15, 
  a.[LastModifiedBy] AS COL16, 
  a.[SubjectId] AS COL17, 
  a.[SubjectId] AS COL18, 
  a.[Summary] AS COL19 
FROM [EventTracker].[Events] a 
WHERE @p0 = 1 
AND a.[DateCreated] >= @p1 
AND not (EXISTS (SELECT 1 FROM [EventTracker].[Labels] b JOIN [EventTracker].[EventLabels] AS c ON (b.[Id] = c.[LabelId]) WHERE b.[Name] = 'Copied' AND c.[EventId] = a.[EventId])) 
AND EXISTS (SELECT 1234567 
           FROM [EventTracker].[Resource] d 
WHERE a.[SubjectId] = d.[ResourceId] AND ((EXISTS (SELECT 1 
                                                              FROM [EventTracker].[ResourcesInGroups] e 
  WHERE d.[ResourceId] = e.[ResourceId] 
AND e.[GroupId] = 'f6ee3591-1bfd-4941-8a17-da0c5dad3e23') 
  OR EXISTS (SELECT 1
             FROM [EventTracker].[ResourcesInGroups] f W
 WHERE [ResourceId] = f.[ResourceId] 
AND f.[GroupId] = '881fc249-812d-43a2-bc26-b67ed9134324')))) ORDER BY COL1 

 

I'm wondering if there is anything I can do to address this issues.  I've tried setting the filter expression to do a select().Count > 0 but that appears to result in the same generated query.  Any help pointing me in the right direction would be greatly appreciated!

[Ralph Waldenmaier]

Hello Justin,
Thank you for contacting us.
The generated SQL is not what I would have expected either. Unfortunately, we have to examine that further.
Though you can try another approach to fulfill your requirement. See the following snippet:

List<string> myGuidList = new List<string>(){"f6ee3591-1bfd-4941-8a17-da0c5dad3e23", "881fc249-812d-43a2-bc26-b67ed9134324"};

....
1 == 1
&& (DateCreated >= DateTime.Parse(\"5/28/2016 12:00:00 AM\"))
&& !Labels.Any(Name.Equals(\"Copied\"))
&& EventSubject.GroupAssignment.Where(x => myGuidList.Contains(x.GroupId))

Here I am generating a list, containing all the data matching the search criteria and use that within the where method comparing the values using the Contains method. That should allow you to push several values to your database.

I hope that this helps to get you back on track.
Do come back in case you have any other question.

Regards,
Ralph Waldenmaier
Telerik

 

[Justin]

Thank you Ralph - this is very similar to how I ended up solving the problem.  My client application is only able to send filterexpressions as strings, so instead of trying to build the resources in groups filter as I did before by chaining a filterexpression mask with or statements, I now pass an collection of group ids to the service operation and from there I identify the resources in groups and apply a resourceid filter to the query expression after applying the rest of the filter expression I had previously built.  This results in the expected output.

 

I was a little disappointed that my filter expression builder didn't work for this one case, but the general approach works for all the other filters I apply so it is only a minor disappointment.  I wouldn't be surprised to find that newer versions of the product may handle this better, however, I'm still very much loving the data model GUI so I haven't moved off this older version.  

 

Thanks again!