c# - 使用 MySQL 5、简单成员身份提供程序、ASP.NET MVC4 和 Entity Framework 5

Question

我正在尝试:使用 ASP.NET MVC 4 对 MySQL 使用基于简单成员身份提供程序的身份验证 使用以下给出的教程配置为使用 MySQL 的默认 Web 应用程序: http://www.nsilverbullet.net/2012/11/07/6-steps-to-get-entity-framework-5-working-with-mysql-5-5/

这里是ASP.NET MVC 4 EF5 with MySQL

我将 MySQL 连接器 6.6.4 与 EF 5.0 和 Visual Studio 2012 一起使用，并使用 ASP.NET MVC 4 互联网应用模板。

我面临的错误是:

Server Error in '/' Application.
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 '[UserId] FROM [UserProfile] WHERE (UPPER([UserName]) = 'Z')' at line 1
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: MySql.Data.MySqlClient.MySqlException: 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 '[UserId] FROM [UserProfile] WHERE (UPPER([UserName]) = 'Z')' at line 1

Source Error:


Line 55:                     }
Line 56: 
Line 57:                     WebSecurity.InitializeDatabaseConnection("DefaultConnection", "UserProfile", "UserId", "UserName", autoCreateTables: false);
Line 58:                 }
Line 59:                 catch (Exception ex)


Source File: e:\Projects\TestApp\TestApp\TestApp\Filters\InitializeSimpleMembershipAttribute.cs    Line: 57

我的代码是:

InitializeSimpleMembershipAttribute.cs

private class SimpleMembershipInitializer
        {
            public SimpleMembershipInitializer()
            {
                //Database.SetInitializer<UsersContext>(null);
                //Database.SetInitializer(new DropCreateMySqlDatabaseIfModelChanges<UsersContext>());
                //Database.SetInitializer(new CreateMySqlDatabaseIfNotExists<UsersContext>());
                Database.SetInitializer(new DropCreateMySqlDatabaseIfModelChanges<UsersContext>());
                try
                {
                    using (var context = new UsersContext())
                    {
                        if (!context.Database.Exists())
                        {
                            // Create the SimpleMembership database without Entity Framework migration schema
                            //((IObjectContextAdapter)context).ObjectContext.CreateDatabase();
                            //context.Database.CreateIfNotExists();
                        }
                    }

                    WebSecurity.InitializeDatabaseConnection("DefaultConnection", "UserProfile", "UserId", "UserName", autoCreateTables: false);
                }
                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);
                }
            }

        }

AccountModel.cs(仅相关代码)

