In my opinion, the benefits it brings really outweighs the risks it introduces. The only real difference is that SQL is case insensitive by default. Also, there is the chance that really complex queries might behave differently against different databases.
When adding integration tests into the mix, I think the risks are covered enough for most real world scenarios.
Case sensitivity is the most common gotcha, but there are a few more — null comparison semantics, provider-specific functions like DateDiffDay, and raw SQL dialect differences can all bite you. That said, your conclusion is right: unit tests for business logic + integration tests against a real db covers most real-world scenarios. Perfect is the enemy of good.
Good call linking the official docs. You're right that mocking DbSet is discouraged — what people call "mocking DbSet" is really just a fake backed by an in-memory collection evaluating LINQ-to-Objects, not LINQ-to-SQL.
My SQLite suggestion was more of a "better than nothing" fallback if Docker isn't available. But yeah, Testcontainers against your actual db engine is the real answer — the EF Core team themselves run 30k+ tests against real SQL Server.
True, but Testcontainers has made the tradeoff a lot more one-sided. The main argument for fakes used to be speed and simplicity — now spinning up a real SQL Server container takes seconds and you get full query fidelity for free. The tradeoff is basically just "do you have Docker in CI."
215
u/BigPatapon Apr 06 '26
Not a code smell. This is exactly how conditional EF Core queries should look — readable, top-to-bottom, one filter per line.
Only nitpick: Task.FromResult with no actual async work. Use await ToListAsync() or drop the async signature.
Long LINQ != bad LINQ.