Bug #75566 "You have an error in your SQL syntax" after upgrading from EF 6.1.1 to 6.1.2
Submitted: 20 Jan 2015 20:20 Modified: 22 Jan 2015 6:55
Reporter: Mike McGuinness Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:Connector 6.9.5 / MySQL 5.1.73-community, 5.6.21 OS:Microsoft Windows (2008 R2)
Assigned to: Assigned Account CPU Architecture:Any
Tags: .net Connector, entityframework

[20 Jan 2015 20:20] Mike McGuinness
Description:
After upgrading a Visual Studio 2013 MVC web project from EntityFramework 6.1.1 to 6.1.2, I get the following error from 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 'FROM ((SELECT
CASE WHEN (`Extent2`.`RoleId` IS  NULL) THEN (NULL)  ELSE (1) END' at line 31

The error is caught by the CookieAuthenticationProvider OnException callback in Startup.Auth.cs.  I believe the query is triggered somewhere in the Microsoft Identity code.

app.UseCookieAuthentication(new CookieAuthenticationOptions
            {
                AuthenticationType = DefaultAuthenticationTypes.ApplicationCookie,
                LoginPath = new PathString("/Account/Login"),
                Provider = new CookieAuthenticationProvider
                {
                    // Enables the application to validate the security stamp when the user logs in.
                    // This is a security feature which is used when you change a password or add an external login to your account.  
                    OnValidateIdentity = SecurityStampValidator.OnValidateIdentity<MakeUserManager, MakeUser, int>(
                        validateInterval: TimeSpan.FromMinutes(30),
                        regenerateIdentityCallback: (manager, user) => user.GenerateUserIdentityAsync(manager),
                        getUserIdCallback: (id) => (Int32.Parse(id.GetUserId()))),
                    OnException = context => { ErrorLogging.LogError("Make.Designer.Web.Startup.ConfigureAuth()", "An error occured while initializing the CookieAuthenticationProvider.", context.Exception); }
                }
            });  

How to repeat:
Create an MVC web project in Visual Studio 2013, using individual user accounts.  Add the EntityFramework 6.1.2 Nuget Package.  Set up the cookie authentication in Startup.Auth.cs as follows:

app.UseCookieAuthentication(new CookieAuthenticationOptions
            {
                AuthenticationType = DefaultAuthenticationTypes.ApplicationCookie,
                LoginPath = new PathString("/Account/Login"),
                Provider = new CookieAuthenticationProvider
                {
                    // Enables the application to validate the security stamp when the user logs in.
                    // This is a security feature which is used when you change a password or add an external login to your account.  
                    OnValidateIdentity = SecurityStampValidator.OnValidateIdentity<MakeUserManager, MakeUser, int>(
                        validateInterval: TimeSpan.FromMinutes(30),
                        regenerateIdentityCallback: (manager, user) => user.GenerateUserIdentityAsync(manager),
                        getUserIdCallback: (id) => (Int32.Parse(id.GetUserId()))),
                    OnException = context => { Exception ex = context.Exception); }
                }
            });
[22 Jan 2015 6:55] Chiranjeevi Battula
Hello Mike McGuinness,

Thank you for the bug report.
Verified this behavior on Visual Studio 2013 MVC web project, EntityFramework 6.1.2 with MySQL Connector/Net 6.9.5.

Thanks,
Chiranjeevi.