linq 关联查询

  

   可得会在以后的实体类中能用到
   

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;

namespace test2
{
    class Student
    {
        public string First { get; set; }
        public string Last { get; set; }
        public int ID { get; set; }
        public string Street { get; set; }
        public string City { get; set; }
        public List<int> Scores;  

    }

    class Teacher
    {
        public string First { get; set; }
        public string Last { get; set; }
        public int ID { get; set; }
        public string City { get; set; }
      
    }

    public partial class _Default : System.Web.UI.Page
    {




        protected void Page_Load(object sender, EventArgs e)
        {
            List<Student> students = new List<Student>()
        {
            new Student {First="张",
                Last="三", 
                ID=111, 
                Street="123 Main Street",
                City="太原",
                Scores= new List<int> {97, 92, 81, 60}},
            new Student {First="李",
                Last="四", 
                ID=112,
                Street="124 Main Street",
                City="太原",
                Scores= new List<int> {75, 84, 91, 39}},
            new Student {First="王",
                Last="五",
                ID=113,
                Street="125 Main Street",
                City="上海",
                Scores= new List<int> {88, 94, 65, 91}},
        };

   List<Teacher> teachers = new List<Teacher>()
        {                
            new Teacher {First="李", Last="老师", ID=945, City = "太原"},
            new Teacher {First="赵", Last="老师", ID=956, City = "郑州"},
            new Teacher {First="秦", Last="老师", ID=972, City = "北京"}
        };

            // 相当于sql union 关联
            var peopleInSeattle = (from student in students
                                   where student.City == "太原"
                                   select student.First+student.Last)
                                 
                           .Concat(from teacher in teachers
                                where teacher.City == "太原"
                                select teacher.First+teacher.Last);



            this.Response.Write("老师和学生都生活在太原的是:<br />");
            // Execute the query.
            foreach (var person in peopleInSeattle)
            {
                this.Response.Write(person + "<br />");
              
            }
              //相当于sql inner
            var query = students.AsQueryable().Join(teachers,
                        student => student.City,
                        teacher =>teacher.City,
          (student, teacher) =>new { 老师姓名 = teacher.First+teacher.Last, 城市 = student.City,学生姓名=student.First+student.Last });
            
            this.Response.Write("老师和学生都生活在同一城市的是:<br />");
            foreach (var obj in query)
            {
                this.Response.Write(obj.老师姓名  + ","+ obj.城市+","+obj.学生姓名+"<br/>");
            }
       
        }
    }