Frans,
I've spent some time testing this, and I think there is another issue. Not sure if it's exactly related or not.
I'm trying now to translate something like the following SQL:
SELECT P.GUID
FROM ENTITYW p
LEFT OUTER JOIN PROPERTYW c ON p.GUID = c.ENTITYGUID
GROUP BY P.GUID
HAVING
NOT
(
SUM(CASE WHEN UPPER(c.Name) IN ('X', 'Y', 'HORIZONTAL_COORDINATE_SYSTEM') THEN 1 ELSE 0 END) = 3
OR
SUM(CASE WHEN UPPER(c.Name) IN ('LATITUDE', 'LONGITUDE', 'HORIZONTAL_COORDINATE_SYSTEM') THEN 1 ELSE 0 END) = 3
)
I've created a QueryFactory that is created as follows:
var havingPredicate = new PredicateExpression();
foreach (var group in dependentPropertyGroups)
{
var normalizedGroup = group.Select(s => s.ToUpperInvariant()).ToList();
havingPredicate.AddWithOr(Functions.IIF(PropertyWorkspaceFields.Name.ToUpper().In(normalizedGroup), 1, 0).SumDistinct().Equal(normalizedGroup.Count()));
}
var q = qf.Create()
.From(qf.EntityHeaderWorkspace
.LeftJoin(qf.PropertyWorkspace)
.On(PropertyWorkspaceFields.EntityGuid == EntityHeaderWorkspaceFields.Guid))
.Where(EntityHeaderWorkspaceFields.Guid.In(entityGuids)
.And(EntityHeaderWorkspaceFields.WorkspaceId == workspaceId))
.Select(() => EntityHeaderWorkspaceFields.Guid.ToValue<string>())
.GroupBy(EntityHeaderWorkspaceFields.Guid)
.Having(!havingPredicate);
I added a couple extra constraints to the Where clause, but I don't think that matters.
In any case, what LLBLGen is creating looks like this:
SELECT TechlogRepository.EntityW.Guid
FROM (TechlogRepository.EntityW
LEFT JOIN TechlogRepository.PropertyW
ON TechlogRepository.PropertyW.EntityGuid =
TechlogRepository.EntityW.Guid)
GROUP BY TechlogRepository.EntityW.Guid
HAVING (NOT ( SUM (
DISTINCT CASE
WHEN CASE
WHEN UPPER (
TechlogRepository.PropertyW.Name) IN ( :p11,
:p13,
:p15)
THEN
1
ELSE
0
END = 1
THEN
:p17
ELSE
:p19
END) = :p9
OR SUM (
DISTINCT CASE
WHEN CASE
WHEN UPPER (
TechlogRepository.PropertyW.Name) IN ( :p23,
:p25,
:p27)
THEN
1
ELSE
0
END = 1
THEN
:p29
ELSE
:p31
END) = :p21))
It's adding an extra WHEN clause that is changing the essence of the query. Is there something I'm doing wrong?
EDIT: I noticed that I had an inaccurate SumDistinct(), so I changed to just Sum(). The essence of the query is retained, but there's still an extra CASE WHEN for each clause added to the predicate.
Thanks,
Sergio
)
Glad it's solved