Tip 51 – How to load EF metadata from arbitrary streams

In Tip 45 I showed you how to build a connection string at runtime, which is pretty nifty.

The problem with that was that it relies on having metadata files (.csdl .ssdl and .msl) on local disk.

But what if they live on a web-server or something and you don’t even have access to the local file system to copy them locally?

Well it turns out you can load the metadata from streams too, and this Tip shows you how.

Step 1: Get XmlTextReaders for the CSDL, MSL and SSDL:

This can be as simple as ‘new XmlTextReader(url)’.

But in this example I’m going to show you how to do it from string variables, which of course you could have got from anywhere:

string csdl = "…";
string ssdl = "…";
string msl = "…";

var csdlReader = new StringReader(csdl);
var ssdlReader = new StringReader(ssdl);
var mslReader = new StringReader(msl);

var csdlXmlReader = new XmlTextReader(csdlReader);
var ssdlXmlReader = new XmlTextReader(ssdlReader);
var mslXmlReader = new XmlTextReader(mslReader);

Step 2: Create metadata ItemCollections:

Next you need an EdmItemCollection for the CSDL, a StoreItemCollection for the SSDL and a StorageMappingItemCollection for the MSL:

var edmItemCollection = new EdmItemCollection(
new[] { csdlXmlReader }
var storeItemCollection = new StoreItemCollection(
new[] { ssdlXmlReader }
var storeMappingItemCollection =
new StorageMappingItemCollection(
new [] { mslXmlReader }

The only one that is even remotely interesting is the StorageMappingItemCollection, which along with MSL needs the other ItemCollections to validate the mapping.

Step 3: Create a MetadataWorkspace:

Next you need to group the ItemCollections together in a MetadataWorkspace:

var mdw = new MetadataWorkspace();

Step 4: Create an EntityConnection:

Finally we need an EntityConnection. To create an EntityConnection we need a native database connection - generally this is a SqlConnection, but of course because EF has a provider model it could be something else like Oracle:

var sqlConnectionString = @"Data Source=.\SQLEXPRESS;Ini…";
var sqlConnection = new SqlConnection(sqlConnectionString);
var entityConnection = new EntityConnection(

Step 5: Create ObjectContext and use as Required

The last step is to use the EntityConnection you just created to construct your ObjectContext and begin using as just like normal:

using (var ctx = new ProductCategoryEntities(entityConnection))
foreach (var product in ctx.Products)

That’s it… not necessarily simple but easy enough once you know how.