Using the Entity Framework Profiler is easy. First, we need to make the application we are profiling aware of the profiler. Then, just start the profiler.

Preparing an application to be profiled

Install EntityFrameworkProfiler NuGet package (or EntityFrameworkProfiler.Appender < /a> to install only the profiler's Appender):

Install-Package EntityFrameworkProfiler -IncludePrerelease

In the application startup (Application_Start in web applications, Program.Main in console applications, or the App constructor for WPF applications), make the following call:

HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();

Beyond the standard operating systems shortcut keys, the profiler supports the following shortcut keys:

One of the DLLs that comes with the profiler isHibernatingRhinos.Profiler.Integration. This DLL provides programmatic access to the profiler object model.On NuGet it's available as EntityFrameworkProfiler.Integration package. For example, you can use it inside your unit tests to assert the behavior of your code.

    var captureProfilerOutput = new CaptureProfilerOutput(@"/path/to/profiler.exe");
    captureProfilerOutput.StartListening();
    EntityFrameworkProfiler.Initialize();
    // run code that uses Entity Frameowrk
    Report report = captureProfilerOutput.StopAndReturnReport();
    // Assert / inspect on the report instance

1. Create a new CaptureProfilerOuput file and pass it the path to the efprof.exe executable.
2. Call StartListening to start listening to your application's behavior.
3. Execute your code
4. Call StopAndReturnReport to get the report and assert or inspect it.

You can also use this DLL to provide programmatic access to the XML report files generated during the continuous integration build. This is done using the following code:

var xml = new XmlSerializer(typeof(Report));
using(var reader = File.OpenText(reportFile))
{
    Report result = (Report)xml.Deserialize(reader);
}

The profiler supports the following command line interface, which allows you to execute the profiler as part of your continuous integration builds:

/CmdLineMode[+|-]         (short form /C)
/File:<string>            (short form /F)
/ReportFormat:{Json|Html|Xml}  (short form /R)
/Port:<int>               (short form /P)
/InputFile:<string>       (short form /I)
/Shutdown[+|-]            (short form /S)

Starting up with no options will result in the UI being displayed.

Here is how we can get a report from our continuous integration build:

EFProf.exe /CmdLineMode /File:report-output /ReportFormat:Json /ReportFormat:Html # starts listening to applications xunit.console.exe Northwind.IntegrationTests.dll EFProf.exe /Shutdown # stop listening to applications and output the report

This approach should make it simple to integrate into your CI process. The JSON and XML output allow programmatic access to the report, while the HTML version is human-readable. You can generate JSON, XML, and HTML reports by specifying the ReportFormat option multiple times.

One thing you might want to be aware of is that writing the report file is done asynchronously, so the shutdown command may return before the file writing is completed. If you need to process the file as part of your build process, you should wait until the first profiler instance completes. Using PowerShell, this is done like this:

EFProf.exe /CmdLineMode /File:report-output /ReportFormat:Json /ReportFormat:Html
xunit.console.exe Northwind.IntegrationTests.dll
EFProf.exe /Shutdown
get-process EFProf | where {$_.WaitForExit() } # wait until the report export is completed

Licensing

Please note that from a licensing perspective, the CI mode is the same as the normal GUI mode. On one hand, this means that you don't need to do anything if you already have the profiler and want to run the CI build on your machine. On the other hand, if you want to run it on a CI machine, you would need an additional license for that.

Entity Framework Profiler can aggregate related sessions under the same scope. By default, in web applications, we aggregate sessions that are opened in the same web page under the same scope. However, we provide an API that you can use to aggregate related sessions in your application using a different strategy.

To start a new scope and dispose of the returned object to end the scope, you can use:

HibernatingRhinos.Profiler.Appender.ProfilerIntegration.StartScope();

You can also give a name to the scope using:

ProfilerIntegration.StartScope("Task #" + taskId);

In addition, you can override the ProfilerIntegration.GetCurrentScopeName() method in order to set the current scope using your application-specific details:

ProfilerIntegration.GetCurrentScopeName = () => { return "Scope ID" };

By default, the profiler logs are sent from an application via TCP, although sometimes your application might not be able to reach the profiler over the network. Using the Azure Storage Channel feature, you can configure the appender to send the logs as blobs to the Azure storage container and analyze them in Entity Framework Profiler. This way, you can profile your Entity Framework queries from Azure Functions.

How it works

The idea behind the Azure Storage Channel is that profiler logs are uploaded as blobs to an Azure container. In Azure, you can configure a subscription so that, on each BlobCreated event, an appropriate message will be created in the Azure storage queue.

The Entity Framework Profiler will listen to those queue messages, download the blobs, analyze them, and display your queries in real time. You can also load existing profiler logs later on.

Setup on Azure

To make the uploaded logs available for Entity Framework Profiler, you need to execute the following steps:

• create a storage account,
• create a container,
• create a storage queue,
• create a subscription to enqueue messages about blob creation.

You can define it using the Azure portal, as shown here , or use an Azure CLI script (Bash, PowerShell)

Appender configuration

When initializing the appender, you need to specify the AzureStorageToLogTo property and provide the connection string and container name where the profiler log blobs will be sent.

HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize(new EntityFrameworkAppenderConfiguration()
                    {
                        AzureStorageToLogTo = new AzureStorageConfiguration("azure-storage-connection-string", "container-name")
                    });

Connecting to Azure storage from the profiler

To load the logs, you need to connect to Azure using Options > Azure Storage Channel

Once you're connected to the Azure channel, the profiler will start showing data from the profiled application that is configured to send logs to the Azure container. You can choose whether the logs should be deleted from the container after processing or if they should stay there. If the container already contains some profiling data, you can also load it into the profiler by providing a date range.

It is possible to generate a snapshot file from your application to be analyzed later using the following code:

HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.InitializeOfflineProfiling(filename);

This will generate a file with a snapshot of all the Entity Framework activity in the application, which you can use for later analysis by loading it into the profiler.

You can instruct the profiler to ignore some parts of your application and not publish any events generated from those sections.

This can be achieved using one of the following ways:

using (ProfilerIntegration.IgnoreAll())
{
    // Ignore all events generated from here
}

Or

ProfilerIntegration.IgnoreAll();
// Ignore all events generated from here
ProfilerIntegration.ResumeProfiling();

You'll want to use this feature when you have a massive amount of operations that you don't need to profile. A typical scenario for this is building a huge database as part of the test process, which can be time-consuming to profile.

Sometimes you want to profile your production application. It can be a website running on a remote machine or a service application. Using the production profiler feature of Entity Framework Profiler, you can connect to your production application via Options > Remote Servers:

Once you're connected to the remote application, the profiler will start showing data from it.

To allow production profiling, your application should add the following line at the very beginning of your application:

EntityFrameworkProfiler.InitializeForProduction(9090, "FfIVXyUkze38r/b2ulve26LQ88NK5AYig+ecYzp3r88=");

The above method will make your application start listening on port 9090 for incoming connections. The second parameter we send to this method is a password that will be used to filter unauthorized access to your application. You should make it strong enough to secure your application.

The traffic over the connection between the profiler and your application will use SslStream, ensuring it is encrypted and secure.

Troubleshooting

To diagnose issues in your production profiling environment, you can set the following value:

ProfilerInfrastructure.ThrowOnConnectionErrors = true;

Common errors include missing permissions in the running service. Setting this value to true will throw an error in your application, allowing you to diagnose the issue.

You can star some statements in the profiler using the ProfilerIntegration API by calling:

HibernatingRhinos.Profiler.Appender.ProfilerIntegration.StarStatements("Yellow");

After this call, each new statement will be marked with a yellow star. You can stop new statements from being starred by using:

ProfilerIntegration.StarStatementsClear();

Here is a usage example:

using (var db = new Entities(conStr))
{
    var post1 = db.Posts.FirstOrDefault();

    using (ProfilerIntegration.StarStatements("Blue"))
    {
        var post2 = db.Posts.FirstOrDefault();
    }

    var post3 = db.Posts.FirstOrDefault();

    ProfilerIntegration.StarStatements();
    var post4 = db.Posts.FirstOrDefault();
    ProfilerIntegration.StarStatementsClear();

    var post5 = db.Posts.FirstOrDefault();
}

In the above example, only post2 and post4 will be starred.post2 will get a blue star, while post4 will get the default star, which is yellow.

Note that StarStatements can use the following colors: Yellow, Orange, Red, Green, Blue, and Gray.

While using the profiler you may be prompted by your firewall to allow the profiler to continue working.

The profiler works by listening on TCP sockets for events from Entity Framework. To do this, it needs to listen to a socket, which can sometimes trigger an alert in firewalls.

Denying permission to start listening on a socket will disable the profiler's ability to do live profiling. It is strongly recommended that you allow it.

The profiler also checks for updates at each startup. In the event of a profiler error, it will ask if you allow it to submit the error details for resolution.

In addition, the profiler will periodically report (anonymously) which features are being used in the application, helping us focus on the most commonly used parts of the product.

If you purchased the licenses, you may use them wherever you want, as long as only you are using them.

If your company purchased the license, you would need a separate license for home use. 'Per seat' refers to the number of people in an organization using the software, not the number of machines it is installed on.

Note that there is a corollary to this: a single machine used by two developers, with both using LINQ to SQL Prof, requires two licenses, one for each user of the profiler.

Unable to cast ProfiledConnection to SqlConnection

If you try to cast IDbConnection to SqlConnection in your code, you will encounter an unable to cast ProfiledConnection to SqlConnection exception when using the Entity Framework profiler. This is because when you call EntityFrameworkProfiler.Initialize() method, we replace the default DbProviderFactory with our own ProfiledDbProviderFactory.

The solution is to avoid casting IDbConnection to SqlConnection, and instead cast it to DbConnection. It's better practice not to assume the DbConnection type in your code and always use the base IDbConnection and DbConnection classes.

The profiler shows the session but without any statements

If you run a program that exits very quickly, you might see the session in the profiler without any log statements. This happens because the profiler hasn't had the chance to log the statements before your application exits. To resolve this, you can force the profiler to log all the statements by adding the following line at the end of your code:

ProfilerInfrastructure.FlushAllMessages();

If you're using LinqPAD and want to profile your Entity Framework usage with the Entity Framework Profiler, you can use the following code snippet to achieve that:

void Main()
{
    HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();
    Blogs.Take(50).Dump();
}

Make sure to select the C# Program option from the language ComboBox, and that's it. You can now view the profiling data in the Entity Framework Profiler.

Since Entity Framework Profiler 3.0, there is an option called 'Detect Connection String' .
When enabled, it adds the connection string of the most recent connection, if it does not already exist in the connection list, and sets it as the default connection.

Since the connection string can contain sensitive information that we don't want to expose, we provide the option to ignore the passing of the connection string when initializing the HibernatingRhinos Profiler Appender.

Example of initialization code:

EntityFrameworkProfiler.Initialize(new EntityFrameworkAppenderConfiguration
{
    IgnoreConnectionStrings = false
});

You might encounter an error denying access to the license file when first using the Profiler. This is typically caused by Windows locking the Profiler because it was downloaded from the Internet.

To resolve this issue, navigate to the folder where you extracted the Profiler, right-click on EFProf.exe, and select Properties.

Next, click on the Unlock button to grant the Profiler standard application permissions and resolve the issue.

There is a known compatibility issue between the Entity Framework Profiler and the UltraMon program on some computers. If you are running the UltraMon program on your computer and try to maximize the profiler window on one of your monitors, you may encounter the following exception:

An error occurred in Entity Framework Profiler which is known to be caused by using UltraMon on your computer. Please follow the instructions in the following URL in order to solve this issue: http://nhprof.com/Learn/Faq/UltraMon

This is because the UltraMon program tries to inject controls into the Entity Framework Profiler windows.

If you get the above message, instruct your UltraMon program not to inject buttons into the Entity Framework Profiler windows. To do this, follow these steps:

1. Right-click on the UltraMon icon in the System Tray and select option.
2. On the Compatibility tab, select Configure Settings.
3. From the Running Applications list, select the Entity Framework Profiler and click Next.
4. Ensure that the Disable Window Buttons option is checked, then click Finish.
5. Click OK to exit the UltraMon options window.

The license includes all updates within a point release. If you purchase a license for v1.0, you will receive all updates for any v1.x version at no additional cost.

The profiler has detected a query with a large number of joins, such as the following:

select * from Blogs blog0_
inner join Posts posts1_
    on blog0_.Id = posts1_.BlogId
inner join Comments comments2_
    on posts1_.Id = comments2_.PostId
inner join Users user3_
    on posts1_.UserId = user3_.Id
inner join UsersBlogs users4_
    on blog0_.Id = users4_.BlogId
inner join Users user5_
    on users4_.UserId = user5_.Id

Queries with too many joins can cause performance issues.

Each join requires the database to perform additional work, and the complexity and cost of the query grow rapidly with each additional join. While relational databases are optimized for handling joins, it is often more efficient to perform several separate queries rather than a single query with multiple joins.

For OLTP systems, you should consider simplifying your queries or the data model. While we do not recommend avoiding joins entirely, we strongly discourage queries with a large number of joins.

Another issue to pay attention to is the potential for Cartesian products in queries containing joins. It is easy to create such a situation without noticing it during development.

With an ORM abstraction like Entity Framework, it's easy to generate queries that are not optimized. In particular, this alert is triggered when we detect that the type of the entity's field, used as a parameter in the query, does not exactly match the table's column type in the database.

A column type mismatch between the entity's property type and the table's column type can lead to several implicit conversion issues, which may cause performance problems and conversion overflow errors. Most commonly, these issues prevent the database from utilizing indexes properly.

Queries still work, and everything appears fine externally, but the query cost is significantly higher than it would be otherwise.

Common examples of type mismatches include:

• Int32 vs bigint

• Int64 vs int

• String vs char / varchar / nvarchar(len mismatch)

• AnsiString vs nvarchar / nchar / varchar (len mismatch)

To resolve this issue, ensure that the column type and entity definitions match in all respects. Refer to the common type mismatches listed above for guidance.

The profiler detected identical statements using different sizes for the same parameters. For example, consider two queries issued to find users by name.

We can do this using the following queries:
(Note: I am using a syntax that shows the parameter sizes to demonstrate the issue).

exec sp_executesql
      N'SELECT * FROM Users WHERE Username = @username',
      N'@username nvarchar(3)',
      @username=N'bob'

exec sp_executesql
      N'SELECT * FROM Users WHERE Username = @username',
      N'@username nvarchar(4)',
      @username=N'john'

This type of code results in two query plans being stored in the database query cache due to the different parameter sizes. In fact, assuming the Username column has a length of 16, this single query could occupy 16 entries in the query cache.

Worse, if you have two parameters with varying sizes, such as username (length 16) and password (length 16), you could occupy up to 256 entries in the query cache. Clearly, if you use more parameters or if their lengths increase, the number of cache entries a single query can consume grows rapidly.

This can cause performance problems, as the database needs to keep track of more query plans, consuming more memory. It may also require evicting query plans from the cache, which leads to rebuilding the query plan, increasing server load and query time.

Entity Framework 3.5 doesn't provide a solution for this issue. Instead, you should consider upgrading to Entity Framework 4.0, which resolves this problem. Alternatively, you can switch from using an Entity Framework query to an ADO.NET query, explicitly specifying the parameter sizes.

This alert is triggered when the profiler detects that a query was generated from the view in an MVC application. Issuing queries from the view is considered a bad practice for several reasons:

• It increases the time the connection to the database needs to stay open. The recommendation is to keep the connection open only for the duration of the action, not throughout the lifetime of the request.
• It makes it much harder to understand the data requirements for a particular action.
• When writing views, you shouldn't have to worry about persistence or the number of queries your views are generating.
• Views are often the most changeable parts of an application, and having the application issue queries from the views can lead to significant changes in the data access patterns between revisions.
• Most often, queries from views result from lazy loading, Select N+1, or similar bad practices.

We strongly recommend avoiding generating queries in the view. Instead, perform all queries in the action and provide in-memory access to the view for rendering.

The database is very efficient at handling queries that search for an exact match, such as this:

select * from Users where Name = 'ayende'

But it requires a lot more work when you use a LIKE query, such as this:

select * from Users where Name like 'ayende%'

In many cases, the database can still optimize this query, and if you have an index on the field, it will use the index to speed up the search.

But things are drastically different when you have a query that checks for "contains" :

select * from Users where Name like '%ayende%'

Or using "ends with" :

select * from Users where Name like '%ayende'

The problem is that the database cannot use an index for this type of query, and is forced to issue a full table scan, inspecting each value in the database for a match. This tends to be very inefficient process.

You should carefully consider whether you should use this feature. If you really need to support "ends with" and "contains" queries, you should consider using either the database's own full-text search indexing or an external full-text search solution, such as Lucene or Solr.

The excessive number of rows returned warning is generated by the profiler when a query returns a large number of rows. The simplest scenario is when we load all the rows from a large table, using something like the following code snippet:

var allPosts = blogDataContext.Posts.ToList();

This is a common mistake when binding to a UI component (such as a grid) that performs its own paging. This is problematic on several levels:

• We tend to want to see only part of the data.
• We just loaded a lot of unnecessary data.
• We are sending more data than necessary over the network.
• We have a higher memory footprint than we should.
• In extreme cases, we may crash due to an out-of-memory exception.

None of these are ideal, and like the discussion on unbounded result sets, this problem can be easily prevented by applying a limit at the database level to the number of rows we want to load at any given time.

This warning is raised when the profiler detects that you are writing a lot of data to the database. Similar to the warning about too many calls to the database, the main issue here is the number of remote calls and the time they take.

Unlike the more common Select N+1, the issue with this alert isn't the number of selects, but the number of write statements (delete, create, update) being sent to the database. There is no single solution to this problem, but a common approach is to change the write behavior from directly using Entity Framework to a batch or bulk approach, such as performing the changes in a stored procedure in the database itself.

Using more than one object context per request is generally bad practice. Here's why:

1. Each object context has its own database connection. Using more than one object context means using more than one database connection per request, resulting in additional strain on the database and slower overall performance.
2. Typically, we expect the object context to keep track of our entities. When we have multiple object contexts, each object context is not aware of the entities tracked by the other object context and might have to query the database again for their current state or issue an unnecessary update.
3. Having more than a single object context also means that we can't take advantage of Entity Framework's Unit of Work and have to manually manage our entities' change tracking. We might end up with multiple instances of the same entities in the same request, which using a single object context for the whole request would prevent.
4. Having more than one object context means that the ORM has more work to do. In most cases, this is unnecessary and should be avoided.

It's usually recommended to use one object context per request. You should consider investigating theSession Per Request pattern.

Typically, this situation results from micromanaging the object context, meaning we create the object context just before the query and destroy it immediately after the query or operation is executed. For example, see the following code:

public T GetEntity<T>(int id)
{
    using (var ctx = new NorthwindContext())
    {
         return ctx.Get<T>(id);
    }
}

It is strongly recommended to use a contextual object context, such as the one described in the link above.

Writing to the database from more than one object context in the same request is bad for several reasons. Here's why:

1. Each object context uses a separate database connection. Using more than one object context means using more than one database connection per request. This can hurt overall performance and put more pressure on the database.
2. Using different object contexts means that we cannot take advantage of the database's built-in transaction support and must rely on System.Transactions, which is significantly slower.
3. We can't rely on the database to ensure a transactionally safe view of the data since we are using several different transactions to access the database. Note that this remains true even when using System.Transactions.
4. When using System.Transactions, it forces you to useDTC in order to to keep all sessions in the same transaction. Using DTC leads to poor performance and is more brittle than avoiding it.
5. Without using System.Transactions, there is no ability to use a transaction across all the sessions.

For example, consider the following code, which uses multiple object contexts to perform a single operation:

public void TransferMoney(Account from, Account to, Money amount)
{
    Dao.Withdraw(from, amount);
    Dao.Deposit(to, amount);
}

public void Withdraw(Account account, Money amount)
{
    using(var ctx = new MyBankContext())
    {
        account.MoenyAmount -= amount;
        ctx.Accounts.Attach(account);
        ctx.SaveChanges();
    }
}

public void Deposit(Account account, Money amount)
{
    using(var ctx = new MyBankContext())
    {
        account.MoenyAmount += amount;
        ctx.Accounts.Attach(account);
        ctx.SaveChanges();
    }
}

In this example, we call the database twice, each time from a different object context. Because of this, we cannot take advantage of the database's native transaction support, since each operation happens in a different transaction. Our choices are to either operate essentially without transactions (in which case, money can literally disappear into the air), or participate in a distributed transaction (System.Transactions).

The solution for this is simple: Use just one object context per request.

This alert is raised whenever the profiler detects that a query was generated as a result of a data binding operation.

The simplest form of this is binding to a LINQ query against the database:

DataContext = from user in ctx.Users
where user.IsActive
select user;

The problem with this is that the Win Forms / WPF data binding code was designed with the assumption that it would work against in-memory data. Therefore, setting a data source usually triggers multiple calls to interface methods to obtain the data. As a consequence, when performing data binding against a database query directly, the query is often evaluated a couple of times, triggering multiple round-trips to the server.

The recommendation is to bind to the result of the query:

var activeUsers = from user in ctx.Users
where user.IsActive
select user;
DataContext = activeUsers.ToList();

Another problem with data binding that the profiler will detect and warn about is lazy loading as a result of a data binding operation. For example, binding to Order.Customer.Name will lead to loading the customer entity and binding to its Name property. The problem with those sorts of queries is that they are likely to repeat for as many items as there are in the query, leading to Select N+1.

Even assuming you are binding to just a single entity, lazy loading through data binding is a bad practice. You should use eager loading to load the data upfront, rather than letting the UI load it in the worst possible manner.

We have detected a query that was made on an unindexed column. The problem here is that a query on an unindexed column forces the database to perform a table scan. Such an action requires the database to go through all the rows in the table.

In some cases, on very small tables, that is fine and the best way to find the results you want. On larger tables, however, that can be an excessively expensive operation, which usually ends up biting you only after the dataset has grown to production size, often failing at 2 AM or thereabouts.

Consider this query carefully and evaluate whether an index needs to be created, the query needs to be modified, or if this table is small enough that a table scan is the appropriate behavior.

Select N + 1 is a data access anti-pattern where the database is accessed in a suboptimal way. Take a look at this code sample, and then we'll discuss what is going on. Say you want to show the user all comments from all posts so that they can delete all of the nasty comments. The naive implementation would be something like:

// SELECT * FROM Posts
var postsQuery = from post in blogDataContext.Posts
                 select post;

foreach (Post post in postsQuery)
{
    // lazy loading of comments list causes:
    // SELECT * FROM Comments where PostId = @p0
    post.Comments.Load();
    foreach (Comment comment in post.Comments)
    {
        //print comment...
    }
}

In this example, we can see that we are loading a list of posts (the first select) and then traversing the object graph. However, we access the collection in a lazy fashion, causing Entity Framework to go to the database and bring the results back one row at a time. This is incredibly inefficient, and the Entity Framework Profiler will generate a warning whenever it encounters such a case.

The solution to this example is simple. Force an eager load of the collection using the Include method to specify what pieces of the object model we want to include in the initial query.

// SELECT * FROM Posts JOIN Comments ...
var postsQuery = (from post in blogDataContext.Posts.Include("Comments")
                 select post);

foreach (Post post in postsQuery)
{
    // no lazy loading of comments list causes
    foreach (Comment comment in post.Comments)
    {
        // print comment...
    }
}

In this case, we will get a join and only a single query to the database.

Note: this is the classical appearance of the problem. It can also surface in other scenarios, such as calling the database in a loop, or more complex object graph traversals. In those cases, it is generally much harder to see what is causing the issue.

Having said that, Entity Framework Profiler will detect those scenarios just as well and give you the exact line in the source code that caused this SQL to be generated.

Select N + 1 is a data access anti-pattern where the database is accessed in a suboptimal way. This alert, in essence, is a combination of the classical N+1 and themore than one session per request alerts.

Take a look at this code sample, then we'll discuss what is going on. Say you want to show the user all comments from all posts so that they can delete all of the nasty comments. The naive implementation would be something like:

var posts = Dao.GetRecentPosts();
foreach(var post in posts)
{
       post.Comments = Dao.GetCommentsForPost(post);
}

public IEnumerable<Post> GetRecentPosts()
{
    using(var ctx = new MyBlogContext())
    {
        return (from post in ctx.Posts
            orderby post.PublishedDate descending
            select post)
            .Take(50)
            .ToList();
    }
}
public IEnumerable<Comment> GetCommentsForPost(Post post)
{
    using(var ctx = new MyBlogContext())
    {
        return (from comment in ctx.Comments
            where comment.Post.Id = post.Id
            select comment)
            .ToList();
    }
}

In this example, we can see that we are opening a session and loading a list of posts (the first select). Then, for each of the posts that we loaded, we open up a new session, including a new connection to the database, and make an additional query.

Because we are using multiple sessions in this fashion, we can't really take advantage of Entity Framework's features that are meant to deal with this exact situation. The first step is to avoid using more than one session per request, and the next step is to follow the guidance on solving Select N+1 problems for Entity Framework.

One of the most expensive operations that we can do in our applications is to make a remote call. Going beyond our own process is an extremely expensive operation. Going beyond the local machine is more expensive still.

Calling the database, whether to query or to write, is a remote call, and we want to reduce the number of remote calls as much as possible. This warning is raised when the profiler notices that a single request is making an excessive number of calls to the database. This is usually an indication of a potential optimization in the way the sessions are used in the request.

There are several reasons why this can be:

• A large number of queries as a result of a Select N+1
• Calling the database in a loop
• Updating (or inserting / deleting) a large number of entities
• A large number of (different) queries that we execute to perform our task

For the first reason, see the suggestions for Select N+1.

Calling the database in a loop is generally a bug and should be avoided. Usually you can restructure the code in such a way that you are not required to call the database in that way.

The last two issues have to be dealt with on an individual basis. Sometimes, using a different approach (such as bulk copy for batching inserts) or a specific query or stored procedure that will get all the data from the database in a single round trip.

One of the most expensive operations that we can do in our applications is to make a remote call. Going beyond our own process is an extremely expensive operation. Going beyond the local machine is more expensive still.

Calling the database, whether to query or to write, is a remote call, and we want to reduce the number of remote calls as much as possible. This warning is raised when the profiler notices that a single session is making an excessive number of calls to the database. This is usually an indication of a potential optimization in the way the session is used.

There are several reasons why this can be:

• A large number of queries as a result of a Select N+1
• Calling the database in a loop
• Updating (or inserting / deleting) a large number of entities
• A large number of (different) queries that we execute to perform our task

For the first reason, see the suggestions for Select N+1.

Calling the database in a loop is generally a bug and should be avoided. The code can usually be restructured to eliminate the need for such calls.

The last two issues should be addressed on a case-by-case basis. Sometimes, using a different approach, such as bulk copy for batching inserts, or a specific query or stored procedure that retrieves all the data from the database in a single round trip, can be more efficient.

With the abstraction of an ORM, such as Entity Framework, it's easy to generate queries that are hard to follow and have performance penalties, since you don’t see the exact SQL that is produced. This often leads to writing code that produces inefficient SQL queries.

The SQL is bad not only because of its readability but also because of the additional work the database's query optimizer must perform to process such a query.

In this case, we detected a query with complex expressions in the WHERE clause. Having too many expressions in the WHERE statement can lead to poor performance, especially as the dataset grows.

Consider the complexity of your statement and whether you can reduce the work done by refactoring your code to minimize the effort required by the database to process your queries.

With the abstraction of an ORM like Entity Framework, it is easy to generate queries that are hard to follow and come with performance penalties. Since you don't see the exact SQL that is generated, you might write code that results in inefficient SQL. The issue is not only with the readability of the SQL but also with the workload required by the database's query optimizer to handle such a query.

To demonstrate such a query, consider the one below. It contains many nested SELECT statements, making the SQL hard to follow and slower for the database to execute. The query is lengthy and performs numerous operations, none of which are particularly fast.

The problem with such queries is that the code generating them may seem innocent, yet it hides their costly impact. Consider modifying the queries that triggered this warning; in some cases, splitting them into separate, independently executed queries may offer better performance.

SELECT [Project4].[Id]                             AS [Id],
       [Project4].[Name]                           AS [Name],
       [Project4].[Description]                    AS [Description],
       [Project4].[CategoryTemplateId]             AS [CategoryTemplateId],
       [Project4].[MetaKeywords]                   AS [MetaKeywords],
       [Project4].[MetaDescription]                AS [MetaDescription],
       [Project4].[MetaTitle]                      AS [MetaTitle],
       [Project4].[ParentCategoryId]               AS [ParentCategoryId],
       [Project4].[PictureId]                      AS [PictureId],
       [Project4].[PageSize]                       AS [PageSize],
       [Project4].[AllowCustomersToSelectPageSize] AS [AllowCustomersToSelectPageSize],
       [Project4].[PageSizeOptions]                AS [PageSizeOptions],
       [Project4].[PriceRanges]                    AS [PriceRanges],
       [Project4].[ShowOnHomePage]                 AS [ShowOnHomePage],
       [Project4].[IncludeInTopMenu]               AS [IncludeInTopMenu],
       [Project4].[HasDiscountsApplied]            AS [HasDiscountsApplied],
       [Project4].[SubjectToAcl]                   AS [SubjectToAcl],
       [Project4].[LimitedToStores]                AS [LimitedToStores],
       [Project4].[Published]                      AS [Published],
       [Project4].[Deleted]                        AS [Deleted],
       [Project4].[DisplayOrder]                   AS [DisplayOrder],
       [Project4].[CreatedOnUtc]                   AS [CreatedOnUtc],
       [Project4].[UpdatedOnUtc]                   AS [UpdatedOnUtc]
FROM   (SELECT [Limit1].[Id]                             AS [Id],
               [Limit1].[Name]                           AS [Name],
               [Limit1].[Description]                    AS [Description],
               [Limit1].[CategoryTemplateId]             AS [CategoryTemplateId],
               [Limit1].[MetaKeywords]                   AS [MetaKeywords],
               [Limit1].[MetaDescription]                AS [MetaDescription],
               [Limit1].[MetaTitle]                      AS [MetaTitle],
               [Limit1].[ParentCategoryId]               AS [ParentCategoryId],
               [Limit1].[PictureId]                      AS [PictureId],
               [Limit1].[PageSize]                       AS [PageSize],
               [Limit1].[AllowCustomersToSelectPageSize] AS [AllowCustomersToSelectPageSize],
               [Limit1].[PageSizeOptions]                AS [PageSizeOptions],
               [Limit1].[PriceRanges]                    AS [PriceRanges],
               [Limit1].[ShowOnHomePage]                 AS [ShowOnHomePage],
               [Limit1].[IncludeInTopMenu]               AS [IncludeInTopMenu],
               [Limit1].[HasDiscountsApplied]            AS [HasDiscountsApplied],
               [Limit1].[SubjectToAcl]                   AS [SubjectToAcl],
               [Limit1].[LimitedToStores]                AS [LimitedToStores],
               [Limit1].[Published]                      AS [Published],
               [Limit1].[Deleted]                        AS [Deleted],
               [Limit1].[DisplayOrder]                   AS [DisplayOrder],
               [Limit1].[CreatedOnUtc]                   AS [CreatedOnUtc],
               [Limit1].[UpdatedOnUtc]                   AS [UpdatedOnUtc]
        FROM   (SELECT [Distinct1].[Id] AS [Id]
FROM   (SELECT [Extent1].[Id]               AS [Id1],
                [Extent1].[ParentCategoryId] AS [ParentCategoryId],
                [Extent1].[LimitedToStores]  AS [LimitedToStores]
        FROM   [dbo].[Category] AS [Extent1]
                LEFT OUTER JOIN [dbo].[AclRecord] AS [Extent2]
                    ON ([Extent1].[Id] = [Extent2].[EntityId])
                    AND (N'Category' = [Extent2].[EntityName])
        WHERE  ([Extent1].[Published] = 1)
                AND ([Extent1].[Deleted] <> 1)
                AND (([Extent1].[SubjectToAcl] <> 1)
                        OR (([Extent2].[CustomerRoleId] IN (3))
                            AND ([Extent2].[CustomerRoleId] IS NOT NULL)))) AS [Filter1]
        LEFT OUTER JOIN [dbo].[StoreMapping] AS [Extent3]
            ON ([Filter1].[Id1] = [Extent3].[EntityId])
            AND (N'Category' = [Extent3].[EntityName])
WHERE  ([Filter1].[ParentCategoryId] = 7 /* @p__linq__0 */)
        AND (([Filter1].[LimitedToStores] <> 1)
                OR (1 /* @p__linq__1 */ = [Extent3].[StoreId]))) AS [Distinct1]) AS [Project2]
OUTER APPLY (SELECT TOP (1) [Filter3].[Id2]                            AS [Id],
                    [Filter3].[Name]                           AS [Name],
                    [Filter3].[Description]                    AS [Description],
                    [Filter3].[CategoryTemplateId]             AS [CategoryTemplateId],
                    [Filter3].[MetaKeywords]                   AS [MetaKeywords],
                    [Filter3].[MetaDescription]                AS [MetaDescription],
                    [Filter3].[MetaTitle]                      AS [MetaTitle],
                    [Filter3].[ParentCategoryId]               AS [ParentCategoryId],
                    [Filter3].[PictureId]                      AS [PictureId],
                    [Filter3].[PageSize]                       AS [PageSize],
                    [Filter3].[AllowCustomersToSelectPageSize] AS [AllowCustomersToSelectPageSize],
                    [Filter3].[PageSizeOptions]                AS [PageSizeOptions],
                    [Filter3].[PriceRanges]                    AS [PriceRanges],
                    [Filter3].[ShowOnHomePage]                 AS [ShowOnHomePage],
                    [Filter3].[IncludeInTopMenu]               AS [IncludeInTopMenu],
                    [Filter3].[HasDiscountsApplied]            AS [HasDiscountsApplied],
                    [Filter3].[SubjectToAcl]                   AS [SubjectToAcl],
                    [Filter3].[LimitedToStores]                AS [LimitedToStores],
                    [Filter3].[Published]                      AS [Published],
                    [Filter3].[Deleted]                        AS [Deleted],
                    [Filter3].[DisplayOrder]                   AS [DisplayOrder],
                    [Filter3].[CreatedOnUtc]                   AS [CreatedOnUtc],
                    [Filter3].[UpdatedOnUtc]                   AS [UpdatedOnUtc]
        FROM   (SELECT [Extent4].[Id]                             AS [Id2],
                    [Extent4].[Name]                           AS [Name],
                    [Extent4].[Description]                    AS [Description],
                    [Extent4].[CategoryTemplateId]             AS [CategoryTemplateId],
                    [Extent4].[MetaKeywords]                   AS [MetaKeywords],
                    [Extent4].[MetaDescription]                AS [MetaDescription],
                    [Extent4].[MetaTitle]                      AS [MetaTitle],
                    [Extent4].[ParentCategoryId]               AS [ParentCategoryId],
                    [Extent4].[PictureId]                      AS [PictureId],
                    [Extent4].[PageSize]                       AS [PageSize],
                    [Extent4].[AllowCustomersToSelectPageSize] AS [AllowCustomersToSelectPageSize],
                    [Extent4].[PageSizeOptions]                AS [PageSizeOptions],
                    [Extent4].[PriceRanges]                    AS [PriceRanges],
                    [Extent4].[ShowOnHomePage]                 AS [ShowOnHomePage],
                    [Extent4].[IncludeInTopMenu]               AS [IncludeInTopMenu],
                    [Extent4].[HasDiscountsApplied]            AS [HasDiscountsApplied],
                    [Extent4].[SubjectToAcl]                   AS [SubjectToAcl],
                    [Extent4].[LimitedToStores]                AS [LimitedToStores],
                    [Extent4].[Published]                      AS [Published],
                    [Extent4].[Deleted]                        AS [Deleted],
                    [Extent4].[DisplayOrder]                   AS [DisplayOrder],
                    [Extent4].[CreatedOnUtc]                   AS [CreatedOnUtc],
                    [Extent4].[UpdatedOnUtc]                   AS [UpdatedOnUtc]
                FROM   [dbo].[Category] AS [Extent4]
                    LEFT OUTER JOIN [dbo].[AclRecord] AS [Extent5]
                        ON ([Extent4].[Id] = [Extent5].[EntityId])
                            AND (N'Category' = [Extent5].[EntityName])
                WHERE  ([Extent4].[Published] = 1)
                    AND ([Extent4].[Deleted] <> 1)
                    AND (([Extent4].[SubjectToAcl] <> 1)
                            OR (([Extent5].[CustomerRoleId] IN (3))
                                AND ([Extent5].[CustomerRoleId] IS NOT )))NULL) AS [Filter3]
            LEFT OUTER JOIN [dbo].[StoreMapping] AS [Extent6]
                ON ([Filter3].[Id2] = [Extent6].[EntityId])
                    AND (N'Category' = [Extent6].[EntityName])
        WHERE  ([Filter3].[ParentCategoryId] = 7 /* @p__linq__0 */)
            AND (([Filter3].[LimitedToStores] <> 1)
                    OR (1 /* @p__linq__1 */ = [Extent6].[StoreId]))
            AND ([Project2].[Id] = [Filter3].[Id2])) AS [Limit1]) AS [Project4]
