Implementing composed queries

Composed queries are an effective way to link separate but related pieces of filtering logic into reusable pieces. In this recipe, we will compose queries together to accomplish a certain filter without needing to write the code twice but still having it translated into a single SQL execution.

Getting ready

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

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

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

Open the Improving Transaction Scope solution in the included source code examples.

How to do it...

Carry out the following steps in order to accomplish this recipe.

  1. We will start by adding a new unit test named QueryTests to the test project. We will make a test that connects to the database and retrieves a couple of records with a reusable query using the following code:
    using System;
using System.Collections.Generic;
using System.Data.Entity.Validation;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Transactions;
using BusinessLogic;
using BusinessLogic.Queries;
using DataAccess;
using DataAccess.Database;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Test.Properties;
using System.Data.Entity;

namespace Test
{
  [TestClass]
  public class QueryTests
  {
    [TestMethod]
    public void ShouldReturnRecordsFromTheDatabaseByName()
    {
      //Arrange
      var init = new Initializer();
      var context = new 
        BlogContext(Settings.Default.BlogConnection);
      init.InitializeDatabase(context);
      IBlogRepository repo = new BlogRepository(context);
            

      //Act
      var items = repo.Set<Blog>().FilterByBlogName("Test");
            
      //Assert
      Assert.AreEqual(2, items.Count());
    }

    [TestMethod]
    public void ShouldReturnRecordsFromTheDatabaseByDescription()
    {
      //Arrange
      var init = new Initializer();
      var context = new 
        BlogContext(Settings.Default.BlogConnection);
      init.InitializeDatabase(context);
      IBlogRepository repo = new BlogRepository(context);
           

      //Act
      var items = repo.Set<Blog>().FilterByBlogName("es");

      //Assert
      Assert.AreEqual(2, items.Count());
    }

    [TestMethod]
    public void ShouldReturnRecordsFromTheDatabaseByBoth()
    {
      //Arrange
      var init = new Initializer();
      var context = new 
        BlogContext(Settings.Default.BlogConnection);
      init.InitializeDatabase(context);
      IBlogRepository repo = new BlogRepository(context);
            

      //Act
      var items = repo.Set<Blog>()
        .FilterByNameAndDescription("Test","es");
      //Assert
      Assert.AreEqual(1, items.Count());
    }
  }

}
  2. Add an initializer to the DataAccess project Database folder with the following code to set up data:
    using System;
using System.Data.Entity;
using BusinessLogic;

namespace DataAccess.Database
{

  public class Initializer : DropCreateDatabaseAlways<BlogContext>
  {
    public Initializer()
    {
            
    }
    protected override void Seed(BlogContext context)
    {
      context.Set<Blog>().Add(new Blog()
      {
        Creationdate = DateTime.Now,
        ShortDescription = "not this one",
        Title = "Test Blog"
      });
      context.Set<Blog>().Add(new Blog()
      {
        Creationdate = DateTime.Now,
        ShortDescription = "Testing",
        Title = "Test Blog 2"
            });
            context.Set<Blog>().Add(new Blog()
            {
              Creationdate = DateTime.Now,
              ShortDescription = "Testing",
              Title = "not Blog"
            });
          context.SaveChanges();
      }

  }
}
  3. In the BusinessLogic project, add a new C# class named Blog with the following code:
    using System;
using System.ComponentModel.DataAnnotations;
using System.Text.RegularExpressions;

namespace BusinessLogic
{
  public class Blog
  {
    public int Id { get; set; }
    public DateTime Creationdate { get; set; }
    public string ShortDescription { get; set; }
    public string Title { get; set; }
    public double Rating { get; set; }
  }
}
  4. Add a Mapping folder to the DataAccess project and then add a BlogMapping class to the folder with the following code:
    using System.ComponentModel.DataAnnotations;
using System.Data.Entity.ModelConfiguration;
using BusinessLogic;

