Linq自连接查询
本文关键字:查询 自连接 Linq | 更新日期: 2023-09-27 18:10:06
我需要一些帮助与Linq自连接。
我有以下类:
public class Owner
{
public string OwnerId {get;set;}
public string Name {get;set;}
public string Area {get;set;}
public string City {get;set;}
public string Sex {get;set;}
public List<Dog> dog {get;set;}
}
和表……
ID OwnerId OwnerName TypeId TypeName TypeValue TypeCodeId
1 1 John 1 Area United States 440
2 1 John 2 City Los-Angeles 221
3 1 John 3 Sex Female 122
4 2 Mary 1 Area Mexico 321
4 2 Mary 2 City Cancun 345
............................................................
我需要以最快的方式将表1的结果解析为所有者列表。注意:类型可以为空,但我仍然需要显示所有者(所以,我假设左连接应该工作)。
我是这样做的。(owner是包含table1结果的webservice类)
public IEnumerable<Owner> GetOwners() {
return (from owner in owners
join area in owners into owner_area
from oa in owner_area.DefaultIfEmpty()
join City in owners into owner_city
from oc in owner_city.DefaultIfEmpty()
join sex in owners into owner_sex
from os in owner_sex.DefaultIfEmpty()
where oa.TypeId == 1 && oc.TypeId ==2 && os.TypeId ==3
select new Owner() {OwnerId = owner.OwnerId,
Name = owner.Name,
Area = oa.TypeValue,
City = oc.TypeValue,
Sex = os.TypeValue}).Distinct();
}
这个查询有几个问题:
- 它返回多个结果,distinct不按预期工作
- 我试过使用GroupBy,但它说不能隐式地将Owner转换为
IEnumerable <int, Owner>
如何使用self join获得不同的记录并提高性能?由于
更新:谢谢你们的回答,现在正在测试,但我发现我忘了提供另外一个东西。我在表布局中添加了一个名为TypeCodeId的新列(见上文)用户可以根据他们的选择筛选值。我有typeecodeid + TypeValue字典用于区域,城市和性别。所有这些参数都是可选的(如果用户没有选择任何,我只显示所有记录)。
因此,假设用户选择了过滤器Area: Unites States
和过滤器City: Los Angeles
他们我的查询看起来像这样:
Select Projects where Area equals United States(440) and City equals Los Angeles(221)
如果只选择了Area:Mexico,那么我的查询将是这样的:
Select Projects where Area equals Mexico(321)
我不确定如何使用您在示例中提供的可选where子句。
由于表没有规范化,我们需要从对象/表中获取地区用户。这可以通过以下命令完成:
owners.Select(o => new { o.OwnerId, o.OwnerName }).Distinct()
然后我们需要用两个匹配的值连接"类型",一个用于ownerId,另一个用于特定类型。
var ownerQuery =
from o in owners.Select(o => new { o.OwnerId, o.OwnerName }).Distinct()
join area in owners on new { o.OwnerId, TypeId = 1 } equals new { area.OwnerId, area.TypeId } into areas
from area in areas.DefaultIfEmpty()
join city in owners on new { o.OwnerId, TypeId = 2 } equals new { city.OwnerId, city.TypeId } into cities
from city in cities.DefaultIfEmpty()
join sex in owners on new { o.OwnerId, TypeId = 3 } equals new { sex.OwnerId, sex.TypeId } into sexes
from sex in sexes.DefaultIfEmpty()
select new
{
owner = o,
Area = (area != null) ? area.TypeValue : null,
City = (city != null) ? city.TypeValue : null,
Sex = (sex != null) ? sex.TypeValue : null,
};
您可能需要更改上面示例中的投影
为了获得最佳性能,我认为这样做是正确的。
public IEnumerable<Owner> GetOwners(IEnumerable<Tuple<int, int>> filter)
{
var q = (from o in owners
join f in filter on
new {o.TypeId, o.TypeCodeId} equals
new {TypeId = f.Item1, TypeCodeId = f.Item2}
select o).ToList();
var dic = q.ToDictionary (o => new {o.OwnerId, o.TypeId}, o => o.TypeValue);
foreach (var o in q.Select(o => new { o.OwnerId, o.OwnerName }).Distinct())
{
var owner = new Owner()
{
OwnerId = o.OwnerId,
Name = o.OwnerName
};
string lookup;
if(dic.TryGetValue(new {o.OwnerId, TypeId = 1}, out lookup))
owner.Area = lookup;
if(dic.TryGetValue(new {o.OwnerId, TypeId = 2}, out lookup))
owner.City = lookup;
if(dic.TryGetValue(new {o.OwnerId, TypeId = 3}, out lookup))
owner.Sex = lookup;
yield return owner;
}
}
为了获得更多的性能,您可以编写一个IEqualityComparer
类,只比较int OwnerId并将其发送到Distinct
函数