Entity framework database first

[michelsmit]

Hi. we have a project which is using a database-first generated model. When executing a query I receive the following error:

Unable to cast object of type 'ReliableDbProvider.SqlAzure.SqlAzureConnection' to type 'System.Data.SqlClient.SqlConnection'.

Do you have any thoughts on how to fix this? Connectionstring is posted below.

add name="Entities" connectionString="metadata=res:///Model1.csdl|res:///Model1.ssdl|res://*/Model1.msl;provider=ReliableDbProvider.SqlAzure;provider connection string="data source=blabla.database.windows.net;initial catalog=blabla;persist security info=True;user id=bla@blabla;password=bla;App=EntityFramework"" providerName="System.Data.EntityClient"

[robdmoore]

Hi @michelsmit,

Try changing the providerName from System.Data.EntityClient to ReliableDbProvider.SqlAzure and make sure you have the following in your web.config:

  <system.data>
    <DbProviderFactories>
      <add name="Sql Azure Reliable Provider" invariant="ReliableDbProvider.SqlAzure" description="Reliable Db Provider for SQL Azure" type="ReliableDbProvider.SqlAzure.SqlAzureProvider, ReliableDbProvider" />
    </DbProviderFactories>
  </system.data>

This is as per the readme at: https://github.com/MRCollective/ReliableDbProvider.

Let me know if that works for you? :)

[michelsmit]

Hi @robdmoore,

Thanks for your quick reply. Below is the content of my app.config in this case.

<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <connectionStrings>
    <add name="Entities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=mysampledb.database.windows.net;initial catalog=mysampledb;persist security info=True;user id=user@mysampledb;password=pwd;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v11.0" />
      </parameters>
    </defaultConnectionFactory>
  </entityFramework>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="EntityFramework" publicKeyToken="b77a5c561934e089" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-5.0.0.0" newVersion="5.0.0.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <system.data>
    <DbProviderFactories>
      <add name="Sql Azure Reliable Provider" invariant="ReliableDbProvider.SqlAzure" description="Reliable Db Provider for SQL Azure" type="ReliableDbProvider.SqlAzure.SqlAzureProvider, ReliableDbProvider" />
    </DbProviderFactories>
  </system.data>
</configuration>

When I change the connection string to:

<add name="Entities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=mysampledb.database.windows.net;initial catalog=mysampledb;persist security info=True;user id=user@mysampledb;password=pwd;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="ReliableDbProvider.SqlAzure" />

I receive the following error:

Keyword not supported: 'metadata'.

When I change the provider in the connectionString attribute:

<add name="Entities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=ReliableDbProvider.SqlAzure;provider connection string=&quot;data source=mysampledb.database.windows.net;initial catalog=mysampledb;persist security info=True;user id=user@mysampledb;password=pwd;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

I receive the following error:

Unable to cast object of type 'ReliableDbProvider.SqlAzure.SqlAzureConnection' to type 'System.Data.SqlClient.SqlConnection'.

We've built a database using a regular database project and generated an entity model for it using Entity Framework 5.

Hope this rings a bell to you :)

Best regards,

Michel

[robdmoore]

