什么是正确的方式来动态地添加的条款人数不详到LINQ 2 SQL查询？ [英] What is the correct way to dynamically add an undetermined number of clauses to a Linq 2 Sql query?

问题描述

该函数用于返回联系人列表为用户搜索输入。的搜索术语的数目总是至少有一个，但也可以是许多

This function is used to return a contact list for a users search input. The number of search terms is always at least one, but could be many.

public IList<Contact> GetContacts(string[] searchTerms)
{
    using (dbDataContext db = new dbDataContext())
    {
        var contacts = from _contacts in db.Contacts
                       orderby _contacts.LastName ascending, _contacts.FirstName ascending
                       select _contacts;

        foreach (string term in searchTerms)
        {
            contacts = (IOrderedQueryable<Contact>)contacts.Where(x => SqlMethods.Like(x.FirstName, "%" + term + "%")
                                                                    || SqlMethods.Like(x.MiddleName, "%" + term + "%")
                                                                    || SqlMethods.Like(x.LastName, "%" + term + "%")
                                                                    || SqlMethods.Like(x.PreferredName, "%" + term + "%"));
        }

        return contacts.ToList<Contact>();
    }
}

的问题是在循环。只有最后的搜索字词使用过，即使生成的SQL看起来是正确的（如第正确金额的项数生成）。

The problem is in the loop. Only the last search term is ever used, even though the generated sql looks correct (as in the correct amount of clauses are generated for the number of terms).

例如 - 如果我通过两个术语（安德烈，SM），生成的SQL节目条款两块为expeted，但仅使用平方米作为两个块的参数

Example - if I pass two terms ('andr','sm'), the sql generated shows two blocks of clauses as expeted, but only uses 'sm' as the param in both blocks.

我是什么做错了吗？应我甚至可以用SqlMethods？

What am I doing wrong? Should I even be using SqlMethods?

推荐答案

也许问题是捕获循环变量的术语。 试试这个：

Maybe the problem is with capturing the loop variable term. Try this:

foreach (string term in searchTerms) 
{
    string t = term;  
    contacts = ... // use t instead of term
}

这篇关于什么是正确的方式来动态地添加的条款人数不详到LINQ 2 SQL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！