Peter Bromberg's .NET Blog All Things Programming

public static T ExecuteSql<T>(DbContext ctx, string sql, List<DbParameter> parms)

{

DbCommand cmd = ctx.Database.Connection.CreateCommand();

cmd.CommandText = sql; cmd.CommandType = CommandType.Text;

foreach (DbParameter p in parms) cmd.Parameters.Add(p);

try {

ctx.Database.Connection.Open();

DbDataReader reader = cmd.ExecuteReader();

ObjectResult<T> jInfo = ((IObjectContextAdapter) ctx) .ObjectContext .Translate<T>(reader);

return jInfo.FirstOrDefault();

}

finally { ctx.Database.Connection.Close();

}

}

 I have an MVC application for which I have implemented a Custom ExtendedMembership - derived Membership class that is hosted via a WCF Service.

I have a custom MembershipProviderForwarder class that plugs right into my web.config as the Membership provider, but what it actually does is forward all Membership calls to my WCF Service, which uses the real custom Membershp provider, and returns back all the results to the app from over the wire.

In this manner I can have any number of MVC apps all using the same provider via my Webservice.

Since I have custom Membership fields, but only a couple, I didn't want to get into writing a lot of Profile code. Instead, I have a Custom User object that has these extra fields which can be returned from the UserProfile table in SQL Server.

The issue is that I only want to make Webservice calls when the user first logs in, and I want to store my custom fields in the Forms Ticket (it has a UserData property for just this purpose). In this manner I can use the PostAuthenticateRequest event to pull my custom data our of the forms cookie and attach my custom IPrincipal to the HttpContext.Current.User property for each subsequent request. Here is how I did that:

 

1. Create the interface

 

interface ICustomPrincipal : IPrincipal

{

    int UserId { get; set; }

    string FirstName { get; set; }

    string LastName { get; set; }

}

 

2. CustomPrincipal

 

public class CustomPrincipal : ICustomPrincipal

{

    public IIdentity Identity { get; private set; }

    public bool IsInRole(string role) { return false; }

 

    public CustomPrincipal(string email)

    {

        this.Identity = new GenericIdentity(email);

    }

 

    public int UserId { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

}

 

3. CustomPrincipalSerializeModel - for serializing custom information into userdata field in FormsAuthenticationTicket object.

 

public class CustomPrincipalSerializeModel

{

    public int UserId { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

}

 

4. LogIn method - setting up a cookie with custom information

 

if (Membership.ValidateUser(viewModel.Email, viewModel.Password))

{

    var user = userRepository.Users.Where(u => u.Email == viewModel.Email).First();

 

    CustomPrincipalSerializeModel serializeModel = new CustomPrincipalSerializeModel();

    serializeModel.UserId = user.Id;

    serializeModel.FirstName = user.FirstName;

    serializeModel.LastName = user.LastName;

 

    JavaScriptSerializer serializer = new JavaScriptSerializer();

 

    string userData = serializer.Serialize(serializeModel);

 

    FormsAuthenticationTicket authTicket = new FormsAuthenticationTicket(

             1,

             viewModel.Email,

             DateTime.Now,

             DateTime.Now.AddMinutes(15),

             false,

             userData);

 

    string encTicket = FormsAuthentication.Encrypt(authTicket);

    HttpCookie faCookie = new HttpCookie(FormsAuthentication.FormsCookieName, encTicket);

    Response.Cookies.Add(faCookie);

 

    return RedirectToAction("Index", "Home");

}

 

5. Global.asax.cs - Reading cookie and replacing HttpContext.User object, this is done by overriding PostAuthenticateRequest

 

protected void Application_PostAuthenticateRequest(Object sender, EventArgs e)

{

    HttpCookie authCookie = Request.Cookies[FormsAuthentication.FormsCookieName];

    if (authCookie != null)

    {

        FormsAuthenticationTicket authTicket = FormsAuthentication.Decrypt(authCookie.Value);

        JavaScriptSerializer serializer = new JavaScriptSerializer();

        CustomPrincipalSerializeModel serializeModel = serializer.Deserialize<CustomPrincipalSerializeModel>(authTicket.UserData);

        CustomPrincipal newUser = new CustomPrincipal(authTicket.Name);

        newUser.UserId = serializeModel.UserId;

        newUser.FirstName = serializeModel.FirstName;

        newUser.LastName = serializeModel.LastName;

        HttpContext.Current.User = newUser;

    }

}

 

 

6. Access in Razor views

 

@((User as CustomPrincipal).Id)

@((User as CustomPrincipal).FirstName)

@((User as CustomPrincipal).LastName)

 

and in code:

 

    (User as CustomPrincipal).Id

    (User as CustomPrincipal).FirstName

    (User as CustomPrincipal).LastName

When you do a function import to a stored proc that has dynamically - generated SQL in it, Entity Framework will report that no columns were mapped.

 

Here is an easy, non-destrctive way to make sure EF gets the column metadata to complete your function import.

 

What EF does when it attempts to map your sproc is it executes the sproc with all parameters having null values.

Of course if the resultset is generated via dynamically assembled SQL or comes from a Table Variable, EF cannot "see" the results.

 

So what we do is the following. 

 

Say your sproc returns six columns. You would return each column as a null, but casting it to it's actual datatype:

 

if ( @deptId is null and @chkExemptOnly is null  and @chkActiveOnly is null and @msYearAC is null and @msdepttypeid is null and @Firstname is null and @lastName is null ) 
begin
select
cast(null as int) as empid,
cast(null as varchar(11)) as emplID,
cast(null as int) as Active,
cast(null as varchar(25)) as lastName,
cast(null as varchar(25)) as firstName,
cast(null as varchar(25) ) as middleName
end

You can put this kind of code right at the beginning of the sproc just after the AS statement, and Entity Framework will correctly map the resultset for you. You do not need to worry about leaving the above SQL block in the sproc, since it only executes when all the input parameters are NULL.