Geek Answers Handbook

Are Linq-To-Sql dynamic clauses even possible in Framework 3.5?

UPDATE: now works.
I was able to finally finish it. A working example is described in detail in the answer below (which I can mark in 2 days).

Everything below was part of the original question.

Over the past 3 days, I have been trying to create a dynamic-where clause on a DBML DataContext using code samples from the questions posted here and from other sources , as well ... no one worked !

For the reasons below, I'm starting to wonder if this is even POSSIBLE within Framework 3.5:

  • Predicate Builder notes Framework 4.0 on its website.
  • Some answers here talk about ambiguous versions Invokein 4.0 (so I hope here).
  • ... I could continue, but you understand.

I am really at a loss and seem to “grab onto the lines” ... and I need good advice on how to approach this.

The original version of Had SOME success But only when:
The only time I had a “suspicious” success was to receive data (all 6178 rows), but it wasn’t WHERE CLAUSE. This is evidenced by the lack of anyone WHERE CLAUSEapplied to SQL, found in dataContext.GetCommand(query).CommandText.

Other versions # 1 Fails:
And it generates this error: The "Method" System.Object DynamicInvoke (System.Object []) 'does not support SQL translation. "

// VERSION 1:
public static class PredicateBuilder
{
    public static Expression<Func<T, bool>> True<T>() { return f => true; }
    public static Expression<Func<T, bool>> False<T>() { return f => false; }

    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1, Expression<Func<T, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>(Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
    }
    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1, Expression<Func<T, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>(Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);
    }
    public static Expression<Func<T, bool>> StringLike<T>(Expression<Func<T, string>> selector, string pattern)
    {
        var predicate = PredicateBuilder.True<T>();
        var parts = pattern.Split('%');
        if (parts.Length == 1) // not '%' sign
        {
            predicate = predicate.And(s => selector.Compile()(s) == pattern);
        }
        else
        {
            for (int i = 0; i < parts.Length; i++)
            {
                string p = parts[i];
                if (p.Length > 0)
                {
                    if (i == 0)
                    {
                        predicate = predicate.And(s => selector.Compile()(s).StartsWith(p));
                    }
                    else if (i == parts.Length - 1)
                    {
                        predicate = predicate.And(s => selector.Compile()(s).EndsWith(p));
                    }
                    else
                    {
                        predicate = predicate.And(s => selector.Compile()(s).Contains(p));
                    }
                }
            }
        }
        return predicate;
    }
}
// VERSION 1:
public List<QuickFindResult> QueryDocuments(string searchText, string customerSiteId, List<int> filterIds)
{
    var where = PredicateBuilder.True<vw_QuickFindResult>();

    var searches = new List<String>(searchText.Split(' '));
    searches.ForEach(productName =>
    {
        string like = productName.Replace('"', '%')
                                 .Replace('*', '%');

        where = PredicateBuilder.StringLike<vw_QuickFindResult>(x => x.DocumentName, like);
    });


    var results = DocumentCollectionService.ListQuickFind(where, null);

    // Do other stuff here...

    return results;
}
// VERSION 1:
public static List<vw_QuickFindResult> ListQuickFind(Expression<Func<vw_QuickFindResult, bool>> where, Expression<Func<vw_QuickFindResult, bool>> orderBy)
{
    var connectionString = GetConnectionString(ES_DOCUMENTS_CONNECTION_NAME);
    List<vw_QuickFindResult> results = null;

    using (HostingEnvironment.Impersonate())
    {
        using (var dataContext = new ES_DocumentsDataContext(connectionString))
        {
            IQueryable<vw_QuickFindResult> query = dataContext.vw_QuickFindResults;
            query = query.Where(where);

            results = query.ToList();
        }
    }

    return results;
}

# 2 Fails:
: "" Boolean Like (System.String, System.String) " , SQL".

// VERSION 2:
public List<QuickFindResult> QueryDocuments(string searchText, string customerSiteId, List<int> filterIds)
{
    Func<vw_QuickFindResult, bool> where = null;
    Func<string, Func<vw_QuickFindResult, bool>> buildKeywordPredicate = like => x => SqlMethods.Like(x.DocumentName, like);
    Func<Func<vw_QuickFindResult, bool>, Func<vw_QuickFindResult, bool>, Func<vw_QuickFindResult, bool>> buildOrPredicate = (pred1, pred2) => x => pred1(x) || pred2(x);

    // Build LIKE Clause for the WHERE
    var searches = new List<String>(searchText.Split(' '));
    searches.ForEach(productName =>
    {
        string like = productName.Replace('"', '%')
                                 .Replace('*', '%');

        where = (where == null) ? buildKeywordPredicate(like) : buildOrPredicate(where, buildKeywordPredicate(like));
    });

    var results = DocumentCollectionService.ListQuickFind(where, null);

    // Do other stuff here...

    return results;
}
// VERSION 2:
public static List<vw_QuickFindResult> ListQuickFind(Expression<Func<vw_QuickFindResult, bool>> where, Expression<Func<vw_QuickFindResult, bool>> orderBy)
{
    var connectionString = GetConnectionString(ES_DOCUMENTS_CONNECTION_NAME);
    List<vw_QuickFindResult> results = null;

    using (HostingEnvironment.Impersonate())
    {
        using (var dataContext = new ES_DocumentsDataContext(connectionString))
        {
            var query = dataContext.vw_QuickFindResults.AsEnumerable();
            query = query.Where(where);

            results = query.ToList();
        }
    }

    return results;
}
+5
3


