Entity Framework without App.config, Database first, Repository Pattern, N-tier Solution Architecture

Question

OK, now I give up. My challenge is to build a generic DAL using Entity Framework 6, Repository Pattern and N-Tier Solution Architecture.

Synopsis: Using this approach https://blog.magnusmontin.net/2013/05/30/generic-dal-using-entity-framework, I created my DAL and BLL. All good and well, Unittests completes without a hitch. Nice.

Challenge: I then created a Presentation Layer, which includes an Excel AddIn. But since the database used can be different from each client, the database ConnectionString needs to be changed from the client. OK, adding a app-config to the Excel-template project… doesn't work. No matter what I do, I get no data back from the database.

Now I need to remove the need for an app.config and instead just supply the ConnectionString from the Client, when initialising the BL.

I tried the suggestions from Entity Framework - Database First without config and How can l use Entity Framework without App.config, but I cannot seem the get it to fit nicely into my challenge. I did use some of the solutions mentioned in those, but since my DAL is using the Repository Pattern, I cannot quite get my limited head around how to implement the solutions mentioned...

What I have done: I added a DbConfiguration-class in the DataAccessLayer, which implements multiple constructors. (I cannot figure out HOW to hit the other constructors - only the parameterles constructor, but that's an issue for another question). My constructor for ythis classe looks like this:

private DefaultConfiguration()
{

    SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder()
    {
        DataSource = database,
        InitialCatalog = initialCatalog,
        UserID = userId,
        Password = password,
        IntegratedSecurity = useTrustedConnection
    };

    // Initialize the EntityConnectionStringBuilder.
    EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder()
    {
        Provider = providerName,
        // Set the provider-specific connection string.
        ProviderConnectionString = sqlBuilder.ToString(),
        // Set the Metadata location.
        Metadata = @"res://*/CommonModel.csdl|res://*/CommonModel.ssdl|res://*/CommonModel.msl"
    };

    SetExecutionStrategy("System.Data.SqlClient", () => new DefaultExecutionStrategy(), database);      
}

EntityConnection entityCon = new EntityConnection(entityBuilder.ToString());
//SetDefaultConnectionFactory(new SqlConnectionFactory(entityCon.ConnectionString));
//SetDefaultConnectionFactory(new SqlConnectionFactory(sqlBuilder.ToString()));

I just cant seem to figure out HOW to use my EntityConnection object in the initialisation of my DbConfiguration. Neither of the 2 last lines of code seems to be working properly.

In my DbContext, i changed the Constructor from

public CommonEntities()
    : base("name=CommonEntities")
{
    this.Configuration.LazyLoadingEnabled = false;
    this.Configuration.ProxyCreationEnabled = false;
}

to

public CommonEntities(string ConnectionString)
    : base(ConnectionString)
{
    this.Configuration.LazyLoadingEnabled = false;
    this.Configuration.ProxyCreationEnabled = false;
}

This though, only brings out the dreaded UnIntentionalCodeFirstException, and I am now stuck.

Question:

How do I get the ConnectionString from my PL all the way through to the DAL? I would try to create a non-parameterless Repository-constructor, which would accept a SQL-sonnectionstring as parameter, but how?

I know this is a huge question, but I have tried to give as much information I could without causing a SystemOutOfMemoryException and tried to boil my issue down to a single question - more questions WILL arise, later on, but for now this must suffice…

Hope some of you prodigies can give me a pointer or two…

This is not an answer, however EF is already a Repository pattern, and 99.99% percent of the time when i see them in use, they create more problems then they solve — TheGeneral, Aug 03 '18 at 07:41
How many different database would you have ? If this number is small, I would have one DAL process per database with its own configuration file. — , Aug 03 '18 at 07:51
I absolutely agree - thank you for your input, I might just dismiss the whole repo-pattern all together if it comes to that, but for now, I want this to work :) — Martin Frank Moesby Petersen, Aug 03 '18 at 07:53
@OlivierMATROT - The whole point is to eliminate the need of a configuration file. — Martin Frank Moesby Petersen, Aug 03 '18 at 07:54
Well, apparently I do, since the app.config from an Excel-template project doesn't seem to supply the needed information to the DbContext, please don't ask me why - I cannot seem to get that part to work. If I could, this entire question would be obsolete... — Martin Frank Moesby Petersen, Aug 03 '18 at 08:59
@MartinFrankMoesbyPetersen `an Excel-template project` that's **important**. You are building an Excel template, hence VSTO. Why do you need EF in the first place when Excel has PowerQuery? Why not configure Excel's own data sources and transformations? — Panagiotis Kanavos, Aug 03 '18 at 10:40
@MartinFrankMoesbyPetersen in any case you don't need a config file to configure EF. The only thing useful thing in there is the connection string, which you can pass to the constructor of your DbContext. You can pull the information from *Excel* itself - from a Sheet with configuration data, or from a data connection etc. — Panagiotis Kanavos, Aug 03 '18 at 10:43
@MartinFrankMoesbyPetersen in any case you'd need EF only if you packaged a business application as an Excel addin. Otherwise, EF is getting in the way of Excel's powerful data processing functionaltiy. PowerPivot uses the same columnstore engine as Analysis Services and PowerQuery is a *very* powerful ETL system — Panagiotis Kanavos, Aug 03 '18 at 10:44
"How do I get the ConnectionString from my PL all the way through to the DAL?" Why does such a simple question come with so much word-salad before it? The answer is: Pass it through the layers on creation. — Davesoft, Aug 03 '18 at 11:33
@PanagiotisKanavos - It is a typo from my part - it is an Excel AddIn, not Template - sorry for confusions. I've edited the question... — Martin Frank Moesby Petersen, Aug 03 '18 at 12:04
@Davesoft - Thank you Dave - I've done that, and now i Get UnIntentionalCodeFirstException error…. — Martin Frank Moesby Petersen, Aug 03 '18 at 12:05
@AmitJoshi - I have done exactly that, which is proposed in your reference - I do not use the Generic Repo directly, but each entity have it's own repo, inheriting from the Generic - but this stil doesn't answer my question….. — Martin Frank Moesby Petersen, Aug 09 '18 at 10:48

score 1 · Accepted Answer · answered Aug 09 '18 at 13:30

OK, I never got it working like I wanted to, so i changed my DomainModel to NOT use an EDMX file - which is the entire cause of the final error i got…

Så basically I've changed the EF modelling from Database First to Code-First....

I also added EntityFramework package to my Excel-Addin project.

Entity Framework without App.config, Database first, Repository Pattern, N-tier Solution Architecture

1 Answers1