How to mock the limitations of EntityFramework's implementation of IQueryable

Question

I am currently writing unit tests for my repository implementation in an MVC4 application. In order to mock the data context, I started by adopting some ideas from this post, but I have now discovered some limitations that make me question whether it is even possible to properly mock IQueryable.

In particular, I have seen some situations where the tests pass but the code fails in production and I have not been able to find any way to mock the behavior that causes this failure.

For example, the following snippet is used to select Post entities that fall within a predefined list of categories:

var posts = repository.GetEntities<Post>(); // Returns IQueryable<Post>
var categories = GetCategoriesInGroup("Post"); // Returns a fixed list of type Category
var filtered = posts.Where(p => categories.Any(c => c.Name == p.Category)).ToList();

In my test environment, I have tried mocking posts using the fake DbSet implementation mentioned above, and also by creating a List of Post instances and converting it to IQueryable using the AsQueryable() extension method. Both of these approaches work under test conditions, but the code actually fails in production, with the following exception:

System.NotSupportedException : Unable to create a constant value of type 'Category'. Only primitive types or enumeration types are supported in this context.

Although LINQ issues like this are easy enough to fix, the real challenge is finding them, given that they do not reveal themselves in the test environment.

Am I being unrealistic in expecting that I can mock the behavior of Entity Framework's implementation of IQueryable?

Thanks for your ideas,

Tim.

Hoax

It won't be a unit test but what if you did ToString() (on the DbQuery) or ToTraceString() (on ObjectQuery)? It will dump the SqlQuery corresponding to your query meaning it will go through the whole EF query pipeline, yet won't send the query to the database. It should reveal cases like this. – Sibella

@Pawel. Thanks - this is a great step in the right direction, although it would be nice if I could somehow automate this. – Hoax

Answer 1

I think it is very very hard, if impossible, to mock Entity Framework behaviour. First and foremost because it would require profound knowledge of all peculiarities and edge cases where linq-to-entites differs from linq-to-objects. As you say: the real challenge is finding them. Let me point out three main areas without claiming to be even nearly exhaustive:

Cases where Linq-to-Objects succeeds and Linq-to-Entities fails:

• .Select(x => x.Property1.ToString(). LINQ to Entities does not recognize the method 'System.String ToString()' method... This applies to nearly all methods in native .Net classes and of course to own methods. Only a few .Net methods will be translated into SQL. See CLR Method to Canonical Function Mapping. As of EF 6.1, ToString is supported by the way. But only the parameterless overload.
• Skip() without preceding OrderBy.
• Except and Intersect: can produce monstrous queries that throw Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.
• Select(x => x.Date1 - x.Date2): DbArithmeticExpression arguments must have a numeric common type.
• (your case) .Where(p => p.Category == category): Only primitive types or enumeration types are supported in this context.
• Nodes.Where(n => n.ParentNodes.First().Id == 1): The method 'First' can only be used as a final query operation.
• context.Nodes.Last(): LINQ to Entities does not recognize the method '...Last...'. This applies to many other IQueryable extension methods. See Supported and Unsupported LINQ Methods.
• (See Slauma's comment below): .Select(x => new A { Property1 = (x.BoolProperty ? new B { BProp1 = x.Prop1, BProp2 = x.Prop2 } : new B { BProp1 = x.Prop1 }) }): The type 'B' appears in two structurally incompatible initializations within a single LINQ to Entities query... from here.
• context.Entities.Cast<IEntity>(): Unable to cast the type 'Entity' to type 'IEntity'. LINQ to Entities only supports casting EDM primitive or enumeration types.
• .Select(p => p.Category?.Name). Using null propagation in an expression throws CS8072 An expression tree lambda may not contain a null propagating operator. This may get fixed one day.
• This question: Why does this combination of Select, Where and GroupBy cause an exception? made me aware of the fact that there are even entire query constructions that are not supported by EF, while L2O wouldn't have any trouble with them.

Cases where Linq-to-Objects fails and Linq-to-Entities succeeds:

• .Select(p => p.Category.Name): when p.Category is null L2E returns null, but L2O throws Object reference not set to an instance of an object. This can't be fixed by using null propagation (see above).
• Nodes.Max(n => n.ParentId.Value) with some null values for n.ParentId. L2E returns a max value, L2O throws Nullable object must have a value.
• Using EntityFunctions (DbFunctions as of EF 6) or SqlFunctions.

Cases where both succeed/fail but behave differently:

• Nodes.Include("ParentNodes"): L2O has no implementation of include. It will run and return nodes (if Nodes is IQueryable), but without parent nodes.
• Nodes.Select(n => n.ParentNodes.Max(p => p.Id)) with some empty ParentNodes collections: both fail but with different exceptions.
• Nodes.Where(n => n.Name.Contains("par")): L2O is case sensitive, L2E depends on the database collation (often not case sensitive).
• node.ParentNode = parentNode: with a bidirectional relationship, in L2E this will also add the node to the nodes collection of the parent (relationship fixup). Not in L2O. (See Unit testing a two way EF relationship).
• Work-around for failing null propagation: .Select(p => p.Category == null ? string.Empty : p.Category.Name): the result is the same, but the generated SQL query also contains the null check and may be harder to optimize.
• Nodes.AsNoTracking().Select(n => n.ParentNode. This one is very tricky!. With AsNoTracking EF creates new ParentNode objects for each Node, so there can be duplicates. Without AsNoTracking EF reuses existing ParentNodes, because now the entity state manager and entity keys are involved. AsNoTracking() can be called in L2O, but it doesn't do anything, so there will never be a difference with or without it.

And what about mocking lazy/eager loading and the effect of context life cycle on lazy loading exceptions? Or the effect of some query constructs on performance (like constructs that trigger N+1 SQL queries). Or exceptions due to duplicate or missing entity keys? Or relationship fixup?

My opinion: nobody is going to fake that. The most alarming area is where L2O succeeds and L2E fails. Now what's the value of green unit tests? It has been said before that EF can only reliably be tested in integration tests (e.g. here) and I tend to agree.

However, that does not mean that we should forget about unit tests in projects with EF as data layer. There are ways to do it, but, I think, not without integration tests.

Hosanna

Worse: what works in EF depends on the database used. I don't have concrete examples right now, but I have had queries that failed on SQL Server 2000, but worked on 2005+. There are probably also queries that work on SQL Server, but fail on (for example) MySQL. – Ironmaster

Wow! That's an extremely detailed answer and is very enlightening. It's not what I wanted to hear, but it has forced me to take a reality check (and probably saved me from wasting a huge amount of time). Thanks. – Hoax

Yes, in fact, it's not what I like to hear either, but that's life. I added one more thing that just occurred to me. It getting worse all the time... But on the other hand, these things are true for many different data layers. One of my projects has a very simple OR mapper and even there we decided to use unit tests against a database because ordinary unit tests did not tell the whole story. – Hosanna

Great! That's an awesome detailed collection! I must favorite this as the ultimate examples that LINQ != LINQ! For section 1 I have another one: .Select(x => new A { Property1 = (x.BoolProperty ? new B { BProp1 = x.Prop1, BProp2 = x.Prop2 } : new B { BProp1 = x.Prop1 }) }): The type 'B' appears in two structurally incompatible initializations within a single LINQ to Entities query etc., etc. (from here: #10904875) – Devaughn

@Slauma. Thanks! Never ran into this one so far, but I can imagine scenarios where it could bite me too. I'll add it to the first category. – Hosanna

What do you think about using Effort to test the Entity Framework? (see effort.codeplex.com) – Reluctivity

@user89861 I know about this tool, but I'm wary of using it because it adds a complex third-party tool to the test environment of which I don't know if it always behaves correctly. It's basically a query provider for a file-based database. Even different RDMBS query providers behave differently! Also, don't underestimate maintenance of meaningful mock data and test cases if you can't do that through your own application front-end. – Hosanna

Answer 2

I have written a few Unit Tests with Entity Framework 6.1.3 using Moq and used it to override IQueryable. Note that all DbSet that should be tested needs to be marked as virtual. Example from Microsoft themselves:

Query:

using Microsoft.VisualStudio.TestTools.UnitTesting;
using Moq;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;

namespace TestingDemo
{
    [TestClass]
    public class QueryTests
    {
        [TestMethod]
        public void GetAllBlogs_orders_by_name()
        {
            var data = new List<Blog>
            {
                new Blog { Name = "BBB" },
                new Blog { Name = "ZZZ" },
                new Blog { Name = "AAA" },
            }.AsQueryable();

            var mockSet = new Mock<DbSet<Blog>>();
            mockSet.As<IQueryable<Blog>>().Setup(m => m.Provider).Returns(data.Provider);
            mockSet.As<IQueryable<Blog>>().Setup(m => m.Expression).Returns(data.Expression);
            mockSet.As<IQueryable<Blog>>().Setup(m => m.ElementType).Returns(data.ElementType);
            mockSet.As<IQueryable<Blog>>().Setup(m => m.GetEnumerator()).Returns(0 => data.GetEnumerator());

            var mockContext = new Mock<BloggingContext>();
            mockContext.Setup(c => c.Blogs).Returns(mockSet.Object);

            var service = new BlogService(mockContext.Object);
            var blogs = service.GetAllBlogs();

            Assert.AreEqual(3, blogs.Count);
            Assert.AreEqual("AAA", blogs[0].Name);
            Assert.AreEqual("BBB", blogs[1].Name);
            Assert.AreEqual("ZZZ", blogs[2].Name);
        }
    }
}

Insert:

using Microsoft.VisualStudio.TestTools.UnitTesting;
using Moq;
using System.Data.Entity;

namespace TestingDemo
{
    [TestClass]
    public class NonQueryTests
    {
        [TestMethod]
        public void CreateBlog_saves_a_blog_via_context()
        {
            var mockSet = new Mock<DbSet<Blog>>();

            var mockContext = new Mock<BloggingContext>();
            mockContext.Setup(m => m.Blogs).Returns(mockSet.Object);

            var service = new BlogService(mockContext.Object);
            service.AddBlog("ADO.NET Blog", "http://blogs.msdn.com/adonet");

            mockSet.Verify(m => m.Add(It.IsAny<Blog>()), Times.Once());
            mockContext.Verify(m => m.SaveChanges(), Times.Once());
        }
    }
}

Example service:

using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Threading.Tasks;

namespace TestingDemo
{
    public class BlogService
    {
        private BloggingContext _context;

        public BlogService(BloggingContext context)
        {
            _context = context;
        }

        public Blog AddBlog(string name, string url)
        {
            var blog = _context.Blogs.Add(new Blog { Name = name, Url = url });
            _context.SaveChanges();

            return blog;
        }

        public List<Blog> GetAllBlogs()
        {
            var query = from b in _context.Blogs
                        orderby b.Name
                        select b;

            return query.ToList();
        }

        public async Task<List<Blog>> GetAllBlogsAsync()
        {
            var query = from b in _context.Blogs
                        orderby b.Name
                        select b;

            return await query.ToListAsync();
        }
    }
}

Source: https://learn.microsoft.com/en-us/ef/ef6/fundamentals/testing/mocking

Plaster