标签化查询
1. 目标
1.1. 框架目标
- 为数据查询提供高效的标签化查询
- 一切皆可标签化查询
1.2. 文档目标
- 让开发者在各自的项目中使用标签化查询
- 让开发者快速完成查询的切换
2. 开发环境
2.1. .Net Framework
2.2. Entity Framework
2.3. SQL Server 2005+
3. 数据表准备
3.1. 创建一个表用来查询目标对象的标签
所有标签将以32个为一组存放在此表的N个字段中（默认支持4个字段，可以适当修改代码扩展支持个数），比如：
IF OBJECT_ID('MemberTags') IS NOT NULL
BEGIN
DROP TABLE MemberTags;
END;
CREATE TABLE MemberTags
(
Id INT NOT NULL
IDENTITY
PRIMARY KEY ,
MemberId INT NOT NULL ,
Tags1 BIGINT NULL ,
Tags2 BIGINT NULL ,
Tags3 BIGINT NULL ,
Tags4 BIGINT NULL
);
GO
3.2. 创建一个查询视图将目标对象与标签表建立关联
比如下面是把WechatUser与上面的表关联：
IF ( OBJECT_ID('ViewTagedMembers') IS NOT NULL )
BEGIN
DROP VIEW ViewTagedMembers;
END;
GO
CREATE VIEW ViewTagedMembers
AS
SELECT WechatUsers.ID ,
OpenID ,
NickName ,
Country ,
Province ,
City ,
HeaderImageURL ,
Gender ,
Remark ,
SubscribeTime ,
MemberId ,
Tags1 ,
Tags2 ,
Tags3 ,
Tags4
FROM WechatUsers
INNER JOIN MemberTags ON MemberTags.MemberId = WechatUsers.ID;
GO
3.3. 创建一个视图计算目标对象引用到的所有可能标签
需要包含：ID,Name,BitMapValue,FieldNumber，具体计算参考下面的脚本：
IF ( OBJECT_ID('ViewTagedMemberTags') IS NOT NULL )
BEGIN
DROP VIEW ViewTagedMemberTags;
END;
GO
CREATE VIEW ViewTagedMemberTags
AS
SELECT ID = CONVERT(INT, ID) ,
Name = Name ,
BitMapValue = POWER(2,CONVERT(INT, ( ROW_NUMBER() OVER ( ORDER BY ID ) - 1 ) % 32) + 1) ,
FieldNumber = CONVERT(INT, ( ROW_NUMBER() OVER ( ORDER BY ID ) - 1 ) / 32 + 1) ,
IsActive
FROM dbo.Groups
WHERE IsActive = 1;
GO
3.4. 刷新标签数据
就是把所有更新目标对象标签的地方，增加一段逻辑处理一下标签值，存储到第一步创建的表中。参考代码：
INSERT INTO dbo.MemberTags
( MemberId
)
SELECT MemberId = ID
FROM dbo.WechatUsers;
UPDATE m
SET Tags1 = t1.Tags ,
Tags2 = t2.Tags ,
Tags3 = t3.Tags ,
Tags4 = t4.Tags
FROM MemberTags m
CROSS APPLY ( SELECT ISNULL(SUM(t.BitMapValue), 0) AS Tags
FROM MemberGroups g
INNER JOIN ViewTagedMemberTags t ON g.GroupID = t.ID
WHERE g.MemberID = m.MemberId
AND t.FieldNumber = 1
) AS t1
CROSS APPLY ( SELECT ISNULL(SUM(t.BitMapValue), 0) AS Tags
FROM MemberGroups g
INNER JOIN ViewTagedMemberTags t ON g.GroupID = t.ID
WHERE g.MemberID = m.MemberId
AND t.FieldNumber = 2
) AS t2
CROSS APPLY ( SELECT ISNULL(SUM(t.BitMapValue), 0) AS Tags
FROM MemberGroups g
INNER JOIN ViewTagedMemberTags t ON g.GroupID = t.ID
WHERE g.MemberID = m.MemberId
AND t.FieldNumber = 3
) AS t3
CROSS APPLY ( SELECT ISNULL(SUM(t.BitMapValue), 0) AS Tags
FROM MemberGroups g
INNER JOIN ViewTagedMemberTags t ON g.GroupID = t.ID
WHERE g.MemberID = m.MemberId
AND t.FieldNumber = 4
) AS t4;
GO
4. 如何标签查询
4.1. 使用方法
首先需要注入ITagQueryService，然后通过：
var queryService = ContainerManager.Instance.GetService<ITagQueryService
var count = queryService.TagQuery().InTags("Testd1","Test2").OrInTags("Test3", "Test4").NotInTags("Test5").Count();
这个和我们使用Lambda查询一样，目前支持的查询包含：InTags OrInTags和NotInTags
4.2. 实现代码
public static IQueryable<T> GetEntitiesWithAnyTags<T>(this IQueryable<T> query, params string[] tags) where T : TagedEntity
{
List<BitMapOperation> list = new List<BitMapOperation>();
foreach (var tag in tags)
{
var tagInfo = GetTagInformation<T>(tag);
if (tagInfo == null)
{
continue;
}
var opt = list.FirstOrDefault(m => m.FieldNumber == tagInfo.FieldNumber);
if (opt == null)
{
list.Add(new BitMapOperation()
{
BitMapValue = tagInfo.BitMapValue,
Result = 0,
FieldNumber = tagInfo.FieldNumber
});
}
else
{
opt.BitMapValue |= tagInfo.BitMapValue;
}
}
var param = Expression.Parameter(typeof(T));
BinaryExpression rule = null;
foreach (var item in list)
{
var field = Expression.PropertyOrField(param, "Tags" + item.FieldNumber.ToString());
var bit = Expression.Constant(item.BitMapValue);
var result = Expression.Constant(item.Result);
var shift = Expression.MakeBinary(ExpressionType.And, field, bit);
var where = Expression.MakeBinary(ExpressionType.GreaterThan, shift, result);
if (rule == null)
{
rule = where;
}
else
{
rule = Expression.MakeBinary(ExpressionType.Or, (Expression)rule, (Expression)where);
}
}
query = query.Where(Expression.Lambda<Func<T, bool>>(rule, param));
return query;
}