Project summary: EF operation and Linq writing record

1. Introduction of EF

After creating an MVC project, to reference the EF framework, follow these steps:

1), add project in Models

2) select Entity Data Model and fill in the name again

3) after selecting code first, select the connected database and tables, and then generate them. At this time, the entities in the model are equivalent to the tables in the database

2. Linq and some lambda writing methods

1) single table query:

using (var db = new DBModel())
{
    DepartmentList = (from n in db.output_Auth where n.badge == badge select n.department).Distinct().ToList<string>();
}
//from n(Table alias) in table(form) where n.id == id(condition) select n(Find all)，Then you can select the filter conditions, such as.Distinct()\FirstOrDefault()\Sum()etc.
//To query a specific field in select, write: select new {n.id, n.name, n.age}. To give an alias to a field, write: select new {otherid = n.id, othername = n.name}

Note: several methods commonly used in this project:
1,OrderByDescending(n => n.id)； //Descending order
2,FirstOrDefault(); //Get the first row of query results
3,ToList(); //List query results
4,Distinct(); //Single query results, similar to distinct in SQL
5,Sum(n => n.id); //Total result

The rest is not used in this project and will be used in the future.

2) multi table query:

var time1 = (from a in Table_A join b in Table_B on a.id equals b.id where a.id == id & b.time == timeselect new { a.time, a.data}).ToList();

//Here and SQL There are different ways to write multi table query statements, on The following conditions are: a.x equals b.x

//Supplement 1: you can't format time directly in Linq to SQL statement, you can only query it first, and then format time in Linq to Entity
　　That is, the above query statement where b.time == time Medium, b.time Can not be written b.time.ToString("yyyy-MM-dd") And so on.
　　You can only put the time found in time1 In, format the time as follows:
　　var time2 = (from t in time1 where t.time.GetDataTimeFormats()[5].ToString() == nowDate select new{Data = t.data}).Sum(t => t.Data);
　　GetDateTimeFormats()[5]Time format: yyyy-MM-dd

//Supplement 2: if the query result in LinQ to SQL is 0, you can't use. Sum() directly. You should first use. ToList() to sum the query result in LinQ to Entity. Similar to the above method, you need to write two statements.

3) insert and update statements

using (var db = new DBModel())
{
　　 //Check whether the data exists before inserting
    var data =  (from tb in db.Table where tb.id == id select tb).FirstOrDefault();

　　 //If the data is not available, the insert statement is executed
    if (data == null)
    {
        var table = new Table();

        table.name = name;
　　　   table.age = age;
//Perform insert operation
        db.Table.Add(table); 
　　　   db.SaveChanges(); 
　　　}

　　 //If there is data in the current month
    else
    {
        //Linq You cannot directly update the primary key data. You can only copy this piece of information, delete the original piece of data, and then insert a new piece of modified data. If it is not the primary key data, you can directly update it
        var tableNew = new Table();

        tableNew.name = name;
        tableNew.age = age;

        db.Table.Remove(data); //Remove old data
        db.Table.Add(tableNew); //Add new data
        //Perform update operation
        db.SaveChanges();
    }
}      

  //New writing method of Lambda expression:

　　var data = db.Table.FirstOrDefault(tb => tb.id == id & tb.name == name);
　　if (data == null)
　　{
　　　　var table = new Table();

　　　　table.name = name;

　　　　table.age = age;

　　　　db.Table.Add(table);
　　　　db.SaveChanges();
　　}

 //Lambda modification:

　　var table = db.Table.FirstOrDefault(tb => tb.id == id);
　　table.name = name;
　　table.age = age;
　　db.SaveChanges();

4) delete

//Lambda Expression writing:
using (var db = new DBModel())
{
    var user = db.Table.FirstOrDefault(opAu => opAu.ID == userID);
    db.Table.Remove(user);
    db.SaveChanges();
}

//Linq Writing:
using (var db = new DBModel())
{
    var user = (from tb in db.Table select tb).FirstOrDefault();
    db.Table.Remove(tb);
    db.SaveChanges();
}