How to Query from Asp.Net Profile Properties using

问题:

I have Asp.net profile property Profile.Location, Gender etc

i need to get list of all users whose Location belongs to "London" and Gender = male

how do i perform a search on Asp.net Profile using LINQ

回答1:

Actually, you can do it. But you need to put a couple of things in place first:

1. A function that parses and returns the serialized property/values
2. Optionally, a view that calls the function for each user row. This is optional because some ORM's support composing queries with user defined functions. I recommend putting the view in place anyway.

Here's a CLR function that I wrote that parses the PropertyNames and PropertyValuesString column values from the aspnet_Profile table. It returns a table with a Property column and a Value column.

using System.Collections;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.Linq;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    private static readonly Regex ProfileRegex = new Regex(@"([a-zA-Z]+):[A-Z]:(\d+):(\d+)");

    [SqlFunction(FillRowMethodName = "FillProfileRow",TableDefinition="Property nvarchar(250), Value nvarchar(2000)")]
    public static IEnumerable ParseProfileString(SqlString names, SqlString values)
    {
        var dict = ProfileRegex
            .Matches(names.Value)
            .Cast<Match>()
            .ToDictionary(
                x => x.Groups[1].Value,
                x => values.Value.Substring(int.Parse(x.Groups[2].Value), int.Parse(x.Groups[3].Value)));

        return dict;
    }

    public static void FillProfileRow(object obj, out string Property, out string Value)
    {
        var x = (KeyValuePair<string, string>) obj;
        Property = x.Key;
        Value = x.Value;
    }
};

Deploy that function and then create a view for your user's profile data. Here's an example:

CREATE VIEW UsersView
AS

SELECT *
FROM (
    SELECT u.UserId
        ,u.Username
        ,m.Email
        ,f.Property
        ,f.Value
    FROM aspnet_Profile p
    INNER JOIN aspnet_Users u ON p.UserId = u.UserId
    INNER JOIN aspnet_Membership m ON m.UserId = u.Userid
    INNER JOIN aspnet_Applications a ON a.ApplicationId = m.ApplicationId
    CROSS APPLY ParseProfileString(p.PropertyNames, p.PropertyValuesString) f
    WHERE a.ApplicationName = 'MyApplication'
    ) src
pivot(min(value) FOR property IN (
            -- list your profile property names here
            FirstName, LastName, BirthDate
            )) pvt

Voila, you can query the view with SQL or the ORM of your choice. I wrote this one in Linqpad:

from u in UsersView
where u.LastName.StartsWith("ove") 
select u

回答2:

No, you can't do this through the default ASP.NET profile provider (which saves profile data in a single string field in the database) though you still can do this after separating profile data in another database table (which is a common approach as well to store your profile data in another table and wire it with the default users table via the User GUID key), then you can use LINQ to query your users profiles data.

回答3:

consider using something like this:

   matches = 
       matches.Union(
          memberDB.aspnet_Profile
          .Where("it.PropertyValuesString Like @first",
           new ObjectParameter("first", "%<FirstName>%" + firstName + "%</FirstName>%")
           ).Select(p => p.UserId));

I should probably mention that we've created an edmx file for our membership database. That said, I'd consider moving all of that interesting information into it's own tables in your application database when you get a chance.