Testing queries

One of the questions that you will undoubtedly come across in using Entity Framework is the usage of LINQ statements getting transformed into SQL statements everywhere, and how to output those for testing. These tests are not meant to truly unit test the generated SQL, but rather provide a simple way to inform the development staff, possibly DataBase Administrators (DBAs), as to what SQL is actually being executed for a given LINQ statement.

Getting ready

We will be using NuGet Package Manager to install the Entity Framework 4.1 assemblies.

The package installer can be found at http://www.nuget.org/.

We will also be using a database for connecting to the data and updating it.

Open the Testing SQL Output solution in the included source code examples.

Execute the database setup script from the code samples included with this recipe. This can be found in the DataAccess project within the Database folder.

How to do it...

  1. First, we start by adding a new unit test in the Test project to extract the SQL statements for us and a test to verify the filters on a given set of data:
    using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using BusinessLogic;
using DataAccess;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Rhino.Mocks;
namespace Test
{
  [TestClass]
  public class QueryTest
  {
    [TestMethod]
    public void ShouldFilterDataProperly()
    {
      IUnitOfWork mockContext = MockRepository.GenerateMock<IUnitOfWork>();
      mockContext.Expect(x => x.Find<Blog>()).Return(new List<Blog>()
      {
        new Blog(){Id = 1,Title = "Title"},
        new Blog(){Id=2,Title = "no"}
      }.AsQueryable());
      IBlogRepository repository = new BlogRepository(mockContext);
      var items = repository.Set<Blog>().Where(x=>x.Title.Contains("t"));
      mockContext.AssertWasCalled(x => x.Find<Blog>());
      Assert.AreEqual(1,items.Count());
      Assert.AreEqual("Title",items.First().Title);
    }
    [TestMethod]
    public void ShouldAllowSqlStringOutput()
    {
      IBlogRepository repository = new BlogRepository(new BlogContext(Settings.Default.BlogConnection));
      var items = repository.Set<Blog>();
      var sql = items.ToString();
      Console.WriteLine(sql);
      Assert.IsTrue(sql.Contains("SELECT"));
    }
  }
}
  2. In the Test project, add a setting for the connection to the database, as shown in the following screenshot:
    How to do it...
  3. In the test results window, we want to right-click and open the View Test Result Details for our SQL string test, as shown in the following screenshot:
    How to do it...
  4. Notice the output for SQL console in the following screenshot:
    How to do it...

How it works...

The first test is to make sure that our LINQ statements are executing the filters that we believe them to be. This will allow us to encapsulate the filters and sorts that we use throughout our application to keep the query footprint small. Entity Framework writes parameterized SQL. The fewer queries we use in structure, the better the performance will be. The query paths for our set of queries will be stored in SQL Server, just like the query plans of stored procedures, which provides us huge performance gains without sacrificing the code base of our application.

With this recipe, we start leveraging the abstraction layers built in the repository and the unit of work patterns that we implemented earlier. We leverage the unit of work to get a false set of data in the first test. This is the set that allows us to verify filters, and if you have a complex data structure, this can be abstracted into a factory so we only need to provide the dummy list at one time, but then can test multiple filters and sorts against it.

The second test requires a fully formed context, which is why we loaded a connection string to the Test project. This is not hitting the database for data, but is connecting at the construction of the context to check metadata and schema definition. This is the metadata which the context will use along with the standard convention, and any exceptions that you have configured to translate the LINQ statements into SQL statements.

There's more...

Some of the Entity Framework presentations that we have seen over the last couple of years have implied that we can ignore the database with an object relational mapper such as Entity Framework. This is not entirely true. We can ignore the structure of the database while defining our objects, but we still must be aware of it while querying and mapping our objects.

Query execution plan

As SQL is declarative, there are often many ways to get the same set of results, each of these varying widely in performance. When a query is submitted to the database, it is run through the query optimizer that evaluates some of the possible plans for executing the query, and returns what it considers the best of them. The query optimizer is not perfect, but it is good. The cost of this optimizer is that it takes some overhead on the query. When a query is sent with the parameters, the optimizer evaluates it and returns it, but caches the resulting plan. If the same query is called with different parameters, the optimizer knows the resulting plan will be the same, and uses the cached version. This storage of query plans is what gives Entity Framework an advantage because it uses parameterized SQL statements. If we are able to keep the query footprint (the number of different queries) in our application small, then we will reap the most benefit from this optimization and storage.

Query performance

When looking at using Entity Framework, we all need to consider performance, as we do not control the query directly. Some developers will write LINQ statements without a thought to translating it to SQL at the backend. This can lead to performance problems that are blamed on Entity Framework. The problem rests with the LINQ code that was written. There are several tools on the market which will allow you to analyze the generated SQL, and some even allow you to get a real-time look at the query execution plan.

Here are some of them:

See also

In this chapter:

  • Implementing the repository pattern
  • Implementing the unit of work pattern

In Chapter 5, Improving Entity Framework with Query Libraries:

  • Creating reusable queries
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset