Use tools: sql server 2012, vs2017
Owing to the sql server used in books, the database used in the study of this book is sql server 2012. Before learning, add the database used in books.
Data sharing: northwnd
Links: https://pan.baidu.com/s/1i6FSRlj Password: fuh7

Catalog

1.DBFirst

2 Entity Framework Addendum, Delete and Change Check

3 LINQ To EF

4 Code First Development Mode

1.DBFirst

• First, add the entity model (ado.net entity data model) and install it all the way. It won't encounter all kinds of problems like connecting mysql.
  
• Verify installation
  As you can see from edmx, several tables have been loaded in
  
  Then we try to query the order data with id 10248 from the orders table by debugging.
  
  From the above figure, we can see that the corresponding data has been found.

2 Entity Framework Addendum, Delete and Change Check

2.1 NEW

Here you can note the use of the following method, DbEntityEntry. Because of the limitations of the Order in the database, although set 11090, the actual addition is incremental 1 data addition.

2.2 Sort and query by condition

To query in the following way, we need to introduce System.Linq.Expressions first.
In this way, you need to understand Experssion and lambda first.
Method code:

 #region Test sorting and querying by condition
        public static List<Customers> GetListBy<Tkey>(Expression<Func<Customers, bool>>
            whereLambda, Expression<Func<Customers, Tkey>> orderLambda)
        {
            using (NorthwindEntities entity = new NorthwindEntities())
            {
                return entity.Customers.Where(whereLambda).OrderBy(orderLambda).ToList();
            }
        }
        #endregion

Call method:

 static void Main(string[] args)
        {
            Expression<Func<Customers, bool>> whereLambda = (Customers) => Customers.CustomerID  == "alen";
            Expression<Func<Customers, string>> orderLambda = (Customers) => Customers.CustomerID;
            var result = FunctionMethods.GetListBy<string>(whereLambda,orderLambda);
        }

Call result:

2.3 Paging Query

