代码：
public class Person
{
public int ID { get; set; } public string Name { get; set; } public int Age { get; set; }
}
public class Dog
{
public int ID { get; set; }
public string Name { get; set; }
public int PersonID { get; set; }
}
public class DogClothes
{
public int ID { get; set; }
public string Name { get; set; }
public int DogID { get; set; }
}
public class ActionPerson
{
public List<Person> GetPersons()
{
List<Person> list = new List<Person>();
for (var i = ; i < ; i++)
{
Person p = new Person {ID=i+, Name = "hongda" + (i + ), Age = + i };
list.Add(p);
}
return list;
}
public List<Dog> GetDogs()
{
List<Dog> dogs = new List<Dog>();
for (var i = ; i < ; i++)
{
Dog dog = new Dog { ID = i, Name = "dogs" + i, PersonID = (i %)+ };
dogs.Add(dog);
}
return dogs;
}
public List<DogClothes> GetDogClotheses()
{
List<DogClothes> DogClothes = new List<DogClothes>();
for (var i = ; i < ; i++)
{
DogClothes clothes = new DogClothes() { ID = i, Name = "DogClothes" + i, DogID = (i % ) + };
DogClothes.Add(clothes);
}
return DogClothes;
}
public void Show<T>(List<T> list)
{
foreach (var l in list)
{
ShowObj(l);
}
}
public void ShowObj<T>(T data)
{
Type t = data.GetType();
PropertyInfo[] infos = t.GetProperties();
StringBuilder strBuilder = new StringBuilder();
foreach (var p in infos)
{
object value = p.GetValue(data, null);
string name = p.Name.ToString();
strBuilder.Append(name + ":" + value + ",");
}
Console.WriteLine(strBuilder.ToString());
}
}
对list操作
上面的代码可以不看，反正就是一些临时用的数据
ActionPerson ap = new ActionPerson();
List<Person> list = ap.GetPersons();
ap.Show(list);
Console.WriteLine("========================================");
List<Dog> dogs = ap.GetDogs();
ap.Show(dogs);
Console.WriteLine("========================================");
上面的是list,下面的是dogs
ap(dogClothes);
1.取有狗的人
var list2 = from a in list from b in dogs where a.ID == b.PersonID select a;
ap.Show(list2.ToList());
Console.WriteLine("========================================");
ap.Show(list2.Distinct().ToList());
Console.WriteLine("========================================");
发现，自连接就是左表一条条数据与右表一条条数据交叉，取得符合条件的（a.ID==b.PersonID)，再取得需要的字段（a），不会自动合并。
看这个就清楚了
var list4 = from a in list from b in dogs where a.ID == b.PersonID select new { a.ID, a.Name, a.Age, DogsID = b.ID, DogsName = b.Name };
ap.Show(list4.ToList());
这样上面的就不要区分（distinct）了，之后再取需要的字段
2.join
var list2 = from a in list join b in dogs on a.ID equals b.PersonID into c select new { a.ID, a.Name, a.Age,Count=c.Count() };
ap.Show(list2.ToList());
在上面这种into一个对象时，会发现b不能够调用了，a还是可以使用的，当然新形成的对象c也可以，c有一些特殊的属性，例如count
var list2 = from a in list join b in dogs on a.ID equals b.PersonID select new { a.ID, a.Name, a.Age, DogsID = b.ID, DogsName = b.Name, b.PersonID };
var list2 = from a in list join b in dogs on a.ID equals b.PersonID select new { a.ID, a.Name, a.Age };
ap.Show(list2.ToList());
跟自连接一样
LINQ TO SQL中的join，如果带有into,可以提前对表进行过滤条件的操作，而不用等到两表进行迪卡尔积产生虚似表后再进行join on的条件过滤。
关于left join
var list2 = from a in list
join b in dogs on a.ID equals b.PersonID into c
from o in c.DefaultIfEmpty()
select new
{
a.ID,
a.Name,
a.Age,
DogsID =o==null?: o.ID,
DogsName = o == null ? "" : o.Name,
PersonID=o==null?:o.PersonID
};
在数据库中就不需要这么复杂，这里list中要考虑右侧可能为空，
数据库查询就不需要。
多查询
static void Main(string[] args)
{
ActionPerson ap = new ActionPerson();
List<Person> list = ap.GetPersons();
List<Dog> dogs = ap.GetDogs();
List<DogClothes> dogClotheses = ap.GetDogClotheses();
var result = from a in list
join b in dogs
on a.ID equals b.PersonID
join c in dogClotheses
on b.ID equals c.DogID
select new
{
a.ID ,
a.Name ,
a.Age,
DogID=b.ID ,
DogName=b.Name,
b.PersonID ,
DogClothesID=c.ID,
DogClothesName=c.Name ,
DogID2=c.DogID
};
ap.Show(result.ToList ());
Console.ReadLine();
}
var result = from a in list
from b in dogs
from c in dogClotheses
where a.ID == b.PersonID && b.ID == c.DogID
select new
{
a.ID,
a.Name,
a.Age,
DogID = b.ID,
DogName = b.Name,
b.PersonID,
DogClothesID = c.ID,
DogClothesName = c.Name,
DogID2 = c.DogID
};
结果上同
http://developer.51cto.com/art/200909/152189.htm
http://www.cnblogs.com/ASPNET2008/archive/2008/12/21/1358152.html