Tiger Tiger - 4 months ago 16
C# Question

Where condition on dependent entities causing slow performance Entity Framework

context.PaymentEntity.Include("UserPaymentEntity")
.Include("UserPaymentEntity.RefundEntity")
.Include("RequestEntity")
.Include("RequestEntity.PremiumEntity")
.Include("RequestEntity.PremiumEntity.TypeEntity")
.Include("RequestEntity.PremiumEntity.TypeEntity.CategoryEntity")
.Include("RequestEntity.PremiumEntity.TypeEntity.CategoryEntity.CategoryTypeEntity")
.Where(x => x.RequestKey != null &&
x.RequestEntity.UserKey == usrKey &&
'03/01/2016' <= x.ReceivedDate &&
x.ReceivedDate <= '03/31/2016');


This where condition slows down the performance. Because it is accessing userKey from dependent entity.

How can I improve it? These tables have half a million to 1 million records in each table.

Thanks

Answer

1) Make small queries and join them on memory, if possible.

2) Your query will not work you need a DbFunction for date and time '03/31/2016'

3) in your UserPaymentEntity you need CategoryTypeEntity entity, why you do not just add a navigation property in UserPaymentEntity.CategoryTypeEntity? this will reduce the joins hell.

4) when EF is struggling and can not achive your performance requirements, then just use Views.

5) use nameof(UserPaymentEntity) and not string "UserPaymentEntity" this is cleaner for refactoring.

6) This include: .Include("RequestEntity.PremiumEntity.TypeEntity.CategoryEntity.CategoryTypeEntity")

Will automatically include the whole navigation path "RequestEntity" and "RequestEntity.PremiumEntity", etc. You to not have to write them all!

7) If you want only to read the data, then use:

this.Configuration.AutoDetectChangesEnabled = false;

or get the data with AsNoTracking

8) Maybe you can divide your query logic in pages and use Take and Skip.

9) Use second level cash for the fixed data.https://efcache.codeplex.com/