Niklas Niklas - 2 years ago 69
SQL Question

LINQ to Entities conditionals give weird results

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)

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?

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download