Linq Compiled Query using Contains (Like SQLs IN statement)

Question

I'm in the process of speeding up Linq to SQL queries in a web app by converting them to Compiled queries and I ran into a problem converting a very simple statement.

I want to compile a simple statement that uses the following parameters to get valid employees from the database:

TestParams myParams = new TestParams
{
    ValidEmps = new int[] { 1, 2, 3 }
};

Here is the working query:

IQueryable<Employees> MySelectedEmps =
    from emps in db.Employees
    where myParams.ValidEmps.Contains(emps.EmployeeID)
    select emps;

Here is my attempt at compiling it:

private static Func<MyDataContext, TestParams, IQueryable<Employee>> myCompiledQuery =
    CompiledQuery.Compile((MyDataContext db, TestParams myParams) =>
     from emps in db.Employees
     where myParams.ValidEmps.Contains(emps.EmployeeID)
     select emps);

This statement with compile and build, but when I run it, I receive the following run time error:

Comparison operators not supported for type 'System.Int32[]'

I have also tried passing a List and an IEnumerable with the same error message.

I have also tried replacing the .Contains statement with a .Any(valemp => valemp == emps.EmployeeID) statement but I still get the same error.

Is it possible to have a compiled query that uses the equivalent of the SQL "IN" statement? What am I doing wrong?

Answer 1

call a stored procedure, and pass in a CVS list of values, using this method:

Before you use my function, you need to set up a "helper" table, you only need to do this one time per database:

CREATE TABLE Numbers
(Number int  NOT NULL,
    CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
    SET @x=@x+1
    INSERT INTO Numbers VALUES (@x)
END

use this function to split your string, which does not loop and is very fast:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn              char(1)              --REQUIRED, the character to split the @List string on
    ,@List                 varchar(8000)        --REQUIRED, the list to split apart
)
RETURNS
@ParsedList table
(
    ListValue varchar(500)
)
AS
BEGIN

/**
Takes the given @List string and splits it apart based on the given @SplitOn character.
A table is returned, one row per split item, with a column name "ListValue".
This function workes for fixed or variable lenght items.
Empty and null items will not be included in the results set.


Returns a table, one row per item in the list, with a column name "ListValue"

EXAMPLE:
----------
SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B')

    returns:
        ListValue  
        -----------
        1
        12
        123
        1234
        54321
        6
        A
        *
        |||
        B

        (10 row(s) affected)

**/



----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
INSERT INTO @ParsedList
        (ListValue)
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''



RETURN

END --Function FN_ListToTable

you can use this function as a table in a join:

SELECT
    Col1, COl2, Col3...
    FROM  YourTable
        INNER JOIN FN_ListToTable(',',@YourString) s ON  YourTable.ID = s.ListValue

so for Linq, create a stored procedure:

CREATE PROCEDURE YourProcedure (

 @param1  int
,@param2  varchar(8000) --csv list is here

) as

SELECT
    Col1, COl2, Col3...
    FROM YourTable
        INNER JOIN FN_ListToTable(',',@param2  ) s ON  YourTable.ID = s.ListValue
    WHERE Col1=@param1

go

Answer 2

Doesn't the

CompiledQuery.Compile

function need to be

CompiledQuery.Compile<MyDataContext, TestParams, IQueryable<Employee>>

instead?

And have you tried using an extension for Int32 (i asume EmployeeID is an Int32) with an In function?

public static class Int32Extensions
{
    public static bool In(this int input, List<int> list)
    {
        if (list == null || list.Count == 0) return false;
        return list.Contains(input);
    }
}

private static Func<MyDataContext, TestParams, IQueryable<Employee>> myCompiledQuery =  
  CompiledQuery.Compile<MyDataContext, TestParams, IQueryable<Employee>>((MyDataContext db, TestParams myParams) =>
    from emps in db.Employees
    where emps.EmployeeID.In(myParams.ValidEmps)
    select emps);

Grtz,

Answer 3

I ran into this same problem once, and I'm pretty sure (I'll have to research) that I was able to solve the problem by creating a class with a method on it that returns a boolean that I would then pass the item from the data row and return true/false.