Hibernating Rhinos

Zero friction databases

New: Uber Profiler “appender only” nuget package is now available

We started to provide nuget packages for our Uber Profilers early on when nuget was available. Back then, we decided to provide you with a one click solution: you add the NHibernate Profiler (or any other of our profilers) nuget package to your project, and you can start the profiler right away. We open the profiler interface after you install the package and add some initialization code the your project to hook the profiler.

That worked great most of time, when you do a fresh installation of the profiler. But when you are doing upgrades and you do not want to use the default initialization code we provide, it can be annoying to keep removing it once you upgrade. This is way we created this issue, based on user demand.

So you can now use the NHibernateProfiler.Appender or EntityFrameworkProfiler.Appender (the same for our other profilers) package, which contains just the appender dll. No code setup and it is also a much slimmer package, so package restore will also be faster.

Get an insight into what Entity Framework really does under the hoods using EFProf

When using relational databases like SQL Server, MySQL, PostgreSQL, using an O/RM tool like Entity Framework can drastically simplify how you interact with the database data by mapping (or abstracting) the relational data into objects, which are much easier to consume and use in your application.

The problem with such an abstraction is that in a lot of cases, you, or someone else in your team, may use the ORM in a way it you should not be used, which will lead to an application with bad performance. And the real issue about that is that most of the time developers tries to solve those issues down the road, when have some basic mistakes all over the place.

In order to avoid such errors, we developed the Entity Framework Profiler which will give you an insight about what is going on on under the hoods, by showing you the underline SQL that was generated and giving you some highlights/warnings on a bad SQL which should be improved.

In order to see the benefits you can get from using the Entity Framework Profiler we will take nopCommerce, a popular open source e-commerce application that make use of Entity Framework profiler it with the Entity Framework Profiler.

I opened the NopCommerce solution, set the Nop.Web as the default project, installed the EFProf NuGet package and run the website by hitting F5. The browser opened and I get an installation form to fill. I enter the connection string to the SQL Server and I choose the “Use sample data” option. Now let look on the profiler to see what actually the installation action did.

Step1

See what happened. We opened the object context session 3 times. The first object context does nothing. The second one executes 69 queries, most of them to create indexes or stored procedures.  And the third one is inserting our sample data. In both of them we get some alerts such as Select N+1, Too Many Joins or Too Many Database Calls. But since this is an installer which take place just once so we can ignore that.

Now, lets make a search for a product. Searching for shoes, make like 29 queries to the DB:

Step4

As we can see in the alerts section, we can lower the number of queries that we having by fixing the select N+1 issue that we have here.

Clicking on the Computers category and after that on the electronics category and looking on the profiler, I see the following:

Step5

Note that it executed 87 queries. Let pick one of them and analyze it:

Step6

Wow, can you figure out when this query tries to do? I cannot. There is not wondering why the profiler gives the following alerts on this statement:

Step7

So, how can I figure it out? Without the EF Profiler, there is no why to connect between an SQL query to the code that was responsible to generate it. But with EFProf we can just look on the stack trace window and jump right to the code that generated this query:

Step8

And here it is:

