EF数据持久化笔记-2-linq

LINQ

关键字
from 指定范围变量和数据源
where 根据bool表达式筛选数据
select 指定查询结果中的元素所具有的类型或表现形式
group 对查询结果分组
into 根据一个标识符，对join，group，select等结果子句的引用
orderby 对查询的结果进行排序
join 按照两个指定匹配条件连接数据源
let 产生一个用于存储子表达式查询结果的范围变量

写法结构
from [type] id in source
[join [type] id in source on expr equals expr [into subGroup]]
[from [type] id in source | let id = expr |where condition]
[orderby ordering,orderint,orderint…]
select expr | group expr by key
[into id query]

案例

			List<string> list = new List<string>();
            list.Add("111");
            list.Add("123");
            list.Add("124");
            list.Add("567");
            list.Add("89");
            //List<int> lists = list.Where(num=>num%2==0).ToList();
            var lists = (from num in list where num.Contains("1")|num=="89" select num).ToList();
            foreach (var item in lists)
            {
                Console.WriteLine(item);
            }

新建girl类

        static void Main(string[] args)
        {
            List<girl> list = Default();
            //全部
            List<girl> l = (from girl in list select girl).ToList();
            foreach (var item in l)
            {
                Console.WriteLine(item.id+item.name+item.age+item.text);
            }
            Console.WriteLine("---------------------------------------");
            //精准查询
            //SingleOrDefault()返回集合第一个
            girl g = (from girl in list where girl.id == 1 select girl).SingleOrDefault();
            Console.WriteLine(g.id+g.name);
            Console.WriteLine("---------------------------------------");
            //模糊查询
            var lists = (from girl in list where girl.name.Contains("1") select girl).ToList();
            foreach (var item in lists)
            {
                Console.WriteLine(item.id + item.name + item.age + item.text);
            }
            Console.WriteLine("---------------------------------------");
            //范围
            var newlist = (from girl in list where girl.id>=2&girl.id<=4 select girl).ToList();
            foreach (var item in newlist)
            {
                Console.WriteLine(item.id + item.name + item.age + item.text);
            }
            Console.WriteLine("---------------------------------------");
            //排序
            var newlists = (from girl in list orderby girl.age descending select girl).ToList();
            foreach (var item in newlists)
            {
                Console.WriteLine(item.id + item.name + item.age + item.text);
            }
            Console.WriteLine("---------------------------------------");
            //分组
            var list1 = (from girl in list group girl by girl.age into o select new {age = o.Key,count = o.Count() }).ToList();
            foreach (var item in list1)
            {
                Console.WriteLine(item.age +":"+ item.count);
            }
            Console.WriteLine("---------------------------------------");
            //分页
            int pagenow = 1;//页数
            int pagecount = 2;//每页条数
            int now = (pagenow - 1) * pagecount;//每页初始值
            var list2 = (from girl in list select girl).Skip(now).Take(pagecount).ToList();
            foreach (var item in list2)
            {
                Console.WriteLine(item.id + ":" + item.name);
            }
            Console.WriteLine("---------------------------------------");
        }
        public static List<girl> Default()
        {
            return new List<girl> { 
                new girl(1,"1",1,"1"),
                new girl(2,"2",2,"2"),
                new girl(3,"3",3,"3"),
                new girl(4,"4",4,"4"),
                new girl(5,"5",5,"5")
            };
        }

连表查询
新建一个类

		public static List<boy> Defaultboy()
        {
            return new List<boy> {
                new boy(1,"11",1,"18648"),
                new boy(2,"21",2,"2486"),
                new boy(3,"31",3,"38464"),
                new boy(4,"41",4,"4878"),
                new boy(5,"51",5,"54531587")
            };
        }
        public static List<girl> Defaultgirl()
        {
            return new List<girl> {
                new girl(1,"11",1,"14534"),
                new girl(2,"21",2,"28434"),
                new girl(3,"31",3,"38486"),
                new girl(4,"41",4,"44864"),
                new girl(5,"51",5,"58464")
            };
        }

查询

			List<girl> list1 = Defaultgirl();
            List<boy> list2 = Defaultboy();
            //两表联合查询
            var lists = (from b in list2
                         join g in list1
                         on b.id equals g.id
                         where b.name.Contains("1")
                         orderby b.id descending
                         select new { bname = b.name, gname = g.name, btext = b.text, gtext = g.text }).ToList().Skip(0).Take(2);
            foreach (var item in lists)
            {
                Console.WriteLine(item.bname+"||"+item.gname);
                Console.WriteLine(item.btext + "||" + item.gtext);
            }

-----------------------------------------------------------------------------------------------
聚合函数运算符
count()计数
longcount()大计数
max()最大值
min()最小值
sum()总数
average()平均值

元素操作运算符方法
Elementat()返回集合中指定索引处元素
ElementatorDefault()返回指定处元素，若超范围则返回默认值
First()返回满足条件的第一个元素
FirstOrDefault()返回满足条件的第一个元素，若没有则返回默认值
Last()返回最后一个元素
LastOrDefault()返回最后一个元素，若没有则返回默认值
Single()返回满足条件的唯一元素
SingleOrDefault()返回满足条件的唯一元素，若没有则返回默认值

数据类型转换运算符方法
ToList()将集合转换为list
ToArray()将集合转换为数组
ToDictionary()根据键选择器将元素放到Dictionary中
ToLookup()根据键选择器将元素放到Lookup中
AsEnumerable()将序列转换为Enumerable集合
AsQueryable()将Enumerable转换为Queryable
Cast()将集合元素强转为指定类型
OfType()根据值强制转换为指定类型的能力筛选值