如何从使用LINQ Asp.Net配置文件属性查询 [英] How to Query from Asp.Net Profile Properties using LINQ
问题描述
我有Asp.net配置文件属性Profile.Location,性别等
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
如何执行使用LINQ在Asp.net档案搜索
how do i perform a search on Asp.net Profile using LINQ
推荐答案
其实,你可以做到这一点。但是,你需要把一些事情的地方第一:
Actually, you can do it. But you need to put a couple of things in place first:
- 的解析,并返回序列化的属性/值的函数
- (可选),调用每个用户行功能的看法。因为有些ORM的支持与撰写用户自定义函数的查询这是可选的。我建议把到位的观点呢。
下面是我写的分析从aspnet_Profile表PropertyNames和PropertyValuesString列值CLR函数。它返回一个属性列和值列的表。
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
瞧,您可以查询与SQL或您所选择的ORM的看法。我写了这一个在Linqpad:
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
这篇关于如何从使用LINQ Asp.Net配置文件属性查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!