JDS JDS - 1 year ago 187
C# Question

Why is EF so slow to get data?

I'm still learning how to use EF but I'm not sure why it's taking me so long to pull data from the database.

My EF statement:

IEnumerable<DOC_TRACK_MAIN> data;
Stopwatch st = new Stopwatch();

data = context.TABLE1.AsNoTracking().Include(x => x.TABLE2).AsNoTracking().Where(x => startDate <= DbFunctions.TruncateTime(x.LAST_UPDATED_DATE) && endDate >= DbFunctions.TruncateTime(x.LAST_UPDATED_DATE));

var result = data.Where(x => x.TABLE2.MAP_TYPE_CODE.Trim().ToUpper() == reportCode.Trim().ToString()).ToList();
var cnt = result.Count();
var tm = st.Elapsed;
return result;

When I test a similar query in TOAD it takes about 5 seconds..

Answer Source

Please rewrite your EF Query as shown below and let us know about the performance now.

context.Configuration.LazyLoadingEnabled = false;//to remove lazy loading for this query only

data = context.TABLE1.Where(x => startDate <= 
DbFunctions.TruncateTime(x.LAST_UPDATED_DATE) && endDate >= 
DbFunctions.TruncateTime(x.LAST_UPDATED_DATE)).Include(x => x.TABLE2);   

var result = data.Where(x => x.TABLE2.MAP_TYPE_CODE.Trim().ToUpper() == 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download