ASP.NET Core 3 EF Framework - transient failure when running lots of queries


I am using EF Core for an ASP.NET website. In one method I have about 7 complex queries similar to (simple example):

var query1 = context.Playarea
            .Include(x => x.Cats)
            .Where(x => x.Cats.Any())
            .SelectMany(x => x.Cats.Select(y => new MyClass(x.Id, y.Name, y.Age))).ToList();

var query2 = context.Playarea
            .Include( x => x.Dogs)
            .Where(x => x.Dogs.Any())
            .SelectMany(x => x.Dogs, (x, y) => new MyClass(x.Id, y.Name, y.Age)).ToList();

var query3 = context.Playarea
            .Include( x => x.Dogs)
            .Include( x => x.Leads)
            .Where(x => x.Dogs.Any())
            .SelectMany(x => x.Dogs, (x, y) => new MyClass(x.Id, y.Leads.Name, y.Age)).ToList();

var query4 = context.Playarea
            .Include( x => x.Birds)
            .Where(x => x.Birds.Any())
            .SelectMany(x => x.Birds, (x, y) => new MyClass(x.Id, y.Name, y.Age)).ToList();

return query1.Concat(query2).Concat(query3).Concat(query4).ToList();

This usually works, but occasionally the page crashes with:

An unhandled exception was thrown by the application. System.InvalidOperationException: An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding 'EnableRetryOnFailure()' to the 'UseSqlServer' call.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)
 ---> System.ComponentModel.Win32Exception (121): The semaphore timeout period has expired.

I know I could add 'EnableRetryOnFailure()' but I am worried that the true cause is that it is running multiple queries at the same time. Is there a way to make this safer and more efficient?

I've tried looking up guides, but none of them cover what to do if you are trying lots of queries at once.

asked on Stack Overflow Apr 1, 2020 by Hawke

1 Answer


Honestly, I think this probably has less to do with your code and more to do with some sort of network or database issue. While there's ways this code could be improved, there's really no reason it shouldn't consistently work, as-is.

That said, you're blocking on all these queries, which is never a good idea. Everything EF Core does is async. The sync methods merely block on the async ones, and exist only for scenarios where async cannot be used, such as event delegates in desktop apps and such. In short, you should always use the async methods, unless you run into a specific situation where you can't. In an ASP.NET Core application, there is no such situation, so async should always be used at all times. Long and short, use ToListAsync instead of ToList and await each line.

Next, there's no point in your where clauses. Whether you select many on only items that have dogs, for example, or all items, whether or not they have dogs, you still get back the same results. All of this will be run at the database, either way, so there isn't even a performance benefit to one approach or the other. You also don't need to use Include if you're selecting from the relationships. EF is smart enough to issue the joins based on the data it needs to return.

var query1 = await context.Playarea
    .SelectMany(x => x.Cats.Select(y => new MyClass(x.Id, y.Name, y.Age))).ToListAsync();

There's also the issue of your two dog queries. The same results will be returned for both queries. The only difference is that one set will have y.Name and the other set will have y.Leads.Name as the Name value. The act of including Leads doesn't somehow filter out result that don't have Leads, and of course, the first query does no filtering at all, either way. I'd imagine you'd want something like the following instead:

var query3 = await context.Playarea
        .SelectMany(x => x.Dogs, (x, y) => new MyClass(x.Id, y.Leads is null ? y.Name : y.Leads.Name, y.Age)).ToListAsync();

In other words, Leads.Name will be used if the relationship exists, and it will fallback to Name otherwise.

answered on Stack Overflow Apr 1, 2020 by Chris Pratt

User contributions licensed under CC BY-SA 3.0