I've figured out how to write the query that produces the results I want, but now I can't figure out how to make this work using the FieldCompareSetPredicate. Does anyone now how to convert this sql into a FieldCompareSetPredicate?

In this query the frameStyleCode is a variable that is being passed into the method that creates this query.

DECLARE
@frameStyleCode char (2)

SET @frameStyleCode = 'AM'

SELECT
    P.Model, P.StyleCode, F.*
FROM
    dbo.Fork F
INNER JOIN
    dbo.Product P ON P.ProductID = F.ForkID
WHERE
    P.StyleCode = @frameStyleCode
OR
    @frameStyleCode IN (SELECT AdditionalStyles FROM dbo.Fork WHERE ForkID = F.ForkID)
ORDER BY
    P.Model

Can the query be simplified as follows (assuming that ForkID is the PK of Fork):

DECLARE
@frameStyleCode char (2)

SET @frameStyleCode = 'AM'

SELECT
    P.Model, P.StyleCode, F.*
FROM
    dbo.Fork F
INNER JOIN
    dbo.Product P ON P.ProductID = F.ForkID
WHERE
    P.StyleCode = @frameStyleCode
OR
    F.AdditionalStyles = @frameStyleCode
ORDER BY
    P.Model

since the "(SELECT AdditionalStyles FROM dbo.Fork WHERE ForkID = F.ForkID)" will only return one AdditionalStyles for each row, so no need for the IN clause.

Oh, yeah I see that I left out a piece of information that is important. The AdditionalStyles column contains a comma delimited list of values which is why I need to use the IN clause.

Walaa wrote:

Can the query be simplified as follows (assuming that ForkID is the PK of Fork):

DECLARE
@frameStyleCode char (2)

SET @frameStyleCode = 'AM'

SELECT
    P.Model, P.StyleCode, F.*
FROM
    dbo.Fork F
INNER JOIN
    dbo.Product P ON P.ProductID = F.ForkID
WHERE
    P.StyleCode = @frameStyleCode
OR
    F.AdditionalStyles = @frameStyleCode
ORDER BY
    P.Model

since the "(SELECT AdditionalStyles FROM dbo.Fork WHERE ForkID = F.ForkID)" will only return one AdditionalStyles for each row, so no need for the IN clause.

OK, here is a test method that I created that uses the sub-query.

        public EntityCollection GetForksForMountainTest(FrameVersionEntity frameVersion, CustomerFitEntity customerFit, RearShockEntity rearShock)
        {
            using(WSDataAccessAdapter adapter = new WSDataAccessAdapter(DBUtil.ConnectionString))
            {
                EntityCollection results = new EntityCollection(new ForkEntityFactory());

                IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.ForkEntity);
                IPrefetchPathElement2 forkVersionNode = prefetchPath.Add(ForkEntity.PrefetchPathForkVersion);
                IPrefetchPathElement2 productNode = prefetchPath.Add(ForkEntity.PrefetchPathProduct);
        
                ISortExpression sortVersions = new SortExpression();
                sortVersions.Add(ProductVersionFields.RPPrice | SortOperator.Ascending);
                sortVersions.Add(ProductVersionFields.MfrGrams | SortOperator.Ascending);
                productNode.SubPath.Add(ProductEntity.PrefetchPathProductVersion, 1, null, null, sortVersions);

                productNode.SubPath.Add(ProductEntity.PrefetchPathProductBlurb);
                IPrefetchPathElement2 manufacturerNode = productNode.SubPath.Add(ProductEntity.PrefetchPathManufacturer);
                manufacturerNode.SubPath.Add(ManufacturerEntity.PrefetchPathCompany);
        
                RelationPredicateBucket filter = new RelationPredicateBucket();
                filter.Relations.Add(ForkEntity.Relations.ProductEntityUsingForkID);
                filter.Relations.Add(ProductEntity.Relations.ProductBlurbEntityUsingProductID, JoinHint.Left);
                filter.Relations.Add(ProductEntity.Relations.ManufacturerEntityUsingManufacturerID);
                filter.Relations.Add(ManufacturerEntity.Relations.CompanyEntityUsingManufacturerID);
                filter.Relations.Add(ForkEntity.Relations.ForkVersionEntityUsingForkID);
                filter.Relations.Add(ForkVersionEntity.Relations.ProductVersionEntityUsingProductVersionID);

                string generalStyle = frameVersion.ProductVersion.Product.RidingStyle.StyleGeneral;
                decimal tubeDiameter = frameVersion.FrameSize.HeadTubeDiameter;

                filter.PredicateExpression.Add(ProductFields.ActiveState == ActiveState.Web);
                filter.PredicateExpression.Add(ProductVersionFields.ActiveState == ActiveState.Web);
                filter.PredicateExpression.Add(ProductVersionFields.IsPartSpecific == false);
                filter.PredicateExpression.Add(ForkVersionFields.SteererTubeDiameter == tubeDiameter);

                /* START: Building RidingStyle Travel Filter. */
                IPredicateExpression travelFilter = new PredicateExpression();
                decimal frameTravelMax = 0;
                decimal frameTravelMin = 0;
                string frameStyleCode = frameVersion.ProductVersion.Product.StyleCode.Trim();
                string forkStyleCode = string.Empty;
            
                Hashtable frameStyles = new Hashtable();
                frameStyles.Add("DJ", "DJ");
                frameStyles.Add("XC", "HT,ST,XC");
                frameStyles.Add("AM", "AM");
                frameStyles.Add("FR", "FR");
                frameStyles.Add("DH", "DH");

                // Frame/Fork RidingStyle Rule.
                foreach(DictionaryEntry entry in frameStyles)
                {
                    string[] entryValues = entry.Value.ToString().Split(',');
                    foreach(string style in entryValues)
                        // If the value equals the frame style then use key.
                        if ( frameStyleCode == style )
                            forkStyleCode = entry.Key.ToString();
                }
                travelFilter.Add(ProductFields.StyleCode == forkStyleCode);
                // frameStyleCode in ForkFields.AdditionalStyles
                // This is the sub-query that I'm trying to get to work which I ask about in another post.
                travelFilter.AddWithOr(new FieldCompareSetPredicate(
                    ProductFields.StyleCode, null,
                    ForkFields.AdditionalStyles, null,
                    SetOperator.In,
                    ForkFields.ForkID == ForkFields.ForkID));
            
                // Frame/Fork travel rules.
                if ( rearShock != null )
                {
                    frameTravelMin = rearShock.ForkTravelMin;
                    frameTravelMax = rearShock.ForkTravelMax;
                }
                else
                {
                    frameTravelMin = frameVersion.ProductVersion.Product.Frame.ForkTravelMin;
                    frameTravelMax = frameVersion.ProductVersion.Product.Frame.ForkTravelMax;
                }
                travelFilter.Add(ForkVersionFields.TravelMax >= frameTravelMin);
                travelFilter.Add(ForkVersionFields.TravelMax <= frameTravelMax);
                filter.PredicateExpression.Add(travelFilter);
                /* END: Building RidingStyle Travel Filter. */

                adapter.FetchEntityCollection(results, filter, prefetchPath);           
                return results;
            }
        }

