I have a Entity Framework Database First Model.

I want to write a MSTest/nUnit test to verify that all the stored procs, tables and views that are defined in my edmx model are still valid on the database.

  • My stored procedures & tables may exist in a different schema, other than dbo. – Brian Jan 22 '14 at 17:03
  • I know how to check the database for existence. I need to get the list from EF to check. – Brian Jan 22 '14 at 17:14
  • A similar question: stackoverflow.com/questions/19847192/… – Brian Jan 23 '14 at 16:31

Query the system tables. This will verify the presence of all of the required objects in your database.

For example, the following query uses the sys.objects catalog view to return all database objects that have been modified in the last 10 days.

SELECT name AS object_name 
  ,SCHEMA_NAME(schema_id) AS schema_name
FROM sys.objects
WHERE modify_date > GETDATE() - 10
ORDER BY modify_date;

See Also
Querying the SQL Server System Catalog
ADO.NET Code Examples

  • 1
    Not sure why this was downvoted. It is a legitimate solution, probably the best one. – Robert Harvey Jan 22 '14 at 17:10
  • 1
    I didn't vote it down, but my issue is with getting the list of SP, tables and views from EF. – Brian Jan 22 '14 at 17:15
  • 1
    I'm looking for calling a .Net method to get a list of objects defined in my model, then I can execute your SQL to verify the existence of the stored procedure. Its the .net side I'm trying to figure out. – Brian Jan 22 '14 at 17:20
  • 1
    There's some ADO.NET code examples here that you can use to execute the query. – Robert Harvey Jan 22 '14 at 17:27
  • 1
    Have a look here and here – Robert Harvey Jan 22 '14 at 18:54

I found the answer:

var list = context.MetadataWorkspace.GetItems<EdmFunction>(DataSpace.SSpace).Where(i=>i.ReturnParameter == null);

This returned the correct schema and stored procedure name. I then could call for each item in the list:

var sqlCommands = string.Format("SELECT 'x' FROM sys.objects WHERE object_id = OBJECT_ID(N'{0}') AND type in (N'P', N'PC')", storedProcedureName);
var exists = dbContext.Database.SqlQuery<string>(sqlCommands).Any();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.