Using a different connection provider for Entity Framework on ASP.NET Core

Question

I'm using ASP.NET Core 1.1 with Entity Framework 6 to connect to a SQL Server Compact 3.5 database. How can I configure what provider to use when creating the DbContext?

MyDbContext.cs

public class MyDbContext: DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // omitted
    }

    public static DbMasContext Create(string connString)
    {
        var entityBuilder = new EntityConnectionStringBuilder();
        entityBuilder.ProviderConnectionString = connString;
        entityBuilder.Provider = "System.Data.SqlServerCe.3.5";
        return new DbMasContext(entityBuilder.ConnectionString);
    }
}

Startup.cs

public class Startup
{
    public void ConfigureServices(IServiceCollection services)
    {
        services.AddMvc();
        services.AddScoped(_ => DbMasContext.Create(Configuration.GetConnectionString("MyDbContext")));
    }

    // omitted
}

appsettings.json

{
  "ConnectionStrings": {
    "MyDbContext": "DataSource=C:\\MyDb.sdf;Max Database Size=2048;",
  }
}

My code causes this error:

ArgumentException: Keyword not supported: 'provider'

provider=System.Data.SqlServerCe.3.5;provider connection string="DataSource=C:\MyDb.sdf;Max Database Size=2048;" — Lucius
– Lucius, Commented Sep 5, 2017 at 1:35

Lucius · Accepted Answer · 2017-09-05 01:54:32Z

1

You need to specify the provider in a class that inherits from DbConfiguration.

SqlCeDbConfiguration

public class SqlCeDbConfiguration: DbConfiguration
{
    public DbConfig()
    {
        SetProviderServices("System.Data.SqlServerCe.3.5", System.Data.Entity.SqlServerCompact.Legacy.SqlCeProviderServices.Instance);
    }
}

Then you can apply a DbConfigurationType attribute to your DbContext-derived class

MyDbContext.cs

[DbConfigurationType(typeof(SqlCeDbConfiguration))]
public class MyDbContext: DbContext
{
    // ...
}

answered Sep 5, 2017 at 1:54

Lucius

2,8824 gold badges24 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TidyDev · Accepted Answer · 2017-09-04 10:25:05Z

0

SQL Server Compact dosen't yet work out of the box in .net core, so it involves a little bit more work than simply editing the provider to get it working. You need to create a model for it to work.

You can use the EntityFramework.SqlServerCompact package and use their example model as a template.

using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;

namespace Sample
{
public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlCe(@"Data Source=C:\data\Blogging.sdf");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Blog>()
            .HasMany(b => b.Posts)
            .WithOne(p => p.Blog)
            .HasForeignKey(p => p.BlogId);
    }
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }

    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}
}

Hope this helps.

answered Sep 4, 2017 at 10:25

TidyDev

3,7189 gold badges35 silver badges55 bronze badges

1 Comment

platinums Over a year ago

using the template i get 'DbContextOptionsBuilder' does not contain a definition for 'UseSqlCe' ?

Collectives™ on Stack Overflow

Using a different connection provider for Entity Framework on ASP.NET Core

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related