Linq Lambda Expressin Where Conditions And Performance Problem

Question

I created where conditions with linq lambda expression for reporting. My conditions are running very slowly or it is throwing error.

Error Detail: Evoluation timed out.

How can i find correct query?

My codes:

var query = Worker.Members
        .Where(u => string.IsNullOrEmpty(model.TcNumber) || u.TcNumber.Contains(model.TcNumber))
        .Where(u => string.IsNullOrEmpty(model.FullName) || u.FullName.Contains(model.FullName))
        .Where(u => model.BirthYear == null || u.BirthDate.Year >= model.BirthYear)
        .Where(u => model.BirthYearLevel == null || u.BirthDate.Year <= model.BirthYearLevel)
        .Where(u => model.BirthYearLevel == null || u.BirthDate.Year <= model.BirthYearLevel)
        .Where(u => model.GenderId == null || u.GenderId == model.GenderId)
        .Where(u => model.DistrictCode == null || u.Contacts.Any(c => c.DistrictCode == model.DistrictCode && c.IsActive))
        .Where(u => model.StreetCode == null || u.Contacts.Any(c => c.StreetCode == model.StreetCode && c.IsActive))
        .Where(u => model.ExteriorDoor == null || u.Contacts.Any(c => c.ExteriorDoor == model.ExteriorDoor && c.IsActive))
        .Where(u => model.InteriorDoor == null || u.Contacts.Any(c => c.InteriorDoor == model.InteriorDoor && c.IsActive))
        .Where(u => model.DisabledGroupId == null || u.Disableds.Any(d => d.ReportDisabledGroups.Any(c => c.DisabledGroupId == model.DisabledGroupId)))
        .Where(u => model.ContributionTypeId == null || u.Demands.Any(d => d.ContributionTypeId == model.ContributionTypeId && !d.IsDeleted))
        .Where(u => model.DemandStatusId == null || u.Demands.Any(d => d.DemandStatusId == model.DemandStatusId && !d.IsDeleted))
        .Where(u => model.DemandDateStart == null || u.Demands.Any(d => d.DemandDate >= model.DemandDateStart && !d.IsDeleted))
        .Where(u => model.DemandDateEnd == null || u.Demands.Any(d => d.DemandDate <= model.DemandDateEnd && !d.IsDeleted))
        .Where(u => u.IsTcCitizen == model.IsForeign)
        .Where(u => u.IsDisabled == model.IsDisabled)
        .Where(u => u.IsAlive == model.IsAlive);

Well your code isn't well factored. For example, you duplicate code which filters out softly deleted demands and contacts numerous times, damaging readability and likely performance as well; use a `let` clause instead. However, it's impossible to know what the performance of the code will be without the query provider. Is it EF? If so what version? — Aluan Haddad, Jul 03 '21 at 17:08
Please look at the query plan before posting a question on query performance. — Gert Arnold, Jul 04 '21 at 09:46

score 1 · Answer 1 · answered Jul 03 '21 at 17:26

1

The correct pattern (assuming Worker.Members is an IQueryable, not an IEnumerable) here is to conditionally add the criteria, like this:

var query = Worker.Members;

if (!string.IsNullOrEmpty(model.TcNumber))
    query = query.Where(u => u.TcNumber.Contains(model.TcNumber));
if (!string.IsNullOrEmpty(model.FullName)
    query = query.Where(u => u.FullName.Contains(model.FullName));
if (!model.BirthYear == null)
    query = query.Where(u => u.BirthDate.Year >= model.BirthYear);
. . .

answered Jul 03 '21 at 17:26

David Browne - Microsoft

80,331
6
39
67

1

No idea why this was downvoted. In EF6 these inlined null checks get transpiled right into the generated SQL and can be very detrimental to the query plan. – Gert Arnold Jul 04 '21 at 09:55

Sajidur Rahman · Answer 2 · 2021-07-14T03:28:52.240

-3

Did you check this? Here detailed descriptions are given.

edited Jul 14 '21 at 03:28

answered Jul 03 '21 at 17:15

Sajidur Rahman

613
6
9

It's not clear what you mean by connected collection of objects and you're not correct about the number of database interactions. It likely wouldn't time out if that were the case anyway but rather just run slowly – Aluan Haddad Jul 03 '21 at 17:23

Linq Lambda Expressin Where Conditions And Performance Problem

2 Answers2