LINQ查询

1.LINQ基本语法

 string[] nameList = new string[5] { "张1", "张2", "张3", "李四", "王五" };

            //基本查询语句  from  实例名 in  数组或表名 select 实例名
            var name = from n in nameList select n;
            foreach (var s in name)
            {
                Console.WriteLine("姓名:{0}", s);
            }

            Console.WriteLine("\n\n");

            //where筛选条件
            name = from n in nameList where n == "张1" select n;
            foreach (var s in name)
            {
                Console.WriteLine("姓名:{0}", s);
            }

            Console.WriteLine("\n\n");


            //或运算符||  &&  and or
            name = from n in nameList where n == "张1" || n=="李四" select n;
            foreach (var s in name)
            {
                Console.WriteLine("姓名:{0}", s);
            }

 

 

2.orderby字句排序

enum sex
    {
        男, 女
    }
    class Student
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public sex Sex { get; set; }
    }
    class Program
    {
        static void Main(string[] args)
        {
            List<Student> students = new List<Student>
            {
                new Student{ID=2,Name="李四",Sex=sex.女},
                new Student{ID=3,Name="王五",Sex=sex.女},
                new Student{ID=1,Name="张三",Sex=sex.男},
                new Student{ID=4,Name="赵六",Sex=sex.女}
            };
            var student = from s in students orderby s.ID select s;         //orderby 列名            升序
            foreach (var s in student)
            {
                Console.WriteLine("姓名：{0}\t性别：{1}\t学号：{2}", s.Name, s.Sex, s.ID);
            }

            student = from s in students orderby s.ID descending select s;  //orderby  列名 descending  降序

        }
    }

 

3.group字句分组

enum sex
    {
        女,男
    }
    class Student
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public sex Sex { get; set; }
    }
    class Program
    {
        static void Main(string[] args)
        {
            List<Student> students = new List<Student>
            {
                new Student{ID=1,Name="李四",Sex=sex.男},
                new Student{ID=2,Name="王五",Sex=sex.女},
                new Student{ID=3,Name="张三",Sex=sex.女},
                new Student{ID=4,Name="赵六",Sex=sex.男}
            };

            var student = from s in students orderby s.ID descending group s by s.Sex;     //依照 列名进行分组排列
            foreach (var sKey in student)            //遍历 查询出的student里的sex
            {
                Console.WriteLine(sKey.Key);       
                foreach (Student stu in sKey)       //遍历  查询出的student信息
                {
                    Console.WriteLine("姓名：{0}\t性别：{1}\t学号：{2}", stu.Name, stu.Sex, stu.ID);
                }
            }
        }
    }

 

4.join子句的内部连接

    class Student
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public int ClassID { get; set; }
    }
    class MyClass
    {
        public int ClassID { get; set; }
        public string ClassName { get; set; }
    }
    class Program
    {
        static void Main(string[] args)
        {
            List<Student> students = new List<Student>
            {
               new Student(){ID=1,Name="张三",ClassID=101},
               new Student(){ID=2,Name="李四",ClassID=102},
               new Student(){ID=3,Name="王五",ClassID=101},
            };
            Student zl = new Student() { ID = 4, Name = "赵六", ClassID = 102 };
            students.Add(zl);

            List<MyClass> myclass = new List<MyClass>
            {
                new MyClass(){ClassID=101,ClassName="一零一班"},
                new MyClass(){ClassID=102,ClassName="一零二班"}
            };
            //join 多表查询
            var innerjoin = from s in students
                            join m in myclass on s.ClassID equals m.ClassID     //=用equals取代
                            //orderby s.ID descending                             //排序降序
                            select new { StuID = s.ID, StuName = s.Name, StuClassName = m.ClassName}; //相当于重新命令列名
            foreach (var v in innerjoin)
            {
                Console.WriteLine("学号：{0}\t姓名：{1}\t班级：{2}", v.StuID, v.StuName, v.StuClassName);
            }


            //join添加分组查询         就是在多表集合查询中再添加一个新的组来查询组的组里面的内容
            var groupjoin = from m in myclass
                            join s in students on m.ClassID equals s.ClassID
                            into mGroup                                     //新添分组
                            select new { Myclass = m.ClassName, MyGroup = mGroup };
            foreach (var MyclassGroup in groupjoin)
            {
                Console.WriteLine("班级：{0}", MyclassGroup.Myclass);
                foreach (var item in MyclassGroup.MyGroup)
                {
                    Console.WriteLine("姓名：{0}\t班级：{1}\t学号：{2}", item.Name, item.ClassID, MyclassGroup.Myclass);
                }
            }
            //左外部连接             和数据库的一样是为了防止没有的数据不显示，所以还要设置默认值
            var leftOuterText =
                from m in myclass
                join s in students on m.ClassID equals s.ClassID
                into ClassGroup
                select new
                {
                    ClassName = m.ClassName,
                    MyclassGroup = ClassGroup.DefaultIfEmpty(new Student { ID = 1, Name = "无", ClassID = m.ClassID })
                };

            foreach (var classGrouping in leftOuterText)
            {
                Console.WriteLine("班级：{0}", classGrouping.ClassName);
                foreach (var item in classGrouping.MyclassGroup)
                {
                    Console.WriteLine("学号：{0}\t姓名：{1}\t班级：{2}", item.ID, item.Name, classGrouping.ClassName);
                }
            }

        }
    }

 

