Sql Server Stored Procs vs Linq with C# and unit testing
Take a look at tSQLt (http://tsqlt.org/) which is a great free tool to create unit tests in SQL Server.

You can fake tables (by executing tSQLt.FakeTable) within your tests and then create mock data as you describe. Tests are themselves stored procedures and any changes made as part of your testing suite are rolled back post test.

You run your tests by executing the tSQLt.RunAll stored procedure and it gives you visual feedback summary of your test results.

To get started, navigate to http://tsqlt.org/downloads/ and download. Running Example.sql will create an example database called [tSQLt_Example], create the testing framework and some example tests to see what is possible.

Also available is the Redgate paid product SQL Test (http://www.red-gate.com/products/sql-development/sql-test/). This is a GUI wrapper around tSQLt but is not required to use tSQLt.