DataBase First 基础(二)：Linq语句增删改查(SQL数据库用Like语句来实现增删改查)、单表模糊查询、多表模糊查询

最新推荐文章于 2023-02-02 08:39:50 发布

我爱AI

最新推荐文章于 2023-02-02 08:39:50 发布

阅读量524

点赞数 1

分类专栏： ---------- 【SQL Server】

本文链接：https://blog.csdn.net/xpj8888/article/details/86636522

版权

---------- 【SQL Server】专栏收录该内容

24 篇文章 5 订阅

订阅专栏

一、简介

我们建立了数据库文件（包含了各种数据）后，然后导入到EF实体数据库模型中。这时候，我们就可以直接在EF实体数据库模型中操作数据库了。而我们用到最多便是用Linq语句来查找数据表。

下面重点介绍查找语句。我们接着上篇博客继续写，来操作一个Customers表。

工程目录：

我们建立的是WPF工程，后添加EF数据。所以，我们的工程很简单，只包含了一个MainWindow.xaml.cs文件.

MainWindow.xaml.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace WpfApplication1
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
            AddFunc();
        }

        /// <summary>
        /// 增加
        /// </summary>
        public void AddFunc()
        {
            //数据库实体
            Entities2 dataBase = new Entities2();
            //数据表对应的类
            Customers customers = new Customers();
            //添加数据
            customers.CustID = 11;
            customers.FirstName = "FirstName11";
            customers.LastName =  "LastName11";
            //往数据库的Customers表添加一条数据Customers
            dataBase.Customers.Add(customers);
            //更改数据库
            dataBase.SaveChanges();
        }

        /// <summary>
        /// 删除
        /// </summary>
        public void DeleteFunc()
        {

        }

        /// <summary>
        /// 编辑
        /// </summary>
        public void EditFunc()
        {

        }
    }
}

二、linq语句

参加:https://blog.csdn.net/suneqing/article/details/41964757

1、增加

        public void AddFunc()
        {
            //数据库实体
            Entities2 dataBase = new Entities2();
            //数据表对应的类
            Customers customers = new Customers();
            //添加数据
            customers.CustID = 11;
            customers.FirstName = "FirstName11";
            customers.LastName =  "LastName11";
            //往数据库的Customers表添加一条数据Customers
            dataBase.Customers.Add(customers);
            //更改数据库
            dataBase.SaveChanges();
        }

效果图（多了一行数据）：

2、删除

方法1：删除单条记录

我们根据主键（可以只要主键，就可以删除所有的数据），删除第一行的数据。

   /// <summary>
        /// 删除
        /// </summary>
        public static void DeleteFunc()
        {
            //数据库实体
            AutoLotEntities dataBase = new AutoLotEntities();
            //数据表对应的类
            Customers customers = new Customers();
            //删除CustID == 2的这条数据
            customers.CustID = 2;
            //customers.FirstName = "Dave";
            //customers.LastName = "B1";
            //往数据库的Customers表删除一条数据
            dataBase.Entry<Customers>(customers).State = System.Data.Entity.EntityState.Deleted;
            //更改数据库
            dataBase.SaveChanges();
        }

删除前：

删除后（删除第二行）：

方法2：删除所有记录

以下方法可以删除所有条目。

var list2 = dataBase.StudySeries_Table.Where(p => p.SeriesKey >= 0 ).ToList();
    foreach (var item in list2)
    {
        dataBase.StudySeries_Table.Remove(item);
    }
    dataBase.SaveChanges();

3、修改（必须包含主键以及其他属性）

        /// <summary>
        /// 编辑
        /// </summary>
        public static void EditFunc()
        {
            //数据库实体
            AutoLotEntities dataBase = new AutoLotEntities();
            //数据表对应的类
            Customers customers = new Customers();
            customers.CustID = 1;
            customers.FirstName = "DaveDaveDave";
            customers.LastName = "B1B1B1";
            dataBase.Entry<Customers>(customers).State = System.Data.Entity.EntityState.Modified;
            //更改数据库
            dataBase.SaveChanges();
        }

