Create DataContext from Entity Framework Connection String?

Question

I am trying to make this call in my code:

string conn = ConfigurationManager.ConnectionStrings["MyDBEntities"].ConnectionString; DataContext context = new DataContext(conn); Table<MyApp.Entities.Employee> myTable = context.GetTable<MyApp.Entities.Employee>(); 

Here are my connection strings:

 <connectionStrings> <add name="MyDBEntities" connectionString="metadata=res://*/Entities.MyDB.csdl|res://*/Entities.MyDB.ssdl|res://*/Entities.MyDB.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=STEVEN-PC;Initial Catalog=MyDB;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" /> <add name="MyDB" connectionString="Server=STEVEN-PC;Database=MyDB;Trusted_Connection=yes;" /> </connectionStrings> 

I get an error when creating a DataContext: Keyword is not supported: metadata.

If I use the second connection string, I get an error when I try to get the table: Server error in the '/' application. The type "MyApp.Entities.Employee" is not displayed in the form of a table.

What am I doing wrong here?

Answer 1

If you want to use the Entity Framework, you should use an ObjectContext, not a DataContext, since it is a base class from Linq-To-Sql.

When you create an Entity Data ADO.NET data model, Visual Studio generates (after you finish creating the model from the database wizard or using the constructor), a class derived from ObjectContext that has a default connection string (which you select in the Wizard). Here you can see a good tutorial from the ADO.NET team on how to start using EF.

You should not use the ObjectContext directly, at least not manually create the metadata files and point to them in your connection string (it was never believed that the DataContext class is used directly, so if I make a mistake, someone will correct me), since the wizard that I mentioned above creates all kinds of mapping data - to map SQL tables / views / other materials to Entity classes.

if you want to provide your own connection to the class, you can do it programmatically using EntityConnectionStringBuilder .

This is an example using EntityConnectionStringBuilder from MSDN

Edit: I mistakenly wrote about the DataContext, as if it were the EF base class for the code created by the constructor. This is how casperOne declared the base class for the Linq-To-Sql classes.

My answer has been modified to reflect his comment.

Answer 2

You mix and match LINQ-to-SQL and LINQ- to Entites ; these two are incompatible.

When you create entity models with LINQ-to-Entities, it creates an object derived from an ObjectContext that would have an IQueryable<T> that you would use to base your query. This ObjectContext will also have constructors that accept the appropriate metadata to map entity models to the database; therefore, Entity Framework connection strings require Metadata links.

When you try to use LINQ-to-SQL, you can pass it a regular database connection with the DataContext class (or derived class). DataContext handles the display of your objects in the database differently than the Entity Framework; it relies on attributes on models to match tables / columns (using TableAttribute and ColumnAttribute respectively). These attributes are missing when you created objects using the Entity Framework.

Note. You can use XML mapping files (a different look than the one used in the Entity Framework) with LINQ-to-SQL, but it is usually not used.

Thus, the simplest approach would be to select one technology stack (LINQ-to-SQL or LINQ-to-Entities) and stick to it.

Answer 3

Just in case, this is what I did. I have no links in Web.config since I need a DropDownList to select a connection.

 string connDev = @"metadata=res://*/MyModel.csdl|res://*/MyModel.ssdl|res://*/MyModel.msl;provider=System.Data.SqlClient;provider connection string=""Server=MyDevServer;Database=MyDB;Integrated Security=True"""; EntityConnection ec = new EntityConnection(connDev); MyDBContext db = new MyDBContext(ec); var people = db.People.ToList(); 

Answer 4

The connection string is already defined in EntityModel, so you can try using the default connection as follows:

 using (context = new DataContext()) { var myTable = context.GetTable<MyApp.Entities.Employee>(); } 

You can also try the following:

 MyApp.Entities.Employee myTable = context.GetTable<MyApp.Entities.Employee>(); 

EDIT: GetTable <T> () will return type T, so the above syntax will be correct.

If you want to redefine the connection string, use the second connection string from the web.config file (MyDB)