WCF/WF - Will WCF Dataservices and Entity Framework work with MySQL?

Asked By william apken on 08-Dec-10 07:56 PM
I'm using :
 
WCF DataServices  -- EF  -  MySQL - Connector 6.3.5 VS2010 - Silverlight 4.0


I have a Web service that was created as a WFC Dataservice based on a EntityFramework model that is created from MySQL schema.
When I run the web service in Visual Studio, the Web Service is created and I'm able to retrieve data.
I'm running locally but the Web Service is connected to a remote database (MySQL-net/connector 6.3.5)

I try to used REST formatted URLs.  
http://domain.com/WcfService1.svc/cameras
  
Locally, it will return the cameras like a champ. 

NOW I DEPLOY THE APPLICATION:

When I try to access the web service. I get the following error:

The server encountered an error processing the request. The exception message is 'The specified store provider cannot be found in the configuration, or is not valid.'. See server logs for more details. The exception stack trace is: 
at System.Data.EntityClient.EntityConnection.GetFactory(String providerString) at System.Data.EntityClient.EntityConnection.ChangeConnectionString(String newConnectionString) at System.Data.Objects.ObjectContext..ctor(String connectionString, String defaultContainerName) at BRS_AfterReinstallConnector.Web.hqadminEntities..ctor() in C:\1_VS2010_PROJECTS\1_JOE\1_BRS\BRS_AfterReinstallConnector\BRS_AfterReinstallConnector.Web\Model1.Designer.cs:line 43 at invoke_constructor()
 at System.Data.Services.DataService`1.CreateProvider() at System.Data.Services.DataService`1.HandleRequest() at System.Data.Services.DataService`1.ProcessRequestForMessage(Stream messageBody) at SyncInvokeProcessRequestForMessage(Object , Object[] , Object[] ) at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs) at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage31(MessageRpc& rpc) at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)


The fact that I'm able to retrieve data through the DataServiceCollection from a remote MySQL database and populate controls in Silverlight I think tells me that the net/Connector and MySQL can work with WCF DataServices. 

When the EntityConnection.GetFactory(String providerString)  is called from my local machine, it is created and I'm able to retrieve data via the url using REST notation. 
Once deployed, and EntityConnection.GetFactory(String providerString)  is called, it complains about object cannot be created. 

I searched all day. I could find evolved around MySql.Data and MySql.Data.Entity.  I have a reference from the web side of the silverlight application.  I marked to COPY LOCAL = TRUE.

So these dlls are copied into the /bin directory.  It appears that when it is deployed and calls for the GetFactory(providerString) it is not locating the MySql dlls. (I think)

I have access to 2 different Window Server 2008 servers. One at discountASP and one for the client I'm working for. I have complete access to the client's server but limited access to the shared host. 
It did not matter which server I deployed to, each one did the exact same thing. 


It was also suggested that a possible solution would be :
<add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net
Framework Data Provider for MySQL" 

type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.2.3.0,
Culture=neutral, PublicKeyToken=c5687fc88969c44d" />

But I already have the following in my web.config file so it complained.
<connectionStrings>
    <add name="hqadminEntities"          connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=MySql.Data.MySqlClient;provider connection string=&quot;server=99.xx.1xx.1xx;User Id=brsadmin;database=xxxxx;password=xxxxxx&quot;"
         providerName="System.Data.EntityClient" />
  </connectionStrings>



And the following is located in my machine.config file :

<system.data>
    <DbProviderFactories>
      <add name="Microsoft SQL Server Compact Data Provider" invariant="System.Data.SqlServerCe.3.5" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.1.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
      <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.3.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
    </DbProviderFactories>
  </system.data>


When I committed to the client, I was under the impression I would not have any trouble getting data from MySQL into my DataServiceCollections. That is because I was only running it locally. :<

This line of code executes fine.  I check the local path, absolute path, port and DnsSafeHost properties of the svc.  These are all correct. 

hqadminEntities svc = new hqadminEntities(new Uri("WcfDataService1.svc", UriKind.Relative));


C:\1_VS2010_PROJECTS\1_JOE\1_BRS\BRS_AfterReinstallConnector\BRS_AfterReinstallConnector.Web\Model1.Designer.cs:line 43 at invoke_constructor()
This line of code is what blows everything up::

public hqadminEntities() : base("name=hqadminEntities", "hqadminEntities")
        {
            this.ContextOptions.LazyLoadingEnabled = true;
            OnContextCreated();
        }


When it attempts to create the ServiceFactory it appears that it is not finding the MySQL dlls. What am I not doing wrong??

Thank you so much.





Peter Bromberg replied to william apken on 08-Dec-10 08:51 PM
It sounds from your description that the MySql provider you're using locally (that works) isn't installed at your hosting provider. You'll need to take that up with them, unless you're just missing some provider assemblies that need to be deployed.