Building dynamic Connection String in Entity Framevork

Oct 9, 2009 at 1:19 PM


Building an Entity Connection String from Scratch

What’s important to keep in mind before we dive into code samples, is that an entity connection string consists of two separate connection strings – one for the EntityClient itself and one for the underlying store provider. Therefore we’ll be using two connection string builders to build a full entity connection string.


We need to start with the part specific to EntityClient:


            EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();


            entityBuilder.Provider = Util.ReadKeyValue("Provider");

            entityBuilder.Metadata = Util.ReadKeyValue("Metadata");


Then we dynamically construct a DbConnectionStringBuilder for the given provider (using DbProviderFactory), and we allow the user to enter values for its keywords:


            DbProviderFactory dbFactory = DbProviderFactories.GetFactory(entityBuilder.Provider);

            DbConnectionStringBuilder dbBuilder = dbFactory.CreateConnectionStringBuilder();


            foreach (string key in dbBuilder.Keys)


                dbBuilder.Add(key, Util.ReadKeyValue(key));



What’s left is to set the provider-specific connection string on the EntityConnectionStringBuilder:


            entityBuilder.ProviderConnectionString = dbBuilder.ToString();


To get the connection string out of the connection string builder, you need to call the ToString() method:





The ReadKeyValue utility method that reads a line of text from the console. (Attached is the complete source code.)



Loading an Entity Connection String

EntityConnectionStringBuilder implements the concept of “named” connections. This has been a long time ask. Connection strings are built through VisualStudio wizard, or third-party tools, or by hand, and placed into the app.config/web.config file:




            <add name="Northwind"

                 connectionString="Metadata=..\..\..\..\Northwind; Provider=System.Data.SqlClient; Provider Connection String='Data Source=.; Initial Catalog=Northwind; Integrated Security=true;'"



            <add name="Adventureworks"

                 connectionString="Metadata=..\..\..\..\AdventureWorks; Provider=System.Data.SqlClient; Provider Connection String='Data Source=.; Initial Catalog=AdventureWorks; Integrated Security=true;'"





Then the application no longer needs to worry about “building” the connection string. It only needs to “load”. Like this:


            EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();


            entityBuilder.Name = Util.ReadKeyValue("Name");



Look up the attached source code for details.

Oct 9, 2009 at 1:38 PM



An EntityClient connection string consists of a sequence of keyword/value parameter pairs separated by semicolons. The equals sign (=) connects each keyword and its value. The following table lists the valid names for keyword values in the ConnectionString.





Required if the Name keyword is not specified. The provider name, which is used to retrieve the DbProviderFactory object for the underlying provider. This value is constant.

When the Name keyword is not included in an entity connection string, a non-empty value for the Provider keyword is required. This keyword is mutually exclusive with the Name keyword.

Provider Connection String

Optional. Specifies the provider-specific connection string that is passed to the underlying data source. This connection string is expressed by using valid keyword/value pairs for the data provider. An invalid Provider Connection String will cause a run-time error when it is evaluated by the data source.

This keyword is mutually exclusive with the Name keyword.

The value of the Provider Connection String must be surrounded by quotes. The following is an example:

Provider Connection String ="Server=serverName; User ID = userID";

The following example is not going to work:

Provider Connection String =Server=serverName; User ID = userID


Required if the Name keyword is not specified. A pipe-delimited list of directories, files, and resource locations in which to look for metadata and mapping information. The following is an example:


c:\model | c:\model\sql\mapping.msl;

Blank spaces on each side of the pipe separator are ignored.

This keyword is mutually exclusive with the Name keyword.


The application can optionally specify the connection name in an application configuration file that provides the required keyword/value connection string values. In this case, you cannot supply them directly in the connection string. The Name keyword is not allowed in a configuration file.

When the Name keyword is not included in the connection string, a non-empty values for Provider keyword is required.

This keyword is mutually exclusive with all the other connection string keywords.

The application can supply the keyword/values directly in the ConnectionString property, or it can specify a value for the Name keyword. If the Name keyword is specified, the connection string keyword/values are retrieved from an application configuration file, as follows:


If the Name keyword is used in the ConnectionString property, other keywords are not allowed. The Name keyword refers to a named connection string that is stored in the connectionStrings section in an application configuration file, as shown in the following example. The Provider, Metadata, and Provider Connection String values are retrieved from the configuration file at run time.