Ah right, of course. My bad (I don't use the database-first stuff in EF so I forgot about how it worked for a moment),

So the System.Data.EntityClient is definitely needed in the providerName attribute of the add element.

The one that's inside of the connection string should replace provider=System.Data.SqlClient with provider=ReliableDbProvider.SqlAzure as per your second example.

I think your type-cast problem is likely due to the <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">, which I imagine needs to be changed to a IDbConnectionFactory that returns a SqlAzureConnection.

For the moment I don't have time to write one and add it to the library, however, it should be fairly easy for you to write one and define it. I'll leave this issue open to remind me to create a model-based test case and fix the problems.

Let me know how you go!

[jakehulse]

Any progress on this issue? This looks like a great solution; however, it is incomplete without this resolution.

Thanks for the great work!
Jake

[robdmoore]

Hi @jakehulse.

I made a suggestion above as to the possible fix, which should hopefully be quite easy to achieve.

I don't suppose you can give it a go to see if that works and report back so I know if that's right?

[jakehulse]

@robdmoore

To be honest this is a bit over my head, but I am happy to try. I have altered my connection string to use ReliableDbProvider as the provider inside the metadata; however, I am not sure how you want me to alter the default ConnectionFactory.

Here is the current config:

<?xml version="1.0"?>
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=4.4.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>

  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
  </startup>

  <connectionStrings>
    <add name="LDBEntities" connectionString="metadata=res://*/Models.LDBDB.csdl|res://*/Models.LDBDB.ssdl|res://*/Models.LDBDB.msl;provider=ReliableDbProvider.SqlAzure;provider connection string=&quot;data source=VMDEVSYSTEM;initial catalog=LDB;integrated security=True;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

  <system.data>
    <DbProviderFactories>
      <add name="Sql Azure Reliable Provider" invariant="ReliableDbProvider.SqlAzure" description="Reliable Db Provider for SQL Azure" type="ReliableDbProvider.SqlAzure.SqlAzureProvider, ReliableDbProvider" />
      <add name="Sql Azure Reliable Provider With Timeout Retries" invariant="ReliableDbProvider.SqlAzureWithTimeoutRetries" description="Reliable Db Provider for SQL Azure with Timeout Retries" type="ReliableDbProvider.SqlAzureWithTimeoutRetries.SqlAzureProvider, ReliableDbProvider" />
    </DbProviderFactories>
  </system.data>

  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />      <!--THIS WAS ORIGINAL-->
  </entityFramework>
</configuration>

On first request to the database, I receive the following Exception:

System.Configuration.ConfigurationErrorsException was unhandled
  HResult=-2146232062
  Message=Failed to find or load the registered .Net Framework Data Provider.
  Source=System.Data
  BareMessage=Failed to find or load the registered .Net Framework Data Provider.
  Line=0
  StackTrace:
       at System.Data.Common.DbProviderFactories.GetFactory(DataRow providerRow)
       at System.Data.Common.DbProviderFactories.GetFactory(String providerInvariantName)
       at System.Data.EntityClient.EntityConnection.GetFactory(String providerString)
       at System.Data.EntityClient.EntityConnection.ChangeConnectionString(String newConnectionString)
       at System.Data.EntityClient.EntityConnection..ctor(String connectionString)
       at System.Data.Entity.Internal.LazyInternalConnection.InitializeFromConnectionStringSetting(ConnectionStringSettings appConfigConnection)
       at System.Data.Entity.Internal.LazyInternalConnection.TryInitializeFromAppConfig(String name, AppConfig config)
       at System.Data.Entity.Internal.LazyInternalConnection.Initialize()
       at System.Data.Entity.Internal.LazyInternalConnection.CreateObjectContextFromConnectionModel()
       at System.Data.Entity.Internal.LazyInternalContext.InitializeContext()
       at System.Data.Entity.Internal.InternalContext.Initialize()
       at System.Data.Entity.Internal.InternalContext.ForceOSpaceLoadingForKnownEntityTypes()
       at System.Data.Entity.DbContext.System.Data.Entity.Infrastructure.IObjectContextAdapter.get_ObjectContext()
       at LDB.DataLayer.Models.LDBEntities.People_UpdateData(String fName, String lName, String street, String city) in C:\Projects\LDB\LDB.DataLayer\Models\LDBDbContext.Context.cs:line 174
       at LDB.DataLayer.Utilities.StoredProcedures.People_Update(String FName, String LName, String Street, String City) in C:\Projects\LDB\LDB.DataLayer\Utilities\StoredProcedures.cs:line 54
       at LDB.LUpdater.Providers.BAH.UpdatePpl() in C:\Projects\LDB\LDB.LUpdater\Providers\BAH.cs:line 107
       at LDB.LUpdater.LUpdaterService.LUpdaterWorker() in C:\Projects\LDB\LDB.LUpdater\LUpdaterService.cs:line 51
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

[robdmoore]

Hi @jakehulse.

I've had a little look at this over the last couple of hours and it's actually a lot trickier than I originally suspected.

It looks like it's not possible to fix the problem with EF5 since it seems to hardcode the provider factory that it uses (unless I'm missing something, which is entirely possible). So I've pushed up a work-in-progress pull request at #5 that explores upgrading to EF6 (where it is possible to override the provider factory).

It doesn't currently work however since there are a lot of changes to the provider model in EF6 that I don't fully understand yet.

I'd say at this point, unless I manage to discover better or someone that knows more about it than I do corrects me, that it's not possible to use this library with Entity Framework Database First for EF < 6 and for 6 there is more work required (and potentially a different approach to take advantage of the support the new provider has for wrapping commands). It's worth noting that EF6 builds in some support for connection resiliency (that I haven't look at in depth yet), that might provide at least some of the functionality in this library: http://msdn.microsoft.com/en-US/data/dn456835

[jakehulse]

@robdmoore

Thank you for the update and all your effort on this, I really appreciate it! I will review the link and the work-in-progress version and see what I can do.

Thanks again,
Jake

[robdmoore]

FYI - I've changed the README to indicate that we don't support Database-First and recommen to upgrade to Entity Framework 6 in that instance.

If anyone finds a way around this issue then feel free to pipe up and we can add support :)