English 中文(简体)
Linq-To-Sql Dynamic-Where-Clauses 甚至可能在框架3.5中?
原标题:Are Linq-To-Sql Dynamic-Where-Clauses Even Possible in Framework 3.5?

UPDATE: It Is Now Working
I was able to finally get it completed. A working-example is detailed in an answer below (which I will be able to mark-off in 2 days).


Everything Below Here Was Part of the Original Question

For the last 3 days, I have been trying to build a dynamic-where-clause on a DBML DataContext using code samples from questions posted here and from other sources as well...none have worked!

出于以下原因,我开始问,根据《框架》3.5,这是否甚至是《最低业务安全标准》:

  1. Predicate Builder notes Framework 4.0 on their site.
  2. Some answers here talk about an equivolent Invoke versions in 4.0 (so I have some hope here).
  3. ...I could go on but you get the idea.

我确实遭受损失,似乎“在扼杀时感到悲伤”。 我需要就如何解决这一问题提出一些合理的建议。

Original Version Had SOME Success But Only When:
The ONLY time I had a inkling of success the data came-up (all 6178 rows of it) but no WHERE CLAUSE was applied. This was evidenced by the lack of any WHERE CLAUSE applied into the SQL found in the dataContext.GetCommand(query).CommandText.

Other Version #1 Fails:
And generates this error: "Method System.Object DynamicInvoke(System.Object[]) has no supported translation to SQL."

// 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;
}

Other Version #2 Fails:
And generates this error: "Method Boolean Like(System.String, System.String) cannot be used on the client; it is only for translation to 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;
}
最佳回答

THIS IS THE CORRECT ASWER
Here is the working version for those who need it. The issue was a COMBINATION of things. The first of which was the following line was set to True:

var where = PredicateBuilder.True<vw_QuickFindResult>

页: 1 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;
}
问题回答

Did you try building the query yourself using only Exression classes? There should be no particular problems there. It is actually relatively easy to learn. You can write a sample query, and then in debugging see how it is composed:

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

Then simply look at the exp variable in the watch, and you can see the structure. This particular example should be composed like this:

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

对于我能告诉你的话,我于2010年初利用LinqKit和PredicateBuilder, 包括Net 3.5、EF1.0和EF Poco改编器。 之后,LinqKit被汇编为净额3.5。

Maybe if you ask the author (Albahari), he could send you (or post on the site) the 3.5 version of that. I don t have it anymore because it is in projects at my old workplace and I don t have access to them.

作为一则,我感到,在将近两年的第4号网络周围,你不得不与3.5人合作。





相关问题
Anyone feel like passing it forward?

I m the only developer in my company, and am getting along well as an autodidact, but I know I m missing out on the education one gets from working with and having code reviewed by more senior devs. ...

NSArray s, Primitive types and Boxing Oh My!

I m pretty new to the Objective-C world and I have a long history with .net/C# so naturally I m inclined to use my C# wits. Now here s the question: I feel really inclined to create some type of ...

C# Marshal / Pinvoke CBitmap?

I cannot figure out how to marshal a C++ CBitmap to a C# Bitmap or Image class. My import looks like this: [DllImport(@"test.dll", CharSet = CharSet.Unicode)] public static extern IntPtr ...

How to Use Ghostscript DLL to convert PDF to PDF/A

How to user GhostScript DLL to convert PDF to PDF/A. I know I kind of have to call the exported function of gsdll32.dll whose name is gsapi_init_with_args, but how do i pass the right arguments? BTW, ...

Linqy no matchy

Maybe it s something I m doing wrong. I m just learning Linq because I m bored. And so far so good. I made a little program and it basically just outputs all matches (foreach) into a label control. ...

热门标签