Why dynamic Linq does not work with .contains?

March 25, 2023

I am creating dynamic query for linq using contains but I am not getting any results back. Results exists in SQL.

What am I doing wrong here? Since I can have N values for columns, .Contains should be okay.

Code:

public IAsyncEnumerable<MyTable> GetMyTableRecordsPerFilter(
    IReadOnlyList<string> filters, CancellationToken cancellationToken) 
{
  
  var queryParams = generateQuery(filters);
  
  var query = _dbContext.Exception.AsNoTracking();

  if (queryParams.Result.ContainsKey("Location")) // (COL, NYC)
      query = query.Where(x => x.Location.Contains(queryParams.Result["Location"]));
      
  if (queryParams.Result.ContainsKey("StockNumber")) // ('1111150416', '2222250416', '6666650416')
      query = query.Where(x => x.StockNumber.ToString().Contains(queryParams.Result["StockNumber"]));

  var result = query.AsAsyncEnumerable(); // No results returned
  return result;
}

SQL:

>Solution :

You have the search condition and the target variable mixed up. For example, if your Location contains the string NYC and queryParams.Result["Location"] contains the string (COL, NYC), then you need to first partition the string into a collection and then search whether the collection contains Location value:

public async IAsyncEnumerable<MyTable> GetMyTableRecordsPerFilter(
    IReadOnlyList<string> filters, CancellationToken cancellationToken) 
{
    var queryParams = generateQuery(filters);
  
    var query = _dbContext.Exception.AsNoTracking();

    if (queryParams.Result.ContainsKey("Location")) // (COL, NYC)
    {
        var locations = queryParams
            .Result["Location"]
            .Trim('(', ')') // delete brackets if needed
            .Split(",") // split stinf by comma
            .Select(s => s.Trim()) // delete leading whitespaces 
            .ToArray();
        query = query.Where(x => locations.Contains(x.Location));
    }
      
    if (queryParams.Result.ContainsKey("StockNumber")) // ('1111150416', '2222250416', '6666650416')
    {
        var stockNumbers = queryParams
            .Result["StockNumber"]
            .Trim('(', ')') // delete brackets if needed
            .Split(",") // split stinf by comma
            .Select(s => s.Trim()) // delete leading whitespaces 
            .ToArray();
        query = query.Where(x => stockNumbers.Contains(x.StockNumber.ToString()));
    }

    var result = query.AsAsyncEnumerable();
    return result;
}