修改后：

4、查询

4.1、简单查询

参考：

在这里，我为什么叫做简单查询？难道与或等操作，满足多个条件的查询，不叫做复杂查询吗？显然，不叫做复杂的查询。我们先把举个简单的查询：

public static void Search()
        {
            //数据库实体
            AutoLotEntities dataBase = new AutoLotEntities();
            //数据表对应的类
            Customers customers = new Customers();

            #region 方法1：Linq语句  
            //Ling语句查询数据，其中bbb的名字随便起。
            var item = from bbb in dataBase.Customers
                       where bbb.CustID == 4 || bbb.CustID == 2
                       select bbb;
            //输出表item
            foreach (var customer in item)
            {
                Console.WriteLine(customer.CustID  + "  " +  customer.FirstName + "  " + customer.LastName);
            }
            Console.WriteLine();
            #endregion

            #region 方法2：lambda表达式查询  
            var itemLambda = dataBase.Customers.Where<Customers>(cccc => (cccc.CustID == 22 )).FirstOrDefault();//显示单条数据
            Console.WriteLine(itemLambda.CustID + "  " + itemLambda.FirstName + "  " + itemLambda.LastName);
            Console.WriteLine();
            #endregion

            Console.ReadLine();
        }

输出结果：

4.2、复杂查询

4.2.1、单表的复杂查询（包含关键字的模糊查询）

参考:https://www.cnblogs.com/sxw117886/p/5718367.html

我对复杂查询的定义，如下表所示：

我要输入“Dav”关键字，就能检索出第一条目录。

我要输入“Firs”关键字，就能检索出第四、五条目录。

我要输入

那么怎么做到呢？我们直接看代码和结果：


        /// <summary>
        /// 查询
        /// </summary>
        public static void Search()
        {
            //数据库实体
            AutoLotEntities dataBase = new AutoLotEntities();
            //数据表对应的类
            Customers customers = new Customers();
            Inventory inventory = new Inventory();

            #region 方法一、Linq语句
            var item2 = (from DDD in dataBase.Customers where (DDD.FirstName.Contains("First")) select new { myFirstName = DDD.FirstName });
            var item3 = (from DDD in dataBase.Customers where (DDD.FirstName.Contains("First") || DDD.FirstName.Contains("Dav")) select new{ myFirstName = DDD.FirstName });
            var item4 = (from DDD in dataBase.Customers where (DDD.FirstName.Contains("First") || DDD.FirstName.Contains("Dav")) select new { myCustID = DDD.CustID, myFirstName = DDD.FirstName, myLastName = DDD.LastName });
            foreach (var customerVar in item4)
            {
                /* myCustID、myFirstName、myLastName 是系统自己生成的属性 */
                Console.WriteLine(customerVar.myCustID + "  " + customerVar.myFirstName + "  " + customerVar.myLastName);
            }
            Console.WriteLine();
            #endregion
            Console.ReadLine();
        }

输出结果：

4.2.2、多表的复杂查询（包含关键字的模糊查询）

