user3617629 user3617629 - 2 months ago 4
SQL Question

Extending IQueryable<T> to add a property

I have extended a custom search for IQueryable using Expression trees and it works fine. There are two set of search results in this custom search that gets merged together at the very last line using the Concat method. I would like to retain the order by showing firstRankResult first and secondRankResult second. The current code almost works until it gets sent to a asp:gridview with asp:linqdatasource with pagination and for some reason beyond my understanding LinqToSql throws an OrderBy in the beginning messing the order up. The end end result query then becomes something like:

SELECT Row_Number() Over( Order By ...all fields...) ...

This messes the order of results and the first set no longer shows on the top of the list.

Long story short, I thought of adding a SearchRank field to each resultset and at the end force it to order by SearchRank. So if each IQueryable produces this SQL:
could I make it be
SELECT 1 AS [SearchRank],F1,F2 FROM T1
and similar for the next IQueryable. Then I concat them together with order by SearchRank. Any idea if this can be done using dynamic expression trees?

public static IQueryable<T> RankedSearch<T>(this IQueryable<T> source, string[] fieldNames, string[] searchKeywords)
if (source == null)
throw new ArgumentNullException();

//Building Expression Tree
var string0Expression = Expression.Constant("0", typeof(string));
var string1Expression = Expression.Constant("1", typeof(string));
var alwaysTrueExpression = Expression.Equal(string0Expression, string0Expression);
var alwaysFalseExpression = Expression.Equal(string0Expression, string1Expression);

Expression firstRankExpression = alwaysTrueExpression;
Expression secondRankExpression = alwaysFalseExpression;
var containsMethod = typeof(string).GetMethod("Contains", new[] { typeof(string) });
ParameterExpression pe = Expression.Parameter(typeof(T), "type");

foreach (var keyword in searchKeywords)
Expression cumContainsExpression = alwaysFalseExpression;
Expression valueExpression = Expression.Constant(keyword, typeof(string));
foreach (var fieldName in fieldNames)
Expression propertyExpression;
var fieldNameTree = fieldName.Split('.');
if (fieldNameTree.Length > 1)
var fieldParent = Expression.Property(pe, fieldNameTree[0]);
propertyExpression = Expression.Property(fieldParent, fieldNameTree[1]);
propertyExpression = Expression.Property(pe, fieldName);
Expression containsExpression = Expression.Call(propertyExpression, containsMethod, valueExpression);
cumContainsExpression = Expression.OrElse(cumContainsExpression, containsExpression);
firstRankExpression = Expression.AndAlso(firstRankExpression, cumContainsExpression);
secondRankExpression = Expression.OrElse(secondRankExpression, cumContainsExpression);

MethodCallExpression whereCallExpressionFirstRank = Expression.Call(
new Type[] { source.ElementType },
Expression.Lambda<Func<T, bool>>(firstRankExpression, new ParameterExpression[] { pe }));
IQueryable<T> firstRankResult = source.Provider.CreateQuery<T>(whereCallExpressionFirstRank);
//This becomes SELECT F1,F2 FROM T1 WHERE (F1 LIKE '%keyword%' AND F2 LIKE '%keyword%')
//DESIRED OUTPUT SELECT 1 AS [SearchRank], F1,F2 FROM T1 WHERE (F1 LIKE '%keyword%' AND F2 LIKE '%keyword%')

MethodCallExpression whereCallExpressionSecondRank = Expression.Call(
new Type[] { source.ElementType },
Expression.Lambda<Func<T, bool>>(secondRankExpression, new ParameterExpression[] { pe }));
IQueryable<T> secondRankResult = source.Provider.CreateQuery<T>(whereCallExpressionSecondRank);
//This becomes SELECT F1,F2 FROM T1 WHERE (F1 LIKE '%keyword%' OR F2 LIKE '%keyword%')
//DESIRED OUTPUT SELECT 2 AS [SearchRank], F1,F2 FROM T1 WHERE (F1 LIKE '%keyword%' OR F2 LIKE '%keyword%')

return firstRankResult.Concat(secondRankResult.Except(firstRankResult));


I figured it out.

var firstRankResult1 = firstRankResult.Select(r => new
    baseType = r,
    RankId = 1
var secondRankResult1 = secondRankResult.Except(firstRankResult).Select(r => new
    baseType = r,
    RankId = 2

var unionedResult = firstRankResult1.Concat(secondRankResult1).OrderBy(o => o.RankId).Select(o => o.baseType);
return unionedResult;