using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;namespace 练习LinQ入门{ public partial class Form1 : Form { public Form1() { InitializeComponent(); } private mydbDataContext context = new mydbDataContext(); private void Form1_Load(object sender, EventArgs e) { FillInfo(); cbx_sex.SelectedIndex = 0; } private void FillInfo() { //法一： //List list = context.Info.ToList(); //dataGridView1.DataSource = list; //法二： //var query = from p in context.Info select p; //dataGridView1.DataSource = query; //法三： var query = context.Info; dataGridView1.DataSource = query; } private void btn_ADD_Click(object sender, EventArgs e) { Info data = new Info(); data.Code = txt_code.Text; data.Name = txt_name.Text; data.Nation = txt_nation.Text; data.Sex = Convert.ToBoolean(txt_sex.Text); data.Birthday = Convert.ToDateTime(txt_b.Text); context.Info.InsertOnSubmit(data); context.SubmitChanges();//提交 FillInfo(); } private void button2_Click(object sender, EventArgs e) { var query = from p in context.Info where p.Code == txt_code.Text select p; if (query.Count() > 0) { // query.Single();//第一条 Info data = query.First(); context.Info.DeleteOnSubmit(data); context.SubmitChanges(); } FillInfo(); //Form p in context.Info where // Info data = context.Info.Where( => ); } private void button1_Click(object sender, EventArgs e) { var a = from p in context.Info where p.Code == txt_code.Text select p; if (a.Count() > 0) { Info data = a.First(); data.Name = txt_name.Text; data.Sex = Convert.ToBoolean(txt_sex.Text); data.Nation = txt_nation.Text; data.Birthday = Convert.ToDateTime(txt_b.Text); context.SubmitChanges(); } FillInfo(); } private void btn_select_Click(object sender, EventArgs e) { #region meiyong //// e.Equals(); //Type t = e.GetType(); ////t.GetProperties(); ////t.GetMethods(); //Type s = sender.GetType(); #endregion Button btn = (Button)sender; if (btn.Name == "btn_select") { if (tbx_code2.Text != "") { //单条件等值查询 //var query = from p in context.Info where p.Code == tbx_code2.Text.Trim() select p; var query = context.Info.Where(p => p.Code == tbx_code2.Text.Trim());//扩展方法 Lambda表达式 dataGridView1.DataSource = query; } } else if (btn.Name == "btn_select2") { if (tbx_code2.Text != "") { //单条件不等值查询 //var query = from p in context.Info where p.Code != tbx_code2.Text.Trim() select p; //>= <= 同样可以用于筛选 var query = context.Info.Where(p => p.Code != tbx_code2.Text.Trim()); dataGridView1.DataSource = query; } } else if (btn.Name == "btn_duo") { //多条件查询 var query = from p in context.Info where p.Sex == Convert.ToBoolean(cbx_sex.Text.Trim()) && p.Birthday.Value.Year > Convert.ToInt32(tbx_b_duo.Text.Trim()) select p; //或者 //var query1 = context.Info.Where(p => p.Sex == Convert.ToBoolean(cbx_sex.Text.Trim()) && p.Birthday.Value.Year > Convert.ToInt32(tbx_b_duo.Text.Trim())); //var a=context.Info.Where (p=>p.Sex==Convert.ToBoolean(cbx_sex.Text.Trim())).Where(p=>p.Birthday.Value.Year> Convert.ToInt32(tbx_b_duo.Text.Trim()));//链式表达式 （&& 逻辑与可以用） dataGridView1.DataSource = query; } else if (btn.Name == "btn_select_mohu") { //var query = from p in context.Info where p.Name.Contains(tbx_xing.Text.Trim()) select p;//相当于like'%某' //var query = from p in context.Info where p.Name.StartsWith(tbx_xing.Text.Trim()) select p;//以什么开头 //var query = from p in context.Info where p.Name.EndsWith(tbx_xing.Text.Trim()) select p;//以什么结尾 //var query = from p in context.Info where p.Name.Substring(1, tbx_xing.TextLength) == tbx_xing.Text.Trim() select p;//截取第一位往后 var query = context.Info.Where(p => p.Name.StartsWith(tbx_xing.Text.Trim())); dataGridView1.DataSource = query; } else if (btn.Name == "btn_shua") { FillInfo(); } else if (btn.Name == "btn_jiao") { //交集相当于逻辑与 取公共部分 var p1 = from p in context.Info where p.Sex == true select p; var p2 = context.Info.Where(p => p.Birthday.Value.Year > 1985); var query = p1.Intersect(p2); dataGridView1.DataSource = query; } else if (btn.Name == "btn_bing") { //并集相当于逻辑或 var p1 = from p in context.Info where p.Sex == true select p; var p2 = context.Info.Where(p => p.Birthday.Value.Year > 1985); var query = p1.Union(p2); dataGridView1.DataSource = query; } else if (btn.Name == "btn_bu") { //差集 从一个集合减去另外一个集合 看谁减谁 var p1 = from p in context.Info where p.Sex == true select p; var p2 = context.Info.Where(p => p.Birthday.Value.Year > 1985); var query = p1.Except(p2); dataGridView1.DataSource = query; } } private void btn_jige_Click(object sender, EventArgs e) { Button btn =(Button)sender; if (btn.Name=="btn_jige") { var query = context.Info.Where(p => p.Birthday.Value.Year > 1985); this.Text = query.Count().ToString();//个数 } else if (btn.Name=="btn_sum") { var query = context.Info.Where(p => p.Birthday.Value.Year > 1985); this.Text = query.Sum(p=>DateTime.Now.Year-p.Birthday.Value.Year).ToString();//求和 } else if (btn.Name == "btn_avg") { var query = context.Info.Where(p => p.Birthday.Value.Year > 1985); this.Text = query.Average(p => DateTime.Now.Year - p.Birthday.Value.Year).ToString();//求平均值 } } private void button5_Click(object sender, EventArgs e) { var query = context.Info.Where(p => p.Birthday.Value.Year > 1985); List list = query.ToList();//转集合 dataGridView1.DataSource = list; } private void button7_Click(object sender, EventArgs e) { var query = context.Info.Where(p => p.Birthday.Value.Year > 1985); Info[] list = query.ToArray();//转数组 dataGridView1.DataSource = list; } private void button6_Click(object sender, EventArgs e) { var query = context.Info.Where(p => p.Birthday.Value.Year > 1985); //Info data = query.Single();//单一 Info data = query.First(); dataGridView1.DataSource = data; } private void button3_Click(object sender, EventArgs e) { //每页多少条pagesize，要找第几页pageno var query=context.Info.Skip(1*1).Take(2);//skip跳过多少行，take取多少行 dataGridView1.DataSource=query; } }} 练习源码：
LinQ to sql 入门小教程：
1.查询所有的
var query = from p in _Context.Info select p;
var query = _Context.Info;2.单条件查询
等值查
var query = from p in _Context.Info where p.Code == "p002" select p;注意：双等号，双引号。---C#语法。var query = _Context.Info.Where(p => p.Code == "p002" ); //Lambda表达式不等值查 //var query = from p in _Context.Info where p.Code != "p002" select p; //var query = _Context.Info.Where(p => p.Code != "p002"); //Lambda表达式 //var query = from p in _Context.Info where p.Birthday.Value.Year > 1985 select p; var query = _Context.Info.Where(p=>p.Birthday.Value.Year < 1985);3.多条件查询
//var query = from p in _Context.Info where p.Sex == true && p.Birthday.Value.Year > 1985 select p;
//var query = _Context.Info.Where(p=>p.Sex==true && p.Birthday.Value.Year>1985); var query = _Context.Info.Where(p => p.Sex == true).Where(p=>p.Birthday.Value.Year>1985) ; //多条件逻辑与可以用链式表达式写4.模糊查询
//var query = from p in _Context.Info where p.Name.Contains("张") select p; //相当于like '%张%'
//var query = from p in _Context.Info where p.Name.StartsWith("张") select p; // like '张%' //var query = from p in _Context.Info where p.Name.EndsWith("张") select p; //like '%张' //var query = from p in _Context.Info where p.Name.Substring(1,1) == "张" select p;// like '_张%'var query = _Context.Info.Where(p => p.Name.StartsWith("张"));
5.集合操作
var q1 = from p in _Context.Info where p.Sex== true select p;
var q2 = _Context.Info.Where(p => p.Birthday.Value.Year > 1985); 交var query = q1.Intersect(q2);并var query = q1.Union(q2);差var query = q2.Except(q1);6.统计函数：
var query = _Context.Info.Where(p => p.Birthday.Value.Year > 1985);
//this.Text = query.Count().ToString(); //this.Text = query.Sum(p=>DateTime.Now.Year - p.Birthday.Value.Year).ToString(); //this.Text = query.Average(p => DateTime.Now.Year - p.Birthday.Value.Year).ToString(); //this.Text = query.Max(p => DateTime.Now.Year - p.Birthday.Value.Year).ToString(); this.Text = query.Min(p => DateTime.Now.Year - p.Birthday.Value.Year).ToString();7.转换操作
ToList(),ToArray(),First()/Single()
8.分页
var query = _Context.Info.Skip(2*2).Take(2);
这里主要是将数据库中的常用操作用LAMBDA表达式重新表示了下，用法不多，但相对较常用，等有时间了还会扩展，并将查询语句及LINQ到时也一并重新整理下：1.select语句：books.Select(p=>new { p.Title, p.UnitPrice, p.Author});//需用匿名方式2.where语句：books.Where(p=>p.UnitPrice==100&&p.Title=”ABC”);补充：像数据库中的LIKE ‘%c++%’，LAMBDA中用p.Title.Contains(“c++”)表示；像数据库中的LIKE ‘c%’，LAMBDA中用p.Title.StartWith(“c”)表示；像数据库中的LIKE ‘%c’，LAMBDA中用p.Title.EndsWith(“c”)表示；Where的另一种表现形式：books.Where(p=>{ var ret = p.UnitPrice>30&&p.Title.Contains(“c++”); return ret;});3.排序语句：像数据库中order by 升序：通过 “对象.OrderBy(p=>p.UnitPrice)”实现像数据库中order by 降序：通过 “对象.OrderByDescending(p=>p.UnitPrice)”实现像数据库中order by UnitPrice desc，Title asc：通过 ”对象.OrderByDescending(p=>p.UnitPrice).ThenBy(p=>p.Title)”反过来则是： ”对象.OrderBy(p=>p.UnitPrice).ThenByDescending(p=>p.Title)” 4.组函数： var max = books.Where(p => p.CategoryId == 1001).Max(p => p.UnitPrice); var min = books.Min(p => p.UnitPrice); var count = books.Count( ); var avg = books.Average(p => p.UnitPrice); var sum = books.Sum(p => p.UnitPrice);注意，上面这些获得的东西，不是对象，是单个值 5. GROUP BY函数// select categoryid,max(unitpirce) from books group by categoryid having max(unitprice)>50 var list6 = books.GroupBy(p => p.CategoryId).Where(p=>p.Max(q=>q.UnitPrice)>50); foreach (var item in list6) { Response.Write(string.Format("类别编号:{0},最高价{1} ", item.Key,item.Max(p=>p.UnitPrice))); } 6. TOP函数//取一个范围 如3,5var list7 = books.Skip(2).Take(3).Select(p => new { p.Title, p.CategoryId, p.UnitPrice });// select top 5 var list7 = books.Take(5).OrderByDescending(p => p.UnitPrice) .Select(p => new { p.CategoryId, p.UnitPrice, p.Title, p.Author }); 7.union 函数books.Where(p => p.CategoryId == 1001).Select(p => new { p.CategoryId, p.UnitPrice, p.Title, p.Author }).Union(books.Where(p => p.CategoryId == 1002).Select(p => new { p.CategoryId, p.UnitPrice, p.Title, p.Author }));这里的Select子句中的列需对应，跟数据库中是一样的 8.Join方法，用于实现数据库中双表连接查询//select a.title,a.unitprice,a.categoryid,b.id,b.name from books a,category b //where a.categoryid=b.id and b.name=‘数据库’books.Join(cates.Where(m => m.Name == "数据库"),p => p.CategoryId, q => q.ID, (a, b) => new { a.Title, a.UnitPrice, a.CategoryId, b.ID, b.Name });说明：Join()方法的调用对象类似于在SQL语句中第一张表的表名而Join()方法的第一个形参是第二张表表名的Where条件Join()方法的第二和第三个参数分别表示第一张表与第二张表的关联字段Join()方法的第四个参数表示从两表中需要获取的字段，(a, b)分别表示第一张表和第二张表 http://blog.csdn.net/codefighting/article/details/7048966
.AsEnumerable()是延迟执行的，实际上什么都没有发生，当真正使用对象的时候（例如调用：First, Single, ToList....的时候）才执行。下面就是.AsEnumerable()与相对应的.AsQueryable()的区别：AsEnumerable将一个序列向上转换为一个IEnumerable, 强制将Enumerable类下面的查询操作符绑定到后续的子查询当中。AsQueryable将一个序列向下转换为一个IQueryable, 它生成了一个本地查询的IQueryable包装。
- .AsEnumerable()延迟执行，不会立即执行。当你调用.AsEnumerable()的时候，实际上什么都没有发生。
- .ToList()立即执行
- 当你需要操作结果的时候，用.ToList()，否则，如果仅仅是用来查询不需要进一步使用结果集，并可以延迟执行，就用.AsEnumerable()/IEnumerable /IQueryable
- .AsEnumerable()虽然延迟执行，但还是访问 数据库，而.ToList()直接取得结果放在内存中。比如我们需要显示两个部门的员工时，部门可以先取出放置在List中，然后再依次取出各个部门的员 工，这时访问的效率要高一些，因为不需要每次都访问数据库去取出部门。
- IQueryable实现了IEnumberable接口。但IEnumerable<T> 换成IQueryable<T>后速度提高很多。原因：
- IQueryable接口与IEnumberable接口 的区别： IEnumerable<T> 泛型类在调用自己的SKip 和 Take 等扩展方法之前数据就已经加载在本地内存里了，而IQueryable<T> 是将Skip ,take 这些方法表达式翻译成T-SQL语句之后再向SQL服务器发送命令，它并不是把所有数据都加载到内存里来才进行条件过滤。
- IEnumerable跑的是Linq to Object，强制从数据库中读取所有数据到内存先。