namespace DataAccess.Mappings
{
  public class BlogMapping : EntityTypeConfiguration<Blog>
  {
    public BlogMapping()
    {
      this.ToTable("Blogs");
      this.HasKey(x => x.Id);
      this.Property(x => x.Id).HasDatabaseGeneratedOption
        (DatabaseGeneratedOption.Identity)
          .HasColumnName("BlogId");

      this.Property(x => x.Title)
        .IsRequired().HasMaxLength(250);
      this.Property(x => x.Creationdate)
        .HasColumnName("CreationDate").IsRequired();
      this.Property(x => x.ShortDescription)
        .HasColumnType("Text").IsMaxLength().IsOptional()
           .HasColumnName("Description");
    }

  }
}
  5. Modify the BlogContext class to contain the new mappings for Blogs with the following code:
    using System;
using System.Data.Entity;
using System.Linq;
using BusinessLogic;
using DataAccess.Mappings;

namespace DataAccess
{
  public class BlogContext : DbContext, IUnitOfWork
  {
    public BlogContext(string connectionString)
      : base(connectionString)
    {

    }

    protected override void 
      OnModelCreating(DbModelBuilder modelBuilder)
    {
      modelBuilder.Configurations.Add(new BlogMapping());
      base.OnModelCreating(modelBuilder);
    }

    public IQueryable<T> Find<T>() where T : class
    {
      return this.Set<T>();
    }

    public void Refresh()
    {
      this.ChangeTracker.Entries().ToList()
        .ForEach(x=>x.Reload());
    }

    public void Commit()
    {
      this.SaveChanges();
    }
  }
}
  6. Add a new folder named Queries to the BusinessLogic project and add a new C# class to it named BlogQueries with the following code:
    using System;
using System.Linq;

namespace BusinessLogic.Queries
{
  public static class BlogQueries
  {
    public static IQueryable<Blog>
      FilterByBlogName(this IQueryable<Blog>
        items, string name)
  {
    return items.Where(x => x.Title.Contains(name));
  }

  public static IQueryable<Blog>
    FilterByDescription(this IQueryable<Blog>
      items, string description)
  {
    return items.Where(x=> x.ShortDescription
      .Contains(description));
  }

  public static IQueryable<Blog>
    FilterByNameAndDescription(this IQueryable<Blog> items,
      string name, string description)
    {
      return items.FilterByBlogName(name)
        .FilterByDescription(description);
    }

  }
}
  7. Run our test and it will work.

How it works...

We begin our solution by defining a set of tests that apply filters one at a time and then in combination, and finally test the results. This ensures that we accomplish the functionality that we are aiming at without breaking any individual piece.

We initialize the database with a set of test data which we can assert against for the query filtering. This allows us to know the inputs and test if the outputs adhere to the expected results.

We then set up our blog entity, the mappings for it, and the DbContext, so we could communicate with the database and have our filters translated properly into SQL statements. This is the piece that allows us to ignore the database schema and worry only about the object filtering.

The next step is to define our three queries, so we can test them. Notice that the first two queries are the queries that specify a where statement. However, the third query merely composes the already defined behavior into a single execution without the developer being aware that it combines with the existing functionality. This allows us to adhere to the DRY principle.

There's more...

We leverage several principles here, which will help us in understanding how to implement this in a production environment.

Don't repeat yourself (DRY)

Each piece of functionality should not need to be typed more than once in an application. This is an ideal that if held to, will force us to abstract our code base into patterns that lend themselves to reuse. The effort is well worth it though, as it helps to ensure that each piece is doing just one thing, which helps us to adhere to single responsibility principle.

Single responsibility principle

This principle states that each method and class should have one and only one reason to change. This normally confuses some developers, but the confusion lies in how we define "one reason to change." At the method level, the reason to change should be very specific. For example, "The rules for this filter are now different." However, at a class level, this one reason should be more generalized. For example, "We have added another filter." This one reason to change is essential for our one reason for creating any class or method to begin with. This means that the consumers of the object will never be surprised by a change because it will be in line with what they would expect from your component.

Some people believe that Object Relational Mappers (ORMs), such as Entity Framework, intrinsically break this principle because an entity class might change because of the object or the database. However, as we have already learned, this is not the case. Entity Framework clearly separates mapping rules from the classes, thereby avoiding this conflict with the single responsibility principle.

See also

In this chapter:

  • Improving query testing recipe
..................Content has been hidden....................

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