0

I have a c# code in which I use a LINQ query to retrieve bunch of objects by comparing a specific property of string type with list of strings. The c# code is like as follow:

var list = new List<string>{.....}
var query = from record in context.Records.AsNoTracking()
                        where list.Contains(record.Name)
                        orderby record.Name
                        select new 
                        {
                            ...
                        };

var result = query.ToList();

My problem is that when the list is large the performance of this query is dramatically low in some case it takes several minutes to return the results.

I need to know what are the probable solutions to increase the performance of this query?

Abuzar G
  • 103
  • 1
  • 4
  • I suspect you will need to either break down the query to smaller chunks. Also, see if doing the ordering in memory, that is call .OrderBy after you to ToList (as this will now order it in memory) might help – SomeStudent Dec 04 '19 at 13:54

1 Answers1

0

This request compares each row in the database with each item in a collection.

  • add an index for record.Name to filter and order names
  • sort list collection before using it

measure a request performance after each step separately.

  • review a possibility to use a temporary table inside sql storage to store list items before launching a request
  • It is possible to convert with LinqPad a linq request to sql request to review its performance.

If you suffer with Linq performance and know SQL very well you can use Dapper for read requests and reports.

Eldar
  • 170
  • 3
  • 12