先把准备工作做好，这步可以不看，创建一个数据库和两张表,下面是创建语句,很简单,大家都看得懂.
第一步：创建表和初始化数据
--创建数据库Student create database Student go use Student --创建数据表T_Student create table T_Student( ID int not null identity primary key, Name varchar(20) not null, EnglishName varchar(20) not null, Age int, Birthday date, Country varchar(50) ) --创建数据表T_Score,设置主外键关系 create table T_Score( ID int not null identity primary key, StudentID int not null foreign key references T_Student(ID), English varchar(20) not null, Math varchar(20) not null, Chinese varchar(20) not null ) go --向T_Student插入数据 insert into T_Student select '李正兴','Black_Li',19,'1991-03-17','江苏盐城' union select '罗海','Jason',21,'1991-10-17','江苏南京' union select '张士阳','Huk',35,'1991-12-18','安徽阜阳' union select '吕佳惠','Lily',23,'1991-06-23','四川' union select '金琢','Babylon',23,'1991-03-15','江苏扬州' --向T_Score插入数据 insert into T_Score select 1,'90','84','68' union select 2,'87','78','78' union select 3,'79','87','88' union select 4,'95','84','89' union select 5,'90','84','58' go 学生表：成绩表：
第二步：创建两张表对应的实体
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace LinqDemo
{
/// <summary>
/// 学生实体类
/// </summary>
public class Student
{
#region 属性
public int ID { get; set; }
public string Name { get; set; }
public string EnglishName { get; set; }
public int Age { get; set; }
public DateTime Birthday { get; set; }
public string Country { get; set; }
#endregion
#region 方法
/// <summary>
/// 重写Object类的ToString()方法
/// </summary>
/// <returns></returns>
public override string ToString()
{
return string.Format("学号：{0},姓名：{1},英文名：{2},年龄：{3},生日：{4},家乡：{5}",
this.ID,
this.Name,
this.EnglishName,
this.Age,
this.Birthday,
this.Country);
}
#endregion
}
}using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace LinqDemo
{
/// <summary>
/// 成绩表实体
/// </summary>
public class Score
{
#region 属性
public int ID { get; set; }
public int StudentID { get; set; }
public string English { get; set; }
public string Math { get; set; }
public string Chinese { get; set; }
#endregion
#region 方法
/// <summary>
/// 重写Object类的ToString()方法
/// </summary>
/// <returns></returns>
public override string ToString()
{
return string.Format("学号：{0},英语：{1},数学：{2},语文：{3}",
this.StudentID,
this.English,
this.Math,
this.Chinese);
}
#endregion
}
}第三步：创建学生管理类（其中用到了自己写的SQLHelper，也贴出来）
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace LinqDemo
{
/// <summary>
/// 学生管理类
/// </summary>
public class StudentManage
{
private string commandText = string.Empty;
DataTable table = null;
/// <summary>
/// 获取所有学生信息
/// </summary>
/// <returns></returns>
public List<Student> GetAllStudents()
{
//创建一个集合
List<Student> list = new List<Student>();
commandText = @"SELECT [ID]
,[Name]
,[EnglishName]
,[Age]
,[Birthday]
,[Country]
FROM [Student].[dbo].[T_Student]";
//执行Sql语句，查出所有的学生信息
table = SQLHelper.ExecuteDataTable(commandText);
//遍历添加到list集合中
for (int i = 0; i < table.Rows.Count; i++)
{
list.Add(new Student()
{
ID = Convert.ToInt32(table.Rows[i]["ID"]),
Name = table.Rows[i]["Name"].ToString(),
EnglishName = table.Rows[i]["EnglishName"].ToString(),
Age = Convert.ToInt32(table.Rows[i]["Age"]),
Birthday = Convert.ToDateTime(table.Rows[i]["Birthday"]),
Country = table.Rows[i]["Country"].ToString()
});
}
return list;
}
/// <summary>
/// 获取所有成绩信息
/// </summary>
/// <returns></returns>
public List<Score> GetAllScores()
{
//创建一个集合
List<Score> list = new List<Score>();
commandText = @"SELECT [ID],
[StudentID],
[English],
[Math],
[Chinese]
FROM [Student].[dbo].[T_Score]";
//执行Sql语句，查出所有的学生信息
table = SQLHelper.ExecuteDataTable(commandText);
//遍历添加到list集合中
for (int i = 0; i < table.Rows.Count; i++)
{
list.Add(new Score()
{
ID = Convert.ToInt32(table.Rows[i]["ID"]),
StudentID = Convert.ToInt32(table.Rows[i]["StudentID"]),
English = table.Rows[i]["English"].ToString(),
Math = table.Rows[i]["Math"].ToString(),
Chinese = table.Rows[i]["Chinese"].ToString()
});
}
return list;
}
}
}using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace LinqDemo
{
/// <summary>
/// 后台数据执行类
/// </summary>
public class SQLHelper
{
#region ExecuteNonQuery()方法 返回一个int值
/// <summary>
/// 执行ExecuteNonQuery并返回结果，静态函数可由类SQLHelper直接调用
/// </summary>
/// <param name="commandText">sql语句命令</param>
/// <param name="parameters">用户传过来的参数集合</param>
/// <returns>返回一个int值</returns>
public static int ExecuteNonQuery(string commandText, params SqlParameter[] parameters)
{
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;//建立连接字符串
using (SqlConnection conn = new SqlConnection(connStr))//创建一个连接
{
conn.Open();//打开连接
using (SqlCommand cmd = conn.CreateCommand())//创建命令
{
cmd.CommandText = commandText;//sql命令是用户传过来的命令
//遍历传过来的参数
foreach (SqlParameter p in parameters)
{
cmd.Parameters.Add(p);
}
return cmd.ExecuteNonQuery();//执行命令，并返回执行结果
}
}
}
#endregion
#region ExecuteScalar()方法 返回一个object值
/// <summary>
/// 执行ExecuteScalar并返回结果，静态函数可由类SQLHelper直接调用
/// </summary>
/// <param name="commandText">sql语句命令</param>
/// <param name="parameters">用户传过来的参数集合</param>
/// <returns>返回一个object值</returns>
/* 举例：cmd.CommandText = "SELECT COUNT(*) FROM T_Persons";
Int32 count = (Int32)cmd.ExecuteScalar();
执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
*/
public static object ExecuteScalar(string commandText, params SqlParameter[] parameters)
{
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;//建立连接字符串
using (SqlConnection conn = new SqlConnection(connStr))//创建一个连接
{
conn.Open();//打开连接
using (SqlCommand cmd = conn.CreateCommand())//创建命令
{
cmd.CommandText = commandText;//sql命令是用户传过来的命令
//遍历传过来的参数
foreach (SqlParameter p in parameters)
{
cmd.Parameters.Add(p);
}
return cmd.ExecuteScalar();//执行命令，并返回执行结果
}
}
}
#endregion
#region ExecuteDataTable()方法 返回一个DataTable值
/// <summary>
/// 执行ExecuteDataTable并返回结果，静态函数可由类SQLHelper直接调用
/// </summary>
/// <param name="commandText">sql语句命令</param>
/// <param name="parameters">用户传过来的参数集合</param>
/// <returns>返回一个DataTable值</returns>
public static DataTable ExecuteDataTable(string commandText, params SqlParameter[] parameters)
{
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;//建立连接字符串
using (SqlConnection conn = new SqlConnection(connStr))//创建一个连接
{
conn.Open();//打开连接
using (SqlCommand cmd = conn.CreateCommand())//创建命令
{
cmd.CommandText = commandText;//sql命令是用户传过来的命令
DataSet dataset = new DataSet();//新建一个数据集,把查询数据保存到本地数据集中
SqlDataAdapter adapter = new SqlDataAdapter(cmd);//创建一个适配器adapter
//遍历传过来的参数
foreach (SqlParameter p in parameters)
{
cmd.Parameters.Add(p);
}
adapter.Fill(dataset);//向数据集中填充数据
DataTable table = dataset.Tables[0];//将dataset中的查询结果（一般第一个表）保存起来
return table;//执行命令，并返回执行结果
}
}
}
#endregion
}
}第四步：LINQ开始，在Program.cs类中
static void Main(string[] args)
{
DoWork();
Console.ReadKey();
}
/// <summary>
/// 做某些操作函数
/// </summary>
static void DoWork()
{
//创建学生管理对象
StudentManage SM = new StudentManage();
//获取所有学生存放于集合students中
List<Student> students = SM.GetAllStudents();
//获取所有成绩存放于集合scores中
List<Score> scores = SM.GetAllScores();
}A、查询
//查询语句,将所有学生姓名取出来放入集合中
//IEnumerable<string> names = students.Select(s=>s.Name);
//或者
IEnumerable<string> names = from s in students select s.Name;
foreach (string name in names)
{
Console.WriteLine(name);
}
结果： var stus = students.Select(s=>new { Name = s.Name, Age = s.Age });
//或者
//var stus = from s in students select new { Name = s.Name, Age = s.Age };
foreach (var s in stus)
{
Console.WriteLine(s);
}结果：
B、筛选
//筛选语句,将符合条件的学生姓名取出来
//IEnumerable<Student> stus = students.Where(stu => string.Equals(stu.Country,"四川")).Select(stu=>stu);
//或者
IEnumerable<Student> stus = from stu in students where string.Equals(stu.Country, "四川") select stu;
foreach (Student stu in stus)
{
Console.WriteLine(stu.ToString());
}
结果：C、排序
//根据学号降序，默认升序
//var stus = students.OrderByDescending(stu => stu.ID).Select(stu => new {Number=stu.ID,Name=stu.Name});
//或者
var stus = from stu in students orderby stu.ID descending select new { Number = stu.ID, Name = stu.Name };
foreach (var stu in stus)
{
Console.WriteLine(stu);
}
结果：D、分组
//根据年龄分组
//var stusGroup = students.GroupBy(stu=>stu.Age);
//或者
var stusGroup = from stu in students group stu by stu.Age;
//遍历组信息
foreach (var grop in stusGroup)
{
Console.WriteLine("这是年龄为"+grop.Key+"组");
Console.WriteLine("该组成员如下：");
//遍历组成员信息
foreach(var stu in grop)
{
Console.WriteLine("姓名："+stu.Name+" 年龄："+stu.Age);
}
Console.WriteLine();
}
结果：E、结果集去除重复与计数
//取出所有年龄，过滤掉重复，统计个数 int number = students.Select(stu => stu.Age).Distinct().Count();
F、联合查询
//创建学生管理对象
StudentManage SM = new StudentManage();
//获取所有学生存放于集合students中
List<Student> students = SM.GetAllStudents();
//获取所有成绩存放于集合scores中
List<Score> scores = SM.GetAllScores();
//联合学生和成绩表
//var StuScores = students.Select(stu => stu).Join
(scores, stus => stus.ID, scors => scors.StudentID, (stus, scors) =>
new {stus.Name,stus.Age,scors.Chinese,scors.English});
//输出
//或者
var StuScores = from stu in students join score in scores
on stu.ID equals score.StudentID
select new { stu.Name, stu.Age, score.Chinese, score.English };
foreach (var stuscore in StuScores)
{
Console.WriteLine(stuscore);
}
结果：
喜欢 
顶
难过
囧
围观
无聊
