Hi I am following this page to achieve what i want :

http://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/Linq/gencode_linq_functionmappings.htm

I am suing function mapping to call the full text function "FullTextSearch" this is what I am doing"

public class BARFunctions
    {
        public static bool FullTextSearch(string toFind)
        {
            // empty body, as it's just here to make the query compile. The call is converted to a SQL function.
            return true;
        }
    }

    /// Class which defines the custom mapping between NorthwindFunctions.FullTextSearch and CONTAINS()
    public class BARFunctionMappings : FunctionMappingStore
    {
        public BARFunctionMappings()
            : base()
        {
            this.Add(new FunctionMapping(typeof(BARFunctions), "FullTextSearch", 1, "FREETEXT(*, {0})"));
        }
    }

and then:

LinqMetaData metaData = new LinqMetaData();
metaData.CustomFunctionMappings = new BARFunctionMappings();
var q = from c in metaData.Application select c;
if (!string.IsNullOrEmpty(fullTextSearch))
{
   q = q.Where(c => BARFunctions.FullTextSearch(fullTextSearch));
}

But this resturns all items on the list, so it seems that the function is not even beeing called......what am i foing wrong, the only thing I am doing differently than from the example in the link above is the fact that the example is only searchin in one column I am searching in all columns.

Thanks

Please try the following:

LinqMetaData metaData = new LinqMetaData();
metaData.CustomFunctionMappings = new BARFunctionMappings();

var q = from c in metaData.Application
            Where BARFunctions.FullTextSearch(fullTextSearch)
            select c;

Also would you please examine the generated SQL in both cases, to make sure the predicate is being generated into the output SQL command.

Does not work unfortunately. What is a good way to get the resulting sql query? i am using a tracer in the database server...can i do it in the dev environment, in the immediate window for instance?

Does not work unfortunately.

that's too vague. please explain exactly what you did: paste your code, what query was generated and what error you got (if you got an error) or that the wrong rows were matched etc.

Please check the Troubleshooting section of the manual to know how to get the generated SQL query at debug time, in the output window.

Otis wrote:

Does not work unfortunately.

that's too vague. please explain exactly what you did: paste your code, what query was generated and what error you got (if you got an error) or that the wrong rows were matched etc.

As said above it just returns all rows and performs no search at all...

I will try to trace and see how it goes

Cheers

Luis

OK now i am using:

var q = from c in metaData.Application 
                    where BARFunctions.FullTextSearch(fullTextSearch)
                    select c;

this is what i get on the trace:

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[BAR.DAL.EntityClasses.ApplicationEntity]).Where(c => FullTextSearch(value(BAR.BLL.ApplicationsBLL+<>c__DisplayClass6).fullTextSearch))

Still getting all the rows no matter what i put in fullTextSearch...any idea?

Thanks Guys

Luis

Tracing SqlServerDQE in level 4 i get this:

Generated Sql query:

SELECT * FROM [BAR_DEV].[dbo].[Application] [LPLA_1] WHERE ( ( ( ( @LO12 = @LPFA_11)))) Parameter: @LO12 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1. Parameter: @LPFA_11 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

No reference to the fulltext search in the resulting query...any idea why?

Thanks Again

Luis

Which runtime library version (of each the following) are you using? 1- SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll 2- SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll

Please refer to this thread to know how to get the runtime library version: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7717

1- SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll --> 2.6.10.224 2- SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll --> 2.6.10.225

Reproduced.

Even the following produces the same result:

    public class NorthwindFunctions
    {
        public static bool FullTextSearch(string theField, string toFind)
        {
            return true;
        }

    }

    public class NorthwindFunctionMappings : FunctionMappingStore
    {
        public NorthwindFunctionMappings()
            : base()
        {
            this.Add(new FunctionMapping(typeof(NorthwindFunctions), "FullTextSearch", 2, "FREETEXT({0}, {1})"));
        }
    }

    metaData.CustomFunctionMappings = new NorthwindFunctionMappings();
            var q = from c in metaData.Customers
                    where NorthwindFunctions.FullTextSearch("*", "XYZ")
                    select c;

Only when modifying the above Linq code to the wollowing will generate the FULLTEXT predicate in the output SQL

metaData.CustomFunctionMappings = new NorthwindFunctionMappings();
        var q = from c in metaData.Customers
                where NorthwindFunctions.FullTextSearch(**c.CustomerName**, "XYZ")
                select c;

We will investigate it and get back to you.

Any other way to achieve it, maybe without linq? I need this implemented by tomorrow...

Thanks for your help guys.

Regards

Luis

luis_v_silva wrote:

Any other way to achieve it, maybe without linq? I need this implemented by tomorrow...

Thanks for your help guys.

Regards

Luis

We were swamped by the RC release of v3 yesterday. We're currently looking into your issue. There's a fulltext search predicate class in the normal API, see only docs about that predicate if you want a non-linq solution.

The reason what you do fails is the following: The full query is compiled as an expression tree, which means: constructs which run on the DB and which have to run in-memory. The distinction between the two is: if a lambda / expression refers to an element from the source of the query (Queryable) it is tied to the db, if it doesn't, it's an in-memory piece of code which can be compiled away (it's a piece of code which is compiled to a delegate) as it has no meaning inside the DB.

Your function from your startpost has no ties with the tree elements, it's therefore seen as an in-memory method and compiled away. This way, complex in-memory pieces of code can be combined with db code, as there's no distinction between in-memory code and db targeting code in a linq query. This process is done with special visitor. See: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1353063&SiteID=1

Walaa in his post http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=100274&ThreadID=17912 above made a variance to the mapping, tying the method to the tree by passing in a field from the entity (which is a db element), and therefore the method is seen as a method targeting a db element and isn't compiled away but converted to a db construct.

So use the code Walaa posted above, this will make the mapping work properly.

Just to coninue on what Frans has said.

So if you want to run the Full text search on multiple columns, you will have to pass these columns/fields explicitly otherwise if you want to use '*' you will have to use a field as a dummy input as follows:

    public class NorthwindFunctions
    {
        public static bool FullTextSearchOnAllFileds(string toFind, string dummyField)
        {
            return true;
        }

    }

    public class NorthwindFunctionMappings : FunctionMappingStore
    {
        public NorthwindFunctionMappings()
            : base()
        {
            this.Add(new FunctionMapping(typeof(NorthwindFunctions), "FullTextSearchOnAllFileds", 2, "FREETEXT(*, {0})"));
        }
    }

    metaData.CustomFunctionMappings = new NorthwindFunctionMappings();
            var q = from c in metaData.Customers
                    where NorthwindFunctions.FullTextSearchOnAllFileds("XYZ", c.ContactName)
                    select c;

Hi Again,

Sorry for the later reply (it has been hectic this last few days...), thank you very much you guys saved my skin

I do undettand the above explanation, but that is not the intuitive expected behaviour, so you guys might consider changing that in the future.

Thanks again

Best regards

Luis

It's what Microsoft designed: a query language where you can mix db stuff with in-memory stuff. We can't change that unfortunately.