I have a multi-layer .Net Web Api project where (apart from the Web Api layer itself) I have a DAL which will do all database operations (I'm working with Oracle).
For that I've configured Data Access Layer with NuGet and added these next three packages:
1) EntityFramework
2) Oracle.ManagedDataAccess
3) Oracle.ManagedDataAccess.EntityFramework
And defined an entities class like the next:
using System.Data.Entity;
public class WFRHEntities : DbContext
{
public WFRHEntities() : base("WFRHEntities") {
bool instanceExists = System.Data.Entity.SqlServer.SqlProviderServices.Instance != null;
}
public DbSet<Employee> Employees { get; set; }
public DbSet<Vacancy> Vacancies { get; set; }
public virtual void Commit()
{
base.SaveChanges();
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.HasDefaultSchema("SYSTEM");
modelBuilder.Configurations.Add(new EmployeeConfiguration());
}
Then in my Web Api Vacancy controller I have a post method:
[HttpPost]
public IEnumerable<string> Post([FromBody]Vacancy vacancy)
{
if (vacancy == null)
{
throw new System.ArgumentNullException(nameof(vacancy));
}
WFRHEntities vacancyEntity = new WFRHEntities();
vacancyEntity.Vacancies.Add(vacancy);
return new string[] { "Vacancy request added correctly" };
}
And this method receives a post request from an Angular app form to create a new vacancy request but cannot make it to work as it complains Entity Framework have to be installed.
The point is that from an intuitive perspective (and in theory) you don't have any reason to install any data elements in UI (or Web Api) project, that is the reason you are supposed to have a DAL.
Let's say I wouldn't mind to install Entity Framework (I'm using v6.0) in Web Api project but the fact is that on top of all this is not enough as I also need to add this to the web.config:
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
<providers>
<provider invariantName="Oracle.ManagedDataAccess.Client"
type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.121.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</providers>
</entityFramework>
And for that I also have to add the two Oracle packages mentioned above through NuGet so Entity Framework knows it'll work with Oracle and not SQL Server and this way you start having your UI (or Web Api) full of Data related things references.
I don't want (and I don't think it's a good idea) to have all this stuff anywhere but in the DAL and FYI I'm not working with Repositories and Unit Of Work patterns as I don't know them so I'm trying to keep things simple.
I was reading this next post:
Why do I have to reference EF in my UI project?
but wasn't able to get to a workaround (or a solid solution) to my problem.
Any ideas on how to work around this?
Is it really that difficult to abstract your UI from the Data layer completely?
Thanks.
Edit: Little code rewritten.
I have rewritten some code and now I've created a VacancyRepository class in DAL like this:
namespace WFRH.Data
{
public class VacancyRepository
{
public VacancyRepository() {
}
public void Add(Vacancy entity) {
WFRHEntities vacancyEntity = new WFRHEntities();
vacancyEntity.Vacancies.Add(entity);
}
}
}
After that I removed EntityFramework (and Oracle related packages) from Web Api project and removed all Data settings in Web Api web.config and call add method in controller like this:
[HttpPost]
public IEnumerable<string> Post([FromBody]Vacancy vacancy)
{
if (vacancy == null)
{
throw new System.ArgumentNullException(nameof(vacancy));
}
VacancyRepository vr = new VacancyRepository();
vr.Add(vacancy);
return new string[] { "Vacancy request added correctly" };
}
This is my DAL app.config
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<section name="entityFramework"type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
requirePermission="false"/>
<section name="oracle.manageddataaccess.client"
type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Version=4.122.18.3, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
</configSections>
<system.data>
<DbProviderFactories>
<remove invariant="Oracle.ManagedDataAccess.Client"/>
<add name="ODP.NET, Managed Driver" invariant="Oracle.ManagedDataAccess.Client" description="Oracle Data Provider for .NET, Managed Driver"
type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.122.18.3, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
</DbProviderFactories>
</system.data>
<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly>
<publisherPolicy apply="no"/>
<assemblyIdentity name="Oracle.ManagedDataAccess" publicKeyToken="89b483f429c47342" culture="neutral"/>
<bindingRedirect oldVersion="4.122.0.0 - 4.65535.65535.65535" newVersion="4.122.18.3"/>
</dependentAssembly>
</assemblyBinding>
</runtime>
<oracle.manageddataaccess.client>
<version number="*">
<dataSources>
<dataSource alias="SampleDataSource" descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL))) "/>
</dataSources>
</version>
</oracle.manageddataaccess.client>
<connectionStrings>
<add name="OracleDbContext" providerName="Oracle.ManagedDataAccess.Client"
connectionString="User Id=oracle_user;Password=oracle_user_password;Data Source=oracle"/>
</connectionStrings>
<entityFramework>
<providers>
<provider invariantName="Oracle.ManagedDataAccess.Client" type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.122.18.3, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
</providers>
</entityFramework>
</configuration>
And the error I'm now receiving when trying to do an add operation is the next:
System.Data.SqlClient.SqlException: Permission denied in creating database in master database so now my questions are:
1) Why is it throwing an SQl exception when in my DAL app.config is it clear that all references are to Oracle and not even one Sql Server reference? 2) Why a "Create Database" error when I'm trying to add a record to an existing table?
You need to install everything that the compiler will need to reference during compilation.
Your Api code apparently knows about WFRHEntities, which is a public class from your DAL project. What members does that class have? In order to answer that question, we need to look not only at that class but also at any base classes. And, just like that, BAM, we need to know about Entity Framework's DbContext.
Anything that exposes an entity framework type publicly1 from your DAL would be a reason for the Api code to need that reference - that could be base classes, or any such types used as parameters or return types for any other members; or attributes.
One common way to hide this is to place some kind of façade type in front of the real context, and only make that (and your plain entities) public from your DAL.
but the fact is that on top of all this is not enough as I also need to add this to the web.config:
Yes, "libraries" don't have runtime config files, only applications do. Non Asp.Net applications have their <exename>.exe.config files, ASP.Net applications have web.config (Newer flavours of ASP.Net have expanded what configuration sources are possible). Any runtime configuration settings need to go in that file2. It's unfortunate that some Visual Studio tooling will add an app.config file to libraries that makes it look like those libraries have separate configuration. I see it as a bug in the tooling, others might generously describe it as giving you an example of the configuration settings you need to place in the application config file.
1Used in the broad sense of "external code sees this". Which of course is not just public but also protected
2There can in fact be multiple web.config files at multiple levels but typically you want core configuration to be in the one at the base directory for the application. Also, you can reference other config files to contain parts of your configuration, but that's designed to be able to say "this entire configuration section is found in this file", not "look at this other config file and 'merge' its contents with the current file"