Currently in LLBLGen, you can do this with freetext:

SELECT pk, txtCol1 FROM myFt_Table WHERE CONTAINS(txtCol1, ' "foo" AND "bar" ')

This is represented with something like...

IPredicateExpression wordsPredicate = new PredicateExpression();
IEntityField field = <sometable.txtCol1>
 wordsPredicate.Add(new FieldFullTextSearchPredicate(field , FullTextSearchOperator.Contains, searchWords));

I'd like to be able to do this: SELECT pk, txtCol2, txtCol3 FROM myFt_Table WHERE CONTAINS( ( txtCol2, txtCol3 ) , ' "foo" AND "bar" ')

This is represented with something like...

IPredicateExpression wordsPredicate = new PredicateExpression();
IEntityField[] fields = new IEntityField[] { field1, field2, field3....};
 wordsPredicate.Add(new FieldFullTextSearchPredicate( (fields) , FullTextSearchOperator.Contains, searchWords));

The "FieldFullTextSearchPredicate" class does not currently have an overload letting you pass an array of EntityFields. I suppose I could write the class and add it, but I was sorta hoping that you would be adding that sometime. Any thoughts? Or hopefully....a timeline?

This isn't planned, I didn't know this was possible. I'll add it to the todo list. In the meantime, you could alter the sourcecode for the class. If you're using adapter, you also have to alter InsertPersistenceInfo(predicate) in the generated code of dataaccessadapter. (template addition)

Good to know it will be added. The current workaround is to obviously search each column in the index with a separate predicate and then "OR" then together

string searchwords = "foo"; myPredicate.Add(new FieldFullTextSearchPredicate(<coll1>, FullTextSearchOperator.Contains, searchWords)); myPredicate.AddWithOr(new FieldFullTextSearchPredicate(<col2>, FullTextSearchOperator.Contains, searchWords));

This can be less than optimal though, as using multiple clauses requires the full-text engine to evaluate multiple full-text queries. Providing the column list in a single full-text query clause requires the engine to evaluate only a single query expression On my database, seaching through 400,000 rows with multiple clauses takes over 12 seconds. Passing the column names into a single clause shortens the search down to 1 second. Your results may vary.

