Learn more about the. Net Core Web API Development Series "9": Common Database operations

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