c# - What is the performance overhead of Entity Framework compared to raw SQL execution time? -

in application (ef6 + sql server) dynamically creating ef queries enable rich search functionality.

these queries created chaining bunch of where() predicates, , projecting results using few aggregations known clr types. in cases ef generates single sql query returns small amount of results (about 10).

using sql profiler can see execution time of these generated queries when executed database withing few milliseconds. however, unless query trivially simple, total execution time (calling tolist() or count() code) within few hundred milliseconds! code built in release mode , tested without debugger attached.

can give me hints might wrong approach? can possible ef's overhead 2 orders of magnitude in time compared raw sql execution time?

edit:

these code samples using filter result set:

if (p.pricemin != null)     query = query.where(a => a.terms.any(t => t.price >= p.pricemin.value));  if (p.startdate != null && p.enddate != null)     query = query.where(a => a.terms.any(t => t.date >= p.startdate.value && t.date <= p.enddate.value));  if (p.durationmin != null)     query = query.where(a => a.itinerary.oftype<dayelement>().count() > p.durationmin.value - 2);  if (p.locations != null && p.locations.count > 0) {     var locs = p.locations.select(l => new nullable<int>(l)).tolist();     query = query.where(a => a.itinerary.oftype<movetoelement>().any(e => locs.contains(e.locationid)) ||         a.itinerary.oftype<startelement>().any(e => locs.contains(e.locationid)) ||         a.itinerary.oftype<endelement>().any(e => locs.contains(e.locationid))); } 

then order results this:

if (p.orderbyid)     query = query.orderby(a => a.id); else if (p.orderbyprice)     query = query.orderbydescending(a => a.terms.average(t => t.price)); 

the execution time same if try execute same query multiple times in row (calling multiple query.count() withing same dbcontext), guess in case ef's query generation pretty efficient. seems else bottleneck...

in general, yes ef slower raw sql because it's difficult predict how ef build query , ef has no knowledge of database indexes or how structured.

there's no way overhead is, vary query query. if want optimize query ef have try out various ways of chaining predicates , benchmark results. slightest difference can make big difference.

i ran issue myself there huge difference between using .any() , .contains() can see here: check if list contains item other list in entityframework

the result same, second query 100 times faster. yes, possible queries ef 2 orders of magnitude slower raw sql. other times few milliseconds slower.