How to set dynamic column in linq query

Question

I have following code,

public List<MemberDto> GetMembers(out int rowCount,int pageIndex,int pageSize,  string seachBy = "", string searchTerm = "", string sortBy = "", string sortDiection = "")
{
    var members = (from m in context.Members
                   where (string.IsNullOrEmpty(searchTerm) || m.MemberNumber.Equals(searchTerm))
                         || (string.IsNullOrEmpty(searchTerm) || m.LastName.Equals(searchTerm))
                   select m).AsEnumerable();
                   
    if (!string.IsNullOrEmpty(sortBy))
    {
        PropertyDescriptor prop = TypeDescriptor.GetProperties(typeof(EFModel.ClientData.Member)).Find(sortBy, true);
        members = (sortDiection.ToLower() == "descnding") ? members.OrderByDescending(x => prop.GetValue(x)).ToList() : members.OrderBy(x => prop.GetValue(x)).ToList();
    }

    rowCount =  (!string.IsNullOrEmpty(searchTerm)) ? members.Count() : rowCount = context.Members.Count() ;
    
    members = members.Skip(pageIndex).Take(pageSize).ToList();

    List<MemberDto> memberDtos = new List<MemberDto>();
    mapper.Map(members, memberDtos);
    return memberDtos;
}

In the above method string seachColumn value can be memberno or lastname or sometime empty. when seachColumn value is memberno. I only need to search searchTerm value in MemberNumber column.
when seachColumn value is lastname. I only need to search searchTerm value in LastName column.
sometimes searchTerm can be empty. when it happen I need all the records without considering any search terms.

Above query is working bit. the problem is that when there is a value for searchTerm, That result is given regardless of the column. How can i do this.

You can append `.Where` clauses on an `IQueryable`, they are implicit logical `AND` statements (edit: for the implementation in Entity Framework querying a database). So you would have your "regular" query, and then say `query = query.Where(x => ...)` if your searchTerm isn't null. — BurnsBA, Jun 01 '21 at 18:25
@BurnsBA i updated the other part of my method to get an clear idea for others — Rooter, Jun 01 '21 at 18:29
@Rooter, is this code correct? Where's `searchColumn` defined? — Arca Artem, Jun 01 '21 at 22:06

score 0 · Answer 1 · answered Jun 01 '21 at 18:33

public List<MemberDto> GetMembers(out int rowCount,int pageIndex,int pageSize,  string seachColumn = "", string searchTerm = "", string sortBy = "", string sortDiection = "")
{
   var query = context.Members;
  if(string.IsNullOrWhitespace(searchTerm)
  {
        return query.ToList();
  }


//if you want to check strings equality in ignore-case mode, you should change your db collation using the link below.

return query
        .Where(m => m.MemberNumber.Equals(searchTerm) ||  m.LastName.Equals(searchTerm))
        .ToList();
}

String equality check in ef using db collation

How to set dynamic column in linq query

1 Answers1