LINQ to Entities conditionals give weird results

Question

Trying to implement conditionals in a LINQ query (with Entityframework) creates strange queries. In some cases these queries time out, even though the threshold is set to 180 seconds:

                List<LogEntity> dataList = db.LogEntities.Where(x =>
                x.Source == "Source" &&
                (String.IsNullOrEmpty(from) || x.EventDate >= cFrom) &&
                (String.IsNullOrEmpty(to) || x.EventDate <= cTo) &&
                (String.IsNullOrEmpty(uid) || x.DomainUserLogin == uid) &&
                (String.IsNullOrEmpty(cid) || x.CaseReference == cid) &&
                (String.IsNullOrEmpty(searchtext) || x.Message.Contains(searchtext)))
                .OrderByDescending(y => y.EventDate)
                .Take(500)
                .ToList<LogEntity>();

With somewhat less elegant if-statements, I get no issues, and the queries return in few seconds:

                IQueryable<LogEntity> data = db.LogEntities.Where(x => x.Source == "Source");
            if (!String.IsNullOrEmpty(from))
                data = data.Where(x => x.EventDate >= cFrom);
            if (!String.IsNullOrEmpty(to))
                data = data.Where(x => x.EventDate <= cTo);
            if (!String.IsNullOrEmpty(uid))
                data = data.Where(x => x.DomainUserLogin == uid);
            if (!String.IsNullOrEmpty(cid))
                data = data.Where(x => x.CaseReference == cid);
            if (!String.IsNullOrEmpty(searchtext))
                data = data.Where(x => x.Message.Contains(searchtext));
            data = data.OrderByDescending(x => x.EventDate).Take(500);
            List<LogEntity> dataList = data.ToList<LogEntity>();

The conditionals are all passed from a querystring, which is why they may sometimes be carrying a value and sometimes not.

The same issue arises when using ternary operators like

...Where(x => truth ? x.something == somevalue : x.something == anothervalue)

Is there any reasonable explanation as to why these inline conditionals perform so poorly?

The current EF query translator does not reduce constant expressions. Don't waste your time, the `if` style is the right way to go. — Ivan Stoev, Apr 21 '16 at 06:57
I've changed my mind. Your question (along with some other) made me start thinking of a general solution. You can take a look at my answer to [“Nullable object must have a value” exception after checking for null on a non-primitive/non-struct object](http://stackoverflow.com/questions/36892232/nullable-object-must-have-a-value-exception-after-checking-for-null-on-a-non-p/36896900#36896900) and see if the custom extension method works for you. — Ivan Stoev, Apr 27 '16 at 21:11

score 2 · Accepted Answer · answered Apr 21 '16 at 08:20

When you write queries with LINQ on EF databases they looks very natural but behind the scene there's the query translator that parses your LINQ query and split it into 2 parts:one is executed on the sql server, another - on the client using just LINQ extensions.

When you use some expression that the query translator cannot translate to SQL (e.g. some .NET functions) it minimize the data filtering and you may end up with downloading the entire data table to the client and filtering it their.

In the first query your wrote, you use (String.IsNullOrEmpty(from) || x.EventDate >= cFrom); "from" is external to the LogEntities and the translator couldn't do any assumption about it values and how it is calculated regarding the records. So, most likely, you just downloading the full LogEntities to the client and filter it to the client. If number of records is huge, you'll get the timeout error.

In the second query you joined simple expressions Where(x => x.DomainUserLogin == uid); that is clearly translated to sql. So, you get the correct sql query that filter most records on the sql server side.

You can use the SQL profiler, or VS tools (depending on VS editions, or enabling logging in EF to see the actual query that are executed.

There's some information on MSDN

Thank you for that explanation, however, I would assume the C# statements to be evaluated before passing to the query translator. Evidently, they're not, so I will refrain from using them like so. — Niklas, Apr 21 '16 at 08:37

LINQ to Entities conditionals give weird results

1 Answers1

Linked