A shortcut syntax is the "" character. As in ( contains("", 'foo'). The wildcard lets you search across all columns in the index without explicitly listing them. I would imagine this could be implemented using a constant of some sort.

string searchwords = "foo"; myPredicate.Add(new FieldFullTextSearchPredicate(FieldIndex.WildCardAllColumns, FullTextSearchOperator.Contains, searchWords));

Is this feature added with the current version or any other work arrounds?

This feature was cut from the list of features to implement in v2.0. The workaround mentioned in this thread is the one you should follow.

As I found it a bit of a shame not to use this new functionality of SQL2005 to increase performance, I set out to create my own predicate class. The following predicate class creates for example something like:

CONTAINS ((Column1, Column2),  'search expression')

I'm using the adaptor profile, so I didn't bother to create the selfservicing constructors.

    public class FieldFullTextSearchPredicate : SD.LLBLGen.Pro.ORMSupportClasses.Predicate
    {
        #region Class Member Declarations
        private IEntityFieldCore[]      _fields;
        private IFieldPersistenceInfo   _persistenceInfo;
        private string                  _pattern, _objectAlias;
        private FullTextSearchOperator  _operatorToUse;
        #endregion

        /// <summary>
        /// CTor
        /// </summary>
        public FieldFullTextSearchPredicate()
        {
            InitClass(null, null, string.Empty, FullTextSearchOperator.Contains, false, true, string.Empty);
        }


        public FieldFullTextSearchPredicate(IEntityFieldCore[] fields, IFieldPersistenceInfo persistenceInfo, FullTextSearchOperator operatorToUse, string pattern)
        {
            InitClass(fields, persistenceInfo, pattern, operatorToUse, false, false, string.Empty);
        }

        public FieldFullTextSearchPredicate(IEntityFieldCore[] fields, IFieldPersistenceInfo persistenceInfo, FullTextSearchOperator operatorToUse, string pattern, bool negate)
        {
            InitClass(fields, persistenceInfo, pattern, operatorToUse, negate, false, string.Empty);
        }

        public FieldFullTextSearchPredicate(IEntityFieldCore[] fields, IFieldPersistenceInfo persistenceInfo, FullTextSearchOperator operatorToUse, string pattern, string objectAlias)
        {
            InitClass(fields, persistenceInfo, pattern, operatorToUse, false, false, objectAlias);
        }
        public FieldFullTextSearchPredicate(IEntityFieldCore[] fields, IFieldPersistenceInfo persistenceInfo, FullTextSearchOperator operatorToUse, string pattern, string objectAlias, bool negate)
        {
            InitClass(fields, persistenceInfo, pattern, operatorToUse, negate, false, objectAlias);
        }

        public override string ToQueryText(ref int uniqueMarker)
        {
            return ToQueryText(ref uniqueMarker, false);
        }
        public override string ToQueryText(ref int uniqueMarker, bool inHavingClause)
        {
            if(_fields==null)
            {
                return "";
            }
            if (_fields.Length == 0) {
                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);
            
            if(base.Negate)
            {
                queryText.Append("NOT ");
            }


            uniqueMarker++;
            IDataParameter parameter = base.DatabaseSpecificCreator.CreateLikeParameter(uniqueMarker.ToString(), _pattern);
            parameter.ParameterName += uniqueMarker.ToString();
            parameter.Value = _pattern;
            base.Parameters.Add(parameter);

            queryText.AppendFormat("{0}((", _operatorToUse.ToString());
            for (int i = 0; i < _fields.Length; i++) {
                queryText.AppendFormat(base.DatabaseSpecificCreator.CreateFieldName(_fields[i], _persistenceInfo, _fields[i].Name, _objectAlias, ref uniqueMarker, inHavingClause));
                if (i < _fields.Length - 1) {
                    queryText.AppendFormat(", ");
                }
            }
            queryText.AppendFormat("), {0})", parameter.ParameterName);
            return queryText.ToString();
        }


        private void InitClass(IEntityFieldCore[] fields, IFieldPersistenceInfo persistenceInfo, string pattern, FullTextSearchOperator operatorToUse, bool negate, bool selfServicing, string objectAlias)
        {
            _fields = fields;
            _persistenceInfo = persistenceInfo;
            _pattern = pattern;
            _operatorToUse = operatorToUse;
            base.Negate=negate;
            //base.SelfServicing = selfServicing;
            //base.InstanceType = (int)PredicateType.FieldFullTextSearchPredicate;
            base.InstanceType = -1;
            _objectAlias = objectAlias;
        }

        public virtual IEntityFieldCore[] FieldCores
        {
            get 
            { 
                return _fields; 
            }
        }

        public IFieldPersistenceInfo PersistenceInfo
        {
            get
            {
                return _persistenceInfo;
            }
            set
            {
                _persistenceInfo = value;
            }
        }
        
        public virtual string Pattern
        {
            get { return _pattern; }
            set 
            { 
                _pattern = value; 
            }
        }


        public FullTextSearchOperator OperatorToUse
        {
            get
            {
                return _operatorToUse;
            }
            set
            {
                _operatorToUse = value;
            }
        }
        

        public string ObjectAlias
        {
            get
            {
                return _objectAlias;
            }
            set
            {
                _objectAlias = value;
            }
        }
    }

And in the DataAccessAdapter class:

                        // __LLBLGENPRO_USER_CODE_REGION_START InsertPersistenceInfoObjectsPredicate
                        switch (currentPredicate.InstanceType) {
                            case -1:
                                RSS.TipBaseDBSpecific.Predicate.FieldFullTextSearchPredicate customFullTextSearchPredicate = (RSS.TipBaseDBSpecific.Predicate.FieldFullTextSearchPredicate)currentPredicate;
                                if (customFullTextSearchPredicate.PersistenceInfo == null) {
                                    customFullTextSearchPredicate.PersistenceInfo = GetFieldPersistenceInfo((IEntityField2)customFullTextSearchPredicate.FieldCores[0]);
                                }
                                break;
                        }
                        // __LLBLGENPRO_USER_CODE_REGION_END

As I you can see I'm using only the first field in the fieldcollection to create the persistenceinfo. I'm not shure whether this is the correct approach. Another issue with this code is that in the adapter there is no check whether the field array is empty or not.

While creating this class I stumbled against the FieldPersistenceInfo class. I must say I'm not shure why this is part of every constructor. I found no easy way to create an instance of the FieldPersistenceInfo to use as a parameter for the constructor of the predicate class. So my question is why is the FieldPersistenceInfo class part off all constructors while there is no easy way to create this class, as far as I know, except in the DataAccessAdapter class.

Hello,

When a constructor need theses two parameters : "IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo", you can use null as persistenceinfo, it will create it himself for your case.

True, but I had to edit the DataAccessAdapter class to let the DataAccessAdapter create the FieldPersistenceInfo for my custom predicate class, because I used an instanceType of -1, witch is not recognized by the DataAccessAdapter. The DataAccessAdapter is generaly responsible for creating the FieldPersistenceInfo. Thats why I'm wondering why it is a parameter as you have to always pass a null value as far as I know. On the other hand I couldn't find an easy way to create an instance of FieldPersistenceInfo except for the GetFieldPersistenceInfo in the DataAccessAdapter class.

It's not realy a problem, but I'm just curious why to use a parameter witch is always null?

It's this way for a long time, and by changing: IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo into IEntityField2 field

lead to compile errors in some situations where a second parameter was null, UNLESS the constructor with the persistenceInfo was removed completely. As that would break a lot of code, it wasn't possible. So the initial not necessary addition of the second parameter lead to the status now that it's not removable.

the persistenceinfo isn't necessary for your code, though it is handy in some low level internal code, or at least: it can be handy, if a filter has to be rewritten internally (if that necessity is required, that's the reason the constructor is this way in the first place).