title: How to mock and unit test Stored Procedures in EF
body: <p>I am using a generic repository pattern <code>Repository&lt;TEntity&gt;</code> where repositories access the entities through a context. Then I have a service layer that accepts a context in the constructor. Now I can have multiple repositories in a service, accessing the entities through the same context. Pretty standard. This is perfect for tables/views that map to entities, but I cannot unit test data coming through stored procedures.</p>
  
  <p>This is my current setup:</p>
  
  <p>IDbContext:</p>
  
  <pre><code>public interface IDbContext : IDisposable
  {
      IDbSet&lt;T&gt; Set&lt;T&gt;() where T : class;
  
      DbEntityEntry&lt;T&gt; Entry&lt;T&gt;(T entity) where T : class;
  
      void SetModified(object entity);
  
      int SaveChanges();
  
      // Added to be able to execute stored procedures
      System.Data.Entity.Database Database { get; }
  }
  </code></pre>
  
  <p>Context:</p>
  
  <pre><code>public class AppDataContext : DbContext, IDbContext
  {
      public AppDataContext()
          : base("Name=CONNECTIONSTRING")
      {
          base.Configuration.ProxyCreationEnabled = false;
      }
  
      public new IDbSet&lt;T&gt; Set&lt;T&gt;() where T : class
      {
          return base.Set&lt;T&gt;();
      }
  
  
      public void SetModified(object entity)
      {
          Entry(entity).State = EntityState.Modified;
      }
  
      protected override void OnModelCreating(DbModelBuilder modelBuilder)
      {
          modelBuilder.Configurations.Add(new BookingMap());
      }
  
      // Added to be able to execute stored procedures
      System.Data.Entity.Database Database { get { return base.Database; } }
  }
  </code></pre>
  
  <p>Generic Repository:</p>
  
  <pre><code>public class Repository&lt;T&gt; : IRepository&lt;T&gt; where T : class
  {
      private readonly IDbContext context;
  
      public Repository(IDbContext context)
      {
          this.context = context;
      }
  
      public IQueryable&lt;T&gt; GetAll()
      {
          return this.context.Set&lt;T&gt;().AsQueryable();
      }
  
      public void Add(T entity)
      {
          this.context.Set&lt;T&gt;().Add(entity);
      }
  
      public void Delete(T entity)
      {
          this.context.Set&lt;T&gt;().Remove(entity);
      }
  
      public void DeleteAll(IEnumerable&lt;T&gt; entities)
      {
          foreach (var e in entities.ToList())
          {
              this.context.Set&lt;T&gt;().Remove(e);
          }
      }
  
      public void Update(T entity)
      {
          this.context.Set&lt;T&gt;().Attach(entity);
          this.context.SetModified(entity);
      }
  
      public void SaveChanges()
      {
          this.context.SaveChanges();
      }
  
      public void Dispose()
      {
          if (this.context != null)
          {
              this.context.Dispose();
          }
      }
  }
  </code></pre>
  
  <p>Service:</p>
  
  <pre><code>public class BookingService
  {
      IDbContext _context;
  
      IRepository&lt;Booking&gt; _bookingRepository;
  
      public BookingService(IDbContext context)
      {
          _context = context;
  
          _bookingRepository = new Repository&lt;Booking&gt;(context);
      }
  
      public IEnumerable&lt;Booking&gt; GetAllBookingsForName(string name)
      {
          return (from b in _bookingRepository.GetAll()
                  where b.Name == name
                  select b);
      }
  }
  </code></pre>
  
  <p>Test:</p>
  
  <pre><code>[TestClass]
  public class BookingServiceTest
  {
      [TestMethod]
      public void Test_Get_All_Bookings_For_Name()
      {
          var mock = new Mock&lt;IDbContext&gt;();
          mock.Setup(x =&gt; x.Set&lt;Booking&gt;())
              .Returns(new FakeDbSet&lt;Booking&gt;
              {
                  new Booking { Name = "Foo" },
                  new Booking { Name = "Bar" }
              });
  
          BookingService _bookingService = new BookingService(mock.Object);
  
          var bookings = _bookingService.GetAllBookingsForName(name);
  
          Assert.AreEqual(2, bookings.Count(), "Booking count is not correct");
      }
  }
  </code></pre>
  
  <p>This is perfect for tables/views that map to entities, but I cannot unit test data coming through stored procedures.</p>
  
  <p>I looked up on the internet and found <code>DbContext.Database</code> property and I am able to execute stored procedures with the <code>.SqlQuery()</code> function and map them to an entity type.</p>
  
  <p>This is what I added to the <code>Repository&lt;T&gt;</code> class:</p>
  
  <pre><code>public IEnumerable&lt;T&gt; SqlQuery(string storedProc, params object[] paramList)
  {
      return this.context.Database.SqlQuery&lt;T&gt;(storedProc, paramList);
  }
  </code></pre>
  
  <p>And call the <code>.SqlQuery()</code> function in my service class:</p>
  
  <pre><code>public IEnumerable&lt;Booking&gt; GetAllBookings(string name)
  {
      return _bookingRepository.SqlQuery("EXEC GetAllBookings @name = {0}", name);
  }
  </code></pre>
  
  <p>This works great (I am able to get some data), but my question is how can I mock and unit test this?</p>