The AdditionalStyles column contains a comma delimited list of values which is why I need to use the IN clause.

I think the IN clause won't search inside the text of the AdditionalStyles for values instead it searches for different values of AdditionalStyles. You should use Like %@frameStyleCode%

so the query would be

DECLARE
@frameStyleCode char (2)

SET @frameStyleCode = 'AM'

SELECT
    P.Model, P.StyleCode, F.*
FROM
    dbo.Fork F
INNER JOIN
    dbo.Product P ON P.ProductID = F.ForkID
WHERE
    P.StyleCode = @frameStyleCode
OR
    F.AdditionalStyles Like %@frameStyleCode%
ORDER BY
    P.Model

Walaa wrote:

The AdditionalStyles column contains a comma delimited list of values which is why I need to use the IN clause.

I think the IN clause won't search inside the text of the AdditionalStyles for values instead it searches for different values of AdditionalStyles. You should use Like %@frameStyleCode%

so the query would be

DECLARE
@frameStyleCode char (2)

SET @frameStyleCode = 'AM'

SELECT
    P.Model, P.StyleCode, F.*
FROM
    dbo.Fork F
INNER JOIN
    dbo.Product P ON P.ProductID = F.ForkID
WHERE
    P.StyleCode = @frameStyleCode
OR
    F.AdditionalStyles Like %@frameStyleCode%
ORDER BY
    P.Model

Your query doesn't return the rows that have 'AM' in the AdditionalStyles column which is what I'm looking for. I know that the query I wrote in my first post works exactly as I want, I just don't know how to create it using LLBLGen.

Please could you elaborate with examples of results returned by the 2 queries. Please explain what results you wanted to return and the query I proposed failed to return.

Also if possible, please provide the tables schemas, so I can test it too.

Thanks and Good Luck

Walaa wrote:

Please could you elaborate with examples of results returned by the 2 queries. Please explain what results you wanted to return and the query I proposed failed to return.

Also if possible, please provide the tables schemas, so I can test it too.

Thanks and Good Luck

OK, I finally tried doing this again and much to my surprise it worked using the code you sent me. Now I don't really understand why doing AdditinalStyles in @Param works since it works out like the example below in the data.

AdditionalStyles = 'XC,FR,AM' @Param = 'AM'

The query then looks like this.

'XC,FR,AM' IN 'AM'

This I don't understand.

I don't really understand why doing AdditinalStyles in @Param works since it works out like the example below in the data.

AdditionalStyles = 'XC,FR,AM' @Param = 'AM'

The query then looks like this.

'XC,FR,AM' IN 'AM'

This I don't understand.

Sorry, but I didn't understand your last question.

But anyway let me explain this: Assume that the Fork table contains 3 Rows only, which have the following values for AdditionalStyles: 1- 'A,B,C' 2- 'X,Y,Z' 3- 'L,M,N'

And you want to query for X (@frameStyleCode='X')

So the following Where condition: @frameStyleCode IN (SELECT AdditionalStyles FROM dbo.Fork WHERE ForkID = F.ForkID)

will examine to see, if 'X' == 'A,B,C' // which is not if 'X' == 'X,Y,Z' // which is not if 'X' == 'L,M,N' // which is not

So for this kind of string to subString comparing, we should use "Like %String%".