Creating a DAL within its own class library using Entity Framework

Question

Update: This is not a web application; it's a server service which has a lot of DLL's that access the DB in different ways. I needed a way to have a singleton entity framework DLL that could provide the access to the DB.

I'm attempting to setup a DAL DLL object utilizing Entity Framework 6 within my project. I want to call a single object versus setting up the model within all of the objects that need it. That way I can update the data model in one place and all of my libraries will be updated as well.

So within my project I created a .NET framework project and linked to the database to create the model (DB first). So that project sees the model ok.

I can then link to the DLL object's model within one of my other projects within the solution by creating a reference to the project and linking to the created entity like this:

ModelEntities db = new ModelEntities();

I can see all of the methods and objects that were created. But it seems I cannot access the DB when I run a very simple query like:

var dbRecord = (from record in db.UserKey).FirstOrDefault();

I can create a test within the entity model class and run that above query with no issues. And when I examine the db object, it looks like it's initialized.

Then thinking about it, I'm not sure the entity model is logged into the SQL Server using the outside object and that theory seems verified when I step through the code, the db object in that case has a bunch of exceptions on it even before running the query.

I'm not even sure I'm going about this right in the first place; that could definitely be a possibility. But in the case that I am right, how can I initialize the DB connection in the remote entity class before I call the query?

It's not a good idea to have an open connection lying around waiting to be used. Connections should be opened and closed as needed. Let the connection pool do what it does best. — William Xifaras, Nov 15 '19 at 17:35

Creamer77 · Accepted Answer · 2019-11-15T17:54:05.010

When you create an EntityFramework edmx within a ClassLibrary it creates a config file within that project. Your applications main webconfig will not have the config information from your class library and will not work.

The solution is to copy the connection string config items to your Application project's web config as well as the EntityFramework configuration tag.

Connection Strings:

  <connectionStrings>
    <add name="ADatabase" connectionString="" providerName="System.Data.EntityClient" />
  </connectionStrings>

Entity Framework Tag

  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="mssqllocaldb" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>

Thanks, that's what I was lookin for and gave me something to research further. I found this which will allow me to set the connection string in the DAL and not in the individual objects.: https://stackoverflow.com/questions/29440405/entity-framework-6-set-connection-string-runtime — Zonus, Nov 15 '19 at 17:54

Creating a DAL within its own class library using Entity Framework

1 Answers1