Ok, I am stuck... maybe I've just been looking at it too long. Below is my code and the (what I think to be) the comparable SQL query. I grabbed the values when running the code and plugged them into the SQL query. The query returns one result, which is what I expect. Whent the code executes, attVals contains 300 entities after call GetMulti(). Can anyone point me in the right direction?
Here's my code:
PredicateExpression filter = new PredicateExpression();
ConfigAttributeValueCollection attVals = new ConfigAttributeValueCollection();
List<PredicateExpression> subQueries = new List<PredicateExpression>();
mCurrentTemplate.SetCollectionParametersPages(0, new SortExpression(DrawingLayoutPageFields.PrintOrder | SortOperator.Ascending));
StringBuilder sb = new StringBuilder();
foreach (ConfigAttributeEntity attribute in mCurrentTemplate.Pages[0].Sections[0].Attributes)
{
PredicateExpression p = new PredicateExpression();
if (attribute.IsConfigAttribute)
{
p.Add(ConfigAttributeValueFields.Value == aLine.getConfigAttributeValue(attribute.Name));
sb.Append(attribute.Name + " = " + aLine.getConfigAttributeValue(attribute.Name) + " or " + "*\n");
}
else
{
p.Add(ConfigAttributeValueFields.Value == aLine.getAttributeValue(attribute.Name));
sb.Append(attribute.Name + " = " + aLine.getAttributeValue(attribute.Name) + " or " + "*\n");
}
p.AddWithOr(ConfigAttributeValueFields.Value == "*");
p.AddWithAnd(ConfigAttributeValueFields.ConfigAttributeUid == attribute.Uid);
subQueries.Add(p);
}
for (int subQueryIndex = subQueries.Count - 2; subQueryIndex > -1; subQueryIndex-- )
{
subQueries[subQueryIndex].AddWithAnd(new FieldCompareSetPredicate(ConfigAttributeValueFields.ConfigAttributeValueGroupUid, ConfigAttributeValueFields.ConfigAttributeValueGroupUid, SetOperator.In, subQueries[subQueryIndex + 1]));
}
attVals.GetMulti(subQueries[0]);
sb.Append("Returned: " + attVals.Count + " ConfigAttributeValue Entities");
Here's My SQL:
DECLARE @PRODUCT UNIQUEIDENTIFIER
DECLARE @FIXTTYPE UNIQUEIDENTIFIER
DECLARE @MOUNTING UNIQUEIDENTIFIER
DECLARE @GROUPDESCRIPTION UNIQUEIDENTIFIER
DECLARE @SIZE UNIQUEIDENTIFIER
DECLARE @LAMPTYPE UNIQUEIDENTIFIER
DECLARE @GROUPUID UNIQUEIDENTIFIER
SELECT @PRODUCT = UID FROM CONFIGATTRIBUTE WHERE ISVCATTRIBUTE = 1 AND NAME = 'CLN4_PRDTYPE_R'
SELECT @FIXTTYPE = UID FROM CONFIGATTRIBUTE WHERE ISVCATTRIBUTE = 1 AND NAME = 'CLN4_VSR_R'
SELECT @MOUNTING = UID FROM CONFIGATTRIBUTE WHERE ISVCATTRIBUTE = 0 AND NAME = 'Mounting'
SELECT @GROUPDESCRIPTION = UID FROM CONFIGATTRIBUTE WHERE ISVCATTRIBUTE = 0 AND NAME = 'Group Name'
SELECT @SIZE = UID FROM CONFIGATTRIBUTE WHERE ISVCATTRIBUTE = 0 AND NAME = 'Size - In/Outdoor'
SELECT @LAMPTYPE = UID FROM CONFIGATTRIBUTE WHERE ISVCATTRIBUTE = 0 AND NAME = 'Lamp Type'
SELECT CONFIGATTRIBUTEVALUEGROUPUID FROM CONFIGATTRIBUTEVALUE WHERE CONFIGATTRIBUTEUID = @PRODUCT AND ([VALUE] = 'R' OR [VALUE] = '*') AND CONFIGATTRIBUTEVALUEGROUPUID IN
(SELECT CONFIGATTRIBUTEVALUEGROUPUID FROM CONFIGATTRIBUTEVALUE WHERE CONFIGATTRIBUTEUID = @FIXTTYPE AND ([VALUE] = 'N' OR [VALUE] = '*') AND CONFIGATTRIBUTEVALUEGROUPUID IN
(SELECT CONFIGATTRIBUTEVALUEGROUPUID FROM CONFIGATTRIBUTEVALUE WHERE CONFIGATTRIBUTEUID = @MOUNTING AND ([VALUE] = 'YR' OR [VALUE] = '*') AND CONFIGATTRIBUTEVALUEGROUPUID IN
(SELECT CONFIGATTRIBUTEVALUEGROUPUID FROM CONFIGATTRIBUTEVALUE WHERE CONFIGATTRIBUTEUID = @GROUPDESCRIPTION AND ([VALUE] = 'Wall Mounted' OR [VALUE] = '*') AND CONFIGATTRIBUTEVALUEGROUPUID IN
(SELECT CONFIGATTRIBUTEVALUEGROUPUID FROM CONFIGATTRIBUTEVALUE WHERE CONFIGATTRIBUTEUID = @SIZE AND ([VALUE] = 'SI' OR [VALUE] = '*') AND CONFIGATTRIBUTEVALUEGROUPUID IN
(SELECT CONFIGATTRIBUTEVALUEGROUPUID FROM CONFIGATTRIBUTEVALUE WHERE CONFIGATTRIBUTEUID = @LAMPTYPE AND ([VALUE] = 'E' OR [VALUE] = '*'))))))
