Debugging and Profiling LINQ Queries using Devart LINQ Insight

Language Integrated Query (LINQ) is one of the features that makes .NET developers happy. LINQ opened up the opportunities to uniformly query in-memory objects, data in relational DBs, OData Services and many other data sources. Writing LINQ queries is fun as they reduce the usage of loops in code and keep the code base simple.

There are several ways to write LINQ queries to obtain a given result. As programmers, it is our responsibility to find the best way out of them and use that to make the applications perform better. Also, LINQ queries are hard to debug. It is not possible to debug a LINQ query using immediate window of Visual Studio debugging tools. So, we can’t say if a LINQ query is completely correct unless we write enough unit tests around it. Though unit tests are a good way to verify, nothing beats the ability of debugging the query by changing parameters.

LINQ Insight is a Visual Studio extension from Devart that makes debugging and profiling LINQ queries easier. It also provides profiling data for the LINQ queries and for CRUD operations performed using LINQ to in-memory objects as well as LINQ to remote objects (LINQ to SQL, Entity Framework, NHibernate and others). In this post, we will see a few of the features of this tool.

Debugging LINQ Queries
Once you installed LINQ Insight and restarted Visual Studio, you will see the options to see LINQ Interactive and LINQ Profiler windows under the View menu:

To debug the queries, we need to view the LINQ Interactive window. Select this option to view the window. Say, I have the following generic list of Persons in my application:

var people = new List<Person>() {
    new Person(){Id=1, Name="Ravi", Occupation="Software Professional", City="Hyderabad", Gender='M'},
    new Person(){Id=2, Name="Krishna", Occupation="Student", City="Bangalore", Gender='M'},
    new Person(){Id=3, Name="Suchitra", Occupation="Self Employed", City="Delhi", Gender='F'},
    new Person(){Id=4, Name="Kamesh", Occupation="Business", City="Kolkata", Gender='M'},
    new Person(){Id=5, Name="Rani", Occupation="Govt Employee", City="Hyderabad", Gender='F'}
};

Let’s write a LINQ query to fetch all males or, females from this list using a character parameter. Following is the query:

var gender = 'M';

var males = people.Where(p => p.Gender == gender).Select(p => new { Name=p.Name, Occupation=p.Occupation });

Right click on the query and choose the option “Run LINQ Query”. You will see this query in the LINQ Interactive window and its corresponding results.

As the query accepts a parameter, we can change value of the parameter and test behavior of the query. Hit the Edit Parameters button in the LINQ Interactive window and modify value of the parameters used in the query. On clicking OK, the query is executed and the result is refreshed.

Profiling Calls to Entity Framework
As most of you know, ORMs like Entity Framework convert LINQ Queries and function calls into SQL Queries and statements. The same result can be obtained using different types of queries. It is our responsibility to measure the execution time of all possible approaches of writing a query and choose the best one out of them.

For example, consider a database with two tables: Student and Marks.

The need is to find details of a student and the marks obtained by the student. It can be achieved in two ways. The first way is to find sum of the marks using navigation property on the student object and the other is to query the Marks DbSet using group by clause:

var studMarks1 = context.Students.Where(s => s.StudentId == studentid)
                        .Select(s => new
                        {
                            Name = s.Name,
                            Id = s.StudentId,
                            City = s.City,
                            TotalMarks = s.Marks.Sum(m => m.MarksAwarded),
                            OutOf = s.Marks.Sum(m => m.MaxMarks)
                        });

var studMarks2 = context.Marks.Where(m => m.StudentId == studentid)
                         .GroupBy(sm => sm.StudentId)
                         .Select(marksGroup => new
                         {
                             Name = marksGroup.FirstOrDefault().Student.Name,
                             Id = marksGroup.FirstOrDefault().Student.StudentId,
                             City = marksGroup.FirstOrDefault().Student.City,
                             TotalMarks = marksGroup.Sum(m => m.MarksAwarded),
                             OutOf = marksGroup.Sum(m => m.MarksAwarded)
                         });

Let’s see how much time does each of the above query takes using LINQ Profiler. Go to View menu and choose option to see the LINQ Profiler window. Now debug the application. After both of the queries are executed, visit the profiler window. You will see the data collected by the profiler. Following screenshot shows an instance of profiler’s data after running the above queries:

You can observe how much time each of these queries takes in different instances and with different values of the parameters before concluding that one of them is better than the other one.

As we saw, LINQ Insight adds a value add to the .NET developers. Devart has a number of other tools to help developers as well. Make sure to check them out and use them to make your development experience more enjoyable!

Happy coding!