How to execute a query from Asp.Net profile properties using LINQ

Question

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

I need to get a list of all users whose location belongs to "London" and "Gender = Male"

how to search an asp.net profile using LINQ

Answer 1

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

• A function that parses and returns a serialized property / values
• Optionally, a view that calls a function for each line of the user. This is not necessary because some ORMs support querying with user-defined functions. I recommend putting the view in place anyway.

Here's the CLR function I wrote that parses the values ​​of the PropertyNames and PropertyValuesString columns 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]+):[AZ]:(\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; } }; 

Expand this function, and then create a view for your user profile data. Here is 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 request a view using the SQL or ORM of your choice. I wrote this in Linqpad:

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

Answer 2

No, you cannot do this through the default ASP.NET profile provider (which stores the profile data in one row field in the database), although you can still do this after splitting the profile data in another database table (which is a common approach, as well as saving your profile data in another table and posting it using the default user table using the User GUID), then you can use LINQ to query the data for your user profiles.

Answer 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)); 

Perhaps I should mention that we created an edmx file for our membership database. However, I would think about transferring all this interesting information to your own tables in your application database when you have a chance.