CODEWITHSUNDEEP
c#.netlinqentity-frameworkdatabase-design
FAtBalloon
FAtBalloon

Reputation: 4500

Proper Searching with Linq, Entity Framework and Database Indexes

I need to search products through products in a database and I'm looking to set this up in the right way so that I can have solid performance when there are a lot of rows (1,000,000). I'm somewhat experienced with LINQ and EF, but never written any search algos and I've got the following code, but just have a few lingering questions. 

context.products.Where(i => i.Name.ToLower().Contains(searchText.ToLower());

I also need to search the description. 

context.products.Where(i => i.Description.ToLower().Contains(searchText.ToLower());

  1. Is .ToLower() in this context going to reduce performance?

  2. I have a regular index on Name and FullText on Description? Is this appropriate and does a regular index work well with .contains()?

  3. Should I be using LINQ or some other method?

  4. Is there way to do this where I can get the number of times the search text occurs in the name/description?

Thank you

Upvotes: 4

Views: 6168

Answers (3)

The Muffin Man
The Muffin Man

Reputation: 20004

In my case I didn't want to use stored procedures. I was using Entity Framework and that's what I wanted to use!

See if this method might help you.

    public static IQueryable<T> LikeOr<T>(this IQueryable<T> source, string columnName, string searchTerm)
    {
        IEnumerable<string> words =
            searchTerm.Split(new[] {" "}, StringSplitOptions.RemoveEmptyEntries).Where(x => x.Length > 1);

        var sb = new StringBuilder();
        for (int i = 0; i < words.Count(); i++)
        {
            if (i != 0)
                sb.Append(" || ");

            sb.Append(string.Format("{0}.Contains(@{1})", columnName, i));
        }

        return source.Where(sb.ToString(), words.ToArray());
    }

All the above method does it build up a SQL string and then pass it to a Dynamic LINQ Where method. On a high level all this does is allow you to use straight sql only where you need it, rather than writing the entire query in SQL. It's called by something like this:

    public List<Book> SearchForBooks(string phrase)
    {
        return _db.Books.Include(x=> x.Images).LikeOr("Title", phrase).OrderBy(x => x.Title)
            .Take(6).Select(x => x).ToList()
            .OrderByCountDescending("Title", phrase);
    }

It's made possible by dynamic LINQ dll created by Microsoft but not included in the framework. Dynamic LINQ This will allow you to be more flexible in some areas.

Upvotes: 1

RAS
RAS

Reputation: 3385

  1. Please do not use lower, since it will significantly impact performance.
    use one of the following:

    StringComparison.OrdinalIgnoreCase
    StringComparison.CurrentCultureIgnoreCase

  2. Since you're using contains, which will be translated as like'%text%' it is unlikely that sql server will use indexes. if you implement FullText search, you have to use a stored procedure to use advantages of a fulltext search.

  3. Linq is always slower than a hand-written sql statement. I've seen some performance metrics on dapper.net website

Generally, if you're using the latest entity framework, you should get a pretty good performance since they've had some significant performance improvements in version 5.

Upvotes: 1

Malcolm O&#39;Hare
Malcolm O&#39;Hare

Reputation: 4999

I would seriously consider writing a stored procedure to do this, or raw sql in the DbContext using SqlQuery. If I was writing this code, that is what I would do. To me EntityFramework and performant never really went together.

Upvotes: 1

Related Questions