0

I am trying to get code first Entity Framework to work with an existing MySQL database. I got Entity Framework v6.2.0 and MySQL.Data.Entity v6.9.11 from NuGet. I did try MySQL.Data.Entity v6.10.7, but reverted to 6.9.11 after being blocked by the same Inheritance security rules problem as this question.

The config contains the following (username and password removed for obvious reasons):

  <connectionStrings>
    <add name="MySql" providerName="MySql.Data.MySqlClient" connectionString="SERVER=sql-dev;Database=pen;UID=****;PWD=****;Allow User Variables=True;" />
  </connectionStrings>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
      <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6, Version=6.9.11.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"></provider>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>
  <system.data>
    <DbProviderFactories>
      <remove invariant="MySql.Data.MySqlClient" />
      <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.9.11.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
    </DbProviderFactories>
  </system.data>

By reverting to v6.9.11 I have got to the point where I can query the DbSet and see the SQL that it is generating in the debugger. I can connect, and it recognises that the schema is correct but when I try to perform a simple test query it fails.

 Assert.IsTrue(context.BinLocs.Count() > 0);

Using the debugger I have found out that it fails because the query it generates SQL uses a syntax optimised for SQL Server, which does not work for MySQL.

The SQL that Entity Framework generated was: 

SELECT 
    [Extent1].[TID] AS [TID], 
    [Extent1].[zone] AS [zone], 
    [Extent1].[ProdCode] AS [ProdCode], 
    [Extent1].[chute] AS [chute]
FROM [dbo].[tbl_s_products_binlocs] AS [Extent1];

Unfortunately the database has been set to use ` as quote identifiers and there is no dbo schema. Knowing this I can easily get this SQL to work by manually changing the SQL to take this into account:

SELECT 
    `Extent1`.`TID` AS `TID`, 
    `Extent1`.`zone` AS `zone`, 
    `Extent1`.`ProdCode` AS `ProdCode`, 
    `Extent1`.`chute` AS `chute`
FROM `tbl_s_products_binlocs` AS `Extent1`;

When I run this SQL against the database I get the results that I expected. I cannot change the way database expects identifiers to be quoted because there are already legacy programs that have to be kept running and assume this syntax. So I need to get Entity Framework to generate SQL that will work with the existing database.

How do I get Entity Framework to generate SQL with the backtick quoted identifiers expected by MySQL?

Updated to include the configuration settings

c bunker
  • 55
  • 5
  • `it fails.` What exception does it throw? – mjwills May 02 '18 at 10:51
  • I wonder why the provider is not generating a `SELECT COUNT(*)...` query. – Tsahi Asher May 02 '18 at 10:56
  • @TsahiAsher I think because in EF you can map one or all colums in a table and it the provider cannot know if what you selected to be mapped is every column or just a few. Launching select * will select every column in the table on the database no matter what you need or what you configured to be mapped by the ORM – Liquid Core May 02 '18 at 11:07
  • 1
    The MySQL provider indeed uses (hardcoded) ``` for quoted identifiers (and no `dbo`), and the generated SQL queries look like the desired example. Something is wrong at your side (configuration or something). – Ivan Stoev May 02 '18 at 11:08
  • @LiquidCore I think Tsahi Asher question is reasonable, because `context.BinLocs.Count()` should generate different (`COUNT`) based SQL than the posted by OP. – Ivan Stoev May 02 '18 at 11:11
  • @IvanStoev I don't get what you are saying. I just explained to Tsahi why the framework does not generate a select * statement. There was no criticism at all intended – Liquid Core May 02 '18 at 11:13
  • @LiquidCore I'm saying (like Tsahi) that `context.BinLocs.Count()` should generate `SELECT COUNT(*)` – Ivan Stoev May 02 '18 at 11:15
  • 1
    @IvanStoev `SELECT COUNT(*)`, not `SELECT (*)`. – Tsahi Asher May 02 '18 at 11:17
  • I agree with @IvanStoev. Check that your connection string is configured with the MySql provider (`provider` attribute in the value of the `connectionString` attribute). – Tsahi Asher May 02 '18 at 11:21

1 Answers1

1

You need to configure the Framework generator.

Follow this.

Liquid Core
  • 1
  • 6
  • 27
  • 52