当前位置: 动力学知识库 > 问答 > 编程问答 >

c# - Create WHERE condition with multiple columns

问题描述:

I have the following method:

protected override List<Contractor> GetSearchResults()

{

List<User> users = UnitOfWork.UserRepository.Get(

user =>

(!String.IsNullOrEmpty(FirstName) && user.FirstName.Contains(FirstName)) &&

(!String.IsNullOrEmpty(LastName) && user.LastName.Contains(LastName)))

.ToList();

return users ;

}

This is just a "simplified" example. In reality I have some more "columns" for my where condition.

Both "conditions" are linked to with an and-operator (&&). This means:

  • First name has to be filled out & must match AND
  • Second name has to be filled out & must match

This is not exactly what I want. Changing the code using an or-operator (||) like

protected override List<Contractor> GetSearchResults()

{

List<User> users = UnitOfWork.UserRepository.Get(

user =>

(!String.IsNullOrEmpty(FirstName) && user.FirstName.Contains(FirstName)) ||

(!String.IsNullOrEmpty(LastName) && user.LastName.Contains(LastName)))

.ToList();

return users ;

}

doesn't provide the wanted solution because:

  • First name has to be filled out & must match OR
  • Second name has to be filled out & match

So, what do I want?

For example, if I have to following records in my database:

  • White, Paul
  • Miller, John
  • Miller, Lea

I expect the following results:

  • If only the last name "Miller" is entered I'd like to get the two

    records.

  • If last name and first name are entered ("Miller" and "John") I expect to get only one record.

My conditions just don't fit to those requirements and I have no clue how to "simply" change them.

Would this fit better:

protected override List<Contractor> GetSearchResults()

{

List<User> users = UnitOfWork.UserRepository.Get(

user =>

(String.IsNullOrEmpty(FirstName) || (!String.IsNullOrEmpty(FirstName) && user.FirstName.Contains(FirstName))) &&

(String.IsNullOrEmpty(LastName) || (!String.IsNullOrEmpty(LastName) && user.LastName.Contains(LastName))))

.ToList();

return users ;

}

This seems to work. But with more and more columns it definitely looks ugly and is hard to read. Is there any way to simplify this task?

网友答案:

I guess you can simplify your last piece of code to something like:

List<User> users = UnitOfWork.UserRepository.Get(
           user =>
           ( String.IsNullOrEmpty( FirstName ) || user.FirstName.Contains( FirstName ) ) &&
           ( String.IsNullOrEmpty( LastName ) || user.LastName.Contains( LastName ) ) 
           ).ToList();
网友答案:

As a start, you could create a helper function to get the conditionals out of the main code

public bool FilterColumn(string columnData, string filterData) 
{
    return String.IsNullOrEmpty(filterData) || columnData.Contains(filterData);
}

which could then be used like this

List<User> users = UnitOfWork.UserRepository.Get(
           user => FilterColumn(user.FirstName, FirstName) &&
                   FilterColumn(user.LastName, LastName)).ToList()

Assuming that this code is going to limited by user input, you can also use reflection to help out a little (assuming the filter are local to the class as in the example)

  private bool FilterColumn(User user, string columnName) 
  {
     var filterValue = (string) GetType ().GetProperty (columnName).GetValue (this);
     var userValue = (string) GetType ().GetProperty (columnName).GetValue (user);

     return string.IsNullOrWhiteSpace (userValue) || userValue.Contains (filterValue);
  }

Then you can create an aggregation function to collect the columns

  public bool FilterByColumnNames(User user, params string[] columns) 
  {
     return columns.Aggregate (true, (result, columnValue) => FilterColumn (user, columnValue));
  }

This method could be used as follows

var result = FilterByColumnNames(User user, "FirstName", "LastName");

Now, this isn't very refactoring friendly so we will construct some more helper functions using expressions

  public String ToColumnName(Expression<Func<User,string>> column)
  {
     return ((MemberExpression)column.Body).Member.Name;
  }

  public bool FilterByColumns(User user, params Expression<Func<User,string>>[] columns) 
  {
     return FilterByColumnNames (user, columns.Select (ToColumnName).ToArray());
  }

Which can then be used like this

     var x = FilterByColumns (User, 
                u => u.FirstName,
                u => u.LastName);

Now, this is an unoptimized version, you may be able to dig deeper into the expressions to create optimized versions of the lookups if performance is an issue, but for most things bound to user typing, this should be fast enough.

分享给朋友:
您可能感兴趣的文章:
随机阅读: