Unable to cast object of type 'Microsoft.Data.SqlClient.SqlConnection' to type 'System.Data.Entity.Core.EntityClient.EntityConnection'

[Jeb135]

Hi, I am using EF 6.4.4 in a large .NET Framework 4.7.2 solution and attempting to migrate EF from using System.Data.SqlClient to Microsoft.Data.SqlClient. I found this library for using Microsoft.Data.SqlClient as an EF provider and managed to get this working with a DbConfiguration class like so:

public class EFConfiguration : DbConfiguration
{
    public EFConfiguration()
    {
        SetProviderFactory(MicrosoftSqlProviderServices.ProviderInvariantName, Microsoft.Data.SqlClient.SqlClientFactory.Instance);
        SetProviderServices(MicrosoftSqlProviderServices.ProviderInvariantName, MicrosoftSqlProviderServices.Instance);
        SetExecutionStrategy("Microsoft.Data.SqlClient", () => new MicrosoftSqlAzureExecutionStrategy());
    }
}

However, at runtime, when I attempt to use EF I receive the error in the title, "Unable to cast object of type 'Microsoft.Data.SqlClient.SqlConnection' to type 'System.Data.Entity.Core.EntityClient.EntityConnection'" way down in the guts of System.Data.Entity and not sure how to resolve this. I'm lost on what could be causing this, if its a configuration issue, or something else entirely. Any help would be greatly appreciated.

EF code that causes this issue:

using (EFModels models = new EFModels()) // Uses EFModels connection string below
{
    List<MyEntity> roles = models.MyEntitys.Include(sr => sr.SomeOtherFKBasedEntity).ToList(); // line that bombs out.
}

Connection String:

<add name="EFModels" connectionString="metadata=res://*/EFModels.csdl|res://*/EFModels.ssdl|res://*/EFModels.msl;provider=Microsoft.Data.SqlClient;provider connection string=&quot;data source=it01;initial catalog=test;integrated security=True;persist security info=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="Microsoft.Data.SqlClient" />

Stack Trace of the error:

   at System.Data.Entity.Internal.InternalConnection.CreateObjectContextFromConnectionModel()
   at System.Data.Entity.Internal.LazyInternalContext.InitializeContext()
   at System.Data.Entity.Internal.InternalContext.Initialize()
   at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType)
   at System.Data.Entity.Internal.Linq.InternalSet`1.Initialize()
   at System.Data.Entity.Internal.Linq.InternalSet`1.Include(String path)
   at System.Data.Entity.Infrastructure.DbQuery`1.Include(String path)
   at System.Data.Entity.QueryableExtensions.Include[T,TProperty](IQueryable`1 source, Expression`1 path)
   at My.Application.MoreCode.That.Uses.EF(IIdentity identity)
   at My.Application.MvcApplication.Application_AuthenticateRequest(Object sender, EventArgs e)
   at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
   at System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step)
   at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

[ErikEJ]

I think I need a repro project - you can share privately.

[Jeb135]

Ok, give me some time to create one that's pared all the way down to just the problem area.

[ErikEJ]

Perfect!

[ErikEJ]

Are you using DbContext? Or ObjectContext? I suspect that my package may only work with DbContext.

[Jeb135]

I am using DbContext

[ErikEJ]

Your connection string indicates otherwise. Do you use an edmx file and have you updated that?

[Jeb135]

Yes I do have an EDMX and I have updated it. What in the connection string makes it appear to be using ObjectContext?

<Schema Namespace="EFModel.Store" Provider="Microsoft.Data.SqlClient" ProviderManifestToken="2012" Alias="Self" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns:customannotation="http://schemas.microsoft.com/ado/2013/11/edm/customannotation" xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl">

[ErikEJ]

That the connection string is for a EntityConnection, not a SqlConnection

[Jeb135]

Hmm I do have another connection string for direct sql connections when we're not using EF, but EF doesn't like that one due to the lack of the metadata.

 <add name="SqlDirect" connectionString="data source=it01;initial catalog=test;integrated security=True;persist security info=True;" />

[ErikEJ]

I will await a repro.

[ErikEJ]

Any news?

[Jeb135]

Apologies it's been slow trying to get a working repo together, and got sidetracked looking into if this is a possible "code first" vs "database first" issue somewhere. I should have something available this afternoon, however.

[Jeb135]

Ok so I can't reproduce my specific error in this repo (or I havent added a dependency/bit of code that is relevant to the larger solution that's causing this), but having another issue with the connection strings that might be related. The repo is here: https://github.com/Jeb135/MSSqlClientAndEF

You can use either the ASP.NET app or the console app to run it, but depending on which connection string you use in the config files, you get one of two errors.

1. Use "EFModels" error - Keyword not supported: 'metadata'. This seems to be related to your earlier comment about the connection string looking like an EntityConnection string not a Microsoft.Data.SqlClient connection string.

2. Use "EFModelsNoMetadata" error - The context is being used in Code First mode with code that was generated from an EDMX file for either Database First or Model First development. This will not work correctly. To fix this problem do not remove the line of code that throws this exception. If you wish to use Database First or Model First, then make sure that the Entity Framework connection string is included in the app.config or web.config of the start-up project. If you are creating your own DbConnection, then make sure that it is an EntityConnection and not some other type of DbConnection, and that you pass it to one of the base DbContext constructors that take a DbConnection. To learn more about Code First, Database First, and Model First see the Entity Framework documentation here: http://go.microsoft.com/fwlink/?LinkId=394715

I am using database first and not sure why I would get this error if the metadata part of the connection string was to be left out, but that seems to be the case? Seems like no configuration of connection string is happy.

[ErikEJ]

I did some testing:

1. Your EDMX must live in a "Classic" .csproj, not a modern one.
2. Your connection string is wrong, last part must be "System.Data.EntityClient"

       <add name="EFModels" 
   connectionString="metadata=res://*/EFModels.csdl|res://*/EFModels.ssdl|res://*/EFModels.msl;provider=Microsoft.Data.SqlClient;provider connection string=&quot;data source=it01;initial catalog=mssqlclient;integrated security=True;persist security info=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

[ErikEJ]

I will improve the docs with a sample EDMX connection string

[Jeb135]

Ya know, the more I work on this more I realize I don't understand EF at all. So changing the providerName attribute in the connection string to System.Data.EntityClient resolved the issue in both the repo and the original solution that spawned this issue. I guess I don't really understand the difference between this provider and the provider in the connection string being Microsoft.Data.SqlClient. I really appreciate your help on this.

Also, why does the older csproj format need to be used instead of the newer one? The newer format seems to work fine, only issue I was running into was needing to add

 <Compile Update="OtherTest.cs">
      <DesignTime>True</DesignTime>
      <AutoGen>True</AutoGen>
      <DependentUpon>TestModel.tt</DependentUpon>
    </Compile>

for each new entity to the csproj by hand.

[ErikEJ]

So everything works as expected now?

[Jeb135]

I thought so but doing some testing the error is now occurring in other areas (but working fine in the original ones). I will report back once I know more.

[Jeb135]

Ok this appears to now be a unity injection of EF issue unrelated to this package if you want to close this. Otherwise I can update when I have fully resolved in case anyone might run into a similar problem in the future.

[ErikEJ]

I will leave it open until I have added a note about the edmx connection string to the readme