<add name="AdventureWorksEntities"
provider=System.Data.SqlClient;provider connection string='Data Source=localhost;
Initial Catalog=AdventureWorks;Integrated Security=True;Connection Timeout=60;
multipleactiveresultsets=true'" providerName="System.Data.EntityClient" />
<add name="NorthwindEntities" connectionString="metadata=.\Northwind.csdl|.\Northwind.ssdl.\Northwind.msl;
provider=System.Data.SqlClient;provider connection string='Data Source=localhost;
Initial Catalog=Northwind;Integrated Security=True;MultipleActiveResultSets=True'"
providerName="System.Data.EntityClient" />

The keyword/value pairs can also be supplied directly in the ConnectionString property, as shown in the following example. In this case, the Name keyword is not used.

Provider Connection String= 'Data Source=localhost;
Initial Catalog=AdventureWorks;Integrated Security=True;Connection Timeout=60' "

To avoid inadvertently putting objects such as System.Data.Common.CommandTrees and ObjectContext out of sync with their metadata, EntityConnection must lock its metadata. No changes to the connection string are allowed after the metadata is locked. The following are two scenarios in which metadata is locked:

  • The EntityConnection instance is constructed through the default constructor, or through the EntityConnection(String) constructor, which accepts a connection string. In either case the connection string might be changed multiple times before the connection is opened. Calling [M:System.Data.EntityClient.EntityConnection.Open()] or [M:System.Data.EntityClient.EntityConnection.GetMetadataWorkspace()] locks the metadata.

  • The EntityConnection instance is constructed through the EntityConnection(MetadataWorkspace, DbConnection) constructor, which accepts a MetadataWorkspace and a DbConnection. In this case the metadata is locked at construction time. No changes to the connection string are ever allowed.

When metadata is loaded, the EntityConnection verifies that the conceptual model, the storage model, and the mapping file are all present.

<!-- ApplyClick with current id -->  Examples

The following example demonstrates how to use the EntityConnectionStringBuilder in conjunction with a SqlConnectionStringBuilder. The code sets properties of a SqlConnectionStringBuilder to create a SqlConnection string that supplies part of the underlying provider connection string. Note that the Provider name cannot be set by using the SqlConnectionStringBuilder, because it does not use valid SqlConnection syntax. The code creates the EntityConnection string by setting EntityConnectionStringBuilder properties.

// Specify the provider name, server and database.
string providerName = "System.Data.SqlClient";
string serverName = ".";
string databaseName = "AdventureWorks";

// Initialize the connection string builder for the
// underlying provider.
SqlConnectionStringBuilder sqlBuilder =
new SqlConnectionStringBuilder();

// Set the properties for the data source.
sqlBuilder.DataSource = serverName;
sqlBuilder.InitialCatalog = databaseName;
sqlBuilder.IntegratedSecurity = true;

// Build the SqlConnection connection string.
string providerString = sqlBuilder.ToString();

// Initialize the EntityConnectionStringBuilder.
EntityConnectionStringBuilder entityBuilder =
new EntityConnectionStringBuilder();

//Set the provider name.
entityBuilder.Provider = providerName;

// Set the provider-specific connection string.
entityBuilder.ProviderConnectionString = providerString;

// Set the Metadata location.
entityBuilder.Metadata = @"res://*/AdventureWorksModel.csdl|

using (EntityConnection conn =
new EntityConnection(entityBuilder.ToString()))
Console.WriteLine("Just testing the connection.");

Oct 11, 2009 at 9:22 PM

Switch to another Database


you can create one CSDL, one MSL and two SSDL files compiled into assembly (or distributed with application). Then you just need to change metadata part of entity connection string, and it will be working fine. WIth this you can not only use SQL Server and SQL CE, but i.e. Firebird. Just by changing SSDL (and maybe MSL, if mapping is different).

Oct 11, 2009 at 9:38 PM
Edited Oct 14, 2009 at 9:37 PM

Entity Framework QuerySamples


If you know your structures are the same, I would recommend keeping EDMX and re-generating SSDL for each target database using some automated script.


1. Split EDMX it into 3 pieces (CSDL, SSDL and MSL) - designer does this for you if you choose to copy those artifacts to the output directory.


2. Create a copy of SSDL for each target database,  modify schema names (such as "dbo", type names, column names,  etc.) and most important: change Provider and ProviderManifestToken attributes to match your provider. If this operation is algorithmic, you can write a script to do this automatically.


3. Create your ObjectContext/EntityConnection by passing explicit connection string that indicates your database and points to database-specific SSDL file.


See the sample for more information.


download the source code

Oct 11, 2009 at 10:46 PM
Edited Oct 14, 2009 at 9:16 PM

Sub-dividing the model into smaller models with type reuse and Multiple CSDL files( Models) while sharing MSL and SSDL:


