c# - How to unit test LINQ with EF.Constant? - Stack Overflow

admin管理员组

文章数量:1356104

I'm working with Entity Framework Core 8.13, which uses a SQL Server 2014 database with compatibility level 120. Because of it, I started to use EF.Constant method in my Linq to SQL queries. Code seems to me working in real app. However my unit tests (XUnit 2.4, Moq 4.18) started to fail with the error:

The EF.Constant method may only be used within Entity Framework LINQ queries.

My understanding that during normal work it use database and convert LINQ to SQL normally. But during mocking it cause an error. How to write unit test properly?

Example API, first is standard, second is with "EF.Constant":

    [HttpGet("GetABunch")]
    public async Task<ActionResult<TodoItem>> GetABunch()
    {
        string[] myAnimals = { "dog", "cat" };

        // works in SQL Server 2017
        var animals = await _context.TodoItems
            .Where(i => myAnimals.Contains(i.Name))
            .ToListAsync();

        return Ok(animals);
    }

    [HttpGet("GetABunch120")]
    public async Task<ActionResult<TodoItem>> GetABunch120()
    {
        string[] myAnimals = { "dog", "cat" };

        // works in SQL Server 2014
        var animals = await _context.TodoItems
            .Where(i => EF.Constant(myAnimals).Contains(i.Name))
            .ToListAsync();

        return Ok(animals);
    }

My unit test for second one (not working):

[Fact]
public async Task GetABunch120_ReturnsFilteredTodoItems()
{
    // Arrange
    var mockData = new List<TodoItem>
    {
        new TodoItem { Id = 1, Name = "dog" },
        new TodoItem { Id = 2, Name = "cat" },
        new TodoItem { Id = 3, Name = "bird" }
    }.AsQueryable();

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

    // Mock async enumeration for EF Core
    mockSet.As<IAsyncEnumerable<TodoItem>>()
        .Setup(m => m.GetAsyncEnumerator(It.IsAny<CancellationToken>()))
        .Returns(new TestAsyncEnumerator<TodoItem>(mockData.GetEnumerator()));

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

    var controller = new TodoItemsController(mockContext.Object);

    // Act
    var result = await controller.GetABunch120();

    // Assert
    var actionResult = Assert.IsType<OkObjectResult>(result.Result);
    var returnedItems = Assert.IsType<List<TodoItem>>(actionResult.Value);
    Assert.Equal(2, returnedItems.Count);
    Assert.Contains(returnedItems, i => i.Name == "dog");
    Assert.Contains(returnedItems, i => i.Name == "cat");
}

GitHub with application code.

I'm working with Entity Framework Core 8.13, which uses a SQL Server 2014 database with compatibility level 120. Because of it, I started to use EF.Constant method in my Linq to SQL queries. Code seems to me working in real app. However my unit tests (XUnit 2.4, Moq 4.18) started to fail with the error:

The EF.Constant method may only be used within Entity Framework LINQ queries.

My understanding that during normal work it use database and convert LINQ to SQL normally. But during mocking it cause an error. How to write unit test properly?

Example API, first is standard, second is with "EF.Constant":

    [HttpGet("GetABunch")]
    public async Task<ActionResult<TodoItem>> GetABunch()
    {
        string[] myAnimals = { "dog", "cat" };

        // works in SQL Server 2017
        var animals = await _context.TodoItems
            .Where(i => myAnimals.Contains(i.Name))
            .ToListAsync();

        return Ok(animals);
    }

    [HttpGet("GetABunch120")]
    public async Task<ActionResult<TodoItem>> GetABunch120()
    {
        string[] myAnimals = { "dog", "cat" };

        // works in SQL Server 2014
        var animals = await _context.TodoItems
            .Where(i => EF.Constant(myAnimals).Contains(i.Name))
            .ToListAsync();

        return Ok(animals);
    }

My unit test for second one (not working):

[Fact]
public async Task GetABunch120_ReturnsFilteredTodoItems()
{
    // Arrange
    var mockData = new List<TodoItem>
    {
        new TodoItem { Id = 1, Name = "dog" },
        new TodoItem { Id = 2, Name = "cat" },
        new TodoItem { Id = 3, Name = "bird" }
    }.AsQueryable();

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

    // Mock async enumeration for EF Core
    mockSet.As<IAsyncEnumerable<TodoItem>>()
        .Setup(m => m.GetAsyncEnumerator(It.IsAny<CancellationToken>()))
        .Returns(new TestAsyncEnumerator<TodoItem>(mockData.GetEnumerator()));

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

    var controller = new TodoItemsController(mockContext.Object);

    // Act
    var result = await controller.GetABunch120();

    // Assert
    var actionResult = Assert.IsType<OkObjectResult>(result.Result);
    var returnedItems = Assert.IsType<List<TodoItem>>(actionResult.Value);
    Assert.Equal(2, returnedItems.Count);
    Assert.Contains(returnedItems, i => i.Name == "dog");
    Assert.Contains(returnedItems, i => i.Name == "cat");
}

