Cannot create an EF database in Ntier Structure

Harry

I am trying to separate the API and DataAccessLayer, however I am unable to create a database using EF 6 code first approach.

Based on the structure and set up below, a database is created in the localdb space, but not in the SQL Server Express instance on my machine. What am I missing or not doing correct?

Project structure:

API ( WebApi )
 - Controller
 - App.config ( from shared lib )
 - Web.config

DataAccessLayer ( Shared Library .NET Framework 461)
  - Models
  - DBContext
  - Migrations
  - web.config

Tests

API

Web.config

<?xml version="1.0" encoding="utf-8"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  https://go.microsoft.com/fwlink/?LinkId=301879
  -->
<configuration>

  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
  </configSections>
  <appSettings>
    <add key="webpages:Version" value="3.0.0.0" />
    <add key="webpages:Enabled" value="false" />
    <add key="ClientValidationEnabled" value="true" />
    <add key="UnobtrusiveJavaScriptEnabled" value="true" />
  </appSettings>
  <system.web>
    <compilation debug="true" targetFramework="4.6.1" />
    <httpRuntime targetFramework="4.6.1" />
  </system.web>
  <system.webServer>

    <modules>
      <remove name="TelemetryCorrelationHttpModule" />
      <add name="TelemetryCorrelationHttpModule" type="Microsoft.AspNet.TelemetryCorrelation.TelemetryCorrelationHttpModule, Microsoft.AspNet.TelemetryCorrelation" preCondition="integratedMode,managedHandler" />
    </modules>
    <handlers>
      <remove name="ExtensionlessUrlHandler-Integrated-4.0" />
      <remove name="OPTIONSVerbHandler" />
      <remove name="TRACEVerbHandler" />
      <add name="ExtensionlessUrlHandler-Integrated-4.0" path="*." verb="*" type="System.Web.Handlers.TransferRequestHandler" preCondition="integratedMode,runtimeVersionv4.0" />
    </handlers>
  </system.webServer>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="Antlr3.Runtime" publicKeyToken="eb42632606e9261f" />
        <bindingRedirect oldVersion="0.0.0.0-3.5.0.2" newVersion="3.5.0.2" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Diagnostics.DiagnosticSource" publicKeyToken="cc7b13ffcd2ddd51" />
        <bindingRedirect oldVersion="0.0.0.0-4.0.2.1" newVersion="4.0.2.1" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="Newtonsoft.Json" culture="neutral" publicKeyToken="30ad4fe6b2a6aeed" />
        <bindingRedirect oldVersion="0.0.0.0-11.0.0.0" newVersion="11.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Optimization" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="1.1.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="WebGrease" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="0.0.0.0-1.6.5135.21930" newVersion="1.6.5135.21930" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-5.2.4.0" newVersion="5.2.4.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <system.codedom>
    <compilers>
      <compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:1659;1699;1701" />
      <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:41008 /define:_MYTYPE=\&quot;Web\&quot; /optionInfer+" />
    </compilers>
  </system.codedom>
  <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>
</configuration>

Reference added DataAccessLayer.dll

Controller

 [HttpGet]
        public List<DataAccessLayer.Models.Customer> Get()
        {
            var model = new List<DataAccessLayer.Models.Customer>();
            using(var context = new DataAccessLayer.Models.DatabaseContext.CustomerTransactionContext())
            {
                model = context.Customers.ToList();
            }
            return model;
        }

DataAccessLayer

DatabaseContext

 public class CustomerTransactionContext : DbContext
    {
        public CustomerTransactionContext() : base("CustomerTransactionContext")
        {
            Database.SetInitializer(new CreateDatabaseIfNotExists<CustomerTransactionContext>());
        }

        public DbSet<Customer> Customers { get; set; }
        public DbSet<Transaction> Transactions { get; set; }
        public DbSet<CurrencyCode> CurrencyCodes { get; set; }
        public DbSet<TransactionStatus> TransactionStatuses { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        }
    }

App.config - this is the connection string section

  <connectionStrings>
    <add name="CustomerTransactionContext"
       connectionString="Data Source=DESKTOP-6ERMBUH\SQLEXPRESS01;initial catalog=CustomerTransactionContext;Integrated Security=true"
       providerName="System.Data.SqlClient"/>
  </connectionStrings>

Migrations Configuration

 internal sealed class Configuration : DbMigrationsConfiguration<Models.DatabaseContext.CustomerTransactionContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = true;
    }

    protected override void Seed(Models.DatabaseContext.CustomerTransactionContext context)
    {
        //  This method will be called after migrating to the latest version.

        //  You can use the DbSet<T>.AddOrUpdate() helper extension method 
        //  to avoid creating duplicate seed data.

        context.Customers.AddOrUpdate(c => c.CustomerID,
           new Customer { ContactEmail = "", CustomerName = "Jonathan", MobileNo = 0123456789, CustomerID = 1 },
           new Customer { ContactEmail = "", CustomerName = "Mary", MobileNo = 424356909, CustomerID = 2 },
           new Customer { ContactEmail = "", CustomerName = "Ali", MobileNo = 612456749, CustomerID = 3 },
           new Customer { ContactEmail = "", CustomerName = "Bob", MobileNo = 716356789, CustomerID = 4 },
           new Customer { ContactEmail = "", CustomerName = "Lucy", MobileNo = 313453789, CustomerID = 5 },
           new Customer { ContactEmail = "", CustomerName = "Edwardo", MobileNo = 242346760, CustomerID = 6 }
           );

        context.TransactionStatuses.AddOrUpdate(ts => ts.TransactionStatusID,
            new TransactionStatus { Name = "Success", TransactionStatusID = 1 },
            new TransactionStatus { Name = "Failed", TransactionStatusID = 2 },
            new TransactionStatus { Name = "Canceled", TransactionStatusID = 3 });

        context.CurrencyCodes.AddOrUpdate(cc => cc.CurrencyCodeID,
           new CurrencyCode { Name = "USD", CurrencyCodeID = 1 },
           new CurrencyCode { Name = "JPY", CurrencyCodeID = 2 },
           new CurrencyCode { Name = "THB", CurrencyCodeID = 3 },
           new CurrencyCode { Name = "SGD", CurrencyCodeID = 4 });

        context.Transactions.AddOrUpdate(t => t.TransactionID,
        new Transaction { TransactionStatusID = 1, Amount = 29.99M, CurrencyCodeID = 1, TransactionDateTime = DateTime.Now, CustomerID = 1 },
        new Transaction { TransactionStatusID = 2, Amount = 9.99M, CurrencyCodeID = 3, TransactionDateTime = DateTime.Now, CustomerID = 1 },
        new Transaction { TransactionStatusID = 3, Amount = 1.32M, CurrencyCodeID = 1, TransactionDateTime = DateTime.Now, CustomerID = 1 },
        new Transaction { TransactionStatusID = 3, Amount = 1.32M, CurrencyCodeID = 1, TransactionDateTime = DateTime.Now, CustomerID = 1 },
        new Transaction { TransactionStatusID = 3, Amount = 1.32M, CurrencyCodeID = 1, TransactionDateTime = DateTime.Now, CustomerID = 1 });
    }
}
ingvar

Look at following lines in your Web.config:

<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>

Your connection factory is set to mssqllocaldb, that's why db is created using local db. Fix your connection string to use your SQL instance name or just remove this code in order to make App.config setting work.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related