Tuesday, September 15, 2009 4:57 PM Kazi Manzur Rashid

Shrinkr - Url Shrinking Service Developed with Entity Framework 4.0, Unity, ASP.NET MVC And jQuery (Part 3)

In the previous post, we have created our initial repositories, in this post I will show how you can use the compiled query of Entity Framework in our repository. To use the compiled query we will put each query of our repositories into its own class and create some common interfaces that we can use in our repositories.

IQuery

namespace Shrinkr.Infrastructure.EntityFramework
{
    public interface IQuery<TResult>
    {
        TResult Execute(Database database);
    }
}

IQueryFactory

namespace Shrinkr.Infrastructure.EntityFramework
{
    using System.Collections.Generic;

    public interface IQueryFactory
    {
        bool UseCompiled
        {
            get;
        }

        IQuery<User> CreateUserById(long userId);

        IQuery<User> CreateUserByName(string userName);

        IQuery<User> CreateUserByApiKey(string apiKey);

        IQuery<ShortUrl> CreateShortUrlById(long shortUrlId);

        IQuery<ShortUrl> CreateShortUrlByHash(string urlHash);

        IQuery<ShortUrl> CreateShortUrlByAlias(string alias);

        IQuery<int> CreateShortUrlCountByUserId(long userId);

        IQuery<IEnumerable<ShortUrl>> CreateShortUrlsByUserId(long userId, int start, int max);
    }
}

Now we will modify our RepositoryBase, so that we can pass the IQuaryFactory in its constructor.

RepositoryBase

namespace Shrinkr.Infrastructure.EntityFramework
{
    public abstract class RepositoryBase<TEntity> where TEntity : class, IEntity
    {
        protected RepositoryBase(Database database, IQueryFactory queryFactory)
        {
            Check.Argument.IsNotNull(database, "database");
            Check.Argument.IsNotNull(queryFactory, "queryFactory");

            Database = database;
            QueryFactory = queryFactory;
        }

        protected Database Database
        {
            get;
            private set;
        }

        protected IQueryFactory QueryFactory
        {
            get;
            private set;
        }

        public virtual void Add(TEntity entity)
        {
            Check.Argument.IsNotNull(entity, "entity");

            Database.ObjectSet<TEntity>().AddObject(entity);
        }

        public virtual void Delete(TEntity entity)
        {
            Check.Argument.IsNotNull(entity, "entity");

            Database.ObjectSet<TEntity>().DeleteObject(entity);
        }
    }
}

We can now use the query factory in our repository, for example, in UserRepository we will be able to use:

public User GetById(long id)
{
    IQuery<User> query = QueryFactory.CreateUserById(id);

    return query.Execute(Database);
}

Now, lets check how the query is constructed, first the base class which implements the IQuery<T> interface:

QueryBase

namespace Shrinkr.Infrastructure.EntityFramework
{
    public abstract class QueryBase<TResult> : IQuery<TResult>
    {
        protected QueryBase(bool useCompiled)
        {
            UseCompiled = useCompiled;
        }

        protected bool UseCompiled
        {
            get;
            private set;
        }

        public abstract TResult Execute(Database database);
    }
}

as mentioned that each query will have its own class, for example, for the above user by id query, we will have the following:

namespace Shrinkr.Infrastructure.EntityFramework
{
    using System;
    using System.Data.Objects;
    using System.Linq;
    using System.Linq.Expressions;

    public class UserByIdQuery : QueryBase<User>
    {
        private static readonly Expression<Func<Database, long, User>> expression = (Database database, long id) => database.Users.SingleOrDefault(user => user.Id == id);
        private static readonly Func<Database, long, User> plainQuery = expression.Compile();
        private static readonly Func<Database, long, User> compiledQuery = CompiledQuery.Compile(expression);

        private readonly long userId;

        public UserByIdQuery(bool useCompiled, long userId) : base(useCompiled)
        {
            Check.Argument.IsNotNegative(userId, "userId");

            this.userId = userId;
        }

        public override User Execute(Database database)
        {
            Check.Argument.IsNotNull(database, "database");

            return UseCompiled ?
                   compiledQuery(database, userId) :
                   plainQuery(database, userId);
        }
    }
}

and the implementation of QueryFactory

namespace Shrinkr.Infrastructure.EntityFramework
{
    using System.Collections.Generic;

    public class QueryFactory : IQueryFactory
    {
        public QueryFactory(bool useCompiled)
        {
            UseCompiled = useCompiled;
        }

        public bool UseCompiled
        {
            get;
            private set;
        }

        public IQuery<User> CreateUserById(long userId)
        {
            return new UserByIdQuery(UseCompiled, userId);
        }
    }
}

Now, when unit testing the Repositories we will be using the plain queries, for example the UserRepository will be constructed like the following in unit tests:

