Best ideas IMO (no particular order):
- make SQL dumber, move logic that needs testing out of SQL
- use an ORM that allows composing, disconnect composition & test (ie EF for .NET groups, test the LINQ for correct filtering etc, instead of testing for expected data from a db) (I see this has already been recommended elsewhere)
* edited formatting
1. Moving logic out of SQL can break transactionality and hurt performance (more data leaves the DB)
2. ORMs hide the SQL from you, making all sorts of other things harder
My favoured approach is to test my application against a real local database, built from a fresh snapshot each time.
I do not change my application code to make testing easier. The application code is optimised for maintainability - so simplicity and ease of reading.
Those sorts of things are programmed in EF by LINQ queries. All I'm saying is, test the LINQ queries the same way you would test non-EF LINQ queries. LINQ doesn't require a database.
Kinda, but personally I describe as using LINQ queries. The dbcontext just isn't hooked up. It's a method that takes in an IQueryable<T> (there's the interface I suppose) and outputs a filtered IQueryable<T>. The unit test (see my next response) provides a test collection and expects a certain result.
> and integration tests
No, unit tests
Functionality is pretty easy to verify with LINQ / mock data sets (though it is also easy to mock things in a way that aren't representative of real data by mistake) but the performance characteristics of a real SQL engine vs. unit testing in this way can lead to some real gotchas once it's deployed. Particularly if you're using an ORM, multiple enumerations over database queries, strange ORM choices in joins/exists/whatever can absolutely murder performance.
I would still recommend having integration and/or performance testing downstream of these types of unit tests though I agree they are useful.