Tuesday, July 15, 2008

"Unable to load the specified metadata resource"

Being fairly new to LINQ I have struggled for a bit to set up connections to databases other than the default one against which the Model was built. Anyway, after a bit of hunting I found some useful stuff on MSDN describing how to build firstly, an SQL string, then build an EntityConnection string based on that SQL string. I then use that EntityConnectionString to build an EntityConnection object which is used to create ObjectContext objects - connections to my Model.

private string buildConnectString(string ServerName)
        {
            SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();
            sqlBuilder.DataSource = ServerName;
            sqlBuilder.IntegratedSecurity = true;
            sqlBuilder.InitialCatalog = "MyDatabaseName";
            sqlBuilder.MultipleActiveResultSets = true;
            string sqlConnectString = sqlBuilder.ToString();

            EntityConnectionStringBuilder entBuilder = new EntityConnectionStringBuilder();
            entBuilder.Provider = "System.Data.SqlClient";
            entBuilder.ProviderConnectionString = sqlConnectString;
            entBuilder.Metadata = @"res://*/ConstructorModel.csdl|res://*/ConstructorModel.ssdl|res://*/ConstructorModel.msl";

            return entBuilder.ToString();

This had a couple of problems - initially around problems with open datareaders when doing some nested reads on various tables. For the time being I get around that by opening the ObjectContext objects with my EntityConnection object's connection string, instead of the object itself.

It looks a bit backwards, but I need to revisit this part:

EntityConnections con = new EntityConnection(buildConnectString("myServerName")

MyContext model = new MyContext(con.ConnectionString)

instead of;

MyContext model = new MyContext(con)

seems to do the trick.

There was still a big problem when attempting to use this setup, in a multi-tiered project though.

In my UnitTest project (Visual Studio Team Suite Unit testing) this worked fine. The Unit Test project has a reference directly to my Data Model assembly.

Once of go to the multi-tiered WinForms app though, which references a business component assembly, which in turn references a Data Access component, which is what references the Data Model assembly, the "Unabled to load specified metadata resource" error was thrown when attempting to instantiate the ObjectContext object, in the Data Access Components class.

Turns out, as described in this post - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3030997&SiteID=1 - referencing my ObjectContext object works in a two tier scenario of the Unit Tests project, but when the ObjectContext object doesn't get referenced directly but rather in an assembly a tier or so down the chain, you need to explicitly state the name of the assembly in the connection string, as follows;

private string buildConnectString(string ServerName)
        {
            SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();
            sqlBuilder.DataSource = ServerName;
            sqlBuilder.IntegratedSecurity = true;
            sqlBuilder.InitialCatalog = "MyDatabaseName";
            sqlBuilder.MultipleActiveResultSets = true;
            string sqlConnectString = sqlBuilder.ToString();

            EntityConnectionStringBuilder entBuilder = new EntityConnectionStringBuilder();
            entBuilder.Provider = "System.Data.SqlClient";
            entBuilder.ProviderConnectionString = sqlConnectString;
            entBuilder.Metadata = @"res://MyObjectContextAssemblyName/ConstructorModel.csdl|res://MyObjectContextAssemblyName/ConstructorModel.ssdl|res://MyObjectContextAssemblyName/ConstructorModel.msl";

            return entBuilder.ToString();

No asterix (*) in the connectionstring for you EntityConnection object.