.net How to make a search method non case sensitive?

Question

In my MVC web application I am creating a search function where i need to compare a search string with objects in my product repository - But how do I make sure that the search is not case sensitive? I can use ToLower() on my search string - but the repository?

Controller:

 public ActionResult Search(string q, int page = 1)
 {
        string search = q.ToLower();
        int productCounter = repository.Products.Where(p => p.Name.Contains(search) || p.Description.Contains(search)).Count();

            ProductsListViewModel model = new ProductsListViewModel
            {
                Products = repository.Products
                .Where(p => p.Name.Contains(search) || p.Description.Contains(search))
                .OrderBy(p => p.ProductID)
                .Skip((page - 1) * PageSize)
                .Take(PageSize),
                PagingInfo = new PagingInfo
                {
                    CurrentPage = page,
                    ItemsPerPage = PageSize,
                    TotalItems = productCounter == 0 ? 0 : productCounter
                }
            };
        return View("List", model);
    }

possible duplicate of [linq to entities case sensitive comparison](http://stackoverflow.com/questions/3843060/linq-to-entities-case-sensitive-comparison) — Gert Arnold, Jun 17 '14 at 21:19
looks covered in existing SO posts. was this tagged EF correctly ? — phil soady, Jun 18 '14 at 01:42

score 3 · Answer 1 · answered Jun 17 '14 at 21:14

3

You can replace this:

p.Name.Contains(search)

with this:

p.Name.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0

answered Jun 17 '14 at 21:14

some_name

1,183
8
13

3

really? IndexOf in EF queries. or Did you have an EF Query.ToList() and apply IndexOf on that ? HUGE difference. I just ran a test on EF 6.1 to check. Bombed on my version. Docu suggests its not possible ...http://msdn.microsoft.com/en-us/library/bb738550 – phil soady Jun 18 '14 at 01:40

score 3 · Answer 2 · edited May 23 '17 at 10:33

3

You can use IndexOf to get access to IgnoreCase in In memory collections. You may be disappointed when you apply this to EF provider scenarios. Is this correctly tagged EF ?

EF Provider specification does not use IndexOf. For that matter the Contains(str,comparer) is also not supported.

Supported Linq to Entities features

If using SQLServer with EF the original issue is governed by the column collation property. eg SQL_Latin1_General_CP1_CI_AS case insensitive latin.

If you have DB first, you control the collation sequence at DB level.

All explained here nicely ... LINQ to Entities case sensitive comparison

If using code first the DEFAULT sql server db collation is used. more info on default collation Set database collation in Entity Framework Code-First Initializer

edited May 23 '17 at 10:33

Community

1
1

answered Jun 18 '14 at 01:40

phil soady

11,043
5
50
95

I have a EF repository making a IEnumerable of my products. When use IndexOf on that, it seems to work fine. – Mac Luc Jun 18 '14 at 09:18
1

@MacLuc Ah, finally it appears that you're using the wrong approach. You're pulling everything into memory before filtering. That's highly inefficient as Phil explained in a comment. Your repository should return `IQueryable` and should be used internally. – Gert Arnold Jun 18 '14 at 09:49

score -1 · Answer 3 · answered Jun 17 '14 at 21:15

-1

You can use Invariant Culture and enhance the query a bit by using Length as well:

 public ActionResult Search(string q, int page = 1)
    {
        string s;
        string search = q.ToUpperInvariant();
        int productCounter = repository.Products.Where(p => p.Name.ToUpperInvariant().Contains(search) || p.Description.ToUpperInvariant().Contains(search)).Count();
        int searchlength = search.Length;
        ProductsListViewModel model = new ProductsListViewModel
        {
            Products = repository.Products
            .Where(p => (p.Name.Length >= searchlength && p.Name.ToUpperInvariant().Contains(search)) || (p.Description.Length >= searchlength && p.Description.ToUpperInvariant().Contains(search)))
            .OrderBy(p => p.ProductID)
            .Skip((page - 1) * PageSize)
            .Take(PageSize),
            PagingInfo = new PagingInfo
            {
                CurrentPage = page,
                ItemsPerPage = PageSize,
                TotalItems = productCounter == 0 ? 0 : productCounter
            }
        };
        return View("List", model);
    }

answered Jun 17 '14 at 21:15

Avneesh

654
4
6

If the length is already less than search then why would you do a search? In long strings it will filter faster. – Avneesh Jun 17 '14 at 21:18
1

Bad advice. `ToUpperInvariant` can't be translated into SQL, and it is a bad idea to convert database values before filtering. It disables indexes. – Gert Arnold Jun 17 '14 at 21:23
where did he mention in question he is searching database? Repository could be object based as well. – Avneesh Jun 17 '14 at 21:24
If that is the case then his query is already doing the job. Index of suggested above wont make any difference either. – Avneesh Jun 17 '14 at 21:27
1

The query is an expression tree that gets translated into SQL. The db engine does the real job. – Gert Arnold Jun 17 '14 at 21:39
.IndexOf(search, StringComparison.OrdinalIgnoreCase) >= 0 seems like to be doing the job – Mac Luc Jun 17 '14 at 21:47
2

With the `StringComparison.OrdinalIgnoreCase` part? How does EF translate that? – Gert Arnold Jun 17 '14 at 21:52
If you tagged this EF correctly, then I believe your query must be toList() and you apply the IndexOf, on the returned list. Was the filter applied at DB level. careful ;-) Suggest SQL profile to be sure. In fact post the SQL profile please. That will confirm if the claim is correct – phil soady Jun 18 '14 at 01:46

.net How to make a search method non case sensitive?

3 Answers3