GitHub with application code.

• c#
• linq
• entity-framework-core
• xunit

Share Improve this question Follow edited Mar 29 at 6:46 marc_s 756k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Mar 28 at 18:09 sam sergiy kloksam sergiy klok 6441313 silver badges2727 bronze badges 6

• The best practise would be to abstract the LINQ Query away from the actual logic, so you can test without needing EF.Constant. You could use Expression Trees or you could also wrap the logic into a reusable Query Spec or a class that can switch between EF and in-memory variants. – AztecCodes Commented Mar 28 at 18:51
• You don't need to unit test EF's Linq. If you have low level rules in Linq queries then consider a thin abstraction like a Repository pattern, but one that returns IQueryable<TEntity>. Your code under test mocks the Repository to return an expected result, isolated from what EF does to retrieve it. To test whether EF returns the expected data from a known data state (your Linq expressions are actually correct) you use an integration test. (Test that runs against an actual DbContext pointing at a real database with known state data) – Steve Py Commented Mar 29 at 1:03
• 1 These integration tests would be run less frequently, such as before checking in and as part of automated builds. They should run against the same database engine as production rather than things like in-memory databases to avoid missing issues that might be specific to your database provider. (Collation, etc.) – Steve Py Commented Mar 29 at 1:05
• Why exactly do you need EF.Constant? If it's because of OPENJSON, you don't need it. Set the compatibility level instead.. Having said that, the only answer to this question is: test against a real SQL backend. Maybe SQLite, preferably the db provider used in production. – Gert Arnold Commented Mar 29 at 8:46
• 1 I think the learning principle is that it makes little sense to unit test EF queries. See previous comments. However ingeniously one may succeed in mocking all these query translation and handling instructions like EF.Constant (Include, AsNoTracking, etc.), the outcome will give 0% confidence in actual application code. – Gert Arnold Commented Mar 29 at 18:57

 |  Show 1 more comment

1 Answer 1

Sorted by: Reset to default 0

Because EF.Constant is nearly untestable, I believe that proper way is not to use it and configure Entity Framework with TranslateParameterizedCollectionsToConstants or UseCompatibilityLevel(120) for older version. See for reference Breaking changes in EF Core 8.

If you still want to test EF.Constant than you have to use database. Here is the solution with the Microsoft.EntityFrameworkCore.InMemory which, ironically, is not recommended by Microsoft for testing. Ironically because it was created for testing purposes.

        [Fact]
        public async Task GetABunch120_InMemoryDB_ReturnsFilteredTodoItems()
        {
            var options = new DbContextOptionsBuilder<TodoContext>()
                .UseInMemoryDatabase("TestDb")
                .Options;

            using var context = new TodoContext(options);
            context.TodoItems.AddRange(
                new TodoItem { Id = 1, Name = "dog" },
                new TodoItem { Id = 2, Name = "cat" }
                //,new TodoItem { Id = 3, Name = "bird" }
                );
            await context.SaveChangesAsync();

            var controller = new TodoItemsController(context);
            var result = await controller.GetABunch120();

            var okResult = Assert.IsType<OkObjectResult>(result.Result);
            var items = Assert.IsType<List<TodoItem>>(okResult.Value);
            Assert.Equal(2, items.Count);
            Assert.Contains(items, i => i.Name == "dog");
            Assert.Contains(items, i => i.Name == "cat");
            Assert.DoesNotContain(items, i => i.Name == "Bird");
        }
    }

Seems to me, conceptually better is to rewrite API by separating Controller methods from Data method in separate layer (injectable service). Then test would be mockable:

        [Fact]
        public void GetAbunch_WithMatchingAnimals_ReturnsCorrectItems()
        {
            // Arrange
            var inputAnimals = new[] { "Cat", "Dog" };
            var expectedCount = 2;

            _todoServiceMock.Setup(x => x.GetAbunch(inputAnimals))
                .Returns(_mockData.Where(i => inputAnimals.Contains(i.Name)).ToList());

            // Act
            var result = _todoServiceMock.Object.GetAbunch(inputAnimals);

            // Assert
            Assert.NotNull(result);
            Assert.Equal(expectedCount, result.Count);
            Assert.Contains(result, item => item.Name == "Cat");
            Assert.Contains(result, item => item.Name == "Dog");
            Assert.DoesNotContain(result, item => item.Name == "Bird");
        }

Full code on GitHub.

本文标签： cHow to unit test LINQ with EFConstantStack Overflow