Is it Possible to execute two LINQ queries of different types at the same time?

Question

I have two models of Material Requests and Security Users :

public class MaterialRequestMaster : BaseDomain<long>
{
  public string RequestCode { get; private set; }
  public string Description { get; private set; }
  public GlobalEnums.MaterialRequestStatus Status { get; set; }
  public int UserId { get; private set; }

  public SecurityUser User { get; private set; } 
}  

public class SecurityUser :IdentityUser<int>
{
  public string  Name { get; set; }
  public int  PersonelCodeId { get; set; }
  public ICollection<MaterialRequestMaster> MaterialRequestMaster { get; set; }
}

I want to do a search on Material Requests base on User Name , So I make a View Model of Material Request like this :

public class MaterialRequestMasterViewModel
{
  public long Id { get; set; }
  [Required(ErrorMessage = "Please Enter The Code")]
  public string RequestCode { get; set; }
  public GlobalEnums.MaterialRequestStatus Status{ get; set; }
  [MaxLength(255)]
  public string Description { get; set; }
  public int UserId { get; set; }
  public string UserName { get; set; }
  public bool IsDeleted { get; set; }
  public DateTime CreationDate { get; set; }
}    

I get the user name from view and make a where string like "x=>x.UserName.Contains("Something")" and send it to my Material Request search method as searchQuery:

public IPagedList<MaterialRequestMasterViewModel> GetPagedRecords(int pageNumber, int pageSize, string orderByCol, string order, string searchQuery)
{
IQueryable<MaterialRequestMaster> query;

query = query = _IMRMRepository.Get(); //Gets all records of <MaterialRecustMaster>

IQueryable<MaterialRequestMasterViewModel> searchedQuery;

searchedQuery = query.Select(n => new MaterialRequestMasterViewModel //Fill ViewModel 
 (
  n.Id,
  n.RequestCode,
  n.Status,
  n.Description,
  n.UserId,
  n.User.Name,
  n.CreationDate,
  n.IsDeleted
 ));

if (!string.IsNullOrWhiteSpace(searchQuery))
 searchedQuery = searchedQuery.Where(searchQuery);

return searchedQuery.ToPagedList(pageNumber, pageSize);
}

ToPagedList() method can not execute the query because of LINQ translation issue . Is it a proper way to do it ?

Answer 1

If you pass the "SearchQuery" to GetPagedRecords() method as string format, you can add an attribute in the header of property user id in the "MaterialRequestMasterViewModel" view model with the security user model's name.

public class MaterialRequestMasterViewModel
{
  public long Id { get; set; }
  [Required(ErrorMessage = "Please Enter The Code")]
  public string RequestCode { get; set; }
  public GlobalEnums.MaterialRequestStatus Status{ get; set; }
  [MaxLength(255)]
  public string Description { get; set; }
  [RelationName="SecurityUser"]
  public int UserId { get; set; }
  public string UserName { get; set; }
  public bool IsDeleted { get; set; }
  public DateTime CreationDate { get; set; }
}    

And then use reflection to get the property attribute's value and add it to your where clause. The search query value should be something like this :

RelationPropertyValue = assembly.GetProperty("UserId").GetCustomAttribute<RelationName>();
searchQuery = $"x => x.{RelationPropertyValue}.Contains(\"Something\")"

I think it is working for you.

Answer 2

A parameter of where must be a boolean function, so you should send it like that instead of string.

public IPagedList<MaterialRequestMasterViewModel> GetPagedRecords(int pageNumber, int pageSize, string orderByCol, string order, Func<SecurityUser ,Boolean> searchQuery )
{
IQueryable<MaterialRequestMaster> query;

query = query = _IMRMRepository.Get(); //Gets all records of <MaterialRecustMaster>

IQueryable<MaterialRequestMasterViewModel> searchedQuery;

searchedQuery = query.Select(n => new MaterialRequestMasterViewModel //Fill ViewModel 
 (
  n.Id,
  n.RequestCode,
  n.Status,
  n.Description,
  n.UserId,
  n.User.Name,
  n.CreationDate,
  n.IsDeleted
 ));

if (searchQuery != null)
 searchedQuery = searchedQuery.Where(searchQuery);

return searchedQuery.ToPagedList(pageNumber, pageSize);
}

You can create function variable like this

Func<SecurityUser, boolean> searchFunc = x => x.UserName.Contains("Something");

You can combine mutliple functions of this type like this

Func<SecurityUser, boolean> combined = x=> searchFunc(x) || x.Id > 100;

etc.