CODEWITHSUNDEEP
c#entity-frameworklinq
smatter
smatter

Reputation: 29168

Slowness when chaining LINQ queries

I am doing chaining LINQ queries as show below. I am trying to find out the cause for the slowness of query.ToList();. The SQL queries are fast (milliseconds), but the code takes a minute. The reason for chaining is to reuse the repository function.

  1. Is there any obvious reasons for slowness here?
  2. How could I optimize this ?

  3. How can I check the actual SQL query executed when running query.ToList();?

    //Client
var query = _service.GetResultsByStatus(status, bType, tType);
var result = query.ToList(); //takes a long time to execute 

//Service function
 public IEnumerable<CustomResult> GetResultsByStatus(string status, string bType, string tType) {
    IEnumerable<CustomResult> result = null;
    result = repo.GetResults(bType).Where(item => item.tStatus == status && (tType == null || item.tType == tType))
        .Select(item => new CustomResult {
           A = item.A,
           B = item.B,
        });
    return result;
}


// Repository Function (reused in many places)
public IEnumerable<my_model> GetResults(string bType) {
     return from p in dbContext.my_model()
         where p.bType.Equals(bType)
         select p;
}

Upvotes: 1

Views: 72

Answers (1)

xanatos
xanatos

Reputation: 111820

Your .Where(item => item.tStatus == status && (tType == null || item.tType == tType)) and the .Select are being done "locally" on your PC... Tons of useless rows and columns are being returned by the SQL to be then "filtered" on your PC.

public IEnumerable<my_model> GetResults(string bType) {
     return from p in dbContext.my_model()
         where p.bType.Equals(bType)
         select p;
}

Change it to

public IQueryable<my_model> GetResults(string bType) {

Normally IEnumerable<> means "downstream LINQ will be executed locally", IQueryable<> means "downstream LINQ will be executed on a server". In this case the Where and the Select are "downstream" from the transformation of the query in a IEnumerable<>. Note that while it is possible (and easy) to convert an IQueryable<> to an IEnumerable<>, the opposite normally isn't possible. The AsQueryable<> creates a "fake" IQueryable<> that is executed locally and is mainly useful in unit tests.

Upvotes: 4

Related Questions