performance - Linq query runs slow but query runs fast in isolation -

we have rest service member given id. sql runs fast in isolation (5ms) when run linq (using entity framework 6), runs slow (230ms).

i don't question duplicate of this, this or this feels linq/entityframework related.

here stats: time taken client call member 360ms time taken execute linq query c# code 230ms time taken execute sql on sql server 228ms

sql tracing in production of similar performance (141ms execute sql on sql server) numbers feel real.

i tried running linq query 6 times in row see if perhaps cost of establishing connection datacontext problem. each of linq queries took same amount of time run.

if use same datacontext run sql directly (ie: linq generates), runtime (measured c#) drops 230ms 19ms.

running sql directly on server (sql server management studio) takes 5ms.

c# code (all in same routine, using same datacontext, no using block) produces these numbers:

linq original query run =227ms raw sql query: 19ms linq run 0=228 linq run 1=227 linq run 2=229 linq run 3=229 linq run 4=232 

the linq query looks this:

datetime start = datetime.now;         var memberdetail = await (from member in datacontext.members.asnotracking()                                   join membername in datacontext.membernames.asnotracking() on member.uid equals membername.memberid nameoutput                                   mn in nameoutput.defaultifempty()                                   join memberproperty in datacontext.properties.asnotracking() on member.propertyid equals memberproperty.uid                                   join membershipcycle in datacontext.membershipcyclehistories.asnotracking() on member.uid equals membershipcycle.memberid cycleoutput                                   co in cycleoutput.defaultifempty()                                   member.referencenumber.equals(membernumber) &&                                         memberproperty.externalid.equals(property, stringcomparison.invariantcultureignorecase)                                   select new                                   {                                       member.uid,                                       member.created,                                       member.lastupdated,                                       propertyname = memberproperty.externalid,                                       member.referencenumber,                                       member.active,                                       member.isawaitingsync,                                       member.class,                                       mn.firstname,                                       mn.lastname,                                       mn.preferredname,                                       membershipcreditbalance = co.membershipcredits,                                       member.dob                                   }                                  ).firstordefaultasync();         system.diagnostics.trace.writeline(string.format("linq run original={0}", (datetime.now - start).totalmilliseconds)); 

and connection string is:

data source=sqlserver123;initial catalog=db123;persist security info=true;user id=user123;password=pwd123;multipleactiveresultsets=true 

after fair bit more investigation, found problem. database uses varchars of strings. when linq passes parameters sql server, passes them unicode. sql server looks @ data type , figures cannot use varchar indexes, falls linear scans.

by changing database varchar nvarchar, query speed went 258ms 3ms.