How to use a string to search varbinary columns in SQL Server 2008+ using Linq to Entities

Question

My environment is .NET Framework 4.5.1, C#, Entity Framework 6.1.3 (Object Context), SQL Server 2008+, ASP.NET MVC5.

I have a table in SQL Server with documents objects begin stored in a varbinary(max) column.

I am rewriting an existing web application within which we give the users the ability to enter a search string via a textbox ~ which then searches through each record in the table and returns a list of records that contain the user input search string.

Currently the SQL query, including the user specified search string is built as a string command, and then executed via ADO.NET data adapter and passed to SQL Server and works perfectly.

Here's a test example of the complete SQL query that is built, including the user specified search string that currently runs (taken from SQL Server profiler)

SELECT TOP 1000000 * 
FROM 
    (SELECT DISTINCT docInfo.docInfoID 
     FROM docFile 
     INNER JOIN docInfo ON docInfo.docFileID = docFile.docFileID
     INNER JOIN SOInfoArchive ON SOInfoArchive.docInfoID = docInfo.docInfoID
     WHERE (docInfo.docType LIKE 'SYSOUT')
       AND (SOInfoArchive.serverInfoID IN (1)) 
       AND CONTAINS(docFileObject, 'REM')
   )

The above query successfully returns 89,576 records.

I would like to replace the current process of building the SQL query as a string command within my C# code by using LINQ to Entities.

I am using LINQ to Entities for all of my SQL server interactions through out the project.

However, I am unable to pass the user supplied string to my LINQ queries in such a way that I can replace the SQL command string query as shown above.

At first (wrongly) I thought I could use the .Contains() method and pass to it the user submitted string converted into bytes, an example as follows :

string input = "orderno=012p92"; //passed from the user via textbox
var bytes = System.Text.Encoding.Unicode.GetBytes(input); // convert the string to binary ready for searching the varbinary fields

using (SysviewEntities context = new SysviewEntities())
{
    var docs = from df in context.docFile
               where df.docFileObject.Contains('bytes')
               select df;
}

But this will not even compile as I get the error

'byte[]' does not contain a definition for 'Contains' and the best extension method overload 'System.Linq.ParallelEnumerable.Contains(System.Linq.ParallelQuery, TSource)' has some invalid arguments

I then researched some similar questions that had been asked here on SO and thought the solution would be to use an equals evaluation operator replacing the .Contains() method as follows:

string theString = "orderno=*012p92";
byte[] theBytes = Encoding.Unicode.GetBytes(theString);

using (SysviewEntities context = new SysviewEntities())
{
    string input = "orderno=012p92";
    var bytes = System.Text.Encoding.Unicode.GetBytes(input);

    var docs = (from df in context.docFile
                where df.docFileObject == bytes
                select df).ToList();                
}

Now the query compiles but returns NO results ~ the resultant SQL query passed to SQL Server (from SQL Profiler) from my LINQ query is

exec sp_executesql N'SELECT 
    [Extent1].[docFileID] AS [docFileID], 
    [Extent1].[docFileHash] AS [docFileHash], 
    [Extent1].[docFileObject] AS [docFileObject], 
    [Extent1].[docFilterType] AS [docFilterType]
    FROM [dbo].[docFile] AS [Extent1]
    WHERE [Extent1].[docFileObject] = @p__linq__0',N'@p__linq__0 varbinary(8000)',@p__linq__0=0x6F0072006400650072006E006F003D00300031003200700039003200

So my binary conversion of the search string obviously has not worked.

All the other questions that I've read here at SO that seem similar, don't seem to apply to what I'm trying to achieve here ~ although I do concede it is very possible that I have just not understood the recommended and suggested solutions correctly.

So to summarise my questions are

Is it possible to easily duplicate this SQL query using LINQ to Entities?

SELECT TOP 1000000 * 
FROM 
    (SELECT DISTINCT docInfo.docInfoID 
     FROM docFile 
     INNER JOIN docInfo ON docInfo.docFileID = docFile.docFileID
     INNER JOIN SOInfoArchive ON SOInfoArchive.docInfoID = docInfo.docInfoID
     WHERE (docInfo.docType LIKE 'SYSOUT')
       AND (SOInfoArchive.serverInfoID IN (1)) 
       AND CONTAINS(docFileObject, 'REM')
    )

How / why does this part of the SQL query
```
AND CONTAINS(docFileObject, 'REM')
```
take the string (in this instance REM) and search through all the varbinary docFileObjects in the table to find records that match the search string REM ?

There must be some sort of conversion going on somewhere?
How do I replicate this conversion successfully so that I can pass a converted string value to my LINQ query?
Am I better off using the current process and not using LINQ to Entities for this specific functionality.

I would be very grateful to hear from you if you can explain to me in simple terms what it is that I'm doing wrong, what are the issues of trying to do this using LINQ to Entities and any simple suggestions as to what I can do to get a solution.

score 0 · Accepted Answer · edited May 23 '17 at 11:44

The bottom line is that I cannot use the LINQ to Entities .Contains method to pass a string variable to a SQL Contains statement where the SQL column is Varbinary format.

My solution was to create a stored procedure in SQL and pass the string variable as an input nvarchar within my stored procedure.

My first attempt to do this using temporary SQL tables failed (the stored procedure worked ~ but I was unable to consume the data within EF because EF could not 'see' the metadata of the columns my stored procedure was trying to return).

I eventually solved the problem by using SQL temporary variables.

Both my failed process and the way I resolved that problem are documented in the following SO post

(Additional) EF can't infer return schema from stored procedure selecting from a #temp table

How to use a string to search varbinary columns in SQL Server 2008+ using Linq to Entities

1 Answers1