efcore: ToLower not working with AsQueryable on sqlite
ToLower not working with AsQueryable on sqlite but it is working on MS SQL
[AllowAnonymous]
[HttpPost]
public JsonResult GetReports(string publisher = null, string tag = null)
{
try
{
var draw = Request.Form["draw"].FirstOrDefault();
var start = Request.Form["start"].FirstOrDefault();
var length = Request.Form["length"].FirstOrDefault();
var sortColumn = Request.Form["columns[" + Request.Form["order[0][column]"].FirstOrDefault() + "][name]"].FirstOrDefault();
var sortColumnDirection = Request.Form["order[0][dir]"].FirstOrDefault();
string searchValue = Request.Form["search[value]"].FirstOrDefault().ToLower();
int pageSize = length != null ? Convert.ToInt32(length) : 0;
int skip = start != null ? Convert.ToInt32(start) : 0;
int recordsTotal = 0;
var customerData = _unitOfWork.Reports.GetAll().Include(i => i.Publisher).Include(i => i.ReportTags).ThenInclude(i => i.Tag)
.Where(i => i.IsApproved && i.Language.Culture == CultureInfo.CurrentCulture.Name)
.OrderByDescending(i => i.ReleaseDate).AsQueryable();
if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDirection)))
{
customerData = customerData.OrderBy(i => sortColumn + " " + sortColumnDirection).AsQueryable();
}
if (!string.IsNullOrEmpty(searchValue))
{
customerData = customerData.Where(i => i.Title.ToLower().Contains(searchValue)).AsQueryable();
//customerData = customerData.Where(i => i.Title.ToLower().Contains(searchValue)
// || i.Summary.ToLower().Contains(searchValue)
// || i.Publisher.Name.ToLower().Contains(searchValue)
// ).ToList();
}
if (!string.IsNullOrEmpty(publisher))
{
customerData = customerData.Where(i => i.Publisher.Url == publisher).AsQueryable();
}
if (!string.IsNullOrEmpty(tag))
{
customerData = customerData.Where(i => i.ReportTags.Any(a => a.Tag.Url.Equals(tag))).AsQueryable();
}
recordsTotal = customerData.Count();
var data = customerData.Skip(skip).Take(pageSize)
.Select(i => new { id = i.ReportId, title = i.Title, summary = i.Summary, releaseDate = i.ReleaseDate.Value.ToShortDateString(), publisherName = i.Publisher.Name, url = i.Url })
.ToList();
var jsonData = new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data };
return Json(jsonData);
}
catch
{
return Json("");
}
}
it is working with ToList() on sqlite
About this issue
- Original URL
- State: closed
- Created 2 years ago
- Comments: 16 (10 by maintainers)
@suatsuphi you probably want to have a look at our collations and case sensitivity doc page: doing a case-insensitive match by using ToLower is generally not a good idea in databases, as it prevents index use. Consider defining a case-insensitive collation on the column instead.
Can you share a small repro project?