Entity Framework not using updated connection string




Is it possible to change the connection string within the app.config file and have Entity Framework start using the new values?


I have a multi-threaded app with one thread acting as the dispatcher. This thread has the ability to receive a message telling it to change all or part of the connection string data (such as a password change). It then updates the config file to include the new connection information and clears any pooled database connections. This part is all working correctly.


The problem is, when the dispatcher launches a new thread and this thread creates a new database context the database connection is created with the old connection string values. It appears Entity Framework is keeping a cached copy of this information somewhere. If I completely shutdown and restart the app it works correctly because it, obviously, has to read the new values from the config file.


I have seen other proposed solutions involving keeping the connection string as a variable and passing it to the context constructor. But since the dispatcher thread has no internal knowledge of the "job" threads it creates (some may not even require database access) this won't really work for me. At least not without a lot of code changes and special requirements of my job interface.


This is the code I'm using to update the file. As I said, this part is working. Is there something else I need to do to tell Entity Framework about the change?



```
 // Create new connection string

 SqlConnectionStringBuilder connectionBuilder = new SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings[CONNECTION_STRING_NAME].ConnectionString);
 connectionBuilder.DataSource = server;
 connectionBuilder.InitialCatalog = database;
 connectionBuilder.UserID = user;
 connectionBuilder.Password = password;

 // Update connection string within config file

 Configuration config = ConfigurationManager.OpenExeConfiguration(Assembly.GetExecutingAssembly().Location);
 ConnectionStringsSection section = (ConnectionStringsSection)config.GetSection("connectionStrings");
 section.ConnectionStrings[CONNECTION_STRING_NAME].ConnectionString = connectionBuilder.ToString();

 // Save changes

 ConfigurationManager.RefreshSection("connectionStrings");
 config.Save();

```
The documentation for [`ConfigurationManager.RefreshSection`](https://docs.microsoft.com/en-us/dotnet/api/system.configuration.configurationmanager.refreshsection?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2Fdev15.query%3FappId%3DDev15IDEF1%26l%3DEN-US%26k%3Dk(System.Configuration.ConfigurationManager.RefreshSection);k(TargetFrameworkMoniker-.NETFramework,Version%3Dv4.6.2);k(DevLang-csharp)%26rd%3Dtrue&view=netframework-4.7.2) method does say (emphasis is mine):



> 
> Refreshes the named section **so the next time that it is retrieved** it will be re-read from disk.
> 
> 
> 


The problem is that EF6 caches the [`ConfigurationManager.ConnectionStrings`](https://docs.microsoft.com/en-us/dotnet/api/system.configuration.configurationmanager.connectionstrings?view=netframework-4.7.2) in an internal class static property, so it never gets refreshed.


Unfortunately there is no "official" way to refresh it. Following is a brute force hack using reflection, so use it on your own risk:



```
static readonly Type EF6AppConfigType = typeof(DbContext).Assembly.GetType("System.Data.Entity.Internal.AppConfig");

static readonly PropertyInfo EF6DefaultAppConfig = EF6AppConfigType.GetProperty("DefaultInstance", BindingFlags.Public | BindingFlags.Static);

static readonly FieldInfo EF6ConnectionStrings = EF6AppConfigType.GetField("_connectionStrings", BindingFlags.NonPublic | BindingFlags.Instance);

static void RefreshEF6ConnectionStrings()
{
    EF6ConnectionStrings.SetValue(
        EF6DefaultAppConfig.GetValue(null),
        ConfigurationManager.ConnectionStrings
    );
}

```

Just call it after saving the changes:



```
config.Save();
RefreshEF6ConnectionStrings();

```
As I understand, you are following the following approach:



```
modelBuilder.Entity<IdentityRole>().HasData(new IdentityRole { Name = "Admin", NormalizedName = "Admin".ToUpper() });

```

There are two problems with this code.


The first is the usage of `modelBuilder.Entity<IdentityRole>()`. This makes `IdentityRole` class to be treated by EF Core as an *entity*, hence the `IdentityRoles` table it tries to create. It's because `IdentityRole` class is *different* from what your model is using - `IdentityRole<Guid>`. Change it to `modelBuilder.Entity<IdentityRole<Guid>>()` (and also the `new` operator) and the problem will be solved.


The second problem is that `HasData` *requires* PK values to be specified, as explained in the [Data Seeding](https://docs.microsoft.com/en-us/ef/core/modeling/data-seeding) section of the EF Core documentation:



> 
> The primary key value needs to be specified even if it's usually generated by the database. It will be used to detect data changes between migrations.
> 
> 
> 


So additionally to changing the `HasData` generic type argument, you need to pre generate Guid to be used as Id for the new role (see [Entity Framework Core Auto Generated guid](https://stackoverflow.com/questions/53493025/entity-framework-core-auto-generated-guid/53495148#53495148) for more details a the similar issue) and use something like this:



```
modelBuilder.Entity<IdentityRole<Guid>>().HasData(
    new IdentityRole<Guid>
    {
        Id = new Guid("pre generated value"),
        Name = "Admin",
        NormalizedName = "Admin".ToUpper()
    }
);

``` You can provide an optional parameter to the scaffolding command to update only the table you target.



```
Scaffold-DbContext "Server=(localdb)\mssqllocaldb;Database=DatabaseName;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir DirectoryNameOfYourModels -Tables employee -f

```

If you are using .net core cli then use.



```
dotnet ef dbcontext scaffold "Server=(localdb)\mssqllocaldb;Database=DatabaseName;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -o DirectoryNameOfYourModels -t employee -f

``` Entity Framework Core does not have lazy loading implemented at this time (RTM Build).


It is recommended that you do eager loading by using an .Include extension method. It is also a good idea to use a repository to do the eager loading for you.


Reference: <https://github.com/aspnet/EntityFramework/issues/3797> A couple of notes to get started:



> 
> .apply(literal\_eval)
> 
> 
> 


Wouldn't this be better as `map`?



> 
> I then re-create the Dask DF:
> 
> 
> ddf = dd.from\_pandas(ddf\_out, npartitions=nCores)
> 
> 
> 


`ddf_out` was already a dask dataframe, I don't know why you had to do this.



> 
> The columns in result seem to match those in metadir.
> 
> 
> 


The value of `result.columns` is taken from the meta you provided, no computing happens until you ask for it (dask is lazy in most operations). Does the ValueError exception not provide further information?


Here is a full example



```
x = ({'F9_07_PZ_COMP_DIRECT': '0',
  'F9_07_PZ_DIRTRSTKEY_NAME': 'DEBRA MEALY',
  'F9_07_PZ_COMP_OTHER': '0',
  'F9_07_PZ_COMP_RELATED': '0',
  'F9_07_PZ_TITLE': 'CHAIR PERSON',
  'F9_07_PZ_AVE_HOURS_WEEK': '1.00',
  'F9_07_PC_TRUSTEE_INDIVIDUAL': 'X'},
 {'F9_07_PZ_COMP_DIRECT': '0',
  'F9_07_PZ_DIRTRSTKEY_NAME': 'HELEN GORDON',
  'F9_07_PZ_COMP_OTHER': '0',
  'F9_07_PZ_COMP_RELATED': '0',
  'F9_07_PZ_TITLE': 'VICE CHAIR',
  'F9_07_PZ_AVE_HOURS_WEEK': '1.00',
  'F9_07_PC_TRUSTEE_INDIVIDUAL': 'X'})
df = pd.DataFrame({'a': x})
d = dd.from_pandas(df, 1)
meta = pd.DataFrame(columns=['F9_07_PZ_COMP_DIRECT', 
       'F9_07_PZ_DIRTRSTKEY_NAME',
       'F9_07_PZ_COMP_OTHER', 'F9_07_PZ_COMP_RELATED', 'F9_07_PZ_TITLE',
       'F9_07_PZ_AVE_HOURS_WEEK', 'F9_07_PC_TRUSTEE_INDIVIDUAL'], dtype="O")
d.map_partitions(lambda df: df.a.apply(pd.Series), meta=meta).compute()

```

How did I know what `meta` to use? I applied the function to the pandas dataframe - you might use a small piece of the dataframe to do this.


Some additional notes:


* it is an anti-pattern to load the data with pandas, pass to dask workers and then gather the whole result back to a pandas (in memory) dataframe, you are very unlikely to see a speedup this way and may incur a lot of overhead. You are better off loading with something like `dd.read_csv`, and aggregating or writing with dask functions too. Only `compute()` on something that will be small or not return anything (because it involves writing the output). The official examples do not use from\_pandas.
* string and dict processing are python methods, and so hold the interpreter lock (GIL) of any python function: threads will not actually run in parallel. To get parallelism, you need to run in processes, which is most easily achieved using <https://docs.dask.org/en/latest/setup/single-distributed.html>
* the distributed scheduler also gives you access to the dashboard, which has a lot of useful information to diagnose how your system is running. You can also configure a lot about its behaviour, in case you have firewall rules you need to obey. I have now implemented the following solution.


As CodeNotFound pointed out in the comments, IdentityUser used to have a `Roles` property. This is no longer the case in .NET Core. This [comment/issue](https://github.com/aspnet/Identity/issues/1361#issuecomment-348863959) on GitHub seems to be the current solution for .Net Core. I have attempted to implemented it with the following code:


**ApplicationUser**



```
public class ApplicationUser : IdentityUser
{
    public ICollection<ApplicationUserRole> UserRoles { get; set; }
}

```

**ApplicationUserRole**



```
public class ApplicationUserRole : IdentityUserRole<string>
{
    public virtual ApplicationUser User { get; set; }
    public virtual ApplicationRole Role { get; set; }
}

```

**ApplicationRole**



```
public class ApplicationRole : IdentityRole
{
    public ICollection<ApplicationUserRole> UserRoles { get; set; }
}

```

**DBContext**



```
public class ApplicationDbContext
    : IdentityDbContext<ApplicationUser, ApplicationRole, string, IdentityUserClaim<string>,
    ApplicationUserRole, IdentityUserLogin<string>,
    IdentityRoleClaim<string>, IdentityUserToken<string>>
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        builder.Entity<ApplicationUserRole>(userRole =>
        {
            userRole.HasKey(ur => new { ur.UserId, ur.RoleId });

            userRole.HasOne(ur => ur.Role)
                .WithMany(r => r.UserRoles)
                .HasForeignKey(ur => ur.RoleId)
                .IsRequired();

            userRole.HasOne(ur => ur.User)
                .WithMany(r => r.UserRoles)
                .HasForeignKey(ur => ur.UserId)
                .IsRequired();
        });
    }
}

```

**Startup**



```
services.AddIdentity<ApplicationUser, ApplicationRole>(options => options.Stores.MaxLengthForKeys = 128)
            .AddEntityFrameworkStores<ApplicationDbContext>()
            .AddDefaultTokenProviders();

```

Finally, make sure when you're using it that you eagerly load the User's UserRoles, and then the UserRole's Role like so:



```
this.Users = userManager.Users.Include(u => u.UserRoles).ThenInclude(ur => ur.Role).ToList();

```

I had an issue where the `Role` property of each `UserRole` was null and this was resolved by adding in the `.ThenInclude(ur => ur.Role)` part.


Microsoft doc on multi-level eager loading: <https://docs.microsoft.com/en-us/ef/core/querying/related-data#including-multiple-levels> need to add 



```
services.AddEntityFrameworkSqlite().AddDbContext<ApplicationDbContext>();

```

and delete



```
services.AddIdentity<IdentityUser, IdentityRole>()
                    .AddEntityFrameworkStores<ProductContext>();

```

in startup.
the error was about bad startup configuration.
Was sending too much things.


ApplicationDbContext:



```
 public class ApplicationDbContext : IdentityDbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }
        public Microsoft.EntityFrameworkCore.DbSet<Product> Products { get; set; }
        public Microsoft.EntityFrameworkCore.DbSet<List> Lists { get; set; }

        public Microsoft.EntityFrameworkCore.DbSet<ProductList> ProductLists { get; set; }


        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);

             optionsBuilder.UseSqlite("Data Source=assistant.db");

        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {

            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<ProductList>()
                .HasKey(pl => new { pl.ProductId, pl.ListId });

            modelBuilder.Entity<ProductList>()
                .HasOne(pl => pl.Product)
                .WithMany(p => p.ProductList)
                .HasForeignKey(pl => pl.ProductId)
                .OnDelete(DeleteBehavior.Cascade);

            modelBuilder.Entity<ProductList>()
                .HasOne(pl => pl.List)
                .WithMany(l => l.ProductList)
                .HasForeignKey(pl => pl.ListId)
                .OnDelete(DeleteBehavior.Cascade);
        }
    }

``` You are mixing [expressions](https://docs.microsoft.com/en-us/dotnet/api/system.linq.expressions.expression?view=netframework-4.7.1) with [lambda expressions](https://docs.microsoft.com/en-us/dotnet/api/system.linq.expressions.lambdaexpression?view=netframework-4.7.1). There are many posts showing how you can combine lambda expressions, but the essential part is to compose expressions from lambda expression [bodies](https://docs.microsoft.com/en-us/dotnet/api/system.linq.expressions.lambdaexpression.body?view=netframework-4.7.1) and rebind the [parameters](https://docs.microsoft.com/en-us/dotnet/api/system.linq.expressions.lambdaexpression.parameters?view=netframework-4.7.1).


The later is usually achieved by a custom [`ExpressionVisitor`](https://docs.microsoft.com/en-us/dotnet/api/system.linq.expressions.expressionvisitor?view=netframework-4.7.1) like this:



```
using System.Linq.Expressions;

public static class ExpressionExtensions
{
    public static Expression ReplaceParameter(this Expression expression, ParameterExpression source, Expression target)
    {
        return new ParameterReplacer { Source = source, Target = target }.Visit(expression);
    }

    class ParameterReplacer : ExpressionVisitor
    {
        public ParameterExpression Source;
        public Expression Target;
        protected override Expression VisitParameter(ParameterExpression node)
        {
            return node == Source ? Target : base.VisitParameter(node);
        }
    }
}

```

Now regarding the EF Core combined query filters. 


Using dictionaries and expression lists seems overcomplicated for what are you doing. Since [`IMutableEntityType`](https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.metadata.imutableentitytype?view=efcore-2.1) provides read/write access to the [`QueryFilter`](https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.metadata.imutableentitytype.queryfilter?view=efcore-2.1#Microsoft_EntityFrameworkCore_Metadata_IMutableEntityType_QueryFilter), the same can be achieved with a small set of a custom extension methods.


All they go inside a class like this:



```
using System;
using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

public static class QueryFilterExtensions
{
}

```

First method:



```
public static void AddQueryFilter(this IMutableEntityType target, LambdaExpression filter)
{
    if (target.QueryFilter == null)
        target.QueryFilter = filter;
    else
    {
        var parameter = target.QueryFilter.Parameters[0];
        var left = target.QueryFilter.Body;
        var right = filter.Body.ReplaceParameter(filter.Parameters[0], parameter);
        var body = Expression.AndAlso(left, right);
        target.QueryFilter = Expression.Lambda(body, parameter);
    }
}

```

This is a non generic method which combines the exiting filter with passed filter using `AndAlso` (C# `&&`) operator and shows the aforementioned lambda expression combining principles.


However it's not so useful directly, like inside your entity type configuration loop (it can, but requires you to build manually the lambda expression instead of letting C# compiler do that). So here comes the second method:



```
public static void AddQueryFilter<T>(this IMutableEntityType target, Expression<Func<T, bool>> filter)
{
    LambdaExpression targetFilter = filter;
    if (target.ClrType != typeof(T))
    {
        var parameter = Expression.Parameter(target.ClrType, "e");
        var body = filter.Body.ReplaceParameter(filter.Parameters[0], parameter);
        targetFilter = Expression.Lambda(body, parameter);
    }
    target.AddQueryFilter(targetFilter);
}

```

It's a generic method - not quite type-safe, but allows you to use a compile time lambda expression and bind it to the actual entity type as follows:



```
foreach (var entityType in modelBuilder.Model.GetEntityTypes())
{
    if (typeof(IDeleted).IsAssignableFrom(entityType.ClrType))
        entityType.AddQueryFilter<IDeleted>(e => e.DateTimeDeleted == null);
}

```

Looks better, isn't it :)


The last custom extension method is complement to (replacement of) the standard EF Core generic [`HasQueryFilter`](https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.metadata.builders.entitytypebuilder-1.hasqueryfilter?view=efcore-2.1#Microsoft_EntityFrameworkCore_Metadata_Builders_EntityTypeBuilder_1_HasQueryFilter_System_Linq_Expressions_Expression_System_Func__0_System_Boolean___) method:



```
public static EntityTypeBuilder<TEntity> AddQueryFilter<TEntity>(this EntityTypeBuilder<TEntity> target, Expression<Func<TEntity, bool>> filter)
    where TEntity : class
{
    target.Metadata.AddQueryFilter(filter);
    return target;
}

```

and allows you to replace



```
this.AddToDict<DbMyEntity>(t => t.Name == null || t.Name == "Something");

```

with the more convenient



```
modelBuilder.Entity<DbMyEntity>()
    .AddQueryFilter(t => t.Name == null || t.Name == "Something");

```

**Update (EF Core 3.0):** `QueryFilter` property has been replaced with `GetQueryFilter` and `SetQueryFilter` extension methods. Hi  

This is simple. First check whether you are able to connect Sql Server through SSMS or not.If you are able to connect through SSMS, then there may be a problem in connection string. If you are not able to connect through SSMS, then you had some problem with SQL Environment. The basic idea here is that your adding properties to the model that EF can use to make a relationship. Right you're trying to create a relationship of type `User` and that is creating an error. To assign a composite key each key needs to be a type compatible with a `Key` field, not a navigation property. So we add `UserId` and `FriendId` of type `int`, `string` or `GUID` etc. and create a relationship off those properties.



```
public class Relationship
{
    public User Friend { get; set; }

    public User User { get; set; }

    public int FriendId { get; set; }

    public int UserId { get; set; }

    public DateTimeOffset RelationshipInitializationDate { get; set; }
}

public class User
{
    public int UserId { get; set; }
}

```

You can now define a composite key across `UserId` and `FriendId`. Something like this should do:



```
public class NorthwindContext : DbContext
{
     public NorthwindContext(DbContextOptions<NorthwindContext> options):base(options) { }

     public NorthwindContext() { }

     protected override void OnModelCreating(ModelBuilder builder)
     {
         builder.Entity<Relationship>().HasKey(table => new {
         table.FriendId, table.UserId
         });
     }
     public DbSet<Relationship> Relationships { get; set; }
     public DbSet<User> Users { get; set; }
}

```

[Source: Medium - How To: Entity Framework Core relationships, composite keys, foreign keys, data annotations, Code First and Fluent API](https://medium.com/radzenhq/how-to-entity-framework-core-relationships-composite-keys-foreign-keys-data-annotations-code-f4b238086463) You can call `context.Database.Migrate()`in your `Startup.cs`


eg:



```
using (var context = new MyContext(...))
{
    context.Database.Migrate();
}

```