c# - Parameterized Linq Query -Is there a better performance option? -

i using following parameterized linq query expression query approximately 100,000 records in sql server. there better way?

public ilist<article> getarticles(string language, string category, string subcategory, bool exclusives, int pageindex, int pagesize = 200)     {          iqueryable<article> query;          query = db.articles.where(t => t.isactive && t.articlestatus);          if (exclusives) { query = query.where(t => t.isexclusive); }           if (language.toupper() != "all")         {             query = query.where(t => t.language.toupper() == language);         }         if (category.toupper() != "all")         {             query = query.where(t => t.category.toupper() == category);         }         if (subcategory.toupper() != "all")         {             query = query.where(t => t.subcategory.toupper() == subcategory);         }         query = query.where(t => t.articledate <= datetime.now);          query = query.orderbydescending(t => t.articleno).skip((pageindex - 1) * pagesize).take(pagesize);          if (query.any() == false)         {             return null;         }         else         {             return query.tolist();         }     } 

entity framework isn't known being fastest option @ runtime--it's supposed maintainable, easy, , fast write. if want fast, go different orm lets write t-sql manually (or use ef's database.sqlquery<t>(string) methods).

that said, you're running query twice.

if (query.any() == false) {     return null; } else {     return query.tolist(); } 

each of these methods execute varied form of query against database.

instead, test out performance calling once.

var returnlist = query.tolist();  if (returnlist.count == 0) {     return null; } else {     return returnlist; } 

the improvement here should evident, it's possible statistics messed on database, test.

aside that, check indexes. entity framework builds queries in sometimes-unusual ways, run query plan explorer (or sql profiler) against them.

simplest way put breakpoint @ tolist() call few inputs, hover or use watch windows query.tostring(), paste in can query plans (like vs's database tooling or ssms), , see if has obvious.

it's case rearrange calls ef improve query, case you'll want reassess indexes server-side meet ef giving you.

but, again, remember varied inputs--you're changing query, want make sure you've got bases covered.