Linq排序、分组、模糊查询、调用外部方法、直接执行SQL语句、事务、修改数据

本文介绍Linq的基础用法,包括排序、分组、模糊查询等高级特性,并演示如何结合SQL语句实现复杂的数据操作。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

 

            //Linq语句
            NorthwindEntities db = new NorthwindEntities();
            //orderby子句
            var query = db.Customers.OrderBy(c => c.ContactName).OrderBy(c => c.CompanyName).ToList();
            //OrderByDescending:倒序排列
            query = db.Customers.OrderByDescending(c => c.ContactName).OrderByDescending(c => c.CompanyName).ToList();

            //分组就是把相同的东西放在一块
            var query1 = from q in db.Employees
                         group q by q.TitleOfCourtesy into yoo
                         select new {yoo.Key,yoo};

            //多分组条件:注意key就是一个匿名对象了,里面的字段就相当于是静态字段
            var query2 = from q in db.Products
                         group q by new { q.CategoryID, q.SupplierID }
                             into qq
                             select new {qq.Key,qq };

            foreach (var tmp in query2)
            {
                if (tmp.Key.CategoryID == 1)
                {
                    foreach (var tmp1 in tmp.qq)
                        Console.WriteLine(tmp.Key.CategoryID + "_" + tmp.Key.CategoryID + "\t" + tmp1.Suppliers.CompanyName);
                }
            }

            //判断集合是否为空
            var query3 = from q in db.Customers where q.Orders.Any(c => c.OrderID == 10905) select q;

            //模糊查询:Linq to SQL支持
            var query4 = from q in db.Customers where SqlMethods.Like(q.CustomerID, "A%") select q;

            //检查数据是否有值
            var query5 = from q in db.Employees where !q.BirthDate.HasValue select q;

            //获取指定日期数据
            var query6 = from q in db.Employees where q.BirthDate.Value.Day == 29 select q;

            //注意:去掉AsEnumerable(),就会报一下错误:LINQ to Entities
            //不识别方法“Boolean IsLondon(System.String)”,因此该方法无法转换为存储表达式。

            //解释:IQueryable:提供对数据类型已知的特定数据源的查询进行计算的功能。实体框架集
            //会尝试将LINQ解析成SQL放到数据库服务器上执行,然而,外部方法不能转换成SQL,于是报
            //上面的错误。解决方式就是转换成客户端泛型AsEnumerable<T>
            var query7 = from q in db.Employees where IsLondon(q.City) select q;

            //直接执行SQL语句
            string strSQL="DELETE FROM dbo.Employees WHERE EmployeeID=20";
            //var query8 = db.ExecuteStoreCommand(strSQL, null);
            //db.SaveChanges();
            strSQL = "SELECT * FROM dbo.Employees";
            var query9 = db.ExecuteStoreQuery<Employees>(strSQL, null);

            foreach (var tmp in query9)
            {
                Console.WriteLine(tmp.EmployeeID);
            }
            Console.ReadKey();
        }

        static bool IsLondon(string city)
        {
            if (city == "London")
                return true;
            else
                return false;
        }

        static string SetString(string kk)
        {
            return kk + "__yoyo";
        }

        static void UpdateData()
        {
            NorthwindEntities db = new NorthwindEntities();
            //游离对象使用Attach方法进行批量修改的前后顺序
            Employees tmpEmp = new Employees();
            tmpEmp.EmployeeID = 11;
            db.Employees.Attach(tmpEmp);
            tmpEmp.LastName = "shaoshao";
            db.SaveChanges();

            //本身就在数据上下文中的对象修改方式
            //此处,是包含隐式事务的
            try
            {
                tmpEmp = (from q in db.Employees where q.EmployeeID == 11 select q).FirstOrDefault();
                tmpEmp.LastName = "yoyozhu";
                tmpEmp.ReportsTo = 2;
                tmpEmp = (from q in db.Employees where q.EmployeeID == 19 select q).FirstOrDefault();
                tmpEmp.LastName = "yoyo____zhu";
                tmpEmp.ReportsTo = 2;
                db.SaveChanges();
            }
            catch (Exception ex)
            {
            }
        }

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值