@Tsaukpaetra tries to search for expressions

Tsaukpaetra

Trigger post

@izzion said in The "Good news, everybody: we’re safe from Skynet!" Rant:

@tsaukpaetra said in The "Good news, everybody: we’re safe from Skynet!" Rant:

Of course, now I'm struggling to make an Expression extension to dynamically select rows using a Contains for each of the selected columns. That's throwing me for a loop, and even StackOverflow isn't helping very much...

Feel free to hit me in a DM (or create a different thread and ping me) if you would like. If you can provide a little more information about what you're trying to do, I can probably help query build.

So I'm working on a C# helper class that turns the results of an IQueryable collection into output suitable for the DataTables for jQuery plugin. So far it seems to work very well, you just drop in your IQueryable (or IEnumerable) and return the results back to the http client.

At the moment I'm attempting to implement the Search capability to this, which will ideally cause the IQueryable to gain a where (col1 like '%blah%' or col4 like '%blah%') predicate. As an added bonus, I'd like it to work with database queries too (i.e. Entity Framework)

My latest attempt looks like this:


        public static DataTablesResult<T> getDataTablesResult<T>(int start, int length, int draw, IQueryable<T> result, int totalRecords = 0, DataTableParams.Search search = null, DataTableParams.Order[] order = null, DataTableParams.Column[] columns = null)
        {
            //Snip some setup code //

            //apply Filter (if specified)
            if (search != null && search.value != null && result.FirstOrDefault() != null)
            {
                //This implementation is INCOMPLETE. It should check all the columns[] if they are Searchable or not
                //https://datatables.net/manual/server-side
                foreach (PropertyInfo searchkey in typeof(T).GetProperties())
                {

                    if (searchkey != null)
                    {
                        DataTableParams.Column d = columns.Where(x => x.data == searchkey.Name).FirstOrDefault();
                        if (d != null)
                            result = result.Where(c => searchkey.GetValue(c).ToString().Contains(search.value));
                            
                    }
                }
            }

//Snip the rest of the generation code
    }

This doesn't work, because Entity Framework doesn't understand the GetValue function.

So, searching StackOverflow, I bodged together an expression that looks like this:

        public static Expression<Func<T, bool>> ContainsString<T>(string propertyName, string propertyValue)
        {
            var parameterExp = Expression.Parameter(typeof(T), "type");
            var propertyExp = Expression.Property(parameterExp, propertyName);
            MethodInfo method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
            var someValue = Expression.Constant(propertyValue, typeof(string));
            var containsMethodExp = Expression.Call(propertyExp, method, someValue);

            return Expression.Lambda<Func<T, bool>>(containsMethodExp, parameterExp);
        }

Which I think I can invoke like this:

                //Apply the global search
                foreach (DataTableParams.Column c in columns)
                {
                    result = result.Where(ContainsString(c.data,search.value));
                }

But... um.... it hates me, saying it can't infer the arguments or something.
I believe this code would also technically generate predicate that looks like this where (col1 like '%blah%') AND (col2 like '%blah%').

What else can I try?

AlexMedia

Have you tried invoking your custom expression like a lambda?

result = result.Where(r => ContainsString(r.data,search.value));

Tsaukpaetra

@alexmedia said in @Tsaukpaetra tries to search for expressions:

Have you tried invoking your custom expression like a lambda?
result = result.Where(r => ContainsString(r.data,search.value));

