I need to use a custom database function in the FieldCompareSetPredicate such as new FieldSetComparePredicate(field, null, field, null, SetOperator.In, “MyCustomFunction(guid)”)

I have been trying to use the ideas from these posts: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=3829 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=5123

I have been beating my head against a wall trying to figure this out and just can’t get it. Is there any way to do the above?

The closest I have gotten is:

FunctionExpression subordinateExpression = new FunctionExpression(EntityFieldFactory.Create(PointTransactionFieldIndex.AwardeeId), "FetchSubordinateIds");
                
FieldCompareValuePredicate filter = PredicateFactory.CompareValue(PointTransactionFieldIndex.AwardeeId, ComparisonOperator.Equal, awardeeId);
filter.FieldCore.ExpressionToApply = subordinateExpression;

IEntityField2 field = EntityFieldFactory.Create(PointTransactionFieldIndex.AwardeeId);
field.ObjectAlias = SOURCE_AWARDEE;
IEntityField2 setField = EntityFieldFactory.Create(PointTransactionFieldIndex.AwardeeId);
setField.ObjectAlias = String.Empty;

predicate.Add(new FieldCompareSetPredicate(field, null, setField, null, SetOperator.In, filter));

Which ends up creating sql like:

([SourceAwardee].[AwardeeID] IN (SELECT [PointTransaction].[AwardeeID] AS [AwardeeId] FROM [PointTransaction]  WHERE FetchSubordinateIds( [PointTransaction].[AwardeeID]) = @AwardeeId4))) 

Where what I need is:

([SourceAwardee].[AwardeeID] IN (SELECT FetchSubordinateIds(@AwardeeId4)) 

Why do I need a custom db function? Because subordinates are located in an org chart which is hierarchal, so that a person high in the chart can have numerous subordinates (by recursively going down the chart). I can create predicate with normal predicates but doing so can result in a parameter list that is greater than 2100 parameters at which time an ORMQueryException is thrown. The db function FetchSubordinateIds uses a CTE to fetch all the ids. This way, I “should” be able to pass one parameter.

We still use the 1.2005.1 runtimes but it seems the case to upgrade to 2.0 grows everyday as it contains useful things like ScalarQueryExpressions that would make our lives easier. If this is would best be done in 2.0, theres another reason to upgrade ASAP.

Many Thanks Again ,

Joe

I tried the ideas in: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=2742

To use the SOUNDEX feature in your code, you can opt for creating a new predicate class, which you build using the code from FieldLikePredicate. Just copy the code over from that class in teh runtime libraries code to a class in your own project. Then, alter the ToQueryText in such a way that you instead of emitting: queryText.AppendFormat("{0} LIKE {1}", base.DatabaseSpecificCreator.CreateFieldName(_field, _persistenceInfo, _field.Name, _objectAlias, ref uniqueMarker, inHavingClause), parameter.ParameterName);

you do: queryText.AppendFormat("SOUNDEX({0}) = SOUNDEX({1})", base.DatabaseSpecificCreator.CreateFieldName(_field, _persistenceInfo, _field.Name, _objectAlias, ref uniqueMarker, inHavingClause), parameter.ParameterName);

(just remove the case sensitive code)

Then, when you produce the filter, use something like: PredicateExpression filter = new PredicateExpression() filter.Add(new FieldSoundExPredicate(EntityFieldFactory.Create(CustomerFieldIndex.ContactFirstName), "John")); (this is selfservicing, if you use adapter, you've to pass in null as second parameter) which will then become:

WHERE SOUNDEX([Customer].[ContactFirstName]) = SOUNDEX(@param1)

Where I created custom predicate, copied code from the FieldLikePredicate and modfied the ToQueryText method to call the function.

        public override string ToQueryText(ref int uniqueMarker, bool inHavingClause)
        {
            if(_field==null)
            {
                return "";
            }

            if(base.DatabaseSpecificCreator==null)
            {
                throw new System.ApplicationException("DatabaseSpecificCreator object not set. Cannot create query part.");
            }

            base.Parameters.Clear();

            StringBuilder queryText = new StringBuilder(64);
            
            // create parameter 
            uniqueMarker++;
            IDataParameter parameter = base.DatabaseSpecificCreator.CreateParameter("id", ParameterDirection.Input, this._awardeeId);
            base.Parameters.Add(parameter);

            queryText.AppendFormat(null, "{0} IN (SELECT * FROM FetchSubordinateIds({1})",
                base.DatabaseSpecificCreator.CreateFieldName(_field, _persistenceInfo, _field.Name, _objectAlias, ref uniqueMarker, inHavingClause), 
                parameter.ParameterName);

            return queryText.ToString();
        }

To use it I would do:

IEntityField2 field = EntityFieldFactory.Create(PointTransactionFieldIndex.AwardeeId);
field.ObjectAlias = SOURCE_TRANSACTION;
predicate.Add(new SubordinateIdPredicate(field, null, awardeeId));

I tried this with the FieldLikePredicate and FieldCompareRangePredicate and experiance the same issues.

The problem boils down to the IFieldPersistenceInfo being null.

When I tried to run it the first time, I can an exception about an Invalid cast between SubordinateIdPredicate and FieldLikePredicate so I changed the InstanceType to Undefiened. Then I get the exceptions about the persistanceInfo being null.

Your class should inherit from an existing Predicate class (Say FieldLikePredicate) And then all the coding you should do inside your class is an override of the ToQueryText.

The persistenceinfo should be injected into the fields in the dataaccessadapter routine: InsertPersistenceInfo(predicate). If you pick an existing predicate class, it will there assume things based on the type of the predicate. You can set the predicate type to a different enum so the switch ends up in the default clause, where you can add in a code region your own code to inject persistence info for the fields inside your predicate.

Finally I got it to work.

I got it working by inheriting from FieldCompareSetPredicate to define my own class. Supplying my own constructor and using the base constructor to set the FieldCompareSetPredicate fields.

Then in the ToQueryText, since we are only using adapter, use base.FieldCore to create the parameters and fields.

It actually turned into a much simpler solution than what I was trying to to.

As always, thanks a ton for the help.

Joe