/// Gets all categories filtered by parent category identifier
/// </summary>
/// <param name="parentCategoryId">Parent category identifier</param>
/// <param name="showHidden">A value indicating whether to show hidden records</param>
/// <returns>Category collection</returns>
public virtual IList<Category> GetAllCategoriesByParentCategoryId(int parentCategoryId,
    bool showHidden = false)
{
    string key = string.Format(CATEGORIES_BY_PARENT_CATEGORY_ID_KEY, parentCategoryId, showHidden, _workContext.CurrentCustomer.Id, _storeContext.CurrentStore.Id);
    return _cacheManager.Get(key, () =>
    {
        var query = _categoryRepository.Table;
        if (!showHidden)
            query = query.Where(c => c.Published);
        query = query.Where(c => c.ParentCategoryId == parentCategoryId);
        query = query.Where(c => !c.Deleted);
        query = query.OrderBy(c => c.DisplayOrder);

        if (!showHidden)
        {
            //ACL (access control list)
            var allowedCustomerRolesIds = _workContext.CurrentCustomer.CustomerRoles
                .Where(cr => cr.Active).Select(cr => cr.Id).ToList();
            query = from c in query
                    join acl in _aclRepository.Table
                    on new { c1 = c.Id, c2 = "Category" } equals new { c1 = acl.EntityId, c2 = acl.EntityName } into c_acl
                    from acl in c_acl.DefaultIfEmpty()
                    where !c.SubjectToAcl || allowedCustomerRolesIds.Contains(acl.CustomerRoleId)
                    select c;

            //Store mapping
            var currentStoreId = _storeContext.CurrentStore.Id;
            query = from c in query
                    join sm in _storeMappingRepository.Table
                    on new { c1 = c.Id, c2 = "Category" } equals new { c1 = sm.EntityId, c2 = sm.EntityName } into c_sm
                    from sm in c_sm.DefaultIfEmpty()
                    where !c.LimitedToStores || currentStoreId == sm.StoreId
                    select c;

            //only distinct categories (group by ID)
            query = from c in query
                    group c by c.Id
                    into cGroup
                    orderby cGroup.Key
                    select cGroup.FirstOrDefault();
            query = query.OrderBy(c => c.DisplayOrder);
        }

        var categories = query.ToList();
        return categories;
    });

}

As you can see here, it at least using a cacheManager, but even with it we see un-tuned queries.

When you develop an application and uses the Entity Framework Profiler, you can be aware about those issue right from the beginning and avoid repeating the same mistakes, over and over again.

Tags:

Published at

Originally posted at

EFProf now supports EF 6 alpha 3

We got a mail from a customer asking about the support of Entity Framework 6 alpha 3 by the Entity Framework Profiler. It took 2 hours before we got a new build which now support it.

Part of this speedy patch was that the customer provided a nice small project that reproduce the issue, which is always the best way to get things fixed fast, but the most critical thing was that the EF team addressed the issues that we faced in the previous release, EF 6 alpha 2, as outlined here.

So if you are using EF 6 alpha 3, the workaround that was outlined here is not needed anymore.  You should just call `EntityFrameworkProfiler.Initialize()` at the very first of your application and this is it.

Tags:

Published at

Originally posted at

Entity Framework Profiler support for Entity Framework 6 alpha 2

Recently a lot of our users asked us to support Entity Framework 6 alpha 2 in Entity Framework Profiler. We started to look on how we can support it right away but we found that we could not support it easily.

The main issue was that Entity Framework 6 alpha 2 expose a class called DbConfiguration which you can use as an injection point to EF. But the way that EF currently works, it looks for this class only in the same assembly that uses the DbContext, which is your application executable/dll but not our appender dll.

So we contact the EF team (which they were very helpful and responsive) and discussed some of the issues that we saw. While a few of the issues was been addressed already by the EF team and committed to the EF repository, the good news that we found a way to support Entity Framework 6 alpha 2 by adding just one source file to your application.

Take the following file and add to your application: https://gist.github.com/4539561.

Now make sure to use the use build #2111 or later of Entity Framework Profiler and it will work.

I’m also attaching the same source code from the above gist to here. Enjoy using Entity Framework Profiler!

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.Entity.Config;
using System.Data.Entity.Core.Common;
using System.Data.Entity.Core.EntityClient;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.Migrations.Sql;
using System.Data.Entity.SqlServer;
using System.Reflection;
using HibernatingRhinos.Profiler.Appender.EntityFramework;
using HibernatingRhinos.Profiler.Appender.ProfiledDataAccess;

namespace HibernatingRhinos.Profiler.IntegrationTests.EntityFramework6Beta2
{
    public class ProfiledDbConfiguration : DbConfiguration
    {
        public ProfiledDbConfiguration()
        {
            AddDependencyResolver(new ProfiledDbDependencyResolver(this));
        }
    }

    public class ProfiledDbDependencyResolver : IDbDependencyResolver
    {
        private readonly IDbDependencyResolver rootResolver;

#if DEBUG
        public static HashSet<string> types = new HashSet<string>();
#endif

