using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace LinkSentence
{
class Program
{
static void Main(string[] args)
{
/*
【根据语法的规定，LINQ表达式必须以from子句开头，以select或group子句结束】
*/
//模拟的数据源
List<Person> personList = new List<Person>();
personList.Add(new Person() { Age = 31, Gender = '男', Name = "小编1", Province = "河南" });
personList.Add(new Person() { Age = 21, Gender = '男', Name = "小编2", Province = "河北" });
personList.Add(new Person() { Age = 33, Gender = '男', Name = "小编3", Province = "河南" });
personList.Add(new Person() { Age = 22, Gender = '男', Name = "小编4", Province = "山东" });
personList.Add(new Person() { Age = 25, Gender = '男', Name = "小编5", Province = "山东" });
List<int[]> complexList = new List<int[]>();
complexList.Add(new int[] { 1, 2, 3, 4, 5 });
complexList.Add(new int[] { 10, 20, 30, 40, 50 });
complexList.Add(new int[] { 100, 20, 300, 400, 50 });
/*
【from 指定范围变量和数据源】
=====from a in list; a是任意定义的变量名称，a的数据类型取决于后面的数据源list；所以对a的可以有哪些处理要看a的数据类型，比如list是字符串数组a就是字符串数据类型就可以使用 .indexof()等字符串拥有的方法， 比如list是 List<Person> 那么a就代表person可以使用 “对象”所拥有的一切特性；
【where 根据bool表达式从数据源中筛选数据】
【select 指定查询结果中的元素所具有的类型或表现形式】
====select 通常可以结合匿名函数使用 select new{}
*/
var fromWhereSelectResult1 = from a in personList//{personListt 是List<person>的数据类型，所有a的数据类型就是person，命名是任意的a可以换成b，c，d。。。}
where a.Age > 30//筛选的条件
select a;//===> 从personList里面选取年龄大于30的person信息，得到集合
foreach (var item in fromWhereSelectResult1)
{
Console.WriteLine("fromWhereSelectResult1 姓名：{0},性别：{1},年龄：{2},省份：{3}", item.Name, item.Gender, item.Age, item.Province);
}
var fromWhereSelectResult2 = from a in personList
where a.Age > 23 && a.Age < 33//多条件筛选
select new {name=a.Name,age=a.Age };//===> 获取 部分类来获取person中的部分字段
foreach (var item in fromWhereSelectResult2)
{
Console.WriteLine("fromWhereSelectResult2 姓名：{0},年龄：{1}", item.name, item.age);
}
var fromWhereSelectResult3 = from a in complexList//complexList类型是List<int> a的数据类型就是 int[]
from b in a//可以使用多个from 从集合的集合中”遍历获取“
select b;//返回的事int的集合
foreach (var item in fromWhereSelectResult3)
{
Console.WriteLine("fromWhereSelectResult3" + item);
}
/*
【group 对查询结果按照键值进行分组(IGrouping<TKey,TElement>)；并不是什么类型都适合用var有些var也分辨不出来，解析成不是自己欲求的类型】
====Linq和sqlserver语法差不多，但是分组略有区别
sqlserver： select province from userinfo group by province 只会展示有多少个分组,获取详细信息还需要子查询
linq from a in userinfo group a by province 会把分组的信息放入 IGroup<key,value>键值对中能够把组下面的信息也获取到
*/
var groupResult = from a in personList
where a.Age > 20
group a by a.Province;
foreach (IGrouping<string,Person> it in groupResult)
{
Console.WriteLine("分组"+it.Key);
foreach (var item in it)
{
Console.WriteLine("groupResult 姓名：{0},性别：{1},年龄：{2},省份：{3}", item.Name, item.Gender, item.Age, item.Province);
}
}
/*
【into 提供一个标识符，它可以充当对join、group或select子句结果的引用;】
====join ，，， into intoVal intoVal里面存储的是两个源关联的 join后面的 数据的集合{不是所有数据，数据也可能重复取决于是否有关联}
【order by ascending升序 /descending降序，多个并列即可{order by a.Age ,a.Name先按照age进行排序，在age相同的情况下再按照Name排序}】
*/
var intoOrderByResult = from a in personList
select new { name = a.Name, age = a.Age,pro=a.Province } into intoVal
orderby intoVal.age ascending,intoVal.pro descending//多个排序条件
select intoVal;
foreach (var item in intoOrderByResult)
{
Console.WriteLine("intoOrderByResult 姓名：{0},年龄：{1},省份：{2}", item.name, item.age, item.pro);
}
/*
【let 产生一个用于存储查询表达式中的子表达式查询结果的范围变量】
*/
var letResult = from a in personList
let g = a.Age.ToString().Substring(0,1)
where g == "3"
select a;
foreach (var item in letResult.ToList())
{
Console.WriteLine("letResult 姓名：{0},性别：{1},年龄：{2},省份：{3}", item.Name, item.Gender, item.Age, item.Province);
}
/*
【join 按照两个指定匹配条件来Equals连接两个数据源】
*/
List<Person> persList = new List<Person>();
persList.Add(new Person() { Age = 31, Gender = '男', Name = "小编1", Province = "河南" });
persList.Add(new Person() { Age = 21, Gender = '男', Name = "小编2", Province = "河北" });
persList.Add(new Person() { Age = 25, Gender = '男', Name = "小编5", Province = "*" });
persList.Add(new Person() { Age = 25, Gender = '男', Name = "小编6", Province = "山东" });
persList.Add(new Person() { Age = 25, Gender = '男', Name = "小编7", Province = "江苏" });
persList.Add(new Person() { Age = 33, Gender = '男', Name = "小编3", Province = "河南" });
persList.Add(new Person() { Age = 22, Gender = '男', Name = "小编4", Province = "云南" });
List<Company> comList = new List<Company>();
comList.Add(new Company() { ComName = "A公司", PerName = "小编3" });
comList.Add(new Company() { ComName = "C公司", PerName = "小编4" });
comList.Add(new Company() { ComName = "A公司", PerName = "小编5" });
comList.Add(new Company() { ComName = "B公司", PerName = "小编1" });
comList.Add(new Company() { ComName = "B公司", PerName = "小编2" });
comList.Add(new Company() { ComName = "E公司", PerName = "小编100" });
//内连接：把两个源关联的展示出来；外链接：以谁为准把谁的数据全部展示，另外的源没有对应则呈现null {在sqlserver中以左链接和右链接的方式呈现；在linq语句中则以 into 的方式 into里面是关联的数据，select new{}可以 把关联的和不关联的都展示，模拟实现左右链接}
//模拟内连接 { 把关联的数据查询出来，两个源之间不能根据 equals条件关联的查询不出来 }
var joinResult = from a in persList
join
b in comList
on a.Name equals b.PerName
select new {Name=a.Name,CompName=b.ComName,Gender=a.Gender,Province=a.Province,Age=a.Age };
foreach (var item in joinResult)
{
Console.WriteLine("joinResult 姓名：{0},性别：{1},年龄：{2},省份：{3},公司：{4}", item.Name, item.Gender, item.Age, item.Province,item.CompName);
}
//模拟外链接 以person为准 没关联的呈现空
var joinResultOut = from a in persList
join
b in comList
on a.Name equals b.PerName into intoVal//intoVal这里存储的是 join后面的b所有关联的数据的集合{可以重复}
//【注意】 到这里为止，直接 select intoVal得到的还是 两个源相互关联的数据
from c in intoVal.DefaultIfEmpty()
select new { perInfo = a, comName = c == null ? "公司信息空" : c.ComName };//到这里，intoVal存的是关联的com信息，perInfo则是全部的per信息，包括两者关联的和不关联的，不关联的在 匿名类中以null的形式出现
foreach (var item in joinResultOut)
{
Console.WriteLine("以person为准 name:"+item.perInfo.Name+" age"+item.perInfo.Age+" gender:"+item.perInfo.Gender+" province:"+item.perInfo.Province+" compName"+item.comName);
}
//模拟外链接 以Company为准 没链接的呈现空,有关联的呈现
var joinResultOut2 = from b in comList
join
a in persList
on b.PerName equals a.Name into intoVal//intoVal这里存储的是 join后面的a所有关联的数据的集合{可以重复}
from c in intoVal.DefaultIfEmpty()
select new { compInfo = b, Name = c==null?"空":c.Name,Age=c==null?null:c.Age,Gender=c==null?null:c.Gender,Province=c==null?"空":c.Province };
foreach (var item in joinResultOut2)
{
Console.WriteLine("以Company为准 name:" + item.Name + " age" + item.Age + " gender:" + item.Gender + " province:" + item.Province + " compName" + item.compInfo.ComName);
}
Console.ReadKey();
}
}
public class Person
{
public int? Age { get; set; }
public string Name { get; set; }
public char? Gender { get; set; }
public string Province { get; set; }
}
public class Company
{
public string PerName { get; set; }
public string ComName { get; set; }
}
}
=============================
读这篇文章之前，我先说下，每一种搜索结果集，我都以三种方式变现出来，为啦更好的理解，希望不要嫌我啰嗦。
1.简单的linq语法
//1 var ss = from r in db.Am_recProScheme select r; //2 var ss1 = db.Am_recProScheme; //3 string sssql = "select * from Am_recProScheme";
2.带where的查询
//1 var ss = from r in db.Am_recProScheme where r.rpId > 10 select r; //2 var ss1 = db.Am_recProScheme.Where(p => p.rpId > 10); //3 string sssql = "select * from Am_recProScheme where rpid>10";
3.简单的函数计算（count，min，max，sum）
//1 ////获取最大的rpId //var ss = (from r in db.Am_recProScheme // select r).Max(p => p.rpId); ////获取最小的rpId //var ss = (from r in db.Am_recProScheme // select r).Min(p => p.rpId); //获取结果集的总数 //var ss = (from r in db.Am_recProScheme // select r).Count(); //获取rpId的和 var ss = (from r in db.Am_recProScheme select r).Sum(p =>p.rpId); //2 //var ss1 = db.Am_recProScheme.Max(p=>p.rpId); //var ss1 = db.Am_recProScheme.Min(p => p.rpId); //var ss1 = db.Am_recProScheme.Count() ; var ss1 = db.Am_recProScheme.Sum(p => p.rpId); Response.Write(ss); //3 string sssql ="select max(rpId) from Am_recProScheme"; sssql = "select min(rpId) from Am_recProScheme"; sssql = "select count(1) from Am_recProScheme"; sssql = "select sum(rpId) from Am_recProScheme";
4.排序order by desc/asc
var ss = from r in db.Am_recProScheme where r.rpId > 10 orderby r.rpId descending //倒序 // orderby r.rpId ascending //正序 select r; //正序 var ss1 = db.Am_recProScheme.OrderBy(p => p.rpId).Where(p => p.rpId > 10).ToList(); //倒序 var ss2 = db.Am_recProScheme.OrderByDescending(p => p.rpId).Where(p => p.rpId > 10).ToList(); string sssql = "select * from Am_recProScheme where rpid>10 order by rpId [desc|asc]";
5.top(1)
//如果取最后一个可以按倒叙排列再取值 var ss = (from r in db.Am_recProScheme select r).FirstOrDefault(); //（）linq to ef 好像不支持 Last() var ss1 = db.Am_recProScheme.FirstOrDefault(); //var ss1 = db.Am_recProScheme.First(); string sssql ="select top(1) * from Am_recProScheme";
6.跳过前面多少条数据取余下的数据
//1 var ss = (from r in db.Am_recProScheme orderby r.rpId descending select r).Skip(10); //跳过前10条数据，取10条之后的所有数据 //2 var ss1 = db.Am_recProScheme.OrderByDescending(p => p.rpId).Skip(10).ToList(); //3 string sssql = "select * from (select ROW_NUMBER()over(order by rpId desc) as rowNum, * from [Am_recProScheme]) as t where rowNum>10";
7.分页数据查询
//1 var ss = (from r in db.Am_recProScheme where r.rpId > 10 orderby r.rpId descending select r).Skip(10).Take(10); //取第11条到第20条数据 //2 Take(10): 数据从开始获取，获取指定数量（10）的连续数据 var ss1 = db.Am_recProScheme.OrderByDescending(p => p.rpId).Where(p => p.rpId > 10).Skip(10).Take(10).ToList(); //3 string sssql = "select * from (select ROW_NUMBER()over(order by rpId desc) as rowNum, * from [Am_recProScheme]) as t where rowNum>10 and rowNum<=20";
8.包含，类似like '%%'
//1 var ss = from r in db.Am_recProScheme where r.SortsText.Contains("张") select r; //2 var ss1 = db.Am_recProScheme.Where(p => p.SortsText.Contains("张")).ToList(); //3 string sssql = "select * from Am_recProScheme where SortsText like '%张%'";
9.分组group by
//1 var ss = from r in db.Am_recProScheme orderby r.rpId descending group r by r.recType into n select new { n.Key, //这个Key是recType rpId = n.Sum(r => r.rpId), //组内rpId之和 MaxRpId = n.Max(r => r.rpId),//组内最大rpId MinRpId = n.Min(r => r.rpId), //组内最小rpId }; foreach (var t in ss) { Response.Write(t.Key + "--" + t.rpId + "--" + t.MaxRpId + "--" +t.MinRpId); } //2 var ss1 = from r in db.Am_recProScheme orderby r.rpId descending group r by r.recType into n selectn; foreach (var t in ss1) { Response.Write(t.Key + "--" + t.Min(p => p.rpId)); } //3 var ss2 = db.Am_recProScheme.GroupBy(p => p.recType); foreach (var t in ss2) { Response.Write(t.Key + "--" + t.Min(p => p.rpId)); } //4 string sssql = "select recType,min(rpId),max(rpId),sum(rpId) from Am_recProScheme group by recType";
10.连接查询
//1 var ss = from r in db.Am_recProScheme join w in db.Am_Test_Result on r.rpId equals w.rsId orderby r.rpId descendingselect r; //2 var ss1 = db.Am_recProScheme.Join(db.Am_Test_Result, p => p.rpId, r => r.rsId, (p, r) => p).OrderByDescending(p => p.rpId).ToList(); //3 string sssql = "select r.* from [Am_recProScheme] as r inner join [dbo].[Am_Test_Result] as t on r.[rpId] = t.[rsId] order by r.[rpId] desc";
11.sql中的In
//1 var ss = from p in db.Am_recProScheme where (new int?[] { 24, 25,26 }).Contains(p.rpId) select p; foreach (var p inss) { Response.Write(p.Sorts); } //2 string st = "select * from Am_recProScheme where rpId in(24,25,26)";
