创建Entity Framework数据库时出现Mysql语法错误

I'm playing around with asp.net for the first time. I want to use it with a MySQL database because this is what is offered by my hosting service and I don't want to upgrade/change services. I'm using visual web developer 2010 express. I created an MVC 4 project from the default template. The template created the ASP.NET Simple Membership objects which is what I'm trying to get working. The project builds and runs correctly when using the default database connection string. When I change the web.config file to point to MySQL I get the following error when I attempt to navigate to any of the pages in the account folder.

我第一次玩asp.net。我想将它与MySQL数据库一起使用,因为这是我的托管服务提供的,我不想升级/更改服务。我正在使用Visual Web developer 2010 express。我从默认模板创建了一个MVC 4项目。该模板创建了ASP.NET简单成员资格对象,这是我正在尝试的工作。使用默认数据库连接字符串时,项目可以正确构建和运行。当我将web.config文件更改为指向MySQL时,当我尝试导航到帐户文件夹中的任何页面时,我收到以下错误。

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTITY, RoleName nvarc' at line 2

您的SQL语法有错误;检查与MySQL服务器版本对应的手册,以便在第2行的'IDENTITY,RoleName nvarc'附近使用正确的语法

When I open the MySQL work bench and connect to the local server I notice that the database has been created. If I drop the DB and run the app again it gets recreated. I'm note sure if it was created correctly or if the entire database was created but there is something there.

当我打开MySQL工作台并连接到本地服务器时,我注意到数据库已经创建。如果我删除数据库并再次运行应用程序,则会重新创建。我要注意,如果它是正确创建的,或者是否创建了整个数据库但是那里有一些东西。

Obviously there is an issue with the SQL syntax that is created by the Entity Framework. Do I need to add something to the web.config file to tell it what syntax it should use when creating the queries?

显然,实体框架创建的SQL语法存在问题。我是否需要在web.config文件中添加一些东西来告诉它在创建查询时应该使用哪种语法?

I've been searching for an answer to this for the past two days. any help pointing in the right direction would be appreciated.

过去两天我一直在寻找答案。任何指向正确方向的帮助将不胜感激。

I'm using mysql server version 5.5.27. and connector 6.5.4.0

我正在使用mysql服务器版本5.5.27。和连接器6.5.4.0

here is the mysql part of my web.config file:

这是我的web.config文件的mysql部分:

  <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.5.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
    </DbProviderFactories>
  </system.data>
  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=aspnet-MyWebPage-20120817115958;Integrated Security=SSPI" providerName="System.Data.SqlClient" />
    <add name="myDatabaseConnection" connectionString="server=localhost;Port=3306;uid=root;pwd=****;database=myDatabase;" providerName="MySql.Data.MySqlClient" />        
  </connectionStrings>

Edit adding code

编辑添加代码

 [AttributeUsage(AttributeTargets.Class | AttributeTargets.Method, AllowMultiple = false, Inherited = true)]
public sealed class InitializeSimpleMembershipAttribute : ActionFilterAttribute
{
    private static SimpleMembershipInitializer _initializer;
    private static object _initializerLock = new object();
    private static bool _isInitialized;

    public override void OnActionExecuting(ActionExecutingContext filterContext)
    {
        // Ensure ASP.NET Simple Membership is initialized only once per app start
        LazyInitializer.EnsureInitialized(ref _initializer, ref _isInitialized, ref _initializerLock);
    }

    private class SimpleMembershipInitializer
    {
        public SimpleMembershipInitializer()
        {
            Database.SetInitializer<UsersContext>(null);

            try
            {
                using (var context = new UsersContext())
                {
                    if (!context.Database.Exists())
                    {
                        // Create the SimpleMembership database without Entity Framework migration schema
                        ((IObjectContextAdapter)context).ObjectContext.CreateDatabase();
                    }
                }

                WebSecurity.InitializeDatabaseConnection("LocalMySqlServer", "UserProfile", "UserId", "UserName", autoCreateTables: true);
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException("The ASP.NET Simple Membership database could not be initialized. For more information, please see http://go.microsoft.com/fwlink/?LinkId=256588", ex);
            }
        }
    }
}



    public class UsersContext : DbContext
{
    public UsersContext()
        : base("LocalMySqlServer")
    {
    }

    public DbSet<UserProfile> UserProfiles { get; set; }
}

2 个解决方案

#1

Try to modify the source of the SMP and remove the syntax specific to ms sql server.

尝试修改SMP的源并删除ms sql server特有的语法。

#2

The role provider is still defaulting to the standard ASP one which is expecting a SQLServer DB on the end of the connection, "Identity" is SQLServerese for "autoinc".

角色提供程序仍然默认为标准的ASP,它在连接的末尾期望SQLServer DB,“Identity”是SQLServerese的“autoinc”。

You can set the default providers in the web.config like this:-

您可以在web.config中设置默认提供程序,如下所示: -

<configuration>
  <system.web>
    <profile defaultProvider="MySQLProfileProvider"></profile>
    <roleManager defaultProvider="MySQLRoleProvider"></roleManager>
  </system.web>
</configuration>

#1