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.

image

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.

image

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!!!