Hi,

I am trying to implement free text search as described in the documentation and on the forums. My wrinkle is that I am using building up an IQueryable one step at a time. When I try to convert my query to a List<> I get this error:

The binary expression '(FullTextSearch(EntityField(LPLA_18.Title AS Title), "asset") == True)' can't be converted to a predicate expression.

My code:

// Get an IQueryable for all assets
var assetsByCategory = repository.GetAllAssetsProjected(myCategoryId);

// Refine it
assetsByCategory = (from a in assetsByCategory
                    where SearchFunctions.FullTextSearch(a.Title, “my search term”)
                    select a);

// Error when calling ToList()
var list = assetsByCategory.ToList();

My implementation:

    public class SearchFunctions
    {
        public static bool FullTextSearch(string fieldToSearch, string toFind)
        {
            return true;
        }
    }

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

Exact error:

The binary expression '(FullTextSearch(EntityField(LPLA_18.Title AS Title), "asset") == True)' can't be converted to a predicate expression. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException: The binary expression '(FullTextSearch(EntityField(LPLA_18.Title AS Title), "asset") == True)' can't be converted to a predicate expression.

Source Error:

Line 35: select c); Line 36: Line 37: var list = assetsByCategory.ToList();

Source File: C:\Users\rick.MATRIX6\Documents\Visual Studio 2010\Projects\GreatAmerican\gagroup\CAP\trunk\GA.CAP.Website\Controllers\AssetsController.cs Line: 37

Stack Trace:

