C#FE(Entity Framework)框架使用--CodeFirst模式

第一步：创建ado.net实体数据模型

第二步:选择模型内容(来自数据库的EF设计器)

三．等待20秒左右完成对象创建：

四.数据模型操作方式：

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

namespace EF_Demo_01
{
    class Program
    {
        //EntityFramework (EF)ORM框架 数据库优先  
        static void Main(string[] args)
        {
            //int? num1 = null;
            //Nullable<int> num2 = null;
            //Console.WriteLine(num1.GetType());
            //Console.WriteLine(num2.GetType());

            //EF相当于强大DBHelper
            //AddProduct();//添加产品
            //DeleteProduct();//删除产品
            //ModifyProduct();//修改产品
            //ModifyProductList();//修改产品

            //表联接 外联接
            // GetProductInclude();
            //GetProductListInclude();
            //GetProductListInclude2();

            //内联接 inner join
            //GetProductJoin1();
            //GetProductJoin2();

            //Execute();//执行添加删除修改
            //ExecuteScalar();//查询单行单列
            ExecuteReader();//查询返回多行多列

            //GetProductProc();
        }

        #region EF 添加、删除、修改、简单查询(单表)
        private static void AddProduct()
        {
            Product product = new Product()
            {
                ProductName = "测试产品1",
                Price = 2000,
                Description = "产品测试描述信息",
                TypeId = 2,
                ImageUrl = "images/xxx.jpg"
            };
            //带事务处理机制 添加   对象关系映射
            using (TAOBAODBEntities taobao = new TAOBAODBEntities())
            {
                taobao.Product.Add(product);
                if (taobao.SaveChanges() > 0)
                {
                    Console.WriteLine("添加成功");
                }
            }
        }

        //CRM  3种状态  IsValid 1 正常 0 垃圾箱 -1 废弃箱 update 物理删除 
        private static void DeleteProduct()
        {
            //传进来的编号,真实项目一般不会做物理删除
            int productNo = 1005;
            using (TAOBAODBEntities taobao = new TAOBAODBEntities())
            {
                Product product = taobao.Product.Single<Product>
                    (p => p.ProductNo == productNo);
                taobao.Product.Remove(product);//对象
                if (taobao.SaveChanges() > 0)//提交持久化 commit
                {
                    Console.WriteLine("删除成功");
                }
            }
        }

        /// <summary>
        /// 修改产品
        /// </summary>
        private static void ModifyProduct()
        {
            using (TAOBAODBEntities taobao = new TAOBAODBEntities())
            {
                Product product = taobao.Product.FirstOrDefault(p => p.ProductName == "P20pro");

                product.Price = 2500;
                product.Description = "测试备注信息";

                if (taobao.SaveChanges() > 0)//提交持久化 commit
                {
                    Console.WriteLine("修改成功");
                }
            }
        }

        /// <summary>
        /// 批量修改产品
        /// </summary>
        private static void ModifyProductList()
        {
            using (TAOBAODBEntities taobao = new TAOBAODBEntities())
            {
                List<Product> productList = taobao.Product.Where(p => p.TypeId == 1).ToList<Product>();
                for (int i = 0; i < productList.Count; i++)
                {
                    productList[i].Price -= 5;
                }
                //foreach (var item in collection)//只读迭代只读循环,执行效率比较快
                //{
                //}
                if (taobao.SaveChanges() > 0)//提交持久化 commit
                {
                    Console.WriteLine("批量修改成功");
                }
            }
        }

        #endregion

        #region 跨表查询(表联接)  Include
        private static void GetProductInclude()
        {
            using (TAOBAODBEntities taobao = new TAOBAODBEntities())
            {
                Product product = taobao.Product.Include("ProductType")
                                    .Where(p => p.ProductNo == 1002).Single<Product>();
                Console.WriteLine($"{product.ProductName}-{product.ProductType.TypeName}");
            }
        }
        private static void GetProductListInclude()
        {
            using (TAOBAODBEntities taobao = new TAOBAODBEntities())
            {
                var list = taobao.Product.Include("ProductType")
                                     .Where(p => p.TypeId == 1);
                List<Product> productList = list.OrderBy(p => p.TypeId).ToList();//asc升序
                //聚合函数：统计 求平均 求最大 求最小 求和
                Console.WriteLine(list.ToList().Max(p => p.Price));
                foreach (var product in productList)
                {
                    Console.WriteLine($"{product.ProductName}-{product.ProductType.TypeName}");
                }
            }
        }
        private static void GetProductListInclude2()
        {
            using (TAOBAODBEntities taobao = new TAOBAODBEntities())
            {
                //集合刷1  对象刷2
                ProductType productType = taobao.ProductType.Include("Product")
                                     .Where(t => t.TypeId == 1).Single<ProductType>();

                Console.WriteLine($"类型：{productType.TypeName}");//一对多关系
                foreach (var product in productType.Product)
                {
                    Console.WriteLine($"{product.ProductName}-{product.ProductNo}");
                }

            }
        }