Method code:

 #region Paging query
        public static List<Customers> GetPagedList<Tkey>(int pageIndex, int pageSize, 
            Expression<Func<Customers, bool>> whereLambda, Expression<Func<Customers, Tkey>> orderLambda)
        {
            using (NorthwindEntities entity = new NorthwindEntities())
            {
                return entity.Customers.Where(whereLambda).OrderBy(orderLambda).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
        }
        #endregion

Call method:

 //Paging query
            Expression<Func<Customers, bool>> whereLambda = (Customers) => Customers.CustomerID.Length < 4;
            Expression<Func<Customers, string>> orderLambda = (Customers) => Customers.CustomerID;
            int pageIndex = 1;
            int pageSize = 10;
            var result = FunctionMethods.GetPagedList(pageIndex,pageSize,whereLambda,orderLambda);

Call result:

2.4 amendment

Method code:

 #region modification
        public static void Edit()
        {
            using (NorthwindEntities entiy = new NorthwindEntities())
            {
                Customers _Customer = entiy.Customers.Where(x => x.CustomerID == "alen").FirstOrDefault();
                Console.WriteLine("Before amendment:" + _Customer.ContactName);

                _Customer.ContactName = "Zhang Zhonghua";
                entiy.SaveChanges();
                Console.WriteLine("Successful revision:" + _Customer.ContactName);

                Console.ReadLine();

            }
        }

        #endregion

Call method:

 //modify
            FunctionMethods.Edit();

Call result:

It's a bit slow to change this place. Print it and see how long it took.
No, it took 798 MS to change a record. It took 798 MS to change a record.

Let's see how long the query took.
At this time, it is not clear why the time used has dropped, but queries do take up a large part of the time.

Let's see if it's because EF queries can't keep up with the speed of using SQL to query:?
Method code:

 public static void EditBySql()
        {
            using (NorthwindEntities entity = new NorthwindEntities())
            {
                DateTime d1 = DateTime.Now;

                string sql = "select top 1 * from Northwind.dbo.Customers where CustomerID = 'alen'";


                var result = entity.Database.SqlQuery<Customers>(sql);

                Console.WriteLine("Before amendment:" + result.FirstOrDefault().ContactName);

                DateTime d3 = DateTime.Now;
                Console.WriteLine("Query time:" + (d3 - d1).Milliseconds + "ms");


                string sqlUpdate = "update Northwind.dbo.Customers set ContactName = 'Little Zhang' where CustomerID = 'alen'";

                var result1 = entity.Database.ExecuteSqlCommand(sqlUpdate);
                Console.WriteLine("Successful revision:" + result1);

                DateTime d2 = DateTime.Now;
                Console.WriteLine("Sharing time:" + (d2 - d1).Milliseconds + "ms");
                Console.ReadLine();

            }
        }

According to EF query, the fastest time to use is still about 100 ms slower than using sql directly. This place has been tested twice, so it can be seen that using sql directly to query is much faster than EF, nearly half of the time.

3 LINQ To EF

Method code:

 #region LINQ To EF
        public static void LINQToEF()
        {
            using (NorthwindEntities entity = new NorthwindEntities())
            {
                //Simple query
                var result = from c in entity.Customers select c;
                Console.WriteLine("Simple query, first data ContactName" + result.FirstOrDefault().ContactName);

                //Conditional query
                //Common linq Writing
                var result1 = from c in entity.Customers where c.CustomerID.Length > 0 select c;
                Console.WriteLine("Conditional query:ordinary linq Writing method,Article 1 Data ContactName:" + result1.FirstOrDefault().ContactName);

                //The Writing of Lambda Expressions
                var result2 = entity.Customers.Where(x => x.CustomerID.Length > 0).ToList();
                Console.WriteLine("Conditional query:Lambda Expression Writing,Article 1 Data ContactName:" + result2.FirstOrDefault().ContactName);

                //Sorting paging
                IQueryable<Customers> result3 = (from c in entity.Customers orderby c.CustomerID select c).Skip(0).Take(10);
                Console.WriteLine("Sorting paging:Article 1 Data ContactName:" + result3.FirstOrDefault().ContactName);

                //Connect
                var query = from d in entity.Order_Details
                            join order in entity.Orders on d.OrderID equals order.OrderID
                            select new
                            {
                                OrderId = order.OrderID,
                                ProductId = d.ProductID,
                                UnitPrice = d.UnitPrice
                            };
                foreach (var q in query.Take(5))
                {
                    Console.WriteLine("{0},{1},{2}",q.OrderId,q.ProductId,q.UnitPrice);
                }

                Console.ReadLine();


            }

        }

4 Code First Development Mode

Advantages:
* Development Profile
* Increased efficiency
* Increased automation
* Applicable to old projects
Inferiority:
* Poor performance
* Less known
* High learning costs and relatively high requirements for developers

4.1 Create Code First Demo

• 1. New ASP.NET MVC4 Web application, project template selection "empty"
• 2. Introduce the assembly EntityFramework and System.Data.Entity
• 3 Add the following new Order.cs and OrderDetail.cs model class files to the Models file
  The Order code is as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;

namespace CodeFirstDemo.Models
{
    public class Order
    {
        /// <summary>
        /// Attribute names are followed by Id，Default will be used as the primary key, you can not add[Key]attribute
        /// </summary>
        [Key]
        public int OrderId { get; set; }
        /// <summary>
        /// Order number
        /// </summary>
        [StringLength(50)]
        public string OrderCode { get; set; }
        /// <summary>
        /// Order amount
        /// </summary>
        public decimal OrderAmount { get; set; }
        /// <summary>
        /// Navigation properties are set to virtual，Delayed loading can be achieved
        /// </summary>
        public virtual ICollection<OrderDetail> OrderDetail { get; set; }
    }
}

The OrderDetail code is as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace CodeFirstDemo.Models
{
    public class OrderDetail
    {
        [Key]
        public int OrderDetailId { get; set; }
        /// <summary>
        /// Order Detailed Unit Price
        /// </summary>
        public decimal Price { get; set; }
        /// <summary>
        /// Order Detailed Quantity
        /// </summary>
        public int Count { get; set; }
        /// <summary>
        /// Foreign key, if attribute name and Order Primary key name is the same, default will be treated as a foreign key, can not be added ForeignKey Characteristic
        /// </summary>
        [ForeignKey("OrderBy")]
        public int OrderId { get; set; }

        /// <summary>
        /// Navigation attribute
        /// </summary>
        public virtual Order OrderBy { get; set; }
    }
}

• 4 Write connection strings in configuration files

<connectionStrings>
    <add name="CodeFirstDemoModel" connectionString="server=.;database=CodeFirstDamo;uid=sa;pwd=Password" providerName="System.Data.SqlClient"/>
  </connectionStrings>

• 5 Create context

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;

namespace CodeFirstDemo.Models
{
    public class CodeFirstContext:DbContext
    {
        /// <summary>
        /// Note that the name here should match the name of the context connection string configured in the configuration file
        /// </summary>
        public CodeFirstContext() : base("name=CodeFirstContext") { }
        public DbSet<Order> Order { get; set; }
        public DbSet<OrderDetail> OrderDetail { get; set; }

    }
}

• 6. Create a home controller at the controller layer and then create a database.
  
  HomeController.cs code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using CodeFirstDemo.Models;

namespace CodeFirstDemo.Controllers
{
    public class HomeController : Controller
    {
        // GET: Home
        public ActionResult Index()
        {
            CodeFirstContext db = new CodeFirstContext();
            db.Database.CreateIfNotExists();
            return View(db.Order.ToList());
        }
    }
}

It's not as useful here as it's written in the book, and then we'll see why db is written as an attribute to use.
Home/Index.cshtml code

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

Debugging operation

At this point, there is no database-related display on the interface. But you can see in SQL Server whether the code has been executed:

So the code in Controller is still executed.