The problem is that I need to pass in the columns that need searching, and my expression (the way it's written now) doesn't like that either.

My next attempt is to use the predicate builder at https://petemontgomery.wordpress.com/2011/02/10/a-universal-predicatebuilder/

        public static IQueryable<T> ApplySearch<T>(this IQueryable<T> q, DataTableParams.Column[] cols, DataTableParams.Search search)
        {
            var pred = PredicateBuilder.False<T>();

            foreach (DataTableParams.Column c in cols.Where(x=>x.searchable))
            {

                PropertyInfo searchkey = q.FirstOrDefault().GetType().GetProperty(c.data);
                if (searchkey != null)
                    pred.Or(p => searchkey.GetValue(p).ToString().Contains(search.value))
                    ;
            }

            return q.Where(pred);
    }

I'm currently compiling and about to test if this even tries to work.

Tsaukpaetra

@tsaukpaetra said in @Tsaukpaetra tries to search for expressions:

this even tries to work.

Hmm, it tries, but replaces the original IQueryable with a new one that doesn't make any sense.

Tsaukpaetra

@tsaukpaetra said in @Tsaukpaetra tries to search for expressions:

@tsaukpaetra said in @Tsaukpaetra tries to search for expressions:

this even tries to work.

Hmm, it tries, but replaces the original IQueryable with a new one that doesn't make any sense.

Oh, that was because I wasn't re-assigning the predicate to the results of the Or.

But that just means I'm back to being stuck with EF not being able to understand GetValue...

izzion

@tsaukpaetra
Depending on how many columns there are that are potentially searchable, you could set up a static stored procedure that you call from EF and let the stored procedure use dynamic SQL to build the query on the fly.

So, you might have EF call:

EXEC dbo.usp_SearchProcedure 
    @col1 = NULL
    ,@col2min = -999.99
    ,@col2max = 999.99
    ,@col3min = NULL
    ,@col3max = NULL
    ,@col4 = 'Bob'

And then the back end procedure would look like:

DECLARE @Query VARCHAR(MAX) 
SET @Query = 'SELECT col1,col2,col3,col4,col5,col6 FROM table t WHERE activeRecord = 1'

IF @col1 IS NOT NULL
    SET @Query += ' AND col1 = @P_col1'

IF @col2min IS NOT NULL
BEGIN
    IF @col2max IS NOT NULL
        SET @Query += ' AND col2 BETWEEN @P_col2min AND @P_col2max'
    ELSE
        SET @Query += ' AND col2 > @P_col2min'
END
ELSE
BEGIN
    IF @col2max IS NOT NULL
        SET @Query += ' AND col2 < @P_col2max'
END

-- snip 

-- Execute the dynamically constructed query
sp_executesql @Query
-- Define the column types that we will pass in as parameters to the query (your search column parameters, from above, with the column types that match the underlying table(s)
,N'@P_col1 VARCHAR(25), @P_col2min DECIMAL(6,2), @P_col2max DECIMAL(6,2), @P_col3min INT, @P_col3max INT, @P_col4 INT'
-- Passthrough the parameters
,@P_col1 = @col1
,@P_col2min = @col2min
,@P_col2max = @col2max
,@P_col3min = @col3min
,@P_col3max = @col3max
,@P_col4 = @col4

Advantage you pick up here is that SQL optimizes each combination of search parameters as a separate plan (thus not potentially searching the whole table to find the things with values between the col2min and col2max when you pass in a specific user id and no value search), and can still cache the plan for each combination of parameters so it doesn't have to completely recompile every single search.

Tsaukpaetra

@izzion I know I can do this database side (that trick does look neat), but there would be no way to call this dynamically for any particular IQueryable, I'd have to hand-craft the stored procedure for each table.

I found another snippet that I think might work:

                    var param = Expression.Parameter(typeof(T), "p");
                    var prop = Expression.Property(param, search.value);
                    var exp = Expression.Lambda(prop, param);
                    Type[] types = new Type[] { q.ElementType, exp.Body.Type };
                    var mce = Expression.Call(typeof(Queryable), "Contains", types, q.Expression, exp);

q.Provider.CreateQuery<T>(mce);

But I don't know how to chain these into an Or statement...

Ugh...

Tsaukpaetra

@tsaukpaetra said in @Tsaukpaetra tries to search for expressions:

Ugh...

Additional information: No coercion operator is defined between types 'System.Int32' and 'System.String'.

Wat. well then...

Tsaukpaetra

@tsaukpaetra said in @Tsaukpaetra tries to search for expressions:

Wat. well then...

Almost there, but what is this?

The method 'Skip' is only supported for sorted input in LINQ to Entities. The method 'OrderBy' must be called before the method 'Skip'.

Apparently if the resultset contains no items, you can't .Skip(0). Because reasons.

At least, that's how I'm interpreting the error, since it was definitely sorted by the first column!

Tsaukpaetra

@tsaukpaetra said in @Tsaukpaetra tries to search for expressions:

Almost there

I think I got it!



        public static Expression<Func<T, bool>> ContainsString<T>(string propertyName, string propertyValue)
        {
            //Specify that we're getting the type info in order to convert it to string
            var parameterExp = Expression.Parameter(typeof(T), "type");
            //Thee property we're searching
            var propertyExp = Expression.Property(parameterExp, propertyName);
            //Expression to ensure it's a string
            var propertyExpToString = Expression.Call(propertyExp, propertyExp.Type.GetMethod("ToString", new Type[] { }));
            //The method we're calling on that property
            MethodInfo method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
            //The thing we're searching for
            var searchValue = Expression.Constant(propertyValue, typeof(string));
            //Build the expression
            var containsMethodExp = Expression.Call(propertyExpToString, method, searchValue);
            //And give it back to the caller
            return Expression.Lambda<Func<T, bool>>(containsMethodExp, parameterExp);
        }

        public static IQueryable<T> ApplySearch<T>(this IQueryable<T> q, DataTableParams.Column[] cols, DataTableParams.Search search)
        {
            //Start by matching none by default
            var pred = PredicateBuilder.False<T>();

            foreach (DataTableParams.Column c in cols.Where(x=>x.searchable && x.data != null))
            {
                //Check if we have a property by that name
                PropertyInfo searchkey = q.FirstOrDefault().GetType().GetProperty(c.data);
                if (searchkey != null)
                    //We do, add it to the search!
                   pred = PredicateBuilder.Or(pred,ContainsString<T>(c.data, search.value));
            }
            //Spit back the queryable with the search added
            return q.Where(pred);
        }

Now I just need to figure out how to test for the edge cases (when we're not using any kind of test framework)....

Tsaukpaetra

@tsaukpaetra said in @Tsaukpaetra tries to search for expressions:

Now

Oh, and also this is the kind of query it makes:

SELECT 
    [Extent1].[PlayerID] AS [PlayerID], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[LevelName] AS [LevelName], 
    [Extent1].[AspNetUserID] AS [AspNetUserID], 
    [Extent2].[UserName] AS [UserName]
    FROM  [dbo].[CHAR_Characters] AS [Extent1]
    INNER JOIN [dbo].[AspNetUsers] AS [Extent2] ON [Extent1].[AspNetUserID] = [Extent2].[Id]
    WHERE ( CAST( [Extent1].[PlayerID] AS nvarchar(max)) LIKE N'%test00%') OR ([Extent1].[Name] LIKE N'%test00%') OR (CASE WHEN ([Extent1].[LevelName] IS NULL) THEN N'' ELSE [Extent1].[LevelName] END LIKE N'%test00%')

Not terribad all things considered. At least it didn't re-cast the strings.

swayde

@tsaukpaetra
If PlayerID is int it looks like it's search for '%test00%', which an int obviously never can be.
You might need to modify the search value for non string columns.

Tsaukpaetra

@swayde said in @Tsaukpaetra tries to search for expressions:

@tsaukpaetra
If PlayerID is int it looks like it's search for '%test00%', which an int obviously never can be.
You might need to modify the search value for non string columns.

Yeah, that is supposed to be configured on the front request side. Technically, we might want to be able to punch in the player ID in the search bar and be hunky dory, and ignore that column if the input is impossible to do, but for now coercing all the things to strings (that rhymes!) seems to be working OK for now.