current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
    
My stored procedures & tables may exist in a different schema, other than dbo. –  Brian Jan 22 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 at 17:14
    
A similar question: stackoverflow.com/questions/19847192/… –  Brian Jan 23 at 16:31

2 Answers 2

up vote 2 down vote

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

share|improve this answer
1  
Not sure why this was downvoted. It is a legitimate solution, probably the best one. –  Robert Harvey Jan 22 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 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 at 17:20
1  
There's some ADO.NET code examples here that you can use to execute the query. –  Robert Harvey Jan 22 at 17:27
1  
Have a look here and here –  Robert Harvey Jan 22 at 18:54
up vote 0 down vote accepted

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();
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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