What I am suggesting is to always use user Id for everything internally, including passing it to the SiteUser constructor. This way when user enters an e-mail for the username, we simply look up the user Id for that e-mail in Login.LogginIn event handler, re-assign UserName to the user Id and the proceed to pass it to SiteUser constructor.
SiteUser can always expect a user Id to be passed in to it, nothing else.
No, I don't like that idea. For LDAP users the cookie is going to have the loginname, and for the ones using email it would have the email address. you're suggesting sniffing the value of Context.User.Identity.Name to see if it looks like an email address then lookup ther username then lookup the user. Lots of extra weird logic and also additional hits to the database every time we lookup a user and big changes to code all to support this edge case of wanting to use email/database authentication in addition to ldap but also don't want to put the email address in the loginname field because we are using it to relate to some other custom data.
For this scenario, given the extra requirement that you don't want to put the email address in the LoginName field, I think the best solution is just modify the procedure like this:
ALTER PROCEDURE [dbo].[mp_Users_SelectByLoginName]
@SiteID int,
@LoginName nvarchar(50)
AS
SELECT *
FROM
mp_Users
WHERE
SiteID = @SiteID
AND (LoginName = @LoginName
OR Email = @LoginName)
GO
Simple and effective and not really likely that this procedure will ever be modified by future upgrades. Not saying it could never happen but I don't expect it to, and it hasn't changed in many years so it hasn't been touched by an upgrade script in many years.
This would immediately meet your goal and seems like a reasonable solution to me for this particular scenario.