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
 private static void CreateSSDL(string connStr, string provider, string modelName, string newSSDLFileName)
{
    IList<EdmSchemaError> ssdlErrors = null;
 
    // generate the SSDL
    string ssdlNamespace = modelName + "Model.Store";
    EntityStoreSchemaGenerator essg = new EntityStoreSchemaGenerator (provider, connStr, ssdlNamespace);
    ssdlErrors = essg.GenerateStoreMetadata();
 
    // write out errors
    if ((ssdlErrors != null && ssdlErrors.Count > 0))
    {
        System.Console.WriteLine("Errors occurred during generation:");
        //WriteErrors(ssdlErrors);
        return;
    }
 
    essg.WriteStoreSchema(newSSDLFileName);
}
 

Now call it from your method,

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

Then run the validate method as below,

Validate
 private static void Validate(string newSSDLFile)
{
    //Reading SSDL from generayted file 
    XElement s = XElement.Load(newSSDLFile);
 
    //Reading CSDL and MSL from assembly
    XElement c = XElement.Load(Assembly.GetExecutingAssembly().GetManifestResourceStream("Model1.csdl"));    
    XElement m = XElement.Load(Assembly.GetExecutingAssembly().GetManifestResourceStream("Model1.msl"));
 
    // load the csdl
    XmlReader[] cReaders = { c.CreateReader() };
    IList<EdmSchemaError> cErrors = null;
    EdmItemCollection edmItemCollection =
        MetadataItemCollectionFactory.CreateEdmItemCollection(cReaders, out cErrors);
 
    // load the ssdl 
    XmlReader[] sReaders = { s.CreateReader() };
    IList<EdmSchemaError> sErrors = null;
    StoreItemCollection storeItemCollection = MetadataItemCollectionFactory.CreateStoreItemCollection(sReaders, out sErrors);
 
    // load the msl
    XmlReader[] mReaders = { m.CreateReader() };
    IList<EdmSchemaError> mErrors = null;
    StorageMappingItemCollection mappingItemCollection =
        MetadataItemCollectionFactory.CreateStorageMappingItemCollection(
        edmItemCollection, storeItemCollection, mReaders, out mErrors);
 
 
    // validate the mappings
    IList<EdmSchemaError> viewGenerationErrors = null;
    viewGenerationErrors = EntityViewGenerator.Validate(mappingItemCollection);
            
    if (cErrors.Count > 0 || sErrors.Count > 0 || mErrors.Count > 0 || viewGenerationErrors.Count > 0)
    {
        Console.WriteLine("Error Error!!!");
    }
    else
    {
        Console.WriteLine("No Error :)");
    }
}
 

I found this solution from the EdmGen2.exe. You will find the whole source code available at http://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!!!