public UserRepositoryTests()
{
    database = new Mock<Database>(configurationManager.Object, "Dummy");
    var queryFactory = new QueryFactory(false); // plain query

    repository = new UserRepository(database.Object, queryFactory);
}

and that’s it. But for the data access layer, I would highly recommend to  have the integration tests as well. The reasons are:

  1. It will ensure the underlying Linq Providers does support the Linq queries that we have in your repositories, although the Linq queries we have written here are very simple.
  2. By using the SQL Profiler we can ensure the generated SQLs are really optimized.

But to start writing the integration tests, we have one more important thing to do, the UnitOfWork, which persist the changes in our database.

UnitOfWork

namespace Shrinkr.Infrastructure.EntityFramework
{
    public class UnitOfWork : IUnitOfWork
    {
        private readonly Database database;

        public UnitOfWork(Database database)
        {
            Check.Argument.IsNotNull(database, "database");

            this.database = database;
        }

        public void Commit()
        {
            database.Commit();
        }
    }
}

Now, lets write our first integration test:

namespace Shrinkr.IntegrationTests
{
    using System;

    using Xunit;
    using Xunit.Extensions;

    using IoC = global::Microsoft.Practices.ServiceLocation.ServiceLocator;

    public class UserRepositoryTests : TestBase
    {
        private const string Name = "http://kazimanzurrashid.myopenid.com";

        private readonly IUnitOfWork unitOfWork;
        private readonly IUserRepository repository;

        public UserRepositoryTests()
        {
            unitOfWork = IoC.Current.GetInstance<IUnitOfWork>();
            repository = IoC.Current.GetInstance<IUserRepository>();
        }

        [Fact, AutoRollback]
        public void Should_be_able_to_add_user()
        {
            var user = CreateUser();

            Assert.NotEqual(0, user.Id);
        }

        [Fact, AutoRollback]
        public void Should_be_able_to_update_user()
        {
            var user = CreateUser();

            user.Email = "kazimanzurrashid@gmail.com";

            unitOfWork.Commit();

            var updatedUser = repository.GetById(user.Id);

            Assert.Equal("kazimanzurrashid@gmail.com", updatedUser.Email);
        }

        [Fact, AutoRollback]
        public void Should_be_able_to_delete_user()
        {
            var userId = CreateUser().Id;
            var user = repository.GetById(userId);

            repository.Delete(user);
            unitOfWork.Commit();

            user = repository.GetById(userId);

            Assert.Null(user);
        }

        [Fact, AutoRollback]
        public void Should_be_able_to_get_user_by_id()
        {
            var userId = CreateUser().Id;
            var user = repository.GetById(userId);

            Assert.NotNull(user);
        }

        [Fact, AutoRollback]
        public void Should_be_able_to_get_user_by_name()
        {
            CreateUser();

            var user = repository.GetByName(Name);

            Assert.NotNull(user);
        }

        [Fact, AutoRollback]
        public void Should_be_able_to_get_user_by_api_key()
        {
            var apiKey = CreateUser().ApiSetting.Key;
            var user = repository.GetByApiKey(apiKey);

            Assert.NotNull(user);
        }

        private User CreateUser()
        {
            var user = new User { Name = Name };

            user.ApiSetting.Allowed = true;
            user.ApiSetting.DailyLimit = 1000;
            user.ApiSetting.Key = Guid.NewGuid().ToString().ToUpperInvariant();

            repository.Add(user);
            unitOfWork.Commit();

            return user;
        }
    }
}

When we run the above test, it will generate the following SQL statements, which I think is pretty much optimized:

GetById

exec sp_executesql N'SELECT 
[Limit1].[Id] AS [Id], 
[Limit1].[Name] AS [Name], 
[Limit1].[Email] AS [Email], 
[Limit1].[IsLockedOut] AS [IsLockedOut], 
[Limit1].[CreatedAt] AS [CreatedAt], 
[Limit1].[Role] AS [Role], 
[Limit1].[LastActivityAt] AS [LastActivityAt], 
[Limit1].[C1] AS [C1], 
[Limit1].[ApiKey] AS [ApiKey], 
[Limit1].[ApiAllowed] AS [ApiAllowed], 
[Limit1].[DailyLimit] AS [DailyLimit]
FROM ( SELECT TOP (2) 
	[Extent1].[Id] AS [Id], 
	[Extent1].[Name] AS [Name], 
	[Extent1].[Email] AS [Email], 
	[Extent1].[IsLockedOut] AS [IsLockedOut], 
	[Extent1].[CreatedAt] AS [CreatedAt], 
	[Extent1].[Role] AS [Role], 
	[Extent1].[ApiKey] AS [ApiKey], 
	[Extent1].[ApiAllowed] AS [ApiAllowed], 
	[Extent1].[DailyLimit] AS [DailyLimit], 
	[Extent1].[LastActivityAt] AS [LastActivityAt], 
	1 AS [C1]
	FROM [dbo].[User] AS [Extent1]
	WHERE [Extent1].[Id] = @p__linq__0
)  AS [Limit1]',N'@p__linq__0 bigint',@p__linq__0=125

