EntityFrameworkCore throws exceptions, but tests pass

Question

I use Microsoft.EntityFrameworkCore, Microsoft.EntityFrameworkCore.InMemory and Microsoft.EntityFrameworkCore.SqlServer version 5.0.17

We have a problem with unit test, using context database inmemory, beacuse the execution of some queries (with same values that real cases) pass the tests. However, when I run a use case that reaches the same point as the test with the same values, the query throws exceptions.

I know the solution of the exception, but the problem is, Why aren't exceptions thrown when I run tests that hit the same queries with the same input parameters?

Example:

public Hotel GetHotel(int codHotel)
{
	var query = _context
		.Hotel
		.Where(c => c.CodHotel == codHotel)
		.Select(s => new Hotel()
		{
			HotelCode = codHotel,
			TaxInclude = GetTaxType(s.Impuestos) //Exception in real case, not exception in tests
		})
		.FirstOrDefault();
	return query;
}

public static string GetTaxType(decimal? taxType)
{
    return taxType.HasValue ? Common.Constants.INCLUDEDTAXES : Common.Constants.EXCLUDEDTAXES;
}

EXCEPTION:

The client projection contains a reference to a constant expression of 'Barcelo.Integrator.Booking.Repository.HotelRepository' through the instance method 'GetTaxType'.

This could potentially cause a memory leak; consider making the method static so that it does not capture constant in the instance.

I know change this function to static and resolve the exception.

BUT, the real problem is... Why don't the tests throw those exceptions?

Other example:

public List<Cancelation> GetAgreements(int hotelCode, string checkIn, string checkOut)
{
	var query = _context
	.AcuerdosTarifaTramos
	.Where(c => c.CodHotel == hotelCode
		&& c.FechaHasta.ToDate(Common.Constants.FORMATDATETIME_YYYYMMDD) >= checkOut.ToDate(Common.Constants.FORMATDATETIME_YYYYMMDD))
	.Select(s => new CancelationPolicy()
	{
		Release = Release
	})
	.ToList();
	return query;
}

Throw exception:

ToDate("yyyyMMdd") >= __ToDate_3)' could not be translated. Additional information: Translation of method 'System.Extensions.ToDate' failed.

Translation of method 'System.Extensions.ToDate' failed.

Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

Again, real case throws exception, but Tests pass with same inputs parameters.

The assembly in the execution is the same database context and entityFrameworkCore

The difference is only that the real case load database with Microsoft.EntityFrameworkCore.SqlServer 5.0.17

and Tests load database with Microsoft.EntityFrameworkCore.InMemory 5.0.17

Is it possible that sqlserver handles those exceptions and inmemory doesn't?

How could I make the tests reliable?

with the current problem, tests are worthless.

Thanks

Answer 1

@Jesús Torres, Welcome to Microsoft Q&A,

First of all, I will explain why test could be passed but use a case throw the related exceptions.

Please read the Microsoft Learning EF Core In-Memory Database Provider, which has the following mention:

The In-Memory provider was not designed for use outside of testing environments and should never be used as such.

Therefore, you may not get it worked in real cases.

Now I will explain why these two exceptions will throw.

EXCEPTION:

The client projection contains a reference to a constant expression of 'Barcelo.Integrator.Booking.Repository.HotelRepository' through the instance method 'GetTaxType'.

This could potentially cause a memory leak; consider making the method static so that it does not capture constant in the instance.

Based on my test, I reproduced this problem. According to my research, the possible reason may be that If you include an instance method in an IQueryable, it will be cached and the method will not be released even after your context has been processed. If you want to learn more about it, you could refer to this answer.

Translation of method 'System.Extensions.ToDate' failed.

Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

EF Core doesn't know how to translate ToDate method into a SQL command. Here you could try the following code to replace ToDate method:

.Where(c => c.CodHotel == hotelCode
                && DateTime.ParseExact(c.FechaHasta, Common.Constants.FORMATDATETIME_YYYYMMDD, System.Globalization.CultureInfo.InvariantCulture) >= DateTime.ParseExact(checkOut, Common.Constants.FORMATDATETIME_YYYYMMDD, System.Globalization.CultureInfo.InvariantCulture))
            .Select(s => new Cancelation()
            {
                Release = Release
            })

Hope my explanation could be helpful.

Best Regards,

Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thank you very much!!!,

I will change all test to SqlLite

https://learn.microsoft.com/en-us/ef/core/providers/in-memory/?tabs=vs

https://learn.microsoft.com/en-us/ef/core/testing/

https://learn.microsoft.com/en-us/ef/core/testing/choosing-a-testing-strategy#sqlite-as-a-database-fake

Thank you again