query optimization - How to optimize this LINQ expression? -

i have code:

var query = deal in db.deals             deal.enddate >= datetime.utcnow             select deal;  var pricelist = filters.price.getpricerangelist(); foreach(var price in pricelist) {     var startprice = price.startprice;     var endprice = price.endprice;     var priceresult = deal in query           (deal.discountprice >= startprice && deal.discountprice <= endprice)           select deal;     if(priceresult.count() != 0)         priceresults = (priceresults == null) ? priceresult : priceresult.union(priceresults); } query = priceresults != null ? query.intersect(priceresults) : enumerable.empty<deal>().asqueryable(); 

my query slow when pricelist has more ten values.

i use intersect filters.

how optimize these queries?

you have few issues. first 1 query executed every iteration in foreach loop. calling tolist or toarray ensure executed once secondly union costly. iterate priceresult every iteration of foreach loop thirdly count iterate priceresult. use .any instead if wish know if theres elements. think can avoid that. if i've read code correctly believe below should have same result not have above 3 issues

var query = (from deal in db.deals              deal.enddate >= datetime.utcnow              orderby deal.discountprice ascending              select deal).tolist();  var priceresults = (from price in filters.price.getpricerangelist()                     let startprice = price.startprice                     let endprice = price.endprice                     select query.skipwhile(d => deal.discountprice < startprice)                            .takewhile(d => deal.discountprice <= endprice)                     ).selectmany(x => x); 

instead of iterating each union there's distinct once