3 worst things you can do with Linq to Database

Disclaimer: By and beyond Linq to Database we understand the most common .NET libraries for accessing relational databases using Linq queries. Such as Linq2SQL, Entity Framework, NHibername, Linq2db and the like

Since the advent of IQueryable providers to access databases, writing queries has become much easier. On the one hand, this is good, because applications have become much easier to do. On the other hand, the quality of requests fell dramatically. And the point is not that providers generate bad SQL, but that people write queries, not understanding how it works under the hood. For many C # programmers, IQueryable is magic, and there is a natural desire to reduce the effect of magic on applications, to isolate magic from the rest of the code. Such an approach not only does not correct the situation, but exacerbates it ...

Code example

In one of the recent holivars, one of the participants published a stunning code example:

 public IEnumerable<DocumentDto> GetDocumentsToday() { foreach (var item in Db.Documents.Where(x => EntityFunctions.TruncateTime(x.CreatedOn) == DateTime.Today)) { yield return Mapper.Map<Document, DocumentDto>(item); } } 

It was assumed that this code will be able to isolate the "complexity" of IQueryable providers from the consumer. But this, seemingly simple, code contains 3 bad things when working with Linq to Database.

So the hit parade:

Third place

Long-term processing of query results. Most providers keep the DataReader open while the IQueryable is being enumerated. This leads to the fact that all resources allocated by the server for this request are retained until the end of the crawl and the closure of the DataReader . In this example, another yield return is used, that is, the processing of each element can take a very long time.
')
In the case of using SQL Server (or another locking DB) with the Repeatable Read isolation level, the locks will also hold until the end of processing the results of the query, and if escalated, then the whole table.

How to fix

Always explicitly perform materialization of results, for example using .Load() or .ToList() or .ToArray() . If the query results are very large, and you need to involve everything, then it is best to organize paging blocks that do not cause escalation of locks.

Second place

Inefficient predicate. In this example, the predicate is converted to SQL into something like this, depending on the provider:

 ... WHERE cast(CreatedOn as Date) = cast(GETDATE() as Date) 

Naturally, no index can optimize such a sample and there will be a complete crawl of the table.

Even if such a predicate is not a problem in this particular query, the code tends to be copied to other places along with all the problems.

How to fix

Do not use functions and type conversions for column values ​​in predicates and joins. This example can be rewritten as:

 Db.Documents.Where(x => x.CreatedOn >= DateTime.Today && x.CreatedOn < DateTime.Today.AddDays(1)) 

Now you can make an index on the field CreatedOn .

First place

Lack of projection. It would seem a trivial thing - the query pulls all the fields in the table, and then it saves on the DTO, which can have much fewer fields ... If there are LOB fields in the table, this can give a very negative effect on the speed of the query. In some ORMs, it is possible to configure delayed loading of LOB fields, but even in this case, the presence of a projection is more efficient. If there is a projection in the query, you can build a covering index, that is, an index that includes all the columns in the projection. That is, the projections allow not only to make the query faster, but also to enable further optimization at the database level.

How to fix

Always make projections only necessary for the fields, except when you need to get an object, and then change it and write it to the database.

Corrected example

 public IEnumerable<DocumentDto> GetDocumentsToday() { return Db.Documents .Where(x => x.CreatedOn >= DateTime.Today && x.CreatedOn < DateTime.Today.AddDays(1)) .Select (x => new DocumentDto {...}) .ToList(); } 

You can also correct the Mapper class so that it generates projections.

Conclusion

In this charts are collected the three most frequent problems that arise when using Linq. I optimized many applications and almost always found these problems.

As for the concrete example, most likely in many cases all DocumentDto fields, but only some of them, will not be used. Also not all entries will be displayed, but with a pagination. Therefore, it is advantageous to give IQueryable from the GetDocumentsToday method, and apply filters and restrictions closer to where the query results are used. But more about that next time.