[ORMQueryConstructionException: The binary expression '(FullTextSearch(EntityField(LPLA_18.Title AS Title), "asset") == True)' can't be converted to a predicate expression.] SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleBinaryExpressionSeparateOperands(BinaryExpression expressionToHandle, Expression leftSide, Expression rightSide) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\QueryExpressionBuilder.cs:2207

---

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1

Thank you in advance for helping me figure this out.

Rick

Hi Rick,

Did you set the CustomFunctionMappings in the LinqMetaData object? As I think you didn't and LLBL2LINQ treats the function like an in-memory function.

metaData.CustomFunctionMappings = new SearchFunctionMappings ();

I think that should be inside your GetAllAssetsProjected.

var assetsByCategory = repository.GetAllAssetsProjected(myCategoryId);

If you are still getting the exception please: - Be sure you are using the latest runtime library version. - Post the code of the GetAllAssetsProjected method.

That did the trick. Thank you for your great support.

As a follow on, how can I specify multiple fields to search in the Linq statement?

Something like this:

                var q = from a in metaData.Asset
                     where SearchFunctions.FullTextSearch(new { a.Title, a.Notes }, searchTerm)
                     select a;

Rick

I have been experimenting with getting multiple columns to work. Here’s my code:

   public class SearchFunctions
    {
        public static bool FullTextSearch(string fieldToSearch, string toFind)
        {
            return true;
        }
    }

    public class SearchFunctionMappings : FunctionMappingStore
    {
        public SearchFunctionMappings()
            : base()
        {
            this.Add(new FunctionMapping(typeof(SearchFunctions), "FullTextSearch", 2, "FREETEXT(*, {1})"));
        }
    }

Example usage:

var metaData = new LinqMetaData(this.Adapter, new SearchFunctionMappings());
            var q = (from a in metaData.Asset
                     where SearchFunctions.FullTextSearch(a.Title, searchTerm)
                     select a);

What seems to be happening is that the query is getting munged into this:

( FREETEXT(*, {1})([LPA_L1].[Title], @p8)

My version is 3.0.10.0915.

Error:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled by user code Message=An exception was caught during the execution of a retrieval query: Incorrect syntax near '{'. Incorrect syntax near 'LPA_L1'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. Source=SD.LLBLGen.Pro.ORMSupportClasses.NET20 RuntimeBuild=09152010 RuntimeVersion=3.0.0.0 QueryExecuted= Query: SELECT [LPA_L1].[RowId], [LPA_L1].[AssetStatusTypeId], [LPA_L1].[ServiceStatusTypeId], [LPA_L1].[Title], [LPA_L1].[Description], [LPA_L1].[Manufacturer], [LPA_L1].[LocationId], [LPA_L1].[ModelNumber], [LPA_L8].[Name] AS [CategoryName], [LPA_L8].[RowId] AS [CategoryId], [LPA_L2].[ParentCategoryId], [LPA_L1].[Quantity], [LPA_L1].[DateManufactured], [LPA_L1].[HasManuals], CASE WHEN ( [LPA_L7].[GATag] = [LPA_L1].[GATag]) THEN 1 ELSE 0 END AS [HasImage], [LPA_L1].[OwnerId], [LPA_L1].[BookValue], [LPA_L1].[GATag], [LPA_L1].[SerialNumber], [LPA_L1].[ClientTag], [LPA_L1].[AvailForRedeploy], [LPA_L1].[AvailForSale], [LPA_L1].[CreatedDate], [LPA_L1].[UpdatedDate], [LPA_L1].[AssetLineId], (([LPA_L6].[FirstName] + @p2) + [LPA_L6].[LastName]) AS [OwnerName], [LPA_L6].[Phone], [LPA_L9].[Email] AS [EMail], (SELECT TOP(@p4) [LPLA_16].[GroupTag] FROM [ClientAccessPortalDB].[dbo].[AssetLines] [LPLA_16] WHERE ( ( ( ( [LPLA_16].[RowId] = [LPA_L1].[AssetLineId]))))) AS [GroupTag] FROM (((((((( [ClientAccessPortalDB].[dbo].[Assets] [LPA_L1] INNER JOIN [ClientAccessPortalDB].[dbo].[Categories] [LPA_L2] ON [LPA_L1].[CategoryId] = [LPA_L2].[RowId]) INNER JOIN [ClientAccessPortalDB].[dbo].[Users] [LPA_L3] ON [LPA_L1].[OwnerId] = [LPA_L3].[RowId]) INNER JOIN [ClientAccessPortalDB].[dbo].[aspnet_Users] [LPA_L4] ON [LPA_L3].[AspnetUserId] = [LPA_L4].[UserId]) INNER JOIN [ClientAccessPortalDB].[dbo].[aspnet_Membership] [LPA_L5] ON [LPA_L4].[UserId] = [LPA_L5].[UserId]) INNER JOIN [ClientAccessPortalDB].[dbo].[UserProfiles] [LPA_L6] ON [LPA_L3].[AspnetUserId] = [LPA_L6].[UserId]) LEFT JOIN [ClientAccessPortalDB].[dbo].[MediaFiles] [LPA_L7] ON [LPA_L1].[GATag] = [LPA_L7].[GATag]) INNER JOIN [ClientAccessPortalDB].[dbo].[Categories] [LPA_L8] ON [LPA_L8].[RowId]=[LPA_L1].[CategoryId]) INNER JOIN [ClientAccessPortalDB].[dbo].[aspnet_Membership] [LPA_L9] ON [LPA_L4].[UserId]=[LPA_L9].[UserId]) WHERE ( ( ( ( ( ( FREETEXT(*, {1})([LPA_L1].[Title], @p )) AND ( ( ( [LPA_L1].[ClientPortalId] = @p9) AND ( [LPA_L5].[ApplicationId] = @p10)) AND ( [LPA_L1].[CategoryId] IN (@p11, @p12, @p13))))))) Parameter: @p2 : String. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: " ". Parameter: @p4 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1. Parameter: @p8 : String. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "model". Parameter: @p9 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 54. Parameter: @p10 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: d6556b3f-d5c1-4c73-9106-18393484b44a. Parameter: @p11 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1. Parameter: @p12 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 3. Parameter: @p13 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 5.

StackTrace: at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 132 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 1576 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Dictionary2 typeConvertersToRun) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 1713

InnerException: System.Data.SqlClient.SqlException Message=Incorrect syntax near '{'. Incorrect syntax near 'LPA_L1'. Source=.Net SqlClient Data Provider ErrorCode=-2146232060 Class=15 LineNumber=1 Number=102 Procedure="" Server=devtfs01 State=1 StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

That wont work. Try this:

public class SearchFunctions
{
      public static bool FullTextSearch(string toFind)
      {
           return true;
      }
}

public class SearchFunctionMappings : FunctionMappingStore
{
     public SearchFunctionMappings(): base()
     {
          this.Add(new FunctionMapping(typeof(SearchFunctions), "FullTextSearch", 1, "FREETEXT(*, {0})"));
      }
}

Usage:

var metaData = new LinqMetaData(this.Adapter, new SearchFunctionMappings());
            var q = (from a in metaData.Asset
                     where SearchFunctions.FullTextSearch(searchTerm)
                     select a);

With your code, using only one parameter, the FREETEXT command never makes it into the query. From reading other posts, this seems to be correct behavior. I think you need to reference one field explicitly in order for FREETEXT to make it into the query.

Thanks,

Rick

You are right. I just wast correcting the code, in the sense that you are specifying 2 parameters but you are using just 1. So there is the error. But your statement about parameters ans FreeText is right.

So how can I get Freetext to work without generating incorrect sql?

Rick

Just use your initial code, and ignore the field in the functionMapping and use * instead, or manually specify the fields as strings.

// Get an IQueryable for all assets
var assetsByCategory = repository.GetAllAssetsProjected(myCategoryId);

// Refine it
assetsByCategory = (from a in assetsByCategory
                    where SearchFunctions.FullTextSearch(a.Title, “my search term”)
                    select a);

// Error when calling ToList()
var list = assetsByCategory.ToList(); 

    public class SearchFunctions
    {
        public static bool FullTextSearch(string fieldToSearch, string toFind)
        {
            return true;
        }
    }

    public class SearchFunctionMappings : FunctionMappingStore
    {
        public SearchFunctionMappings()
            : base()
        {
            this.Add(new FunctionMapping(typeof(SearchFunctions), "FullTextSearch", 2, "FREETEXT(*, {1})"));
        }
    } 

(EDIT) Similar issue: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=17912

I did see that other thread which is what I based my code on.

However, when I use "*, {1}" this is what I get for my where clause:

WHERE ( ( ( ( FREETEXT(*, {1})([LPA_L1].[Title], @p4)))))',N'@p4 nvarchar(4)',@p4=N'test'

It looks like something is wrong with the string replacement.

i was able to reproduce the issue with the latest build (3.0.10.1001 and 3.0.10.0927).

Rick

Full code:

    public class SearchFunctions
    {
        public static bool FullTextSearch(string fieldToSearch, string toFind)
        {
            return true;
        }
    }

    public class SearchFunctionMappings : FunctionMappingStore
    {
        public SearchFunctionMappings()
            : base()
        {
            this.Add(new FunctionMapping(typeof(SearchFunctions), "FullTextSearch", 2, "FREETEXT(*, {1})"));
        }
    }


            var metaData = new LinqMetaData(this.Adapter, new SearchFunctionMappings());

            var q = (from c in metaData.Asset
                     where SearchFunctions.FullTextSearch(c.Title, "test")
                     select c);

You can try a little twist to make the search string as the first parameter.

// Get an IQueryable for all assets
var assetsByCategory = repository.GetAllAssetsProjected(myCategoryId);

// Refine it
assetsByCategory = (from a in assetsByCategory
                    where SearchFunctions.FullTextSearch(“my search term”, a.Title)
                    select a);

// Error when calling ToList()
var list = assetsByCategory.ToList();  

    public class SearchFunctions
    {
        public static bool FullTextSearch(string toFind, string fieldToSearch)
        {
            return true;
        }
    }

    public class SearchFunctionMappings : FunctionMappingStore
    {
        public SearchFunctionMappings()
            : base()
        {
            this.Add(new FunctionMapping(typeof(SearchFunctions), "FullTextSearch", 2, "FREETEXT(*, {0})"));
        }
    }  

The SQL looks a little better but upon closer inspection it is completely mangled. It’s using the field name (Title) instead of @p4.

WHERE ( ( ( ( FREETEXT(*, [LPA_L1].[Title])))))',N'@p4 nvarchar(4)',@p4=N'test'

It seems to me that this is a legitimate bug.

I’m under pressure to have this running by the end of the week. Is there any chance for a bug fix by then?

Rick

I'll ask the development team to take a closer look for you.

Matt

rboarman wrote:

The SQL looks a little better but upon closer inspection it is completely mangled. It’s using the field name (Title) instead of @p4.

WHERE ( ( ( ( FREETEXT(*, [LPA_L1].[Title])))))',N'@p4 nvarchar(4)',@p4=N'test'

It seems to me that this is a legitimate bug.

I’m under pressure to have this running by the end of the week. Is there any chance for a bug fix by then?

The pattern you specified, contains {0} which will be replaced with the first parameter of the function, the search string. It simply uses string.Format() to produce the sql fragment.

It's indeed odd that the second parameter is placed at the {0} position, while the search string is the first one.

We'll look into this. In the meantime, be absolutely sure you pass the right functionmappings class instance to the linqmetadata instance.)

Can't reproduce it:

[Test]
public void CustomFreeTextSearchOnOneFieldToFindEmployees()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter, new NorthwindFunctionMappings());
        var q = from e in metaData.Employee
                where NorthwindFunctions.FreeTextSearch("BA", e.Notes)
                select e;

        int count = 0;
        foreach(var v in q)
        {
            count++;
            Assert.IsTrue(v.Notes.Contains("BA"));
        }
        Assert.AreEqual(4, count);
    }
}


