How to optimize EF query?

Question

I have a query that returns 31,000 records but works very slowly with EF. I want to optimize this query. How should I optimize it?

 [HttpGet]
public IActionResult SearchInTaminJobs(string term)
{
    
    var query = _context.TaminJobs.Where(Ad => Ad.jobName.Contains(term)).Select(c => 
    c.jobName + c.jobCode).ToList();

        return Ok(query);
 
}

This query that I wrote goes and checks all the database records.But now I want a query to go and get a list of the first 20 records that are similar to jobName and not check all the database records

Answer 1

I believe it's not an EF problem. In your query you use contains and full table scan, i.e. you go through all records and for each record you check whether jobname contains a text fragment. And since it's a contains check you can't just build an index on this column. So your query is just slow. Also you return many records, which can make a contribution into the execution time too. Try to add more filtering before doing contains (by some indexed id or similar).

To be sure that your query is slow run this raw sql query in SSMS: select jobName + jobCode from TaminJobs where jobName like '%YOUR_TERM_HERE%'.

You can also try to limit the number of records which will be returned: _context.TaminJobs.Where(Ad => Ad.jobName.Contains(term)).Take(20)