5.let子句的运用

 

class Program
    {
        static void Main(string[] args)
        {
            string[] strings = { "1", "2", "5", "3", "9", "7", "4" };       
            var linqQuery = from s in strings                   //感觉这个let就是用来转型用的
                            let num = Convert.ToInt32(s)
                            where num > 4
                            orderby num descending
                            select num;
            foreach (var v in linqQuery)
            {
                Console.WriteLine(v);
            }
        }
    }

 

6.LINQ to Object

 class Program
    {
        static void Main(string[] args)
        {
            string[] strings = { "1", "2", "5", "3", "9", "7", "4" };       
            var linqQuery = from s in strings                   //感觉这个let就是用来转型用的
                            let num = Convert.ToInt32(s)
                            where num > 4
                            orderby num descending
                            select num;
            foreach (var v in linqQuery)
            {
                Console.WriteLine(v);
            }
        }
    }

 

7.LINQ TO SQL

 (就是用LINQ语言对数据库的增删改查操作，不过要先新建一个LINQ to SQL类项，并将数据库进行连接，在增删改查完成后用SubmitChanges()方法进行确认)

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace LINQ_to_SQL
{

    public partial class Form1 : Form
    {
        DataClasses1DataContext db = new DataClasses1DataContext();
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //多表查询
            dataGridView1.DataSource = from c in db.Class
                                       join s in db.Student on c.ClassId equals s.ClassId
                                       orderby c.ClassId 
                                       select new { 班级编号 = c.ClassId, 班级名称 = c.ClassName, 学生编号 = s.StudentId, 学生姓名 = s.StudentName };
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //添加数据
            Class c1 = new Class
            {
                ClassId = 54,
                ClassName = "NIIT",
                GradeId = 1
            };

            db.Class.InsertOnSubmit(c1);
            try
            {
                db.SubmitChanges();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //删除数据
            var deletText = from c in db.Class
                            where c.ClassName == "NIIT"
                            select c;

            //如果是单个数据用First
            deletText.First().ClassName = "Java班";

            //如果是多个数据用遍历
            //foreach (var d in deletText)
            //{
            //    db.Class.DeleteOnSubmit(d);
            //}
        }

        private void button3_Click(object sender, EventArgs e)
        {
            //更新数据
            var update = from c in db.Class
                         where c.ClassId == 20
                         select c;

            //如果是单个数据用First()
            update.First().ClassName = "高三十班";


            //如果是多个数据用遍历
            //foreach (var v in update)
            //{
            //    v.ClassName = "高三十班";
            //}
            try
            {
                db.SubmitChanges();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            //刷新  重绑数据
            dataGridView1.DataSource=from c in db.Class
                                     select c;
        } 

    }
}