internal class NorthwindFunctions
{
//...

    public static bool FreeTextSearch(string toFind, string fieldToSearch)
    {
        return true;
    }
}

internal class NorthwindFunctionMappings : FunctionMappingStore
{
    /// <summary>
    /// Initializes a new instance of the <see cref="NorthwindFunctionMappings"/> class.
    /// </summary>
    public NorthwindFunctionMappings()
        : base()
    {
//...
        this.Add(new FunctionMapping(typeof(NorthwindFunctions), "FreeTextSearch", 2, "FREETEXT(*, {0})"));
    }
}

query:

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[NW26.Adapter.EntityClasses.EmployeeEntity]).Where(e => FreeTextSearch("BA", e.Notes))
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LPA_L1].[Address], [LPA_L1].[BirthDate], [LPA_L1].[City], [LPA_L1].[Country], [LPA_L1].[EmployeeID] AS [EmployeeId], [LPA_L1].[Extension], [LPA_L1].[FirstName], [LPA_L1].[HireDate], [LPA_L1].[LastName], [LPA_L1].[Notes], [LPA_L1].[HomePhone] AS [Phone], [LPA_L1].[Photo], [LPA_L1].[PhotoPath], [LPA_L1].[PostalCode], [LPA_L1].[Region], [LPA_L1].[RegionID] AS [RegionId], [LPA_L1].[ReportsTo], [LPA_L1].[Title], [LPA_L1].[TitleOfCourtesy] FROM [Northwind].[dbo].[Employees]  [LPA_L1]   WHERE ( ( ( ( FREETEXT(*, @p4)))))
    Parameter: @p4 : String. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "BA".