我有两个表如下，我要输出满足两个搜索关键字“First”、“red”，然后输出组合搜索结果。

   /// <summary>
        /// 查询
        /// </summary>
        public static void Search()
        {
            //数据库实体
            AutoLotEntities dataBase = new AutoLotEntities();
            //数据表对应的类
            Customers customers = new Customers();
            Inventory inventory = new Inventory();

            #region 三、复杂查询：多表查询
            #region 方法1: Linq语句
            //模糊检索条件
            string str1 = "4";
            string str2 = "First";
            string str3 = "Dav";
            string strColor = "red";

            /* 形式1 == 形式2 */
            //形式1
            var item1 = from DDD in dataBase.Customers
                        where (DDD.FirstName.Contains(str2))
                        from EEE in dataBase.Inventory
                        where ( EEE.Color.Contains(strColor))
                        select new { myCustID = DDD.CustID, myFirstName = DDD.FirstName, myLastName = DDD.LastName, myColor = EEE.Color};
            //形式2
            var item2 = from DDD in dataBase.Customers
                        from EEE in dataBase.Inventory
                        where (DDD.FirstName.Contains(str2) && EEE.Color.Contains(strColor))
                        select new { myCustID = DDD.CustID, myFirstName = DDD.FirstName, myLastName = DDD.LastName, myColor = EEE.Color };
            foreach (var customerVar in item1)
            {
                Console.WriteLine(customerVar.myCustID + "  " + customerVar.myFirstName + "  " + customerVar.myLastName  + "  " + customerVar.myColor);
            }
            Console.WriteLine();
            #endregion

            #region 方法2:lambda
            /**/
            #endregion
            #endregion
            Console.ReadLine();
        }

输出结果：

要是我们把 &&改为 || ：

     var item2 = from DDD in dataBase.Customers
                        from EEE in dataBase.Inventory
                        where (DDD.FirstName.Contains(str2) || EEE.Color.Contains(strColor))
                        select new { myCustID = DDD.CustID, myFirstName = DDD.FirstName, myLastName = DDD.LastName, myColor = EEE.Color };
            foreach (var customerVar in item2)
            {
                Console.WriteLine(customerVar.myCustID + "  " + customerVar.myFirstName + "  " + customerVar.myLastName  + "  " + customerVar.myColor);
            }
            Console.WriteLine();

输出结果：

三、总结

1、注意添加引用

2、 SourceCode

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

namespace OperateDatabase2
{
    class Program
    {
        static void Main(string[] args)
        {
            //AddFunc();
            //Search();
            EditFunc();
            //DeleteFunc();
        }

        /// <summary>
        /// 增加
        /// </summary>
        public static void AddFunc()
        {
            //数据库实体
            AutoLotEntities dataBase = new AutoLotEntities();
            //数据表对应的类
            Customers customers = new Customers();
            //添加数据
            customers.CustID = 44;
            customers.FirstName = "F123";
            customers.LastName = "L7fa";
            //往数据库的Customers表添加一条数据Customers
            dataBase.Customers.Add(customers);
            //更改数据库
            dataBase.SaveChanges();
        }

        /// <summary>
        /// 删除
        /// </summary>
        public static void DeleteFunc()
        {
            //数据库实体
            AutoLotEntities dataBase = new AutoLotEntities();
            //数据表对应的类
            Customers customers = new Customers();
            //删除CustID == 2的这条数据
            customers.CustID = 2;
            //customers.FirstName = "Dave";
            //customers.LastName = "B1";
            //往数据库的Customers表删除一条数据
            dataBase.Entry<Customers>(customers).State = System.Data.Entity.EntityState.Deleted;
            //更改数据库
            dataBase.SaveChanges();
        }

        /// <summary>
        /// 编辑
        /// </summary>
        public static void EditFunc()
        {
            //数据库实体
            AutoLotEntities dataBase = new AutoLotEntities();
            //数据表对应的类
            Customers customers = new Customers();
            customers.CustID = 44;
            //customers.FirstName = "DaveDaveDave";
            customers.LastName = "666666";
            dataBase.Entry<Customers>(customers).State = System.Data.Entity.EntityState.Modified;
            //更改数据库
            dataBase.SaveChanges();
        }

