0

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.

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

2 Answers 2

2

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
  ,type_desc
  ,create_date
  ,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() - 10
ORDER BY modify_date;

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

11
  • 1
    Not sure why this was downvoted. It is a legitimate solution, probably the best one. Commented Jan 22, 2014 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
    Commented Jan 22, 2014 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
    Commented Jan 22, 2014 at 17:20
  • 1
    There's some ADO.NET code examples here that you can use to execute the query. Commented Jan 22, 2014 at 17:27
  • 1
    Have a look here and here Commented Jan 22, 2014 at 18:54
0

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 and acknowledge you have read our privacy policy.

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