0
votes

I have ASP.NET MVC web app which uses Entity Framework and Compact DB - "db.sdf" or "db.mdf" file.

It work at localhost.

But I want to publish it to Azure as 1-hour try web app.

I got this error:

The Entity Framework provider type 'System.Data.Entity.SqlServerCompact.SqlCeProviderServices, EntityFramework.SqlServerCompact' registered in the application config file for the ADO.NET provider with invariant name 'System.Data.SqlServerCe.4.0' could not be loaded. Make sure that the assembly-qualified name is used and that the assembly is available to the running application.

What is going on? I have installed packages:

EF.SqlServer
EF.SqlServerCompact
System.Data.SqlServerCe

And the web.config looks:

<configSections>
    <section name="entityFramework"
             type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, 
         EntityFramework, Version=6.0.0.0, Culture=neutral, 
         PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</configSections>

<connectionStrings>
    <add name="DefaultConnection" connectionString="Data 
     Source=|DataDirectory|db.sdf" 
     providerName="System.Data.SqlServerCe.4.0" />
</connectionStrings>

<entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlCeConnectionFactory, EntityFramework">
        <parameters>
            <parameter value="System.Data.SqlServerCe.4.0" />
        </parameters>
    </defaultConnectionFactory>
    <providers>
        <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
        <provider invariantName="System.Data.SqlServerCe.4.0" type="System.Data.Entity.SqlServerCompact.SqlCeProviderServices, EntityFramework.SqlServerCompact" />
    </providers>
</entityFramework>

<system.data>
    <DbProviderFactories>
        <remove invariant="System.Data.SqlServerCe.4.0" />
        <add name="Microsoft SQL Server Compact Data Provider 4.0" 
             invariant="System.Data.SqlServerCe.4.0" 
             description=".NET Framework Data Provider for Microsoft SQL Server Compact" 
             type="System.Data.SqlServerCe.SqlCeProviderFactory, 
              System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, 
           PublicKeyToken=89845dcd8080cc91" />
    </DbProviderFactories>
</system.data>
1
"But I want to publish it to Azure as 1-hour try web app" - not sure what this means. - David Makogon
Maybe you need to make the file "Copy To Local" - to do this, open references, click on SqlServerCe.4.0 and in the properties window choose Copy To Local. That will make sure that the file is part of the deployment package. - Rick Hodder

1 Answers

1
votes

Do you want to check whether SQL Compact database could work on Azure Web App? I just test it on my Web App. It can work fine. You could use it for development environment. For production environment, we suggest you Azure SQL Database.

'System.Data.SqlServerCe.4.0' could not be loaded.

To use SQL Compact in .NET application, we need to install following packages from NuGet.

Microsoft.SqlServer.Compact
EntityFramework.SqlServerCompact

After that, following 2 .NET assemblies and multi native assemblies will be referenced by your application.

enter image description here

enter image description here

For your issue, please check whether the copy local property is set to 'true' for the 2 .NET assemblies.

enter image description here

Before using SQL Compact, you need to configure connection string in web.config as following.

<add name="DefaultConnection" connectionString="Data Source=|DataDirectory|\myDB.mdf" providerName="System.Data.SqlServerCe.4.0"/>

After that, you could use SQL Compact by EF. Code below is for your reference.

public class MyDataContext : DbContext
{
    public MyDataContext():base("DefaultConnection") { }

    public DbSet<Student> Students { get; set; }
}

public class Student
{
    public int ID { get; set; }

    public string Name { get; set; }
}

public ActionResult Index()
{
    MyDataContext context = new MyDataContext();
    context.Students.Add(new Student() { ID = 1, Name = "name1" });
    context.SaveChanges();
    return Content("Create data OK!");
}

If you use code first model, you also need to create App_Data folder under wwwroot folder after deploying your web application to Azure Web App if this folder is not created by default.