Series Catalogue
Learn more about the. Net Core WEB API Development Series Catalog
The source code involved in this series: Https://github.com/seabluescn/Blog_WebApi
I. Overview
This article describes some common database operations, including: Conditional query, sorting, paging, transactions and other basic database operations. The test database is MySQL.
Second, the conditions of inquiry
1. Check all records
List<article> articles = _context. Articles.tolist<article> ();
2, according to the primary key query
Article Article = _context. Articles.find (ID);
3. Query based on non-primary key information fields
list<product> products == = = = P.name = = Name). ToList<Product> ();
Returns null if not found, but does not report an exception.
4, query a record
Article articles = _context. Articles . Single (article=>article. Title==title);
This field should be required to be a unique constraint, and the method expects that a record must be returned, and 0 and more will be reported as exceptions.
The first method takes a number of records, if the exception is not found, but there are a number of eligible to take a non-reporting exception.
Article articles = _context. Articles . First (article=>article. Title.contains (title));
5. Fuzzy query
There are two methods of fuzzy query:
list<article> articles == = = article. Title.contains (title)). ToList (); List<Article> articles == = + EF. Functions.like (article. title,$"{title}%")). ToList ();
The contains method of the like method and the string can implement the fuzzy query, the main difference is: the EF.Functions.Like () method supports wildcards, and the StartsWith, contains, and EndsWith methods do not support wildcard characters, Compare the following two methods, the 1th statement can achieve the desired effect, but the 2nd statement does not work.
(1) EF. Functions.like (article. Title, "%[a-z]%") (2) article. Title.contains ("[A-z]")
Tip: If you need to see the actual executed SQL statement, modify the log level in Application.json.
Third, sort
list<article> articles = _context . Articles.orderby (Article=article. Title) . ToList<Article>(); List<Article> articles = _context. Articles.orderbydescending (Article=article. Title). ToList<Article> ();
Four, pre-loading
You can use the Include method to specify the relevant data to include in the query results.
Article Article == = a.author). First ();
The author property of article in the preceding code is no longer null.
Load multiple items
var blogs === = = = Blog. Owner). ToList ();
Multi-level pre-loading:
list<column> columns == = Column. articles). Theninclude (Article= article.author). ToList<Column> ();
Five, non-tracking query (no-tracking queries)
If it is just a read-only query, using a non-tracking query provides the performance of the query.
list<article> articles = _context. Articles. Asnotracking (). ToList<Article> ();
Vi. the original SQL query
You can use the Fromsql extension method to implement LINQ queries based on the original SQL query.
list<article> articles = _context. Articles. Fromsql ("select * from cms_article"). ToList ();
You can also use the original SQL query to execute the stored procedure.
var blogs = context. Blogs. Fromsql ("EXECUTE dbo. Getmostpopularblogs"). ToList ();
or implement a query with parameters
var sql = $"select * from cms_article where title like '%{titlewithsql}% '"; List<Article> articles = _context. Articles. Fromsql (SQL). ToList ();
Pre-load include, Where, and by
list<article> articles = _context. Articles. Fromsql (SQL). Include (A=a.author). ToList (); var " . NET " ; var blogs = context. Blogs. Fromsql ($"select * FROM dbo.) Searchblogs ({searchterm})"3= b.rating). ToList ();
Note: If you use string concatenation to dynamically generate any part of the query string, you will be responsible for validating any input to prevent SQL injection attacks
Seven, pagination
int Ten ; int 2 ; List<Article> articles = _context. Articles. Asnotracking (). Skip (pageSize* pagenumber). Take (pageSize). ToList<Article> ();
Viii. Modification of data
1. Add Data
Use the Dbset.add method to add a new instance of the entity class.
var New " http://sample.com " };context. Blogs.add (blog); context. SaveChanges ();
2. Update data
var blog = context. Blogs.find ("xx""http://sample.com/blog" ; context. SaveChanges ();
3. Delete data
var blog = context. Blogs.first (); context. Blogs.remove (blog); context. SaveChanges ();
4. Multiple operations in a single SaveChanges
Context. Blogs.add (NewBlog {URL ="Http://sample.com/blog_one"}); context. Blogs.add (NewBlog {URL ="Http://sample.com/blog_two" });//UpdatevarFirstblog =context. Blogs.first (); Firstblog.url="";//RemovevarLastblog =context. Blogs.last (); context. Blogs.remove (lastblog); context. SaveChanges ();
The SaveChanges is transactional, and the above code does not require any additional transactional code to be added.
Ix. Use of connection pooling
String connstr = configuration.getconnectionstring ("mysqlconnection"); services. Adddbcontextpool<SalesContext> (builder=> Builder. Usemysql (CONNSTR));
It is recommended that you connect to the database using connection pooling.
Learn more about the. Net Core Web API Development Series "9": Common Database operations