0

Having audited out SQL queries, we noticed that some of our most expensive queries are made by the .net authentication framework, where the LOWER function is used (see example below)

Our database is case-insensitive, so this provides a needless performance hit.

I am looking for the simplest way to alter these queries, I assume I will need to use some sort of custom membership provider.

SELECT TOP (1) 

1 AS [C1], 

[Extent1].[UserName] AS [UserName], 

[Extent1].[UserId] AS [UserId], 

[Extent2].[Email] AS [Email], 

[Extent2].[PasswordQuestion] AS [PasswordQuestion], 

[Extent2].[Comment] AS [Comment], 

[Extent2].[IsApproved] AS [IsApproved], 

[Extent2].[IsLockedOut] AS [IsLockedOut], 

[Extent2].[CreateDate] AS [CreateDate], 

[Extent2].[LastLoginDate] AS [LastLoginDate], 

[Extent1].[LastActivityDate] AS [LastActivityDate], 

[Extent2].[LastPasswordChangedDate] AS [LastPasswordChangedDate], 

[Extent2].[LastLockoutDate] AS [LastLockoutDate]

FROM   [dbo].[Users] AS [Extent1]

INNER JOIN [dbo].[Memberships] AS [Extent2] ON [Extent2].[UserId] = [Extent1].[UserId]

INNER JOIN [dbo].[Applications] AS [Extent3] ON [Extent3].[ApplicationId] = [Extent2].[ApplicationId]

WHERE ((LOWER([Extent3].[ApplicationName])) = @appName) AND ((LOWER([Extent1].[UserName])) 

= @userName)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
CodeToad
  • 4,656
  • 6
  • 41
  • 53

1 Answers1

1

Not sure if simple enough, but for ASP.NET MVC 4.0, you would need to create your own classes that implement the IIdentify and IPrincipal interfaces. Afterwards, you would reference them in your web.config. In those classes you can use whatever means you chose to retrieve data (so, custom queries, entity framework, etc).

See this post for a more thorough tutorial: https://codeutil.wordpress.com/2013/05/14/forms-authentication-in-asp-net-mvc-4/#mvc-implement-custom-IPrincipal-and-IIdentity

sTodorov
  • 5,435
  • 5
  • 35
  • 55