        public ProfiledDbDependencyResolver(DbConfiguration originalDbConfiguration)
        {
            // Get the original resolver
            var internalConfigProp = originalDbConfiguration.GetType().GetProperty("InternalConfiguration", BindingFlags.Instance | BindingFlags.NonPublic);
            var internalConfig = internalConfigProp.GetValue(originalDbConfiguration, null);
            var rootResolverProp = internalConfig.GetType().GetProperty("RootResolver", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
            rootResolver = (IDbDependencyResolver)rootResolverProp.GetValue(internalConfig, null);
        }

        public object GetService(Type type, object key)
        {
#if DEBUG
            types.Add(type.Name);
#endif

            if (type == typeof(IDbProviderFactoryService))
            {
                var innerFactoryService = (IDbProviderFactoryService) rootResolver.GetService(type, key);
                return new ProfiledDbProviderFactoryService(innerFactoryService);
            }

            if (type == typeof (DbProviderServices))
            {
                var inner = (DbProviderServices)rootResolver.GetService(type, key);
                var appender = new EntityFrameworkAppender(typeof (SqlProviderServices).Name);
                var profiledDbProviderServicesType = EntityFrameworkProfiler.CompiledAssembly.GetType("HibernatingRhinos.Profiler.Appender.EntityFramework.ProfiledDbProviderServices");
                if (profiledDbProviderServicesType == null)
                    throw new InvalidOperationException("Could not get the profiled DbProviderServices.");
                return Activator.CreateInstance(profiledDbProviderServicesType, new object[] {inner, appender});
            }

            if (type == typeof(MigrationSqlGenerator))
            {
                if (rootResolver.GetService(type, key) is SqlServerMigrationSqlGenerator)
                {
                    return new ProfiledMigrationSqlGenerator();
                }
            }

            return null;
        }
    }

    public class ProfiledDbProviderFactoryService : IDbProviderFactoryService
    {
        private readonly IDbProviderFactoryService innerFactoryService;

        public ProfiledDbProviderFactoryService(IDbProviderFactoryService innerFactoryService)
        {
            this.innerFactoryService = innerFactoryService;
        }

        public DbProviderFactory GetProviderFactory(DbConnection connection)
        {
            if (connection is ProfiledConnection)
            {
                var connectionType = connection.GetType();
                if (connectionType.IsGenericType)
                {
                    var innerProviderFactory = connectionType.GetGenericArguments()[0];
                    var profiledDbProviderFactory = EntityFrameworkProfiler.CompiledAssembly.GetType("HibernatingRhinos.Profiler.Appender.EntityFramework.ProfiledDbProviderFactory`1").MakeGenericType(innerProviderFactory);
                    return (DbProviderFactory) Activator.CreateInstance(profiledDbProviderFactory);
                }
            }

            if (connection is EntityConnection)
                return innerFactoryService.GetProviderFactory(connection);

            throw new InvalidOperationException("Should have ProfiledConnection but got " + connection.GetType().FullName + ".If you got here, you probably need to modify the above code in order to satisfy the requirements of your application. This code indented to support EF 6 alpha 2.");
        }
    }

    public class ProfiledMigrationSqlGenerator : SqlServerMigrationSqlGenerator
    {
        protected override DbConnection CreateConnection()
        {
            return DbProviderFactories.GetFactory("System.Data.SqlClient").CreateConnection();
        }
    }
}
Tags:

Published at

Originally posted at

Entity Framework Profiler supports for LinqPAD

If you’re using LinqPAD and wants to profile your Entity Framework usage with the Entity Framework Profiler, you can use the following snippet of code in order to achieve that:

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

Please make sure to use the "C# Program" option from the "language" ComboBox, and that’s it. You can now see the the profiling data in the Entity Framework Profiler.

LinqPAD

Published at

Originally posted at

Entity Framework Profiler supports for LinqPAD

If you’re using LinqPAD and wants to profile your Entity Framework usage with the Entity Framework Profiler, you can use the following snippet of code in order to achieve that:

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

Please make sure to use the "C# Program" option from the "language" ComboBox, and that’s it. You can now see the the profiling data in the Entity Framework Profiler.

LinqPAD

Published at

Originally posted at

Comments (1)