Entity Framework finding the differences between production database and model’s schema

We develop our ADO.NET EF model from the development environment db and then when finally run it to the production we might find that DBA might have added few rules or renamed the database columns. Then our application would start throwing errors. One easy way to get it rectified is to run query against each entity and catch if any error. Otherwise EF gives some public API to use.

Background

I have created my edmx from my dev database and it has just two columns.

Now at production let’s suppose my column is EmpNameTest. How can I programmatically check the difference?

I was going through the tool EdmGen.exe and it gives us an option to validate via

EdmmGen /mode:ValidateArtifacts /inssdl:Model1.ssdl /inmsl:Model1.msl /incsdl:Model1.csdl

All the three files can comes from your edmx file. If you choose the option from edmx file to be part of your output directory, then it will same all of then individually to your output directory.

Now, I will generate the ssdl at runtime by reading the production database, this gets saved to my output directory.

Create SSDL

1. private static void CreateSSDL(string connStr, string provider, string modelName, string newSSDLFileName)
2. {
3.     IList<EdmSchemaError> ssdlErrors = null;
4.  
5.     // generate the SSDL
6.     string ssdlNamespace = modelName + "Model.Store";
7.     EntityStoreSchemaGenerator essg = new EntityStoreSchemaGenerator (provider, connStr, ssdlNamespace);
8.     ssdlErrors = essg.GenerateStoreMetadata();
9.  
10.     // write out errors
11.     if ((ssdlErrors != null && ssdlErrors.Count > 0))
12.     {
13.         System.Console.WriteLine("Errors occurred during generation:");
14.         //WriteErrors(ssdlErrors);
15.         return;
16.     }
17.  
18.     essg.WriteStoreSchema(newSSDLFileName);
19. }

p

Now call it from your method,

1. string newSSDLFileName = "MyTestModel.ssdl";
2.  
3. //Create NEW SSDL at runtime
4. CreateSSDL(myConnectionString, "System.Data.SqlClient", "Sept2010", newSSDLFileName);

Then run the validate method as below,

Validate

1. private static void Validate(string newSSDLFile)
2. {
3.     //Reading SSDL from generayted file
4.     XElement s = XElement.Load(newSSDLFile);
5.  
6.     //Reading CSDL and MSL from assembly
7.     XElement c = XElement.Load(Assembly.GetExecutingAssembly().GetManifestResourceStream("Model1.csdl"));    
8.     XElement m = XElement.Load(Assembly.GetExecutingAssembly().GetManifestResourceStream("Model1.msl"));
9.  
10.     // load the csdl
11.     XmlReader[] cReaders = { c.CreateReader() };
12.     IList<EdmSchemaError> cErrors = null;
13.     EdmItemCollection edmItemCollection =
14.         MetadataItemCollectionFactory.CreateEdmItemCollection(cReaders, out cErrors);
15.  
16.     // load the ssdl
17.     XmlReader[] sReaders = { s.CreateReader() };
18.     IList<EdmSchemaError> sErrors = null;
19.     StoreItemCollection storeItemCollection = MetadataItemCollectionFactory.CreateStoreItemCollection(sReaders, out sErrors);
20.  
21.     // load the msl
22.     XmlReader[] mReaders = { m.CreateReader() };
23.     IList<EdmSchemaError> mErrors = null;
24.     StorageMappingItemCollection mappingItemCollection =
25.         MetadataItemCollectionFactory.CreateStorageMappingItemCollection(
26.         edmItemCollection, storeItemCollection, mReaders, out mErrors);
27.  
28.  
29.     // validate the mappings
30.     IList<EdmSchemaError> viewGenerationErrors = null;
31.     viewGenerationErrors = EntityViewGenerator.Validate(mappingItemCollection);
32.             
33.     if (cErrors.Count > 0 || sErrors.Count > 0 || mErrors.Count > 0 || viewGenerationErrors.Count > 0)
34.     {
35.         Console.WriteLine("Error Error!!!");
36.     }
37.     else
38.     {
39.         Console.WriteLine("No Error :)");
40.     }
41. }

I found this solution from the EdmGen2.exe. You will find the whole source code available at https://code.msdn.microsoft.com/EdmGen2

Two points before you try it out,

• Add the assembly System.Data.Entity.Design from Windows\Assembly folder
• Change the Edmx’s Metadata Artifact Processing property to Embed in Output Assembly

Namoskar!!!

Comments

• Anonymous
  October 23, 2010
  nice article - stumbled across this in my research for hosting edmx generation outside of VS.  thanks!

• Anonymous
  March 27, 2013
  Hi, I'm trying to use your code, but when I call MetadataItemCollectionFactory.CreateStorageMappingItemCollection I get error(in mErrors) "The version of EdmItemCollection must match the version of StoreItemCollection." Any idea what this could mean? Thank you.