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)

Most upvoted comments

@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?