Default membership provider - Query AspnetUsers using AspnetProfile criteria.

Hi

I'm using MVC3 with the default membership provider. In my Lightspeed model I don't have a relationship between AspnetUser and AspnetProfile which I believe is the recommended approach in order for the model to validate since Lightspeed doesn't support the relationship method used by the membership provider (or something like that).

My question is, how do I query for a group of users based on certain profile criteria using Lightspeed? For example, get all users whose profile has a DOB value less than 1990-01-01.

If the relationship existed in the Lightspeed model it would be something like:

var users = UnitOfWork.AspnetUsers.Where(u => u.AspnetProfile.DOB < DateTime.Parse("1990-01-01"));

What's the easiest way to achieve this?

Thanks.

You will want to use a join to associate the two entities given there is no direct association, e.g. from memory they are joined on a common UserId field so you could use:

var users = from u in UnitOfWork.AspnetUsers
join p in UnitOfWork.AspnetProfile on u.UserId = p.UserId
where p.DOB < DateTime.Parse("1990-01-01")
select u

Jeremy

Thanks Jeremy. That's what I needed except that I just realised that UnitOfWork.AspnetProfile doesn't have a "DOB" property since the profile names/values are all stored in a single text field - gah!

Suggestions for making this work would be greatly appreciated!

Ah right, so they are encoded into those PropertyNames and PropertyValues fields. You could either use a UDF on the server to decode these and provide them back as a set of columns so you could join them against the AspnetProfile row and then query against that, or you could handle this as a client side query component (decode these client side and make a DOB property available on your AspnetProfile entry with the associated value extracted from the PropertyValues field) if you are not dealing with many actual rows and its acceptable for you to fetch more data back to the client.

Jeremy

Thanks Jeremy. Would you be able to give me an example of what the client side query component would look like?

In pseudo code you will want to do something like this:

public string DOB
{
  get
  {
    return DecodePropertyValue("DOB");
  }
}

public string DecodePropertyValue(string name)
{
  // locate name as encoded in PropertyNames, if not present return null | default value
  // use index (pos) and length values to read substring from PropertyValues
 return PropertyValues.Substring(pos, length);
}

There is some detail and an example implementation of some Encode/Decode functions at http://msdn.microsoft.com/en-us/library/aa479025.aspx#aspnetprovmodvbprt5topic3 which you can use as a reference to decode the data.

Jeremy