ORDER  BY [Project4].[DisplayOrder] ASC

With an ORM abstraction like Entity Framework, it's easy to generate queries that are not optimized.

In this case, we detected that your query spans a large number of tables, which may lead to poor performance and become too slow, especially as your dataset grows. The more tables involved in the query, the more work the database has to perform, whether it's through subselects, joins, or nested queries.

Consider optimizing the query to use fewer tables, or even splitting it into multiple separate queries if that proves to be more performant.

Example of a query with multiple tables:

select blog0_.Id as Id15_0_,
  posts1_.Id as Id20_1_,
  comments2_.Id as Id19_2_,
  blog0_.Subtitle as Subtitle15_0_,
  blog0_.AllowsComments as AllowsCo4_15_0_,
  blog0_.CreatedAt as CreatedAt15_0_,
  posts1_.Title as Title20_1_,
  posts1_.BlogId as BlogId20_1_,
  comments2_.PostId as PostId1__,
  comments2_.Id as Id1__,
  user3_.Password as Password22_3_,
  user3_.Username as Username22_3_,
  user3_.Bio as Bio22_3_,
  user5_.Password as Password22_4_,
  user5_.Username as Username22_4_,
  user5_.Bio as Bio22_4_,
  users4_.BlogId as BlogId2__,
  users4_.UserId as UserId2__
