C#: Testing Entity Framework FromSql to ensure proper syntax

Question

I am writing to test FromSql Statement with InMemory Database. We are attempting to utilize Sqlite.

Running the following Sql passes the unit test without error.

select * from dbo.Product

However, doing this also passes with incorrect sql syntax. Would like to make the test fail with improper sql syntax. How can we test FromSql properly?

No error came from result of bad syntax .

seledg24g5ct * frofhm dbo.Product

Full Code:

namespace Tests.Services
{
    public class ProductTest
    {
        private const string InMemoryConnectionString = "DataSource=:memory:";
        private SqliteConnection _connection;
        protected TestContext testContext;

        public ProductServiceTest()
        {
            _connection = new SqliteConnection(InMemoryConnectionString);
            _connection.Open();
            var options = new DbContextOptionsBuilder<TestContext>()
                    .UseSqlite(_connection)
                    .Options;
            testContext= new TestContext(options);
            testContext.Database.EnsureCreated();
        }


        [Fact]
        public async Task GetProductByIdShouldReturnResult()
        {
            var productList = testContext.Product
    .FromSql($"seledg24g5ct * frofhm dbo.Product");

            Assert.Equal(1, 1);
        }

Using Net Core 3.1

Answer 1

There are two things to be taken into consideration here.

First, FromSql method is just a tiny bridge for using raw SQL queries in EF Core. No any validation/parsing of the passed SQL string occurs when the method is called except finding the parameter placeholders and associating db parameters with them. In order to get validated, it has to be executed.

Second, in order to support query composition over the FromSql result set, the method returns IQueryable<T>. Which means it is not executed immediately, but only if/when the result is enumerated. Which could happen when you use foreach loop over it, or call methods like ToList, ToArray or EF Core specific Load extension method, which is similar to ToList, but without creating list - the equivalent of foreach loop w/o body, e.g.

foreach (var _ in query) { }

With that being said, the code snippet

var productList = testContext.Product
    .FromSql($"seledg24g5ct * frofhm dbo.Product");

does basically nothing, hence does not produce exception for invalid SQL. You must execute it using one of the aforementioned methods, e.g.

productList.Load();

or

var productList = testContext.Product
    .FromSql($"seledg24g5ct * frofhm dbo.Product")
    .ToList();

and assert the expected exception.

For more info, see Raw SQL Queries and How Queries Work sections of EF Core documentation.