How to create multiple CSDL files that will share MSL and SSDL:

The example uses Northwind sample database.

1. Create a new Ado.Net Entity Data Model using the Entity Data Model Wizard by pointing to Northwind database and choosing Products, Categories and Suppliers tables.

2. Change the “Metadata Artifact Processing” property of Edmx file to “Copy to Output Directory” directory and build the solution. This will drop the CSDL, SSDL and MSL files in the build output path.

3. Copy the schema files (CSDL, SSDL and MSL) to another location. This location will be used in the Metadata parameter of the EntityConnection string. Let’s call these files – model1.csdl, model.ssdl, model.msl

4. Open the CSDL file and copy the Cateogories Entity Type to a separate CSDL file. Let’s call this file model2.csdl. Use a different namespace for this schema. Let’s say this is NorthwindModelBase.

5. Remove the Categories entity type from the Model1.csdl.

6. Add a Using statement to import the newly created namespace.


<Schema Namespace="NorthwindModel" Alias="Self" xmlns="">
  <Using Namespace="NorthwindModelBase"  Alias="BaseModel" />

7. Change the Categories EntitySet and FK_Products_Categories to refer to the Categories Entity type.


<Association Name="FK_Products_Categories">
  <End Role="Categories" Type="BaseModel.Categories" Multiplicity="0..1" />
  <End Role="Products" Type="NorthwindModel.Products" Multiplicity="*" />

8. Use EdmGen to create two different C# files for these models. 


   a.edmgen /mode:EntityClassGeneration /incsdl:model1.csdl

         /refcsdl:model2.csdl /outobjectlayer:model1.cs

   b.edmgen /mode:EntityClassGeneration /incsdl:model2.csdl

        /outobjectlayer: model2.cs

9. You can compile these c# files into the same assembly or a different assembly. 

10. The Metadata property of the Connection string should point to both the CSDL files in addition to the SSDL and MSL files. 

11. Assuming that the base model was built before the model that was using it, the Categories Entity type would not have a navigation property

Oct 14, 2009 at 9:33 PM
Edited Oct 14, 2009 at 9:38 PM

About  problems which  apear while using EF in N -Tier app

Дело в том, что все проходит гладко, когда мы имеем один проект, из которого вызываем наш контекст. Но как только мы выделяем обработку с данными в отдельный проект, а вызываем код из другого проекта, начинаются проблемы. Во-первых, поскольку вызывающий проект (у нас это веб-проект) имеет свой файл конфигурации, в котором содержатся свои строки подключения к БД, он в упор не видит файла конфигурации App.config проекта DataCore, и следовательно, не может подключиться к базе. Как результат, мы видим на экране сообщение EntityConnection error: The specified named connection is either not found in the configuration. Это исправляется легко - достаточно скопировать строку подключения из файла App.config в файл Web.config нашего веб-проекта.

Но тут вылезает другая проблема. Давайте взглянем на строку подключения, которую мы только что скопировали:

<add name="DataCoreConnection" connectionString="metadata=.\FAQEngineModel.csdl|.\FAQEngineModel.ssdl|.\FAQEngineModel.msl;provider=System.Data.SqlClient;provider connection string="Data Source=.\SQLEXPRESS;Initial Catalog=FAQEngine;Integrated Security=True;MultipleActiveResultSets=True"" providerName="System.Data.EntityClient" />

Обратите внимание, на какие файлы метаданных она ссылается. Все эти файлы лежат в каталоге bin/Debug проекта DataCore (.\) - т.е. в каталоге, куда помещается dll. Но поскольку мы вызываем эту dll из другого проекта, то получается, что мы ищем эти файлы там, где их нет, и получаем новую ошибку: "The specified metadata path is not valid. A valid path must be either an existing directory, an existing file with extension '.csdl', '.ssdl', or '.msl', or a URI that identifies an embedded resource.". Нам остается два выхода - либо исправить строку подключения ,либо каждый раз копировать нужные файлы в какой-либо каталог веб-проекта.

Попробуем сначала первый способ. Лучше всего, если бы можно было указать относительный путь, например, с использованием какого-нибудь макроса. Единственное, что мне удается найти - это DataDirectory. Но, поскольку мы вызываем файлы из веб-приложения, этот макрос будет заменен каталогом App_Data, который нам вовсе ни к чему. Поэтому можно просто указать абсолютный путь к файлам на диске. Но потом, после деплоймента, эту строку придется менять. Поэтому этот способ мне не нравится.

