Hi,

I want to query Index Server with a linked server in SQL Server 2008 like this:

select * from some_sqlServer2008_table
where filename in (SELECT * FROM OpenQuery(Catalog, '
SELECT FileName
FROM SCOPE()
WHERE CONTAINS(''searchedtext'')
ORDER BY RANK DESC'))

The tricky bit is the combination of OpenQuery and the "searchedtext" which is a parameter at run time. I have this as my function:

    public class IndexFunctions
    {
        public static bool xxxxx(string col, string text)
        {
            return true;
        }
    }

    public class IndexFunctionMappings : FunctionMappingStore
    {
        public IndexFunctionMappings()
        {
            Add(new FunctionMapping(typeof(IndexFunctions), "xxxxx", 2, @"({0} in (SELECT * FROM OpenQuery(Catalog, '
SELECT FileName
FROM SCOPE()
WHERE CONTAINS({1})
ORDER BY RANK DESC')))"));
        }
    }

and this as the linq query:

                var metaData = new LinqMetaData(daa)
                                {
                                    CustomFunctionMappings = new IndexFunctionMappings()
                                };
                var q = from e in metaData.some_sqlServer2008_table
                        where IndexFunctions.xxxxx(e.Filename, "BA")
                        select e;

However, it uses a parameterized query as you can see from the trace below. Is there any way that I can change the SQL generation to not parameterize the parameters as that is a problem with the OpenQuery construct. Alternatively have you seen another solution to querying linked servers to Index Server (short of writing a database driver)?:

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[TheHub.Entities.EntityClasses.DocumentVersionEntity]).Where(e => xxxxx(e.Filename, "BA"))
Method Enter: DataAccessAdapterBase.FetchEntityCollection(8)
Method Enter: DataAccessAdapterBase.FetchEntityCollectionInternal(7)
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LPLA_1].[key] AS [Key], [LPLA_1].[filename_txt] AS [Filename] FROM [xxxx].[dbo].[some_sqlServer2008_table] [LPLA_1]  WHERE ( ( ( ( ([LPLA_1].[filename_txt] in (SELECT * FROM OpenQuery(Catalog, '
SELECT FileName
FROM SCOPE()
WHERE CONTAINS(@LOce09b7de2)
ORDER BY RANK DESC')))))))
    Parameter: @LOce09b7de2 : String. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "BA".
    Parameter: @LPFA_11 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: True.

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
SqlServer ready query: 
    Query:
DECLARE @LOce09b7de2 NVarChar
SET @LOce09b7de2="BA"
DECLARE @LPFA_11 Bit
SET @LPFA_11=1

SELECT
 [LPLA_1].[key] AS [Key], [LPLA_1].[filename_txt] AS [Filename]
FROM
 [xxxx].[dbo].[some_sqlServer2008_table] [LPLA_1]  
WHERE
 ( ( ( ( ([LPLA_1].[filename_txt] in (
SELECT
 * 
FROM
 OpenQuery(Catalog, '

SELECT
 FileName

FROM
 SCOPE()

WHERE
 CONTAINS(@LOce09b7de2)
ORDER BY RANK DESC')))))))
Method Enter: DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
Method Enter: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
Method Enter: DataAccessAdapterBase.CloseConnection
Method Exit: DataAccessAdapterBase.CloseConnection

System.Data.SqlClient.SqlException: An error occurred while preparing the query "
SELECT FileName
FROM SCOPE()
WHERE CONTAINS(@LOce09b7de2)
ORDER BY RANK DESC" for execution against OLE DB provider "MSIDXS" for linked server "Catalog". 
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(TdsParserStateObject stateObj)
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.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) 
SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: An error occurred while preparing the query "
SELECT FileName
FROM SCOPE()
WHERE CONTAINS(@LOce09b7de2)
ORDER BY RANK DESC" for execution against OLE DB provider "MSIDXS" for linked server "Catalog". . Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, ref IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteEntityProjection(QueryExpression toExecute)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute()
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.SD.LLBLGen.Pro.LinqSupportClasses.ILLBLGenProQuery.Execute<TResult>()

Linked servers is an area where not everything can work, as the linked server can be anything.

About the parameter, you can, though not through linq: you can create your own DbFunctionCallExpression class (based on the one in the sourcecode) and simply not emit the parameter name but the value instead.

It's not doable in linq as you don't get access to the generated elements.