GetByName

exec sp_executesql N'SELECT 
[Limit1].[Id] AS [Id], 
[Limit1].[Name] AS [Name], 
[Limit1].[Email] AS [Email], 
[Limit1].[IsLockedOut] AS [IsLockedOut], 
[Limit1].[CreatedAt] AS [CreatedAt], 
[Limit1].[Role] AS [Role], 
[Limit1].[LastActivityAt] AS [LastActivityAt], 
[Limit1].[C1] AS [C1], 
[Limit1].[ApiKey] AS [ApiKey], 
[Limit1].[ApiAllowed] AS [ApiAllowed], 
[Limit1].[DailyLimit] AS [DailyLimit]
FROM ( SELECT TOP (2) 
	[Extent1].[Id] AS [Id], 
	[Extent1].[Name] AS [Name], 
	[Extent1].[Email] AS [Email], 
	[Extent1].[IsLockedOut] AS [IsLockedOut], 
	[Extent1].[CreatedAt] AS [CreatedAt], 
	[Extent1].[Role] AS [Role], 
	[Extent1].[ApiKey] AS [ApiKey], 
	[Extent1].[ApiAllowed] AS [ApiAllowed], 
	[Extent1].[DailyLimit] AS [DailyLimit], 
	[Extent1].[LastActivityAt] AS [LastActivityAt], 
	1 AS [C1]
	FROM [dbo].[User] AS [Extent1]
	WHERE [Extent1].[Name] = @p__linq__0
)  AS [Limit1]',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'http://kazimanzurrashid.myopenid.com/'

GetByApiKey

exec sp_executesql N'SELECT 
[Limit1].[Id] AS [Id], 
[Limit1].[Name] AS [Name], 
[Limit1].[Email] AS [Email], 
[Limit1].[IsLockedOut] AS [IsLockedOut], 
[Limit1].[CreatedAt] AS [CreatedAt], 
[Limit1].[Role] AS [Role], 
[Limit1].[LastActivityAt] AS [LastActivityAt], 
[Limit1].[C1] AS [C1], 
[Limit1].[ApiKey] AS [ApiKey], 
[Limit1].[ApiAllowed] AS [ApiAllowed], 
[Limit1].[DailyLimit] AS [DailyLimit]
FROM ( SELECT TOP (2) 
	[Extent1].[Id] AS [Id], 
	[Extent1].[Name] AS [Name], 
	[Extent1].[Email] AS [Email], 
	[Extent1].[IsLockedOut] AS [IsLockedOut], 
	[Extent1].[CreatedAt] AS [CreatedAt], 
	[Extent1].[Role] AS [Role], 
	[Extent1].[ApiKey] AS [ApiKey], 
	[Extent1].[ApiAllowed] AS [ApiAllowed], 
	[Extent1].[DailyLimit] AS [DailyLimit], 
	[Extent1].[LastActivityAt] AS [LastActivityAt], 
	1 AS [C1]
	FROM [dbo].[User] AS [Extent1]
	WHERE [Extent1].[ApiKey] = @p__linq__0
)  AS [Limit1]',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'9C1A8F98-9CC6-4967-8B48-269CA92833E5'

Insert

exec sp_executesql N'insert [dbo].[User]([Name], [Email], [IsLockedOut], [CreatedAt], [Role], [ApiKey], [ApiAllowed], [DailyLimit], [LastActivityAt])
values (@0, null, @1, @2, @3, @4, @5, @6, @7)
select [Id]
from [dbo].[User]
where @@ROWCOUNT > 0 and [Id] = scope_identity()',N'@0 nvarchar(256),@1 bit,@2 datetime,@3 int,@4 nchar(36),@5 bit,@6 int,@7 datetime',@0=N'http://kazimanzurrashid.myopenid.com',@1=0,@2='2009-08-02 22:04:17:067',@3=0,@4=N'9C1A8F98-9CC6-4967-8B48-269CA92833E5',@5=1,@6=1000,@7='2009-08-02 22:04:17:067'

Update

exec sp_executesql N'update [dbo].[User]
set [Email] = @0
where ([Id] = @1)
',N'@0 nvarchar(256),@1 bigint',@0=N'kazimanzurrashid@gmail.com',@1=142

Delete

exec sp_executesql N'delete [dbo].[User]
where ([Id] = @0)',N'@0 bigint',@0=125

You will find other tests in the integration test project.

That is it for this post, in the next post we will discuss on other infrastructural item such shrinking logic, http content,  IoC etc etc.

Stay tuned!!!

Shout it
Filed under: , , , , , , , , , , , , ,

Comments

No Comments