from Blogs blog0_
  inner join Posts posts1_
   on blog0_.Id = posts1_.BlogId
  inner join Comments comments2_
   on posts1_.Id = comments2_.PostId
  inner join Users user3_
   on posts1_.UserId = user3_.Id
  inner join UsersBlogs users4_
   on blog0_.Id = users4_.BlogId
  inner join Users user5_
on users4_.UserId = user5_.Id

With the abstraction of an ORM like Entity Framework, it's easy to create code that is hard to follow and has performance penalties, since you don't see the exact SQL produced in the end. This often leads to writing code that results in inefficient SQL. The SQL is not only hard to read but also requires more work from the database's query analyzer to execute.

In this case, we detected that your query contains many WHERE statements. Having too many WHERE statements can lead to poor performance, especially as your dataset grows.

We suggest keeping the WHERE clauses to a minimum.

An unbounded result set occurs when a query is performed without explicitly limiting the number of returned results. Typically, this means the application assumes that a query will always return only a few records. While this may work well in development and testing, it can become a ticking time bomb in production.

The query may suddenly start returning thousands, or even millions, of rows. This leads to increased load on the database server, the application server, and the network. In many cases, it can cause the entire system to grind to a halt, often resulting in application server crashes due to out-of-memory errors.

Here's an example of a query that will trigger the unbounded result set warning:

var query = from post in blogDataContext.Posts
            where post.Category == "Performance"
            select post;

If the performance category contains many posts, all of them will be loaded, which is likely unintended. This can be easily fixed by using pagination with the Take() method:

var query = (from post in blogDataContext.Posts
            where post.Category == "Performance"
            select post)
            .Take(15);

Now, we ensure that only a predictable, small result set is handled. If we need to process all the records, we can page through them as needed. Paging is implemented using the Skip() method, which instruct Entity Framework to skip N records at the database level before fetching the next page.

Another common occurrence of the unbounded result set problem arises from directly traversing the object graph, as shown in the following example:

var post = postRepository.Get(id);
foreach (var comment in post.Comments)
{
    // do something interesting with the comment
}

Here, again, we are loading the entire set without considering how large the result set may be. Entity Framework does not provide a straightforward way to paginate through a collection when traversing the object graph. It is recommended to issue a separate and explicit query for the contents of the collection, allowing you to page through that collection without loading too much data into memory.

Entity Framework Profiler has detected that a data context is being used on a thread different from the one it was opened on.

Entity Framework object contexts are not thread safe. Attempting to use them across multiple threads requires careful synchronization. It is generally recommended to treat an object context as valid only within the thread that created it.

There are valid scenarios for using an object context across threads (e.g., loading data on a background thread). However, this alert usually indicates an issue with serializing access to the data context.

If you are intentionally using the object context across multiple threads and are certain that you're correctly serializing access to it, you can safely ignore this alert.

You can also disable this alert entirely in the options dialog.

Whenever Entity Framework encounters an error (such as a transaction deadlock, optimistic concurrency exception, etc.), the Entity Framework Profiler will detect it and show you the full error, including the exception message and the stack trace.