Shulou(Shulou.com)06/01 Report--

EntityFramework Core Raw SQL basic queries (execute SQL and stored procedures)

Don't say anything, just pick up the keyboard and do it, as follows:

Public class HomeController: Controller {private IBlogRepository _ blogRepository; public HomeController (IBlogRepository blogRepository) {_ blogRepository = blogRepository;} public IActionResult Index () {var list = _ blogRepository.GetList (); return Ok ();}}

Public class BlogRepository: EntityBaseRepository, IBlogRepository {private EFCoreContext _ efCoreContext; public BlogRepository (EFCoreContext efCoreContext): base (efCoreContext) {_ efCoreContext = efCoreContext;} public IEnumerable GetList () {var iQueryTable = _ efCoreContext.Set (). FromSql ("select * from Blog"); return iQueryTable.ToList ();}}

Let's take a look at stored procedures.

CREATE PROCEDURE dbo.GetBlogListASBEGIN SELECT * FROM dbo.BlogENDGO

Public IEnumerable GetList () {var iQueryTable = _ efCoreContext.Set (). FromSql ("EXECUTE dbo.GetBlogList"); return iQueryTable.ToList ();}

Parameter query

Query using parameterized stored procedures.

* FROM dbo.Blog WHERE Id =

As a result, using FromSql becomes as follows:

Public IEnumerable GetList () {var Id = new SqlParameter ("Id", "1"); var iQueryTable = _ efCoreContext.Set (). FromSql ("EXEC dbo.GetBlogList {0}", 1); return iQueryTable.ToList ();}

The above is in the form of string.format to pass parameters, and we can also use SqlParameter to pass parameters, as follows:

Public IEnumerable GetList () {var Id = new SqlParameter ("Id", "1"); var iQueryTable = _ efCoreContext.Set (). FromSql (EXEC dbo.GetBlogList @ id, Id); return iQueryTable.ToList ();}

By turning on debugging, we can clearly see the stored procedure being executed.

From the above we know that there are two forms of parameterized queries, so let's take a look at the linq query.

Linq query

In the above demonstration, we have been using FromSql directly, but after that, we can continue to query through linq, as follows:

Public IEnumerable GetList () {var Id = new SqlParameter ("Id", "2"); var iQueryTable = _ efCoreContext.Set (). FromSql ("EXEC dbo.GetBlogList @ id", Id) .Where (d = > d.Name = = "efcore2"); return iQueryTable.ToList ();}

Previously, we mapped the relationship between Blog and Post. Here we can only query the data of Blog table. By explaining the above linq, we can explicitly load Post table data through inlcude, as follows:

Public IEnumerable GetList () {var Id = new SqlParameter ("Id", "2"); var iQueryTable = _ efCoreContext.Set (). FromSql ("EXEC dbo.GetBlogList @ id", Id) .include (d = > d.Posts); return iQueryTable.ToList ();}

Well, make it clear that we don't support Inlude operations for stored procedures, so we can only do a simple query to Include, as follows:

Public IEnumerable GetList () {var iQueryTable = _ efCoreContext.Set (). FromSql ("select * from blog") .include (d = > d.Posts); return iQueryTable.ToList ();}

When searching the information on the official website, it is found that the table-valued function (TVF) can be Include. The embedded table-valued function is created as follows:

USE [EFCoreDb] GOIF OBJECT_ID ('dbo.GetBlog') IS NOT NULL DROP FUNCTION dbo.GetBlog;GOCREATE FUNCTION dbo.GetBlog (@ Name VARCHAR (max)) RETURNS TABLE WITH SCHEMABINDINGAS RETURN SELECT Id, Name, Url FROM dbo.Blog WHERE Name = @ NameGO

The call is as follows:

Public IEnumerable GetList () {var name = "efcore2"; var iQueryTable = _ efCoreContext.Set (). FromSql ("select * from [dbo]. [GetBlog] {0}", name) .include (d = > d.Posts); return iQueryTable.ToList ();}

The result was an unexpected grammatical error:

The SQL statement sent through SQL Server Profiler is as follows:

Can this be good? the example given on the official website is the same as above, as follows:

He just moved here like him, didn't think too much, or was careless. After thinking about it for a long time, we can call the table-valued function normally. We need to enclose it in parentheses, as follows:

Public IEnumerable GetList () {var name = "efcore2"; var iQueryTable = _ efCoreContext.Set (). FromSql ("select * from [dbo]. [GetBlog] ({0})", name) .include (d = > d.Posts); return iQueryTable.ToList ();}

Both [dbo.GetBlog] and ({0}) can be separated and next to each other as described above. There will be no grammatical errors at this time. The following SQL is correct.

All right, at this point, we are done with the original query in EF Core. There is another knowledge point that has not been mentioned. In EF Core, we can query directly through the underlying ADO.NET. Let's take a look at:

Underlying ADO.NET query

Public IEnumerable GetList () {var list = new List (); using (var connection = _ efCoreContext.Database.GetDbConnection ()) {connection.Open (); using (var command = connection.CreateCommand ()) {command.CommandText = "SELECT * FROM dbo.Blog" Using (SqlDataReader reader = command.ExecuteReader () as SqlDataReader) {while (reader.Read ()) {var blog = new Blog (); blog.Id = Convert.ToInt32 (reader ["Id"]) Blog.Name = reader ["Name"] .ToString (); blog.Url = reader ["Url"] .ToString (); list.Add (blog) } return list;}

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.