        #endregion

        #region 跨表查询  Join 内联接
        private static void GetProductJoin1()
        {
            using (TAOBAODBEntities taobao = new TAOBAODBEntities())
            {
                //select * from product a inner join producttype b on(a.typeid=b.typeid)
                var productList = taobao.Product.Join
                       (
                       taobao.ProductType,
                       a => a.TypeId,
                       b => b.TypeId,
                       (a, b) => new { a.ProductNo, a.ProductName, a.Price, b.TypeName }//投影
                       ).ToList();

                Console.WriteLine($"类型:{productList.GetType()}");
                //var   dynamic
                foreach (var product in productList)
                {
                    Console.WriteLine(product.ProductName + "-" + product.TypeName);
                }
            }
        }

        private static void GetProductJoin2()
        {
            //using (TAOBAODBEntities taobao = new TAOBAODBEntities())
            //{
            //    //linq to sql 查询表达式
            //    List<Product> productList = (from a in taobao.Product
            //                                 join b in taobao.ProductType
            //                                 on a.TypeId equals b.TypeId
            //                                 select a)
            //                                 .ToList<Product>();//比较 equals
            //    foreach (var product in productList)
            //    {
            //        Console.WriteLine(product.ProductName + "-" + product.ProductType.TypeName);
            //    }
            //}

            using (TAOBAODBEntities taobao = new TAOBAODBEntities())
            {
                //linq to sql 查询表达式
                var productList = (from a in taobao.Product
                                   join b in taobao.ProductType
                                   on a.TypeId equals b.TypeId
                                   select new { a.ProductNo, a.ProductName, b.TypeName })//投影信息
                                             .OrderBy(a => a.ProductNo)
                                             .ToList();//比较 equals
                foreach (var product in productList)
                {
                    Console.WriteLine(product.ProductName + "-" + product.TypeName);//DataTable
                }
            }
        }

        #endregion

        #region EF 原生态SQL脚本执行
        //添加 删除 修改
        private static void Execute()
        {
            using (TAOBAODBEntities taobao = new TAOBAODBEntities())
            {
                string sql = @"insert into ProductType(typeid,typename)
                                values(@id,@name)";
                SqlParameter[] paras = {
                    new SqlParameter("@id",5),
                         new SqlParameter("@name","测试2")
                };
                int result = taobao.Database.ExecuteSqlCommand(sql, paras);
                if (result > 0)
                {
                    Console.WriteLine("执行成功!");
                }
                else
                {
                    Console.WriteLine("执行失败...");
                }
            }
        }


        /// <summary>
        /// 查询返回第一行第一列
        /// </summary>
        private static void ExecuteReader()
        {
            using (TAOBAODBEntities taobao = new TAOBAODBEntities())
            {
                string sql = "select * from product where typeid=@typeid";
                SqlParameter[] paras =
                {
                    new SqlParameter("@typeid",1)
                };
                List<Product> productList = taobao.Database
                    .SqlQuery<Product>(sql, paras).ToList();
                foreach (var product in productList)
                {
                    Console.WriteLine(product.ProductName + "-" + product.ProductNo);
                }

                每页显示10条  pagesize *(pageindex-1)
                //List<Product> productList = taobao.Database
                //    .SqlQuery<Product>(sql, paras)
                //    .Skip(11)
                //    .Take(10)//pagesize
                //    .ToList();
            }
        }

        /// <summary>
        /// 查询返回第一行第一列
        /// </summary>
        private static void ExecuteScalar()
        {
            using (TAOBAODBEntities taobao = new TAOBAODBEntities())
            {
                string sql = "select count(1) from product where typeid=@typeid";
                SqlParameter[] paras =
                {
                    new SqlParameter("@typeid",1)
                };
                int result = taobao.Database.SqlQuery<int>(sql, paras).Single();//单个值.Single()
                Console.WriteLine(result);
            }
        }
        //查询表 + 视图 +存储过程
        public static void GetProductProc()
        {
            using (TAOBAODBEntities taobao = new TAOBAODBEntities())
            {
                SqlParameter[] paras =
                {
                    new SqlParameter("@typeid",1),//0
                    new SqlParameter("@count",SqlDbType.Int)//1 
                };
                paras[1].Direction = ParameterDirection.Output;//默认执行参数类型
                List<Product> productList = taobao.Database
                    .SqlQuery<Product>
                    ("PROC_GetProduct @typeid,@count output", paras).ToList();
                int count = (int)paras[1].Value;//获取输出参数

                Console.WriteLine($"共计:{count}");
                foreach (var product in productList)
                {
                    Console.WriteLine($"{product.ProductNo}-  {product.ProductName}");
                }
            }
        }

        #endregion


        //linq to object   
        //linq to sql
    }
}