so please be sure you pass the right functionmappings to the linqmeta-data. I think you pass the old one somewhere.

Okay, I got it working - mostly.

I changed "FullTextSearch" to "FreeTextSearch," renamed my function to match and changed the frreetext parameters to match your test case. This worked fine. The SQL ran and performed as expected.

However, when I add in a bunch of joins and a nested query, I get this error (sorry for the length):

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled by user code
  Message=An exception was caught during the execution of a retrieval query: A CONTAINS or FREETEXT predicate can only operate on one table or indexed view. Qualify the use of * with a table or indexed view name.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
  Source=SD.LLBLGen.Pro.ORMSupportClasses.NET20
  RuntimeBuild=10012010
  RuntimeVersion=3.0.0.0
  QueryExecuted=
    Query: SELECT TOP(@p2) COUNT(*) AS [LPAV_] FROM (SELECT [LPA_L2].[RowId], [LPA_L2].[AssetStatusTypeId], [LPA_L2].[ServiceStatusTypeId], [LPA_L2].[Title], [LPA_L2].[Description], [LPA_L2].[Manufacturer], [LPA_L2].[LocationId], [LPA_L2].[ModelNumber], [LPA_L10].[Name] AS [CategoryName], [LPA_L10].[RowId] AS [CategoryId], [LPA_L3].[ParentCategoryId], [LPA_L2].[Quantity], [LPA_L2].[DateManufactured], [LPA_L2].[HasManuals], CASE WHEN ( [LPA_L9].[GATag] = [LPA_L2].[GATag]) THEN 1 ELSE 0 END AS [HasImage], SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled by user code
  Message=An exception was caught during the execution of a retrieval query: A CONTAINS or FREETEXT predicate can only operate on one table or indexed view. Qualify the use of * with a table or indexed view name.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
  Source=SD.LLBLGen.Pro.ORMSupportClasses.NET20
  RuntimeBuild=10012010
  RuntimeVersion=3.0.0.0
  QueryExecuted=
    Query: SELECT TOP(@p2) COUNT(*) AS [LPAV_] FROM (SELECT [LPA_L2].[RowId], [LPA_L2].[AssetStatusTypeId], [LPA_L2].[ServiceStatusTypeId], [LPA_L2].[Title], [LPA_L2].[Description], [LPA_L2].[Manufacturer], [LPA_L2].[LocationId], [LPA_L2].[ModelNumber], [LPA_L10].[Name] AS [CategoryName], [LPA_L10].[RowId] AS [CategoryId], [LPA_L3].[ParentCategoryId], [LPA_L2].[Quantity], [LPA_L2].[DateManufactured], [LPA_L2].[HasManuals], CASE WHEN ( [LPA_L9].[GATag] = [LPA_L2].[GATag]) THEN 1 ELSE 0 END AS [HasImage], [LPA_L2].[OwnerId], [LPA_L2].[BookValue], [LPA_L2].[GATag], [LPA_L2].[SerialNumber], [LPA_L2].[ClientTag], [LPA_L2].[AvailForRedeploy], [LPA_L2].[AvailForSale], [LPA_L2].[CreatedDate], [LPA_L2].[UpdatedDate], [LPA_L2].[AssetLineId], [LPA_L2].[TransferCost], (([LPA_L7].[FirstName] + @p4) + [LPA_L7].[LastName]) AS [OwnerName], [LPA_L7].[Phone], [LPA_L11].[Email] AS [EMail], [LPA_L8].[Name] AS [Location], (SELECT TOP(@p6) [LPLA_18].[GroupTag] FROM [ClientAccessPortalDB].[dbo].[AssetLines]  [LPLA_18]   WHERE ( ( ( ( [LPLA_18].[RowId] = [LPA_L2].[AssetLineId]))))) AS [GroupTag] FROM ((((((((( [ClientAccessPortalDB].[dbo].[Assets] [LPA_L2]  INNER JOIN [ClientAccessPortalDB].[dbo].[Categories] [LPA_L3]  ON  [LPA_L2].[CategoryId] = [LPA_L3].[RowId]) INNER JOIN [ClientAccessPortalDB].[dbo].[Users] [LPA_L4]  ON  [LPA_L2].[OwnerId] = [LPA_L4].[RowId]) INNER JOIN [ClientAccessPortalDB].[dbo].[aspnet_Users] [LPA_L5]  ON  [LPA_L4].[AspnetUserId] = [LPA_L5].[UserId]) INNER JOIN [ClientAccessPortalDB].[dbo].[aspnet_Membership] [LPA_L6]  ON  [LPA_L5].[UserId] = [LPA_L6].[UserId]) INNER JOIN [ClientAccessPortalDB].[dbo].[UserProfiles] [LPA_L7]  ON  [LPA_L4].[AspnetUserId] = [LPA_L7].[UserId]) INNER JOIN [ClientAccessPortalDB].[dbo].[Locations] [LPA_L8]  ON  [LPA_L2].[LocationId] = [LPA_L8].[RowId]) LEFT JOIN [ClientAccessPortalDB].[dbo].[MediaFiles] [LPA_L9]  ON  [LPA_L2].[GATag] = [LPA_L9].[GATag]) INNER JOIN [ClientAccessPortalDB].[dbo].[Categories] [LPA_L10]  ON  [LPA_L10].[RowId]=[LPA_L2].[CategoryId]) INNER JOIN [ClientAccessPortalDB].[dbo].[aspnet_Membership] [LPA_L11]  ON  [LPA_L5].[UserId]=[LPA_L11].[UserId]) WHERE ( ( ( ( FREETEXT(*, @p10))) AND ( ( ( [LPA_L2].[ClientPortalId] = @p11) AND ( [LPA_L6].[ApplicationId] = @p12)) AND ( [LPA_L2].[CategoryId] IN (@p13, @p14, @p15)))))) [LPA_L1] WHERE ( ( ( ( ( [LPA_L1].[AssetStatusTypeId] = @p17)))))
    Parameter: @p2 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @p4 : String. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: " ".
    Parameter: @p6 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @p10 : String. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: "test".
    Parameter: @p11 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 54.
    Parameter: @p12 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: d6556b3f-d5c1-4c73-9106-18393484b44a.
    Parameter: @p13 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.
    Parameter: @p14 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 3.
    Parameter: @p15 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 5.
    Parameter: @p17 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

  StackTrace:
       at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 132
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 1576
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Dictionary`2 typeConvertersToRun) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 1713
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IEntityFields2 fields, IRelationPredicateBucket filter, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 1675
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProvider2.cs:line 178
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProviderBase.cs:line 264
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProviderBase.cs:line 93
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute[TResult](Expression expression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProviderBase.cs:line 656
       at System.Linq.Queryable.Count[TSource](IQueryable`1 source)
       at MvcContrib.Pagination.LazyPagination`1.TryExecuteQuery() in C:\Users\rick.MATRIX6\Documents\Visual Studio 2010\Samples\mvccontrib-0d5c4cb24931\mvccontrib_0d5c4cb24931\src\MVCContrib\Pagination\LazyPagination.cs:line 62
       at MvcContrib.Pagination.LazyPagination`1.get_TotalItems() in C:\Users\rick.MATRIX6\Documents\Visual Studio 2010\Samples\mvccontrib-0d5c4cb24931\mvccontrib_0d5c4cb24931\src\MVCContrib\Pagination\LazyPagination.cs:line 85
       at MvcContrib.UI.Pager.Pager.ToString() in C:\Users\rick.MATRIX6\Documents\Visual Studio 2010\Samples\mvccontrib-0d5c4cb24931\mvccontrib_0d5c4cb24931\src\MVCContrib\UI\Pager\Pager.cs:line 116
       at System.Web.HttpWriter.Write(Object obj)
       at System.Web.Mvc.ViewPage.SwitchWriter.Write(Object value)
       at System.Web.UI.HtmlTextWriter.Write(Object value)
       at ASP.views_assets_browse_browsepager_ascx.__Render__control1(HtmlTextWriter __w, Control parameterContainer) in c:\Users\rick.MATRIX6\Documents\Visual Studio 2010\Projects\GreatAmerican\gagroup\CAP\trunk\GA.CAP.Website\Views\Assets\Browse\BrowsePager.ascx:line 8
       at System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children)
       at System.Web.UI.Control.RenderChildren(HtmlTextWriter writer)
       at System.Web.UI.Control.Render(HtmlTextWriter writer)
       at System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter)
       at System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter)
       at System.Web.UI.Control.RenderControl(HtmlTextWriter writer)
       at System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children)
       at System.Web.UI.Control.RenderChildren(HtmlTextWriter writer)
       at System.Web.UI.Page.Render(HtmlTextWriter writer)
       at System.Web.Mvc.ViewPage.Render(HtmlTextWriter writer)
       at System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter)
       at System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter)
       at System.Web.UI.Control.RenderControl(HtmlTextWriter writer)
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
  InnerException: System.Data.SqlClient.SqlException
       Message=A CONTAINS or FREETEXT predicate can only operate on one table or indexed view. Qualify the use of * with a table or indexed view name.
       Source=.Net SqlClient Data Provider
       ErrorCode=-2146232060
       Class=16
       LineNumber=1
       Number=7615
       Procedure=""
       Server=devtfs01
       State=1
       StackTrace:
            at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
            at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
            at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
            at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
            at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
            at System.Data.SqlClient.SqlDataReader.get_MetaData()
            at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
            at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
            at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
            at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
            at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
            at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
            at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
            at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 112
       InnerException:].[OwnerId], [LPA_L2].[BookValue], [LPA_L2].[GATag], [LPA_L2].[SerialNumber], [LPA_L2].[ClientTag], [LPA_L2].[AvailForRedeploy], [LPA_L2].[AvailForSale], [LPA_L2].[CreatedDate], [LPA_L2].[UpdatedDate], [LPA_L2].[AssetLineId], [LPA_L2].[TransferCost], (([LPA_L7].[FirstName] + @p4) + [LPA_L7].[LastName]) AS [OwnerName], [LPA_L7].[Phone], [LPA_L11].[Email] AS [EMail], [LPA_L8].[Name] AS [Location], (SELECT TOP(@p6) [LPLA_18].[GroupTag] FROM [ClientAccessPortalDB].[dbo].[AssetLines]  [LPLA_18]   WHERE ( ( ( ( [LPLA_18].[RowId] = [LPA_L2].[AssetLineId]))))) AS [GroupTag] FROM ((((((((( [ClientAccessPortalDB].[dbo].[Assets] [LPA_L2]  INNER JOIN [ClientAccessPortalDB].[dbo].[Categories] [LPA_L3]  ON  [LPA_L2].[CategoryId] = [LPA_L3].[RowId]) INNER JOIN [ClientAccessPortalDB].[dbo].[Users] [LPA_L4]  ON  [LPA_L2].[OwnerId] = [LPA_L4].[RowId]) INNER JOIN [ClientAccessPortalDB].[dbo].[aspnet_Users] [LPA_L5]  ON  [LPA_L4].[AspnetUserId] = [LPA_L5].[UserId]) INNER JOIN [ClientAccessPortalDB].[dbo].[aspnet_Membership] [LPA_L6]  ON  [LPA_L5].[UserId] = [LPA_L6].[UserId]) INNER JOIN [ClientAccessPortalDB].[dbo].[UserProfiles] [LPA_L7]  ON  [LPA_L4].[AspnetUserId] = [LPA_L7].[UserId]) INNER JOIN [ClientAccessPortalDB].[dbo].[Locations] [LPA_L8]  ON  [LPA_L2].[LocationId] = [LPA_L8].[RowId]) LEFT JOIN [ClientAccessPortalDB].[dbo].[MediaFiles] [LPA_L9]  ON  [LPA_L2].[GATag] = [LPA_L9].[GATag]) INNER JOIN [ClientAccessPortalDB].[dbo].[Categories] [LPA_L10]  ON  [LPA_L10].[RowId]=[LPA_L2].[CategoryId]) INNER JOIN [ClientAccessPortalDB].[dbo].[aspnet_Membership] [LPA_L11]  ON  [LPA_L5].[UserId]=[LPA_L11].[UserId]) WHERE ( ( ( ( FREETEXT(*, @p10))) AND ( ( ( [LPA_L2].[ClientPortalId] = @p11) AND ( [LPA_L6].[ApplicationId] = @p12)) AND ( [LPA_L2].[CategoryId] IN (@p13, @p14, @p15)))))) [LPA_L1] WHERE ( ( ( ( ( [LPA_L1].[AssetStatusTypeId] = @p17)))))
    Parameter: @p2 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @p4 : String. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: " ".
    Parameter: @p6 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @p10 : String. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: "test".
    Parameter: @p11 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 54.
    Parameter: @p12 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: d6556b3f-d5c1-4c73-9106-18393484b44a.
    Parameter: @p13 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.
    Parameter: @p14 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 3.
    Parameter: @p15 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 5.
    Parameter: @p17 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

  StackTrace:
       at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 132
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 1576
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Dictionary`2 typeConvertersToRun) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 1713
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IEntityFields2 fields, IRelationPredicateBucket filter, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 1675
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProvider2.cs:line 178
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProviderBase.cs:line 264
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProviderBase.cs:line 93
       at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute[TResult](Expression expression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProviderBase.cs:line 656
       at System.Linq.Queryable.Count[TSource](IQueryable`1 source)
       at MvcContrib.Pagination.LazyPagination`1.TryExecuteQuery() in C:\Users\rick.MATRIX6\Documents\Visual Studio 2010\Samples\mvccontrib-0d5c4cb24931\mvccontrib_0d5c4cb24931\src\MVCContrib\Pagination\LazyPagination.cs:line 62
       at MvcContrib.Pagination.LazyPagination`1.get_TotalItems() in C:\Users\rick.MATRIX6\Documents\Visual Studio 2010\Samples\mvccontrib-0d5c4cb24931\mvccontrib_0d5c4cb24931\src\MVCContrib\Pagination\LazyPagination.cs:line 85
       at MvcContrib.UI.Pager.Pager.ToString() in C:\Users\rick.MATRIX6\Documents\Visual Studio 2010\Samples\mvccontrib-0d5c4cb24931\mvccontrib_0d5c4cb24931\src\MVCContrib\UI\Pager\Pager.cs:line 116
       at System.Web.HttpWriter.Write(Object obj)
       at System.Web.Mvc.ViewPage.SwitchWriter.Write(Object value)
       at System.Web.UI.HtmlTextWriter.Write(Object value)
       at ASP.views_assets_browse_browsepager_ascx.__Render__control1(HtmlTextWriter __w, Control parameterContainer) in c:\Users\rick.MATRIX6\Documents\Visual Studio 2010\Projects\GreatAmerican\gagroup\CAP\trunk\GA.CAP.Website\Views\Assets\Browse\BrowsePager.ascx:line 8
       at System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children)
       at System.Web.UI.Control.RenderChildren(HtmlTextWriter writer)
       at System.Web.UI.Control.Render(HtmlTextWriter writer)
       at System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter)
       at System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter)
       at System.Web.UI.Control.RenderControl(HtmlTextWriter writer)
       at System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children)
       at System.Web.UI.Control.RenderChildren(HtmlTextWriter writer)
       at System.Web.UI.Page.Render(HtmlTextWriter writer)
       at System.Web.Mvc.ViewPage.Render(HtmlTextWriter writer)
       at System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter)
       at System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter)
       at System.Web.UI.Control.RenderControl(HtmlTextWriter writer)
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
  InnerException: System.Data.SqlClient.SqlException
       Message=A CONTAINS or FREETEXT predicate can only operate on one table or indexed view. Qualify the use of * with a table or indexed view name.
       Source=.Net SqlClient Data Provider
       ErrorCode=-2146232060
       Class=16
       LineNumber=1
       Number=7615
       Procedure=""
       Server=devtfs01
       State=1
       StackTrace:
            at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
            at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
            at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
            at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
            at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
            at System.Data.SqlClient.SqlDataReader.get_MetaData()
            at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
            at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
            at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
            at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
            at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
            at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
            at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
            at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 112
       InnerException:

I was able to solve the above error by specifying the table alias in my freetext function:

"FREETEXT([LPA_L2].*, {0})"

This solution seems problematic and smells like a hack. I don’t have control over how the alias is generated and it may change if I change my query right?

Any insight on this new behavior?

Ultimately I am going to need to do a freetext search on three tables and then inner join a handful of others.

Thank you for being patient while I figure this out.

Rick

Then you should not use an '*', and pass the fields you need to use in the FullTextSearch, to your mapped function, in order to emit them in the sql query.

Indeed.

If you need multiple # of fields to filter on, you can of course create a couple of methods and mappings, e.g. one with 1 field, 1 with 2 fields, 1 with 3 fields and one with 4 fields (similar to the Func<> delegate definition in .net, it's predefined to a given limited number of parameters). This solves the problem with the flexible # of parameters which isn't supported in the function mappings as they require to have a fixed number of parameters defined.