public class UsersContext : DbContext
    {
        static UsersContext()
        {
            //Database.SetInitializer(new DropCreateMySqlDatabaseIfModelChanges<UsersContext>());
        }
        public UsersContext()
            : base("DefaultConnection")
        {
            //Database.SetInitializer(new DropCreateMySqlDatabaseIfModelChanges<UsersContext>());
        }

        public DbSet<UserProfile> UserProfiles { get; set; }
    }
    public class DropCreateMySqlDatabaseAlways<TContext>
      : MySqlDatabaseInitializer<TContext> where TContext : DbContext
    {
        public override void InitializeDatabase(TContext context)
        {
            context.Database.Delete();
            CreateMySqlDatabase(context);
        }
    }

    public class DropCreateMySqlDatabaseIfModelChanges<TContext>
     : MySqlDatabaseInitializer<TContext> where TContext : DbContext
    {
        public override void InitializeDatabase(TContext context)
        {
            bool needsNewDb = false;
            if (context.Database.Exists())
            {
                if (!context.Database.CompatibleWithModel(false))
                {
                    context.Database.Delete();
                    needsNewDb = true;
                }
            }
            else
            {
                needsNewDb = true;
            }
            if (needsNewDb) CreateMySqlDatabase(context);
        }
    }

    public class CreateMySqlDatabaseIfNotExists<TContext>
   : MySqlDatabaseInitializer<TContext> where TContext : DbContext
    {
        public override void InitializeDatabase(TContext context)
        {
            if (context.Database.Exists())
            {
                if (!context.Database.CompatibleWithModel(false))
                {
                    throw new InvalidOperationException(
                        "The model has changed!");
                }
            }
            else
            {
                CreateMySqlDatabase(context);
            }
        }
    }

    public abstract class MySqlDatabaseInitializer<TContext>
     : IDatabaseInitializer<TContext>
         where TContext : DbContext
    {
        public abstract void InitializeDatabase(TContext context);

        protected void CreateMySqlDatabase(TContext context)
        {
            try
            {
                // Create as much of the database as we can
                context.Database.Create();

                // No exception? Don't need a workaround
                return;
            }
            catch (MySqlException ex)
            {
                // Ignore the parse exception
                if (ex.Number != 1064)
                {
                    throw;
                }
            }

            // Manually create the metadata table
            using (var connection = ((MySqlConnection)context
                .Database.Connection).Clone())
            using (var command = connection.CreateCommand())
            {
                command.CommandText =
    @"
CREATE TABLE __MigrationHistory (
    MigrationId mediumtext NOT NULL,
    Model mediumblob NOT NULL,
    ProductVersion mediumtext NOT NULL);

ALTER TABLE __MigrationHistory
ADD PRIMARY KEY (MigrationId(255));

INSERT INTO __MigrationHistory (
    MigrationId,
    Model,
    ProductVersion)
VALUES (
    'InitialCreate',
    @Model,
    @ProductVersion);
";
                command.Parameters.AddWithValue(
                    "@Model",
                    GetModel(context));
                command.Parameters.AddWithValue(
                    "@ProductVersion",
                    GetProductVersion());

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        private byte[] GetModel(TContext context)
        {
            using (var memoryStream = new MemoryStream())
            {
                using (var gzipStream = new GZipStream(
                    memoryStream,
                    CompressionMode.Compress))
                using (var xmlWriter = XmlWriter.Create(
                    gzipStream,
                    new XmlWriterSettings { Indent = true }))
                {
                    EdmxWriter.WriteEdmx(context, xmlWriter);
                }

                return memoryStream.ToArray();
            }
        }

        private string GetProductVersion()
        {
            return typeof(DbContext).Assembly
                .GetCustomAttributes(false)
                .OfType<AssemblyInformationalVersionAttribute>()
                .Single()
                .InformationalVersion;
        }
    }

    [Table("UserProfile")]
    public class UserProfile
    {
        [Key]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int UserId { get; set; }
        public string UserName { get; set; }
    }

    public class RegisterExternalLoginModel
    {
        [Required]
        [Display(Name = "User name")]
        public string UserName { get; set; }

        public string ExternalLoginData { get; set; }
    }

    public class LocalPasswordModel
    {
        [Required]
        [DataType(DataType.Password)]
        [Display(Name = "Current password")]
        public string OldPassword { get; set; }

        [Required]
        [StringLength(100, ErrorMessage = "The {0} must be at least {2} characters long.", MinimumLength = 6)]
        [DataType(DataType.Password)]
        [Display(Name = "New password")]
        public string NewPassword { get; set; }

        [DataType(DataType.Password)]
        [Display(Name = "Confirm new password")]
        [Compare("NewPassword", ErrorMessage = "The new password and confirmation password do not match.")]
        public string ConfirmPassword { get; set; }
    }

请帮助我找出我做错了什么。谢谢

Answer 1

我在这里提供了一个类似问题的答案: Using Simple Membership Provider with mysql它基于我在此处找到的 Fabio Costa 的一篇文章:http://fabiocosta.ca/2012/11/24/use-simplemembership-and-oauth-with-any-database-and-datatype/ .您基本上获得了源代码，根据您的需要更新它(让它编写 mysql 查询而不是 sql)并使用它。