I am using Devart dotConnect for MySQL with Entity Framework 6 in a Windows Forms application (.Net Framework 4.8). I'm using the DB first approach with a dynamic connection string. My application can connect to any number of databases all with the same schema, so I am building the connection string based on input from the user. I have used this approach on several other projects and have never encountered this issue before. I have spent the last day searching for someone reporting a similar issue, with no luck.
I've created my EDMX model using an empty database (meaning no data, but full schema) called entity_model. I can create instances of the DBContext-derived entity class, and I can even use the attached database to perform general queries on the attached server (e.g. get a list of installed database). I can even perform SQL queries into the database identified in the connection string (using DbContext.Database.SQLQuery), and the results are what I expect. However, when I try to use the DbSet-derived members of the Entity class, my queries are always being directed at the database that I used to generate the model, regardless of the database identified in the connection string.
Stepping through the code, I can see that the settings in the entity object all look correct (entity.Database.Connection.ConnectionString looks exactly like I would expect, and entity.Database.Connection.Database has the correct database name). However, if I examine the internal query value (DbSet.SQL) for any of the DbSet objects, I get something similar to this:
SELECT Extent1.ID, Extent1.Field1, Extent1.Field2, Extent1.Field3, FROM entity_model.table1 AS Extent1
where entity_model is the database that I used to create the model. I've done enough testing to know that the queries are actually being executed against this incorrect database, rather than the one passed in through the connection string.
As I've said, I've used this same technique on other projects and have not run into this issue. I've even stepped through some old code and I can see that the contents of the DbSet.SQL field contains no reference to any database (neither the one used to create the model, nor the one from the connection string). This is what I would expect to see in my new project as well.
Can someone explain to me how EF decides whether to include the schema name in the DbSet.SQL field? This is making me a little crazy.
I've tried this across a number of the 50 or so databases that I have installed on my machine, so I know the problem is not with any particular database.
I've found a solution (actually 2 solutions) to this problem. The basic problem is that EF is using the schema specified in the edmx EntitySet element by default:
<EntitySet Name="table1" EntityType="Self.table1" Schema="entity_model" store:Type="Tables" />
This behavior can be turned off in 2 ways. Either add the following lines to your applications startup code:
using Devart.Data.MySql.Entity.Configuration;
...
var config = MySqlEntityProviderConfig.Instance;
config.Workarounds.IgnoreSchemaName = true;
Or add the following sections to your config file:
inside the configSections element:
<section name="Devart.Data.MySql.Entity" type="Devart.Data.MySql.Entity.Configuration.MySqlEntityProviderConfigurationSection, Devart.Data.MySql.Entity.EF6, Version=8.21.2066.0, Culture=neutral, PublicKeyToken=09af7300eec23701"/>
And then after the configSections element:
<Devart.Data.MySql.Entity xmlns="http://devart.com/schemas/Devart.Data.MySql.Entity/1.0">
<Workarounds IgnoreSchemaName="true"/>
</Devart.Data.MySql.Entity>
I found a third option online, and that was to simply delete the schema name from each of the EntitySet elements in the edmx file, but I think that could cause some issues if you ever try to update the model from a database.
I'm hopeful that this answer will prove useful to someone else down the road.