, . . : True:

var where = PredicateBuilder.True<vw_QuickFindResult>();

False...

var where = PredicateBuilder.False<vw_QuickFindResult>();

, ... .

public static class PredicateBuilder
{
    public static Expression<Func<T, bool>> True<T>() { return f => true; }
    public static Expression<Func<T, bool>> False<T>() { return f => false; }

    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1, Expression<Func<T, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>(Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
    }
    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1, Expression<Func<T, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>(Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);
    }
}
public List<QuickFindResult> QueryDocuments(string searchText, string customerSiteId, List<int> filterIds)
{
    var wildCards = new string[] { "*", "\"" };
    var where = PredicateBuilder.False<vw_QuickFindResult>();
    var searches = new List<String>(searchText.Split(' ')); // TODO: <-- If more complex searches are needed we'll have to use RegularExpressions

    // SEARCH TEXT - WHERE Clause
    searches.ForEach(productName =>
    {
        Boolean hasWildCards = (productName.IndexOfAny(new char[] { '"', '*' }) != -1);
        if (hasWildCards)
        {
            Int32 length = productName.Length;
            if (length > 1)
            {
                string like = productName.Replace("%", "")
                                         .Replace("*", "");

                string first = productName.Substring(0, 1);
                string last = productName.Substring(length - 1);

                // Contains
                if (wildCards.Contains(first) && wildCards.Contains(last))
                    where = where.Or(p => p.DocumentName.Contains(like) ||
                                         p.DocumentTitle.Contains(like));

                // EndsWith
                else if (wildCards.Contains(first))
                    where = where.Or(p => p.DocumentName.EndsWith(like) ||
                                          p.DocumentTitle.EndsWith(like));

                // StartsWith
                else if (wildCards.Contains(last))
                    where = where.Or(p => p.DocumentName.StartsWith(like) ||
                                          p.DocumentTitle.StartsWith(like));

                // Contains (default)
                else
                    where = where.Or(p => p.DocumentName.Contains(like) ||
                                          p.DocumentTitle.Contains(like));
            }
            else // Can only perform a "contains"
                where = where.Or(p => p.DocumentName.Contains(productName) ||
                                             p.DocumentTitle.Contains(productName));
        }
        else // Can only perform a "contains"
            where = where.Or(p => p.DocumentName.Contains(productName) ||
                                         p.DocumentTitle.Contains(productName));
    });

    // FILTER IDS - WHERE Clause
    var filters = GetAllFilters().Where(x => filterIds.Contains(x.Id)).ToList();
    filters.ForEach(filter =>
    {
        if (!filter.IsSection)
            where = where.And(x => x.FilterName == filter.Name);
    });

    var dataSource = DocumentCollectionService.ListQuickFind(where);
    var collection = new List<QuickFindResult>();

    // Other UNRELATED stuff happens here...

    return collection;
}
public static List<vw_QuickFindResult> ListQuickFind(Expression<Func<vw_QuickFindResult, bool>> where)
{
    var connectionString = GetConnectionString(ES_DOCUMENTS_CONNECTION_NAME);
    List<vw_QuickFindResult> results = null;

    using (HostingEnvironment.Impersonate())
    {
        using (var dataContext = new ES_DocumentsDataContext(connectionString))
        {
            var query = dataContext.vw_QuickFindResults.Where(where).OrderBy(x => x.DocumentName).OrderBy(x => x.DocumentTitle);
            results = query.ToList();
        }
    }

    return results;
}
0

, Exression? . . , , :

Expression<Func<string, bool>> exp = (s) => s.Contains("your query");

exp , . :

Expression constant = Expression.Constant("your query");
Expression p = Expression.Param(typeof(string);
Expression contains = Expression.Call(p, "Contains", constant);
Expression<Func<string, bool>> lambda = Expression.Lamba(contains, p);
//  Now you can send this to your ORM
+2

, LinqKit PredicateBuilder 2010 .Net 3.5, EF 1.0 EF Poco Adapter. LinqKit Net 3.5

, (Albahari), ( ) 3.5. , , .

As a side note, I feel that your pain is forced to work with 3.5 after almost two years .Net 4.

0
source

More articles:


All Articles