这些是我的输入表：
Persons Table:
================
ID Code
------------------------
1 Person1 # Person1: John Smith, 25, 50Kg
2 Person2 # Person2: William Brown, 30, 80Kg
3 Person3 # Person3: James Miller, 32, 73Kg
StringProperties Table:
=========================
ID PersonID Name Value
----------------------------------------------
1 1 FirstName John # Person1: John Smith, 25, 50Kg
2 1 LastName Smith # Person1: John Smith, 25, 50Kg
3 2 FirstName William # Person2: William Brown, 30, 80Kg
4 2 LastName Brown # Person2: William Brown, 30, 80Kg
5 3 FirstName James # Person3: James Miller, 32, 73Kg
6 3 LastName Miller # Person3: James Miller, 32, 73Kg
NumericProperties Table:
=========================
ID PersonID Name Value
-----------------------------------------
1 1 Age 25 # Person1: John Smith, 25, 50Kg
2 1 Weight 50 # Person1: John Smith, 25, 50Kg
3 2 Age 30 # Person2: William Brown, 30, 80Kg
4 2 Weight 80 # Person2: William Brown, 30, 80Kg
5 3 Age 32 # Person3: James Miller, 32, 73Kg
6 3 Weight 73 # Person3: James Miller, 32, 73Kg
我想编写一个生成以下结果的LINQ查询：
Result:
==========
Code FirstName LastName Age Weight
-----------------------------------------------------------------
Person1 John Smith 25 50
Person2 William Brown 30 80
Person3 James Miller 32 73
这是我的代码，但无法正常工作：
var q = from p in db.Persons
join s in db.StringProperties on p.ID equals s.PersonID
join n in db.NumericProperties on p.ID equals n.PersonID
group p by p.Code into g
select new
{
g.Key,
g
};
假设我们的实体看起来像这样：
public class Person
{
public int ID { get; set; }
public string Code { get; set; }
}
public class StringProperty
{
public int ID { get; set; }
public int PersonID { get; set; }
public string Name { get; set; }
public string Value { get; set; }
}
public class NumericProperty
{
public int ID { get; set; }
public int PersonID { get; set; }
public string Name { get; set; }
public int Value { get; set; }
}
像这样的数据：
var persons = new List<Person>
{
new Person { ID = 1, Code = "Person1" },
new Person { ID = 2, Code = "Person2" },
new Person { ID = 3, Code = "Person3" }
};
var stringProperties = new List<StringProperty>
{
new StringProperty { ID = 1, PersonID = 1, Name = "FirstName", Value = "John" },
new StringProperty { ID = 2, PersonID = 1, Name = "LastName", Value = "Smith" },
new StringProperty { ID = 3, PersonID = 2, Name = "FirstName", Value = "William" },
new StringProperty { ID = 4, PersonID = 2, Name = "LastName", Value = "Brown" },
new StringProperty { ID = 5, PersonID = 3, Name = "FirstName", Value = "James" },
new StringProperty { ID = 6, PersonID = 3, Name = "LastName", Value = "Miller" }
};
var numericProperties = new List<NumericProperty>
{
new NumericProperty { ID = 1, PersonID = 1, Name = "Age", Value = 25 },
new NumericProperty { ID = 2, PersonID = 1, Name = "Weight", Value = 50 },
new NumericProperty { ID = 3, PersonID = 2, Name = "Age", Value = 30 },
new NumericProperty { ID = 4, PersonID = 2, Name = "Weight", Value = 80 },
new NumericProperty { ID = 5, PersonID = 3, Name = "Age", Value = 32 },
new NumericProperty { ID = 6, PersonID = 3, Name = "Weight", Value = 73 }
};
我们可以像下面这样将人员加入到字符串值表中并同时进行旋转：
var stringValues = from p in persons
join s in stringProperties on p.ID equals s.PersonID
group s by p.Code into g
select new
{
Code = g.Key,
FirstName = g.Where(s => s.Name == "FirstName").First().Value,
LastName = g.Where(s => s.Name == "LastName").First().Value,
};
对数值执行相同的操作：
var numericValues = from p in persons
join n in numericProperties on p.ID equals n.PersonID
group n by p.Code into g
select new
{
Code = g.Key,
Age = g.Where(n => n.Name == "Age").First().Value,
Weight = g.Where(n => n.Name == "Weight").First().Value,
};
现在将它们一起加入：
var q = from s in stringValues
join n in numericValues on s.Code equals n.Code
select new
{
Code = s.Code,
FirstName = s.FirstName,
LastName = s.LastName,
Age = n.Age,
Weight = n.Weight
};
您可以在一条语句中执行此操作，但是如果按如下所示将其拆分，则操作会更简单。
实际上，我将更可能在存储过程中执行此操作，因为存储过程在那里更快。
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句