        /// <summary>
        /// 查询
        /// </summary>
        public static void Search()
        {
            //数据库实体
            AutoLotEntities dataBase = new AutoLotEntities();
            //数据表对应的类
            Customers customers = new Customers();
            Inventory inventory = new Inventory();
            #region 一、简单查询
            //#region 方法1：Ling语句  
            Ling语句查询数据，其中bbb的名字随便起。
            //var item = from bbb in dataBase.Customers
            //           where bbb.CustID == 4 || bbb.CustID == 2
            //           select bbb;
            输出表item
            //foreach (var customer in item)
            //{
            //    Console.WriteLine(customer.CustID  + "  " +  customer.FirstName + "  " + customer.LastName);
            //}
            //Console.WriteLine();
            //#endregion

            //#region 方法2：lambda表达式查询  
            //var itemLambda = dataBase.Customers.Where<Customers>(cccc => (cccc.CustID == 22 )).FirstOrDefault();//显示单条数据
            //Console.WriteLine(itemLambda.CustID + "  " + itemLambda.FirstName + "  " + itemLambda.LastName);
            //Console.WriteLine();
            //#endregion
            #endregion

            #region 二、复杂查询：单表查询
            #region  方法1、Linq语句
            模糊检索条件
            //string str1 = "4";
            //string str2 = "First";
            //string str3 = "Dav";

            DDD.CustID.ToString().Contains("4"))  表示将整数转换为字符串，以便进行模糊检索
            //var item1 = (from DDD in dataBase.Customers where (DDD.CustID.ToString().Contains(str1)) select new { myCustID = DDD.CustID, myFirstName = DDD.FirstName, myLastName = DDD.LastName });
            //var item2 = (from DDD in dataBase.Customers where (DDD.FirstName.Contains(str2) || DDD.FirstName.Contains(str3) || DDD.CustID.ToString().Contains(str1)) select new { myCustID = DDD.CustID, myFirstName = DDD.FirstName, myLastName = DDD.LastName });
            //foreach (var customerVar in item1)
            //{
            //    /* myCustID、myFirstName、myLastName 是系统自己生成的属性 */
            //    Console.WriteLine(customerVar.myCustID + "  " + customerVar.myFirstName + "  " + customerVar.myLastName);
            //}
            //Console.WriteLine();
            #endregion

            #region  方法2、lambda语句
            /**/
            #endregion
            #endregion


            #region 三、复杂查询：多表查询
            #region 方法1: Linq语句
            //模糊检索条件
            string str1 = "4";
            string str2 = "First";
            string str3 = "Dav";
            string strColor = "red";

            /* 形式1 == 形式2 */
            //形式1
            var item1 = from DDD in dataBase.Customers
                        where (DDD.FirstName.Contains(str2))
                        from EEE in dataBase.Inventory
                        where ( EEE.Color.Contains(strColor))
                        select new { myCustID = DDD.CustID, myFirstName = DDD.FirstName, myLastName = DDD.LastName, myColor = EEE.Color};
            //形式2
            var item2 = from DDD in dataBase.Customers
                        from EEE in dataBase.Inventory
                        where (DDD.FirstName.Contains(str2) && EEE.Color.Contains(strColor))
                        select new { myCustID = DDD.CustID, myFirstName = DDD.FirstName, myLastName = DDD.LastName, myColor = EEE.Color };
            foreach (var customerVar in item2)
            {
                Console.WriteLine(customerVar.myCustID + "  " + customerVar.myFirstName + "  " + customerVar.myLastName  + "  " + customerVar.myColor);
            }
            Console.WriteLine();
            #endregion

            #region 方法2:lambda
            /**/
            #endregion
            #endregion
            Console.ReadLine();
        }
    }
}

我爱AI

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
DataBase First 基础(二)：Linq语句增删改查(SQL数据库用Like语句来实现增删改查)、单表模糊查询、多表模糊查询

一、简介我们建立了数据库文件（包含了各种数据）后，然后导入到EF实体数据库模型中。这时候，我们就可以直接在EF实体数据库模型中操作数据库了。而我们用到最多便是用Linq语句来查找数据表。下面重点介绍查找语句。我们接着上篇博客继续写，来操作一个Customers表。工程目录：我们建立的是WPF工程，后添加EF数据。所以，我们的工程很简单，只包含了一...
复制链接

扫一扫