Второй способ - копирование файлов - тоже не слишком хорош. Получается, что каждый раз, как только я изменяю модель, мне надо куда-то еще копировать сгенерированные файлы? Получается, что так... ладно, пусть это делает за меня студия. Открываем свойства проекта DataCore, идем в Post-Build event и пишем следующее:

copy $(TargetDir)FAQEngineModel.csdl $(SolutionDir)FAQEngine\App_Data\
copy $(TargetDir)FAQEngineModel.msl $(SolutionDir)FAQEngine\App_Data\
copy $(TargetDir)FAQEngineModel.ssdl $(SolutionDir)FAQEngine\App_Data\


Здесь мы просто копируем нужные файлы в каталог App_Data нашего веб-приложения. Теперь остается чуть подправить строку подключения (в файле Web.config):


<add name="DataCoreConnection" connectionString="metadata=|DataDirectory|\FAQEngineModel.csdl||DataDirectory|\FAQEngineModel.ssdl||DataDirectory|\FAQEngineModel.msl;provider=System.Data.SqlClient;provider connection string="Data Source=.\SQLEXPRESS;Initial Catalog=FAQEngine;Integrated Security=True;MultipleActiveResultSets=True"" providerName="System.Data.EntityClient" />


Все работает!
Решение данной проблемы описано на форуме ADO.NET Entity Framework.

Oct 14, 2009 at 9:47 PM

About using Entity Deploy Build Tasks


It sounds like you can use the "embed as resource" feature for entity deployment.  To do this, first open the edmx file in Visual Studio, then click a blank location in the designer surface.  In the Visual Studio Property Window, you should see an entry for "Metadata Artifact Processing".  If you set this value to "Embed In Output Assembly", then the CSDL , SSDL & MSL files will be embeded as resources in your output assembly, and your connection string will be updated to use a different syntax to reference these resources.  By "embedding as resource" and using this new connection string in your other projects, you can just copy around the DLL, and not worry about copying around the EDM artifact files as well.


If you still want the EntityDeploy task to copy output files to some other location, you  will need to override the definition of EntityDeploy in your project's csproj file as follows:


  <Target Name="EntityDeploy"
          DependsOnTargets="$(EntityDeployDependsOn)" >

      <Output TaskParameter="EntityDataModelEmbeddedResources"
              ItemName="EmbeddedResource" />



Note that the only thing that changed here is the value we pass to the OutputPath parameter.   Also note that you will need to update any connection strings to reflect the new location of the EDM artifacts.


Oct 15, 2009 at 11:36 AM

Embedding EDM schema files in Entity Framework Beta 3

Friday, December 14 2007 - Data Access

Note: With the RTM of Entity Framework, the model's Metadata Artifact Processing property is set to Embed in Output Assembly by default, whcih is the opposite of what is described in this blog post based on Beta 3.

One of the new features in Entity Framework Beta3 is the ability to embed the csdl, msl and ssdl schema files into the assembly which contains the model.

This is useful in scenarios where, like in a few of my solutions, you want to have a separate project to host the model and then use that project in various applications. In that case you need to make a reference to the dll with the model and the application using the model will need to know where to find the schema files through the metadata property of the special connection string.

At design time, you work with the EDMX file in the designer and you can also manually edit it's XML if and when necessary. When the project is compiled, the 3 key sections of the EDMX file are spit out to the separate schema files (*.csdl, *.mls, *.ssdl) which are used by the EF APIs.

With the schema files embedded into the dll, we don't have to keep track of the files.

Here's how to pull it off. Note that there is a workaround in these instructions for a known bug.

My project that hosts the model is called AdventureWorksModel. In it are the model as well as some additional code that extends the functionality of the code generated classes.

By default, the EDMX file's Build Action property is set to "EntityDeploy". This is important to note in case you change it for some reason. It does need to be "EntityDeploy". (I learned that the hard way, which is why I make a point of mentioning it.

Open the EDMX file in the design window and then you will see a new property in the property window called Metadata Artifact Processing. The default of this is "Copy to Output".

To get around a current known bug, build the project with the property set to "Copy to Output". Then change the Metadata Artifact Processing property to "Embed in Output Assembly". Eventually you won't need to build with Copy to Output first.

Build the project. After building, if you use a tool like Reflector, you can see the files have been compiled into the dll as in the screenshot below.

If you check the connection string in the app.config for the project you will see that there is a reference to the projects resources in the metadata properties, rather than to a file path.

<add name="Entities" 
provider=System.Data.SqlClient;provider connection string=&quot;
Data Source=;Initial Catalog=AdventureWorksLT;Integrated Security=True;MultipleActiveResultSets=True&quot;"
providerName="System.Data.EntityClient" />