如何通过多个任意值搜索实体

Question

我有一个customer实体，我希望能够通过多个任意属性搜索其记录。

例如：

Dictionary<string, string> searchList = new Dictionary<string, string>();
searchList.Add("LastName", "Foo");
searchList.Add("FirstName", "Bar");

我当然可以这样写：

foreach (KeyValuePair<string, string> kv in searchList)
{
switch (kv.Key)
{
case "FirstName" :
List<Customer> someResultPart = this._dbSet.Where(customer => customer.FirstName == kv.Value).ToList();
break;
case "LastName" :
// etc.
}
}
// Do intersection of all result parts ...

除了查询n次并在以后相交显然很模糊之外，我更希望没有switch语句(因为每当Customer类中的任何内容发生变化时，它都会迫使我更改它)

有没有办法：

• "动态"构建基于searchList字典的查询，从而消除了对switch构造的需求
• 将各种条件"连接"到单个数据库查询中

Answer 1

您可以使用动态LINQ

string predicate = String.Format("{0} = "{1}"", kv.Key, kv.Value);
List<Customer> someResultPart = _dbSet.Where(predicate);

或者这样：

List<Customer> someResultPart = _dbSet.Where("{0}");

我甚至构建了这样的完整搜索谓词：

Dictionary<string, object> searchList = new Dictionary<string, object>();
searchList.Add("LastName", "Foo");
searchList.Add("FirstName", "Bar");
searchList.Add("Id", 42); // yep, not only strings
var conditions = searchList.Select((kvp, i) => String.Format("{0} = @{1}", kvp.Key, i));
string predicate = String.Join(" and ", conditions);
object[] values = searchList.Select(kvp => kvp.Value).ToArray();
var query = _dbSet.Where(predicate, values);

Answer 2

对于第二点，您可以执行：

IQueryable<Customer> filtered = this._dbSet;
foreach (KeyValuePair<string, string> kv in searchList)
{
switch (kv.Key)
{
case "FirstName" :
filtered = filtered
.Where(customer => customer.FirstName == kv.Value);
break;
case "LastName" :
// etc.
}
}
List<Customer> resultList = filtered.ToList();

Answer 3

确保类型安全！

如果有一天你想更改列名，最好构造一个返回良好属性的函数列表，而不是声明一个失败的字符串变量：

public class Customer { public string FirstName; public string LastName;}
public class CustomerFilter { public Func<Customer, string> Selector; public string Filter; }
[TestMethod()]
public void DynamicFilterTest()
{
var jonSkeet = new Customer() { FirstName = "Jon", LastName = "Skeet" };
var joelOnSoftware = new Customer() { FirstName = "Joel", LastName = "OnSoftware" };
var customers = new List<Customer>() { jonSkeet, joelOnSoftware };
var jonSkeetFilters = new List<CustomerFilter>() { 
new CustomerFilter() { Selector = c => c.LastName, Filter = "Skeet" },
new CustomerFilter() { Selector = c => c.FirstName, Filter = "Jon" }};
var query = customers.AsEnumerable();
foreach (var filter in jonSkeetFilters)
{
query = query.Where(c => filter.Selector.Invoke(c) == filter.Filter);
}
var result = query.ToList();
Assert.AreEqual(1, result.Count);
Assert.AreEqual(jonSkeet, result.Single());
}

然后，您可以很容易地进行重构，以便对任何实体使用相同的模式：

public class WhereClause<T>
{
private readonly Func<T, string> _selector;
public Func<T, string> Selector { get { return _selector; } }
private readonly string _value;
public string Value { get { return _value; } }
public WhereClause(Func<T, string> selector, string value)
{
this._selector = selector;
this._value = value;
}
/// <summary>
/// Append the where clause to the given query
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public IEnumerable<T> AppendTo(IEnumerable<T> query)
{
return query.Where(c => this.Selector.Invoke(c) == this.Value);
}

/// <summary>
/// Append the wheres clauses to the given query
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public static IEnumerable<T> AppendTo(IEnumerable<T> query, IEnumerable<WhereClause<T>> wheres)
{
var filteredQuery = query;
foreach (var where in wheres)
{
filteredQuery = where.AppendTo(filteredQuery);
}
return filteredQuery;
}
}
[TestClass]
public class WhereClauseTests
{
public class Customer { public string FirstName; public string LastName;}
[TestMethod()]
public void WhereClauseTest()
{
var jonSkeet = new Customer() { FirstName = "Jon", LastName = "Skeet" };
var joelOnSoftware = new Customer() { FirstName = "Joel", LastName = "OnSoftware" };
var customers = new List<Customer>() { jonSkeet, joelOnSoftware };
var jonSkeetWheres = new List<WhereClause<Customer>>() { 
new WhereClause<Customer>(c => c.LastName, "Skeet"),
new WhereClause<Customer>(c => c.FirstName,  "Jon" )
};
var query = WhereClause<Customer>.AppendTo(customers, jonSkeetWheres);
var result = query.ToList();
Assert.AreEqual(1, result.Count);
Assert.AreEqual(jonSkeet, result.Single());
}
}