{"id":2275,"date":"2023-06-17T11:18:13","date_gmt":"2023-06-17T04:18:13","guid":{"rendered":"https:\/\/csharptutorial.net\/?page_id=2275"},"modified":"2023-06-17T15:19:21","modified_gmt":"2023-06-17T08:19:21","slug":"ef-core-stored-procedure","status":"publish","type":"page","link":"https:\/\/www.csharptutorial.net\/entity-framework-core-tutorial\/ef-core-stored-procedure\/","title":{"rendered":"How to Execute Stored Procedures in EF Core"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: in this tutorial, you&#8217;ll learn how to create stored procedures in the database and how to execute them in EF Core.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Creating and executing a stored procedure in EF Core<\/h2>\n\n\n\n<p>To execute a <a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/\" target=\"_blank\" rel=\"noreferrer noopener\">stored procedure<\/a> in EF core, you follow these steps:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>First, <a href=\"https:\/\/csharptutorial.net\/entity-framework-core-tutorial\/ef-core-migrations\/\">create a new migration<\/a> that manages a stored procedure in the database by running the <code>Add-Migration<\/code> command in Package Console Manager. In the generated migration class, the <code>Up()<\/code> method creates a stored procedure while the <code>Down()<\/code> method drops it.<\/li>\n\n\n\n<li>Second, execute the <code>Update-Database<\/code> command to create the stored procedure in the database. <\/li>\n\n\n\n<li>Third, use the <code>FromSqlRaw()<\/code> method of the <code>DbSet<\/code> to execute the stored procedure.<\/li>\n<\/ul>\n\n\n\n<p>Notice that the result returned by the stored procedure must be materialized into the corresponding entity of the <code>DbSet<\/code>. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Executing a stored procedure in EF core example<\/h2>\n\n\n\n<p>We&#8217;ll use the <a href=\"https:\/\/csharptutorial.net\/entity-framework-core-tutorial\/ef-core-sample-project\/\">EF Core Sample Project<\/a> as the starting point. And we&#8217;ll use the <code>Employees<\/code> table from the HR database for the demonstration:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/csharptutorial.net\/wp-content\/uploads\/2023\/06\/EF-Core-Employees-Table.png\" alt=\"\" class=\"wp-image-2208\" width=\"188\" height=\"181\"\/><\/figure>\n\n\n\n<p>The following stored procedure return employees whose have salaries between a range:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"C#\" data-shcb-language-slug=\"cs\"><span><code class=\"hljs language-cs\"><span class=\"hljs-function\">CREATE PROCEDURE <span class=\"hljs-title\">GetEmployeeBySalaryRange<\/span>(<span class=\"hljs-params\">\n\t@MinSalary <span class=\"hljs-keyword\">as<\/span> <span class=\"hljs-keyword\">decimal<\/span>, \n\t@MaxSalary <span class=\"hljs-keyword\">as<\/span> <span class=\"hljs-keyword\">decimal<\/span><\/span>)\nAS\nBEGIN\n\tSELECT * FROM Employees\n\tWHERE Salary BETWEEN @MinSalary AND @MaxSalary\n        ORDER BY Salary DESC<\/span>;\nEND \nGO<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">C#<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">cs<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>To execute this stored procedure in EF core:<\/p>\n\n\n\n<p>First, create a new migration from the Package Manager Console by executing the <code>Add-Migration<\/code> command:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"C#\" data-shcb-language-slug=\"cs\"><span><code class=\"hljs language-cs\">Add-Migration AddGetEmployeeBySalaryRangeSP<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">C#<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">cs<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Second, modify <code>the AddGetEmployeeBySalaryRangeSP.cs<\/code> in the <code>Migrations<\/code> directory as follows:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"C#\" data-shcb-language-slug=\"cs\"><span><code class=\"hljs language-cs\"><span class=\"hljs-keyword\">using<\/span> Microsoft.EntityFrameworkCore.Migrations;\n\n<span class=\"hljs-meta\">#nullable disable<\/span>\n\n<span class=\"hljs-keyword\">namespace<\/span> <span class=\"hljs-title\">HR.Migrations<\/span>\n{\n    <span class=\"hljs-comment\"><span class=\"hljs-doctag\">\/\/\/<\/span> <span class=\"hljs-doctag\">&lt;inheritdoc \/&gt;<\/span><\/span>\n    <span class=\"hljs-keyword\">public<\/span> <span class=\"hljs-keyword\">partial<\/span> <span class=\"hljs-keyword\">class<\/span> <span class=\"hljs-title\">AddGetEmployeeBySalaryRangeSP<\/span> : <span class=\"hljs-title\">Migration<\/span>\n    {\n        <span class=\"hljs-comment\"><span class=\"hljs-doctag\">\/\/\/<\/span> <span class=\"hljs-doctag\">&lt;inheritdoc \/&gt;<\/span><\/span>\n        <span class=\"hljs-function\"><span class=\"hljs-keyword\">protected<\/span> <span class=\"hljs-keyword\">override<\/span> <span class=\"hljs-keyword\">void<\/span> <span class=\"hljs-title\">Up<\/span>(<span class=\"hljs-params\">MigrationBuilder migrationBuilder<\/span>)<\/span>\n        {\n            <span class=\"hljs-keyword\">var<\/span> command = <span class=\"hljs-string\">@\"CREATE PROCEDURE GetEmployeeBySalaryRange(\n\t                        @MinSalary as decimal, \n\t                        @MaxSalary as decimal)\n                        AS\n                        BEGIN\n\t                        SELECT * FROM Employees\n\t                        WHERE Salary BETWEEN @MinSalary AND @MaxSalary\n                                ORDER BY Salary DESC;\n                        END \n                        GO\"<\/span>;\n\n            migrationBuilder.Sql(command);\n        }\n\n        <span class=\"hljs-comment\"><span class=\"hljs-doctag\">\/\/\/<\/span> <span class=\"hljs-doctag\">&lt;inheritdoc \/&gt;<\/span><\/span>\n        <span class=\"hljs-function\"><span class=\"hljs-keyword\">protected<\/span> <span class=\"hljs-keyword\">override<\/span> <span class=\"hljs-keyword\">void<\/span> <span class=\"hljs-title\">Down<\/span>(<span class=\"hljs-params\">MigrationBuilder migrationBuilder<\/span>)<\/span>\n        {\n            <span class=\"hljs-keyword\">var<\/span> command = <span class=\"hljs-string\">\"DROP PROCEDURE GetDepartmentSalary\"<\/span>;\n            migrationBuilder.Sql(command);\n        }\n    }\n}<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">C#<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">cs<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The <code>Up()<\/code> method uses the <code>Sql()<\/code> method of the <code>MigrationBuilder<\/code> object to execute a command that creates the <code>GetEmployeeBySalaryRange()<\/code> stored procedure. <\/p>\n\n\n\n<p>The <code>Down()<\/code> method also uses the <code>Sql()<\/code> method but executes a statement that drops the <code>GetDepartmentSalary<\/code> stored procedure.<\/p>\n\n\n\n<p>Third, create the <code>GetEmployeeBySalaryRange<\/code> stored procedure in the database by executing the <code>Update-Database<\/code> in the Package Manager Console:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"C#\" data-shcb-language-slug=\"cs\"><span><code class=\"hljs language-cs\">Update-Database<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">C#<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">cs<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>If you view stored procedures in the SQL Server, you&#8217;ll see the stored procedure has been created successfully as follows:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"383\" height=\"280\" src=\"https:\/\/csharptutorial.net\/wp-content\/uploads\/2023\/06\/EF-Core-Execute-Stored-Procedure.png\" alt=\"EF Core Execute Stored Procedure\" class=\"wp-image-2285\" srcset=\"https:\/\/www.csharptutorial.net\/wp-content\/uploads\/2023\/06\/EF-Core-Execute-Stored-Procedure.png 383w, https:\/\/www.csharptutorial.net\/wp-content\/uploads\/2023\/06\/EF-Core-Execute-Stored-Procedure-300x219.png 300w\" sizes=\"auto, (max-width: 383px) 100vw, 383px\" \/><\/figure>\n\n\n\n<p>Finally, execute the <code>GetEmployeeBySalaryRange<\/code> stored procedure from the EF Core to get employees with a salary range of <code>100,000<\/code> and <code>120,000<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"C#\" data-shcb-language-slug=\"cs\"><span><code class=\"hljs language-cs\"><span class=\"hljs-keyword\">using<\/span> <span class=\"hljs-keyword\">static<\/span> System.Console;\n<span class=\"hljs-keyword\">using<\/span> HR;\n<span class=\"hljs-keyword\">using<\/span> Microsoft.EntityFrameworkCore;\n\n\n<span class=\"hljs-keyword\">using<\/span> <span class=\"hljs-keyword\">var<\/span> context = <span class=\"hljs-keyword\">new<\/span> HRContext();\n\n<span class=\"hljs-keyword\">var<\/span> minSalary = <span class=\"hljs-number\">100<\/span>_000;\n<span class=\"hljs-keyword\">var<\/span> maxSalary = <span class=\"hljs-number\">120<\/span>_000;\n\n<span class=\"hljs-keyword\">var<\/span> employees = context.Employees\n                        .FromSqlRaw(<span class=\"hljs-string\">\"EXECUTE GetEmployeeBySalaryRange {0}, {1}\"<\/span>,\n                                    minSalary,\n                                    maxSalary)\n                        .ToList();\n\n<span class=\"hljs-keyword\">foreach<\/span> (<span class=\"hljs-keyword\">var<\/span> e <span class=\"hljs-keyword\">in<\/span> employees)\n{\n    WriteLine(<span class=\"hljs-string\">$\"<span class=\"hljs-subst\">{e.FirstName}<\/span> <span class=\"hljs-subst\">{e.LastName}<\/span> - <span class=\"hljs-subst\">{e.Salary:C0}<\/span>\"<\/span>);\n}<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">C#<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">cs<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"C#\" data-shcb-language-slug=\"cs\"><span><code class=\"hljs language-cs\">Isabella Walker - $<span class=\"hljs-number\">119<\/span>,<span class=\"hljs-number\">856<\/span>\nLuke Walker - $<span class=\"hljs-number\">118<\/span>,<span class=\"hljs-number\">605<\/span>\nCharlotte King - $<span class=\"hljs-number\">117<\/span>,<span class=\"hljs-number\">417<\/span>\nCarter Adams - $<span class=\"hljs-number\">113<\/span>,<span class=\"hljs-number\">398<\/span>\nLiam Scott - $<span class=\"hljs-number\">108<\/span>,<span class=\"hljs-number\">406<\/span>\nDaniel Lewis - $<span class=\"hljs-number\">103<\/span>,<span class=\"hljs-number\">561<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">C#<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">cs<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Behind the scenes, EF Core executes the stored procedure as shown in the log:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"C#\" data-shcb-language-slug=\"cs\"><span><code class=\"hljs language-cs\">-- &#91;Parameters=&#91;p0=<span class=\"hljs-string\">'100000'<\/span>, p1=<span class=\"hljs-string\">'120000'<\/span>], CommandType=<span class=\"hljs-string\">'Text'<\/span>, CommandTimeout=<span class=\"hljs-string\">'30'<\/span>]\nEXECUTE GetEmployeeBySalaryRange @p0, @p1<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">C#<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">cs<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Summary<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use migrations to manage stored procedures.<\/li>\n\n\n\n<li>Use the <code>FromSqlRaw()<\/code> method to execute the stored procedure in EF Core.<\/li>\n<\/ul>\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful ?<\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"2275\"\n\t\t\t\tdata-post-url=\"https:\/\/www.csharptutorial.net\/entity-framework-core-tutorial\/ef-core-stored-procedure\/\"\n\t\t\t\tdata-post-title=\"How to Execute Stored Procedures in EF Core\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"2275\"\n\t\t\t\tdata-post-url=\"https:\/\/www.csharptutorial.net\/entity-framework-core-tutorial\/ef-core-stored-procedure\/\"\n\t\t\t\tdata-post-title=\"How to Execute Stored Procedures in EF Core\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>In this tutorial, you&#8217;ll learn how to execute stored procedures in EF Core.<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":1970,"menu_order":19,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-2275","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/www.csharptutorial.net\/wp-json\/wp\/v2\/pages\/2275","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.csharptutorial.net\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.csharptutorial.net\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.csharptutorial.net\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.csharptutorial.net\/wp-json\/wp\/v2\/comments?post=2275"}],"version-history":[{"count":5,"href":"https:\/\/www.csharptutorial.net\/wp-json\/wp\/v2\/pages\/2275\/revisions"}],"predecessor-version":[{"id":2298,"href":"https:\/\/www.csharptutorial.net\/wp-json\/wp\/v2\/pages\/2275\/revisions\/2298"}],"up":[{"embeddable":true,"href":"https:\/\/www.csharptutorial.net\/wp-json\/wp\/v2\/pages\/1970"}],"wp:attachment":[{"href":"https:\/\/www.csharptutorial.net\/wp-json\/wp\/v2\/media?parent=2275"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}