Advanced topics - EF Core with ASP.NET Core MVC tutorial (10 of 10)
By Tom Dykstra and Rick Anderson
The Contoso University sample web application demonstrates how to create ASP.NET Core MVC web applications using Entity Framework Core and Visual Studio. For information about the tutorial series, see the first tutorial in the series.
In the previous tutorial, you implemented table-per-hierarchy inheritance. This tutorial introduces several topics that are useful to be aware of when you go beyond the basics of developing ASP.NET Core web applications that use Entity Framework Core.
Raw SQL Queries
One of the advantages of using the Entity Framework is that it avoids tying your code too closely to a particular method of storing data. It does this by generating SQL queries and commands for you, which also frees you from having to write them yourself. But there are exceptional scenarios when you need to run specific SQL queries that you have manually created. For these scenarios, the Entity Framework Code First API includes methods that enable you to pass SQL commands directly to the database. You have the following options in EF Core 1.0:
Use the
DbSet.FromSqlmethod for queries that return entity types. The returned objects must be of the type expected by theDbSetobject, and they are automatically tracked by the database context unless you turn tracking off.Use the
Database.ExecuteSqlCommandfor non-query commands.
If you need to run a query that returns types that aren’t entities, you can use ADO.NET with the database connection provided by EF. The returned data isn’t tracked by the database context, even if you use this method to retrieve entity types.
As is always true when you execute SQL commands in a web application, you must take precautions to protect your site against SQL injection attacks. One way to do that is to use parameterized queries to make sure that strings submitted by a web page can’t be interpreted as SQL commands. In this tutorial you’ll use parameterized queries when integrating user input into a query.
Call a query that returns entities
The DbSet<TEntity> class provides a method that you can use to execute a query that returns an entity of type TEntity. To see how this works you’ll change the code in the Details method of the Department controller.
In DepartmentsController.cs, in the Details method, replace the code that retrieves a department with a FromSql method call, as shown in the following highlighted code:
[!code-csharpMain]
1: //#define ScaffoldedCode
2: #define RawSQL
3:
4: using System;
5: using System.Collections.Generic;
6: using System.Linq;
7: using System.Threading.Tasks;
8: using Microsoft.AspNetCore.Mvc;
9: using Microsoft.AspNetCore.Mvc.Rendering;
10: using Microsoft.EntityFrameworkCore;
11: using ContosoUniversity.Data;
12: using ContosoUniversity.Models;
13:
14: namespace ContosoUniversity.Controllers
15: {
16: public class DepartmentsController : Controller
17: {
18: private readonly SchoolContext _context;
19:
20: public DepartmentsController(SchoolContext context)
21: {
22: _context = context;
23: }
24:
25: // GET: Departments
26: public async Task<IActionResult> Index()
27: {
28: var departments = _context.Departments
29: .Include(d => d.Administrator)
30: .AsNoTracking();
31: return View(await departments.ToListAsync());
32: }
33:
34: // GET: Departments/Details/5
35: #if ScaffoldedCode
36: public async Task<IActionResult> Details(int? id)
37: {
38: if (id == null)
39: {
40: return NotFound();
41: }
42:
43: var department = await _context.Departments
44: .AsNoTracking()
45: .SingleOrDefaultAsync(m => m.DepartmentID == id);
46: if (department == null)
47: {
48: return NotFound();
49: }
50:
51: return View(department);
52: }
53: #elif RawSQL
54: #region snippet_RawSQL
55: public async Task<IActionResult> Details(int? id)
56: {
57: if (id == null)
58: {
59: return NotFound();
60: }
61:
62: string query = "SELECT * FROM Department WHERE DepartmentID = {0}";
63: var department = await _context.Departments
64: .FromSql(query, id)
65: .Include(d => d.Administrator)
66: .AsNoTracking()
67: .SingleOrDefaultAsync();
68:
69: if (department == null)
70: {
71: return NotFound();
72: }
73:
74: return View(department);
75: }
76: #endregion
77: #endif
78:
79: // GET: Departments/Create
80: public IActionResult Create()
81: {
82: ViewData["InstructorID"] = new SelectList(_context.Instructors.AsNoTracking(), "ID", "FullName");
83: return View();
84: }
85:
86: // POST: Departments/Create
87: [HttpPost]
88: [ValidateAntiForgeryToken]
89: public async Task<IActionResult> Create([Bind("Budget,InstructorID,Name,StartDate")] Department department)
90: {
91: if (ModelState.IsValid)
92: {
93: _context.Add(department);
94: await _context.SaveChangesAsync();
95: return RedirectToAction(nameof(Index));
96: }
97: #region snippet_Dropdown
98: ViewData["InstructorID"] = new SelectList(_context.Instructors, "ID", "FullName", department.InstructorID);
99: #endregion
100: return View(department);
101: }
102:
103: // GET: Departments/Edit/5
104: public async Task<IActionResult> Edit(int? id)
105: {
106: if (id == null)
107: {
108: return NotFound();
109: }
110:
111: #region snippet_EagerLoading
112: var department = await _context.Departments
113: .Include(i => i.Administrator)
114: .AsNoTracking()
115: .SingleOrDefaultAsync(m => m.DepartmentID == id);
116: #endregion
117: if (department == null)
118: {
119: return NotFound();
120: }
121: ViewData["InstructorID"] = new SelectList(_context.Instructors.AsNoTracking(), "ID", "FullName", department.InstructorID);
122: return View(department);
123: }
124:
125: // POST: Departments/Edit/5
126: // To protect from overposting attacks, please enable the specific properties you want to bind to, for
127: // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
128: #region snippet_EditPost
129: [HttpPost]
130: [ValidateAntiForgeryToken]
131: public async Task<IActionResult> Edit(int? id, byte[] rowVersion)
132: {
133: if (id == null)
134: {
135: return NotFound();
136: }
137:
138: var departmentToUpdate = await _context.Departments.Include(i => i.Administrator).SingleOrDefaultAsync(m => m.DepartmentID == id);
139:
140: if (departmentToUpdate == null)
141: {
142: Department deletedDepartment = new Department();
143: await TryUpdateModelAsync(deletedDepartment);
144: ModelState.AddModelError(string.Empty,
145: "Unable to save changes. The department was deleted by another user.");
146: ViewData["InstructorID"] = new SelectList(_context.Instructors, "ID", "FullName", deletedDepartment.InstructorID);
147: return View(deletedDepartment);
148: }
149:
150: _context.Entry(departmentToUpdate).Property("RowVersion").OriginalValue = rowVersion;
151:
152: if (await TryUpdateModelAsync<Department>(
153: departmentToUpdate,
154: "",
155: s => s.Name, s => s.StartDate, s => s.Budget, s => s.InstructorID))
156: {
157: try
158: {
159: await _context.SaveChangesAsync();
160: return RedirectToAction(nameof(Index));
161: }
162: catch (DbUpdateConcurrencyException ex)
163: {
164: var exceptionEntry = ex.Entries.Single();
165: var clientValues = (Department)exceptionEntry.Entity;
166: var databaseEntry = exceptionEntry.GetDatabaseValues();
167: if (databaseEntry == null)
168: {
169: ModelState.AddModelError(string.Empty,
170: "Unable to save changes. The department was deleted by another user.");
171: }
172: else
173: {
174: var databaseValues = (Department)databaseEntry.ToObject();
175:
176: if (databaseValues.Name != clientValues.Name)
177: {
178: ModelState.AddModelError("Name", $"Current value: {databaseValues.Name}");
179: }
180: if (databaseValues.Budget != clientValues.Budget)
181: {
182: ModelState.AddModelError("Budget", $"Current value: {databaseValues.Budget:c}");
183: }
184: if (databaseValues.StartDate != clientValues.StartDate)
185: {
186: ModelState.AddModelError("StartDate", $"Current value: {databaseValues.StartDate:d}");
187: }
188: if (databaseValues.InstructorID != clientValues.InstructorID)
189: {
190: Instructor databaseInstructor = await _context.Instructors.SingleOrDefaultAsync(i => i.ID == databaseValues.InstructorID);
191: ModelState.AddModelError("InstructorID", $"Current value: {databaseInstructor?.FullName}");
192: }
193:
194: ModelState.AddModelError(string.Empty, "The record you attempted to edit "
195: + "was modified by another user after you got the original value. The "
196: + "edit operation was canceled and the current values in the database "
197: + "have been displayed. If you still want to edit this record, click "
198: + "the Save button again. Otherwise click the Back to List hyperlink.");
199: departmentToUpdate.RowVersion = (byte[])databaseValues.RowVersion;
200: ModelState.Remove("RowVersion");
201: }
202: }
203: }
204: ViewData["InstructorID"] = new SelectList(_context.Instructors, "ID", "FullName", departmentToUpdate.InstructorID);
205: return View(departmentToUpdate);
206: }
207: #endregion
208:
209: // GET: Departments/Delete/5
210: #region snippet_DeleteGet
211: public async Task<IActionResult> Delete(int? id, bool? concurrencyError)
212: {
213: if (id == null)
214: {
215: return NotFound();
216: }
217:
218: var department = await _context.Departments
219: .Include(d => d.Administrator)
220: .AsNoTracking()
221: .SingleOrDefaultAsync(m => m.DepartmentID == id);
222: if (department == null)
223: {
224: if (concurrencyError.GetValueOrDefault())
225: {
226: return RedirectToAction(nameof(Index));
227: }
228: return NotFound();
229: }
230:
231: if (concurrencyError.GetValueOrDefault())
232: {
233: ViewData["ConcurrencyErrorMessage"] = "The record you attempted to delete "
234: + "was modified by another user after you got the original values. "
235: + "The delete operation was canceled and the current values in the "
236: + "database have been displayed. If you still want to delete this "
237: + "record, click the Delete button again. Otherwise "
238: + "click the Back to List hyperlink.";
239: }
240:
241: return View(department);
242: }
243: #endregion
244:
245: // POST: Departments/Delete/5
246: #region snippet_DeletePost
247: [HttpPost]
248: [ValidateAntiForgeryToken]
249: public async Task<IActionResult> Delete(Department department)
250: {
251: try
252: {
253: if (await _context.Departments.AnyAsync(m => m.DepartmentID == department.DepartmentID))
254: {
255: _context.Departments.Remove(department);
256: await _context.SaveChangesAsync();
257: }
258: return RedirectToAction(nameof(Index));
259: }
260: catch (DbUpdateConcurrencyException /* ex */)
261: {
262: //Log the error (uncomment ex variable name and write a log.)
263: return RedirectToAction(nameof(Delete), new { concurrencyError = true, id = department.DepartmentID });
264: }
265: }
266: #endregion
267: }
268: }
To verify that the new code works correctly, select the Departments tab and then Details for one of the departments.
Call a query that returns other types
Earlier you created a student statistics grid for the About page that showed the number of students for each enrollment date. You got the data from the Students entity set (_context.Students) and used LINQ to project the results into a list of EnrollmentDateGroup view model objects. Suppose you want to write the SQL itself rather than using LINQ. To do that you need to run a SQL query that returns something other than entity objects. In EF Core 1.0, one way to do that is write ADO.NET code and get the database connection from EF.
In HomeController.cs, replace the About method with the following code:
[!code-csharpMain]
1: #define UseDbSet // or UseRawSQL
2:
3: using System.Collections.Generic;
4: using System.Linq;
5: using System.Threading.Tasks;
6: using Microsoft.AspNetCore.Mvc;
7: #region snippet_Usings2
8: using System.Data.Common;
9: #endregion
10: #region snippet_Usings1
11: using Microsoft.EntityFrameworkCore;
12: using ContosoUniversity.Data;
13: using ContosoUniversity.Models.SchoolViewModels;
14: #endregion
15:
16: namespace ContosoUniversity.Controllers
17: {
18: #region snippet_AddContext
19: public class HomeController : Controller
20: {
21: private readonly SchoolContext _context;
22:
23: public HomeController(SchoolContext context)
24: {
25: _context = context;
26: }
27: #endregion
28: public IActionResult Index()
29: {
30: return View();
31: }
32:
33: #if UseDbSet
34: #region snippet_UseDbSet
35: public async Task<ActionResult> About()
36: {
37: IQueryable<EnrollmentDateGroup> data =
38: from student in _context.Students
39: group student by student.EnrollmentDate into dateGroup
40: select new EnrollmentDateGroup()
41: {
42: EnrollmentDate = dateGroup.Key,
43: StudentCount = dateGroup.Count()
44: };
45: return View(await data.AsNoTracking().ToListAsync());
46: }
47: #endregion
48: #elif UseRawSQL
49: #region snippet_UseRawSQL
50: public async Task<ActionResult> About()
51: {
52: List<EnrollmentDateGroup> groups = new List<EnrollmentDateGroup>();
53: var conn = _context.Database.GetDbConnection();
54: try
55: {
56: await conn.OpenAsync();
57: using (var command = conn.CreateCommand())
58: {
59: string query = "SELECT EnrollmentDate, COUNT(*) AS StudentCount "
60: + "FROM Person "
61: + "WHERE Discriminator = 'Student' "
62: + "GROUP BY EnrollmentDate";
63: command.CommandText = query;
64: DbDataReader reader = await command.ExecuteReaderAsync();
65:
66: if (reader.HasRows)
67: {
68: while (await reader.ReadAsync())
69: {
70: var row = new EnrollmentDateGroup { EnrollmentDate = reader.GetDateTime(0), StudentCount = reader.GetInt32(1) };
71: groups.Add(row);
72: }
73: }
74: reader.Dispose();
75: }
76: }
77: finally
78: {
79: conn.Close();
80: }
81: return View(groups);
82: }
83: #endregion
84: #endif
85: public IActionResult Contact()
86: {
87: ViewData["Message"] = "Your contact page.";
88:
89: return View();
90: }
91:
92: public IActionResult Error()
93: {
94: return View();
95: }
96: }
97: }
Add a using statement:
[!code-csharpMain]
1: #define UseDbSet // or UseRawSQL
2:
3: using System.Collections.Generic;
4: using System.Linq;
5: using System.Threading.Tasks;
6: using Microsoft.AspNetCore.Mvc;
7: #region snippet_Usings2
8: using System.Data.Common;
9: #endregion
10: #region snippet_Usings1
11: using Microsoft.EntityFrameworkCore;
12: using ContosoUniversity.Data;
13: using ContosoUniversity.Models.SchoolViewModels;
14: #endregion
15:
16: namespace ContosoUniversity.Controllers
17: {
18: #region snippet_AddContext
19: public class HomeController : Controller
20: {
21: private readonly SchoolContext _context;
22:
23: public HomeController(SchoolContext context)
24: {
25: _context = context;
26: }
27: #endregion
28: public IActionResult Index()
29: {
30: return View();
31: }
32:
33: #if UseDbSet
34: #region snippet_UseDbSet
35: public async Task<ActionResult> About()
36: {
37: IQueryable<EnrollmentDateGroup> data =
38: from student in _context.Students
39: group student by student.EnrollmentDate into dateGroup
40: select new EnrollmentDateGroup()
41: {
42: EnrollmentDate = dateGroup.Key,
43: StudentCount = dateGroup.Count()
44: };
45: return View(await data.AsNoTracking().ToListAsync());
46: }
47: #endregion
48: #elif UseRawSQL
49: #region snippet_UseRawSQL
50: public async Task<ActionResult> About()
51: {
52: List<EnrollmentDateGroup> groups = new List<EnrollmentDateGroup>();
53: var conn = _context.Database.GetDbConnection();
54: try
55: {
56: await conn.OpenAsync();
57: using (var command = conn.CreateCommand())
58: {
59: string query = "SELECT EnrollmentDate, COUNT(*) AS StudentCount "
60: + "FROM Person "
61: + "WHERE Discriminator = 'Student' "
62: + "GROUP BY EnrollmentDate";
63: command.CommandText = query;
64: DbDataReader reader = await command.ExecuteReaderAsync();
65:
66: if (reader.HasRows)
67: {
68: while (await reader.ReadAsync())
69: {
70: var row = new EnrollmentDateGroup { EnrollmentDate = reader.GetDateTime(0), StudentCount = reader.GetInt32(1) };
71: groups.Add(row);
72: }
73: }
74: reader.Dispose();
75: }
76: }
77: finally
78: {
79: conn.Close();
80: }
81: return View(groups);
82: }
83: #endregion
84: #endif
85: public IActionResult Contact()
86: {
87: ViewData["Message"] = "Your contact page.";
88:
89: return View();
90: }
91:
92: public IActionResult Error()
93: {
94: return View();
95: }
96: }
97: }
Run the app and go to the About page. It displays the same data it did before.
Call an update query
Suppose Contoso University administrators want to perform global changes in the database, such as changing the number of credits for every course. If the university has a large number of courses, it would be inefficient to retrieve them all as entities and change them individually. In this section you’ll implement a web page that enables the user to specify a factor by which to change the number of credits for all courses, and you’ll make the change by executing a SQL UPDATE statement. The web page will look like the following illustration:
In CoursesContoller.cs, add UpdateCourseCredits methods for HttpGet and HttpPost:
[!code-csharpMain]
1: //#define ScaffoldedCode
2: #define RevisedIndexMethod
3:
4: using System;
5: using System.Collections.Generic;
6: using System.Linq;
7: using System.Threading.Tasks;
8: using Microsoft.AspNetCore.Mvc;
9: using Microsoft.AspNetCore.Mvc.Rendering;
10: using Microsoft.EntityFrameworkCore;
11: using ContosoUniversity.Data;
12: using ContosoUniversity.Models;
13:
14: namespace ContosoUniversity.Controllers
15: {
16: public class CoursesController : Controller
17: {
18: private readonly SchoolContext _context;
19:
20: public CoursesController(SchoolContext context)
21: {
22: _context = context;
23: }
24:
25: // GET: Courses
26: #if ScaffoldedCode
27: public async Task<IActionResult> Index()
28: {
29: var schoolContext = _context.Courses
30: .Include(c => c.Department)
31: .AsNoTracking();
32: return View(await schoolContext.ToListAsync());
33: }
34: #elif RevisedIndexMethod
35: #region snippet_RevisedIndexMethod
36: public async Task<IActionResult> Index()
37: {
38: var courses = _context.Courses
39: .Include(c => c.Department)
40: .AsNoTracking();
41: return View(await courses.ToListAsync());
42: }
43: #endregion
44: #endif
45: // GET: Courses/Details/5
46: #region snippet_Details
47: public async Task<IActionResult> Details(int? id)
48: {
49: if (id == null)
50: {
51: return NotFound();
52: }
53:
54: var course = await _context.Courses
55: .Include(c => c.Department)
56: .AsNoTracking()
57: .SingleOrDefaultAsync(m => m.CourseID == id);
58: if (course == null)
59: {
60: return NotFound();
61: }
62:
63: return View(course);
64: }
65: #endregion
66:
67: // GET: Courses/Create
68: #region snippet_CreateGet
69: public IActionResult Create()
70: {
71: PopulateDepartmentsDropDownList();
72: return View();
73: }
74: #endregion
75: // POST: Courses/Create
76: #region snippet_CreatePost
77: [HttpPost]
78: [ValidateAntiForgeryToken]
79: public async Task<IActionResult> Create([Bind("CourseID,Credits,DepartmentID,Title")] Course course)
80: {
81: if (ModelState.IsValid)
82: {
83: _context.Add(course);
84: await _context.SaveChangesAsync();
85: return RedirectToAction(nameof(Index));
86: }
87: PopulateDepartmentsDropDownList(course.DepartmentID);
88: return View(course);
89: }
90: #endregion
91:
92: // GET: Courses/Edit/5
93: #region snippet_EditGet
94: public async Task<IActionResult> Edit(int? id)
95: {
96: if (id == null)
97: {
98: return NotFound();
99: }
100:
101: var course = await _context.Courses
102: .AsNoTracking()
103: .SingleOrDefaultAsync(m => m.CourseID == id);
104: if (course == null)
105: {
106: return NotFound();
107: }
108: PopulateDepartmentsDropDownList(course.DepartmentID);
109: return View(course);
110: }
111: #endregion
112:
113:
114: // POST: Courses/Edit/5
115: #region snippet_EditPost
116: [HttpPost, ActionName("Edit")]
117: [ValidateAntiForgeryToken]
118: public async Task<IActionResult> EditPost(int? id)
119: {
120: if (id == null)
121: {
122: return NotFound();
123: }
124:
125: var courseToUpdate = await _context.Courses
126: .SingleOrDefaultAsync(c => c.CourseID == id);
127:
128: if (await TryUpdateModelAsync<Course>(courseToUpdate,
129: "",
130: c => c.Credits, c => c.DepartmentID, c => c.Title))
131: {
132: try
133: {
134: await _context.SaveChangesAsync();
135: }
136: catch (DbUpdateException /* ex */)
137: {
138: //Log the error (uncomment ex variable name and write a log.)
139: ModelState.AddModelError("", "Unable to save changes. " +
140: "Try again, and if the problem persists, " +
141: "see your system administrator.");
142: }
143: return RedirectToAction(nameof(Index));
144: }
145: PopulateDepartmentsDropDownList(courseToUpdate.DepartmentID);
146: return View(courseToUpdate);
147: }
148: #endregion
149:
150: #region snippet_Departments
151: private void PopulateDepartmentsDropDownList(object selectedDepartment = null)
152: {
153: var departmentsQuery = from d in _context.Departments
154: orderby d.Name
155: select d;
156: ViewBag.DepartmentID = new SelectList(departmentsQuery.AsNoTracking(), "DepartmentID", "Name", selectedDepartment);
157: }
158: #endregion
159:
160: // GET: Courses/Delete/5
161: #region snippet_DeleteGet
162: public async Task<IActionResult> Delete(int? id)
163: {
164: if (id == null)
165: {
166: return NotFound();
167: }
168:
169: var course = await _context.Courses
170: .Include(c => c.Department)
171: .AsNoTracking()
172: .SingleOrDefaultAsync(m => m.CourseID == id);
173: if (course == null)
174: {
175: return NotFound();
176: }
177:
178: return View(course);
179: }
180: #endregion
181:
182: // POST: Courses/Delete/5
183: [HttpPost, ActionName("Delete")]
184: [ValidateAntiForgeryToken]
185: public async Task<IActionResult> DeleteConfirmed(int id)
186: {
187: var course = await _context.Courses
188: .SingleOrDefaultAsync(m => m.CourseID == id);
189: if (course != null)
190: {
191: _context.Courses.Remove(course);
192: await _context.SaveChangesAsync();
193: }
194: return RedirectToAction(nameof(Index));
195: }
196:
197: #region snippet_UpdateGet
198: public IActionResult UpdateCourseCredits()
199: {
200: return View();
201: }
202: #endregion
203:
204: #region snippet_UpdatePost
205: [HttpPost]
206: public async Task<IActionResult> UpdateCourseCredits(int? multiplier)
207: {
208: if (multiplier != null)
209: {
210: ViewData["RowsAffected"] =
211: await _context.Database.ExecuteSqlCommandAsync(
212: "UPDATE Course SET Credits = Credits * {0}",
213: parameters: multiplier);
214: }
215: return View();
216: }
217: #endregion
218: }
219: }
[!code-csharpMain]
1: //#define ScaffoldedCode
2: #define RevisedIndexMethod
3:
4: using System;
5: using System.Collections.Generic;
6: using System.Linq;
7: using System.Threading.Tasks;
8: using Microsoft.AspNetCore.Mvc;
9: using Microsoft.AspNetCore.Mvc.Rendering;
10: using Microsoft.EntityFrameworkCore;
11: using ContosoUniversity.Data;
12: using ContosoUniversity.Models;
13:
14: namespace ContosoUniversity.Controllers
15: {
16: public class CoursesController : Controller
17: {
18: private readonly SchoolContext _context;
19:
20: public CoursesController(SchoolContext context)
21: {
22: _context = context;
23: }
24:
25: // GET: Courses
26: #if ScaffoldedCode
27: public async Task<IActionResult> Index()
28: {
29: var schoolContext = _context.Courses
30: .Include(c => c.Department)
31: .AsNoTracking();
32: return View(await schoolContext.ToListAsync());
33: }
34: #elif RevisedIndexMethod
35: #region snippet_RevisedIndexMethod
36: public async Task<IActionResult> Index()
37: {
38: var courses = _context.Courses
39: .Include(c => c.Department)
40: .AsNoTracking();
41: return View(await courses.ToListAsync());
42: }
43: #endregion
44: #endif
45: // GET: Courses/Details/5
46: #region snippet_Details
47: public async Task<IActionResult> Details(int? id)
48: {
49: if (id == null)
50: {
51: return NotFound();
52: }
53:
54: var course = await _context.Courses
55: .Include(c => c.Department)
56: .AsNoTracking()
57: .SingleOrDefaultAsync(m => m.CourseID == id);
58: if (course == null)
59: {
60: return NotFound();
61: }
62:
63: return View(course);
64: }
65: #endregion
66:
67: // GET: Courses/Create
68: #region snippet_CreateGet
69: public IActionResult Create()
70: {
71: PopulateDepartmentsDropDownList();
72: return View();
73: }
74: #endregion
75: // POST: Courses/Create
76: #region snippet_CreatePost
77: [HttpPost]
78: [ValidateAntiForgeryToken]
79: public async Task<IActionResult> Create([Bind("CourseID,Credits,DepartmentID,Title")] Course course)
80: {
81: if (ModelState.IsValid)
82: {
83: _context.Add(course);
84: await _context.SaveChangesAsync();
85: return RedirectToAction(nameof(Index));
86: }
87: PopulateDepartmentsDropDownList(course.DepartmentID);
88: return View(course);
89: }
90: #endregion
91:
92: // GET: Courses/Edit/5
93: #region snippet_EditGet
94: public async Task<IActionResult> Edit(int? id)
95: {
96: if (id == null)
97: {
98: return NotFound();
99: }
100:
101: var course = await _context.Courses
102: .AsNoTracking()
103: .SingleOrDefaultAsync(m => m.CourseID == id);
104: if (course == null)
105: {
106: return NotFound();
107: }
108: PopulateDepartmentsDropDownList(course.DepartmentID);
109: return View(course);
110: }
111: #endregion
112:
113:
114: // POST: Courses/Edit/5
115: #region snippet_EditPost
116: [HttpPost, ActionName("Edit")]
117: [ValidateAntiForgeryToken]
118: public async Task<IActionResult> EditPost(int? id)
119: {
120: if (id == null)
121: {
122: return NotFound();
123: }
124:
125: var courseToUpdate = await _context.Courses
126: .SingleOrDefaultAsync(c => c.CourseID == id);
127:
128: if (await TryUpdateModelAsync<Course>(courseToUpdate,
129: "",
130: c => c.Credits, c => c.DepartmentID, c => c.Title))
131: {
132: try
133: {
134: await _context.SaveChangesAsync();
135: }
136: catch (DbUpdateException /* ex */)
137: {
138: //Log the error (uncomment ex variable name and write a log.)
139: ModelState.AddModelError("", "Unable to save changes. " +
140: "Try again, and if the problem persists, " +
141: "see your system administrator.");
142: }
143: return RedirectToAction(nameof(Index));
144: }
145: PopulateDepartmentsDropDownList(courseToUpdate.DepartmentID);
146: return View(courseToUpdate);
147: }
148: #endregion
149:
150: #region snippet_Departments
151: private void PopulateDepartmentsDropDownList(object selectedDepartment = null)
152: {
153: var departmentsQuery = from d in _context.Departments
154: orderby d.Name
155: select d;
156: ViewBag.DepartmentID = new SelectList(departmentsQuery.AsNoTracking(), "DepartmentID", "Name", selectedDepartment);
157: }
158: #endregion
159:
160: // GET: Courses/Delete/5
161: #region snippet_DeleteGet
162: public async Task<IActionResult> Delete(int? id)
163: {
164: if (id == null)
165: {
166: return NotFound();
167: }
168:
169: var course = await _context.Courses
170: .Include(c => c.Department)
171: .AsNoTracking()
172: .SingleOrDefaultAsync(m => m.CourseID == id);
173: if (course == null)
174: {
175: return NotFound();
176: }
177:
178: return View(course);
179: }
180: #endregion
181:
182: // POST: Courses/Delete/5
183: [HttpPost, ActionName("Delete")]
184: [ValidateAntiForgeryToken]
185: public async Task<IActionResult> DeleteConfirmed(int id)
186: {
187: var course = await _context.Courses
188: .SingleOrDefaultAsync(m => m.CourseID == id);
189: if (course != null)
190: {
191: _context.Courses.Remove(course);
192: await _context.SaveChangesAsync();
193: }
194: return RedirectToAction(nameof(Index));
195: }
196:
197: #region snippet_UpdateGet
198: public IActionResult UpdateCourseCredits()
199: {
200: return View();
201: }
202: #endregion
203:
204: #region snippet_UpdatePost
205: [HttpPost]
206: public async Task<IActionResult> UpdateCourseCredits(int? multiplier)
207: {
208: if (multiplier != null)
209: {
210: ViewData["RowsAffected"] =
211: await _context.Database.ExecuteSqlCommandAsync(
212: "UPDATE Course SET Credits = Credits * {0}",
213: parameters: multiplier);
214: }
215: return View();
216: }
217: #endregion
218: }
219: }
When the controller processes an HttpGet request, nothing is returned in ViewData["RowsAffected"], and the view displays an empty text box and a submit button, as shown in the preceding illustration.
When the Update button is clicked, the HttpPost method is called, and multiplier has the value entered in the text box. The code then executes the SQL that updates courses and returns the number of affected rows to the view in ViewData. When the view gets a RowsAffected value, it displays the number of rows updated.
In Solution Explorer, right-click the Views/Courses folder, and then click Add > New Item.
In the Add New Item dialog, click ASP.NET under Installed in the left pane, click MVC View Page, and name the new view UpdateCourseCredits.cshtml.
In Views/Courses/UpdateCourseCredits.cshtml, replace the template code with the following code:
[!code-htmlMain]
1: @{
2: ViewBag.Title = "UpdateCourseCredits";
3: }
4:
5: <h2>Update Course Credits</h2>
6:
7: @if (ViewData["RowsAffected"] == null)
8: {
9: <form asp-action="UpdateCourseCredits">
10: <div class="form-actions no-color">
11: <p>
12: Enter a number to multiply every course's credits by: @Html.TextBox("multiplier")
13: </p>
14: <p>
15: <input type="submit" value="Update" class="btn btn-default" />
16: </p>
17: </div>
18: </form>
19: }
20: @if (ViewData["RowsAffected"] != null)
21: {
22: <p>
23: Number of rows updated: @ViewData["RowsAffected"]
24: </p>
25: }
26: <div>
27: @Html.ActionLink("Back to List", "Index")
28: </div>
Run the UpdateCourseCredits method by selecting the Courses tab, then adding “/UpdateCourseCredits” to the end of the URL in the browser’s address bar (for example: http://localhost:5813/Courses/UpdateCourseCredits). Enter a number in the text box:
Click Update. You see the number of rows affected:
Click Back to List to see the list of courses with the revised number of credits.
Note that production code would ensure that updates always result in valid data. The simplified code shown here could multiply the number of credits enough to result in numbers greater than 5. (The Credits property has a [Range(0, 5)] attribute.) The update query would work but the invalid data could cause unexpected results in other parts of the system that assume the number of credits is 5 or less.
For more information about raw SQL queries, see Raw SQL Queries.
Examine SQL sent to the database
Sometimes it’s helpful to be able to see the actual SQL queries that are sent to the database. Built-in logging functionality for ASP.NET Core is automatically used by EF Core to write logs that contain the SQL for queries and updates. In this section you’ll see some examples of SQL logging.
Open StudentsController.cs and in the Details method set a breakpoint on the if (student == null) statement.
Run the app in debug mode, and go to the Details page for a student.
Go to the Output window showing debug output, and you see the query:
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (56ms) [Parameters=[@__id_0='?'], CommandType='Text', CommandTimeout='30']
SELECT TOP(2) [s].[ID], [s].[Discriminator], [s].[FirstName], [s].[LastName], [s].[EnrollmentDate]
FROM [Person] AS [s]
WHERE ([s].[Discriminator] = N'Student') AND ([s].[ID] = @__id_0)
ORDER BY [s].[ID]
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (122ms) [Parameters=[@__id_0='?'], CommandType='Text', CommandTimeout='30']
SELECT [s.Enrollments].[EnrollmentID], [s.Enrollments].[CourseID], [s.Enrollments].[Grade], [s.Enrollments].[StudentID], [e.Course].[CourseID], [e.Course].[Credits], [e.Course].[DepartmentID], [e.Course].[Title]
FROM [Enrollment] AS [s.Enrollments]
INNER JOIN [Course] AS [e.Course] ON [s.Enrollments].[CourseID] = [e.Course].[CourseID]
INNER JOIN (
SELECT TOP(1) [s0].[ID]
FROM [Person] AS [s0]
WHERE ([s0].[Discriminator] = N'Student') AND ([s0].[ID] = @__id_0)
ORDER BY [s0].[ID]
) AS [t] ON [s.Enrollments].[StudentID] = [t].[ID]
ORDER BY [t].[ID]You’ll notice something here that might surprise you: the SQL selects up to 2 rows (TOP(2)) from the Person table. The SingleOrDefaultAsync method doesn’t resolve to 1 row on the server. Here’s why:
- If the query would return multiple rows, the method returns null.
- To determine whether the query would return multiple rows, EF has to check if it returns at least 2.
Note that you don’t have to use debug mode and stop at a breakpoint to get logging output in the Output window. It’s just a convenient way to stop the logging at the point you want to look at the output. If you don’t do that, logging continues and you have to scroll back to find the parts you’re interested in.
Repository and unit of work patterns
Many developers write code to implement the repository and unit of work patterns as a wrapper around code that works with the Entity Framework. These patterns are intended to create an abstraction layer between the data access layer and the business logic layer of an application. Implementing these patterns can help insulate your application from changes in the data store and can facilitate automated unit testing or test-driven development (TDD). However, writing additional code to implement these patterns is not always the best choice for applications that use EF, for several reasons:
The EF context class itself insulates your code from data-store-specific code.
The EF context class can act as a unit-of-work class for database updates that you do using EF.
EF includes features for implementing TDD without writing repository code.
For information about how to implement the repository and unit of work patterns, see the Entity Framework 5 version of this tutorial series.
Entity Framework Core implements an in-memory database provider that can be used for testing. For more information, see Testing with InMemory.
Automatic change detection
The Entity Framework determines how an entity has changed (and therefore which updates need to be sent to the database) by comparing the current values of an entity with the original values. The original values are stored when the entity is queried or attached. Some of the methods that cause automatic change detection are the following:
DbContext.SaveChanges
DbContext.Entry
ChangeTracker.Entries
If you’re tracking a large number of entities and you call one of these methods many times in a loop, you might get significant performance improvements by temporarily turning off automatic change detection using the ChangeTracker.AutoDetectChangesEnabled property. For example:
Entity Framework Core source code and development plans
The source code for Entity Framework Core is available at https://github.com/aspnet/EntityFrameworkCore. Besides source code, you can get nightly builds, issue tracking, feature specs, design meeting notes, the roadmap for future development, and more. You can file bugs, and you can contribute your own enhancements to the EF source code.
Although the source code is open, Entity Framework Core is fully supported as a Microsoft product. The Microsoft Entity Framework team keeps control over which contributions are accepted and tests all code changes to ensure the quality of each release.
Reverse engineer from existing database
To reverse engineer a data model including entity classes from an existing database, use the scaffold-dbcontext command. See the getting-started tutorial.
## Use dynamic LINQ to simplify sort selection code
The third tutorial in this series shows how to write LINQ code by hard-coding column names in a switch statement. With two columns to choose from, this works fine, but if you have many columns the code could get verbose. To solve that problem, you can use the EF.Property method to specify the name of the property as a string. To try out this approach, replace the Index method in the StudentsController with the following code.
[!code-csharpMain]
1: #define SortFilterPage //or ScaffoldedIndex or SortOnly or SortFilter or DynamicLinq
2: #define ReadFirst //or CreateAndAttach
3: #define DeleteWithReadFirst // or DeleteWithoutReadFirst
4:
5: using System.Linq;
6: using System.Threading.Tasks;
7: using Microsoft.AspNetCore.Mvc;
8: using Microsoft.AspNetCore.Mvc.Rendering;
9: using Microsoft.EntityFrameworkCore;
10: using ContosoUniversity.Data;
11: using ContosoUniversity.Models;
12: using System;
13: using Microsoft.Extensions.Logging;
14:
15: #region snippet_Context
16: namespace ContosoUniversity.Controllers
17: {
18: public class StudentsController : Controller
19: {
20: private readonly SchoolContext _context;
21:
22: public StudentsController(SchoolContext context)
23: {
24: _context = context;
25: }
26: #endregion
27:
28: // GET: Students
29:
30: #if (ScaffoldedIndex)
31: #region snippet_ScaffoldedIndex
32: public async Task<IActionResult> Index()
33: {
34: return View(await _context.Students.ToListAsync());
35: }
36: #endregion
37: #elif (SortOnly)
38: #region snippet_SortOnly
39: public async Task<IActionResult> Index(string sortOrder)
40: {
41: ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
42: ViewData["DateSortParm"] = sortOrder == "Date" ? "date_desc" : "Date";
43: var students = from s in _context.Students
44: select s;
45: switch (sortOrder)
46: {
47: case "name_desc":
48: students = students.OrderByDescending(s => s.LastName);
49: break;
50: case "Date":
51: students = students.OrderBy(s => s.EnrollmentDate);
52: break;
53: case "date_desc":
54: students = students.OrderByDescending(s => s.EnrollmentDate);
55: break;
56: default:
57: students = students.OrderBy(s => s.LastName);
58: break;
59: }
60: return View(await students.AsNoTracking().ToListAsync());
61: }
62: #endregion
63: #elif (SortFilter)
64: #region snippet_SortFilter
65: public async Task<IActionResult> Index(string sortOrder, string searchString)
66: {
67: ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
68: ViewData["DateSortParm"] = sortOrder == "Date" ? "date_desc" : "Date";
69: ViewData["CurrentFilter"] = searchString;
70:
71: var students = from s in _context.Students
72: select s;
73: if (!String.IsNullOrEmpty(searchString))
74: {
75: students = students.Where(s => s.LastName.Contains(searchString)
76: || s.FirstMidName.Contains(searchString));
77: }
78: switch (sortOrder)
79: {
80: case "name_desc":
81: students = students.OrderByDescending(s => s.LastName);
82: break;
83: case "Date":
84: students = students.OrderBy(s => s.EnrollmentDate);
85: break;
86: case "date_desc":
87: students = students.OrderByDescending(s => s.EnrollmentDate);
88: break;
89: default:
90: students = students.OrderBy(s => s.LastName);
91: break;
92: }
93: return View(await students.AsNoTracking().ToListAsync());
94: }
95: #endregion
96: #elif (SortFilterPage)
97: #region snippet_SortFilterPage
98: public async Task<IActionResult> Index(
99: string sortOrder,
100: string currentFilter,
101: string searchString,
102: int? page)
103: {
104: ViewData["CurrentSort"] = sortOrder;
105: ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
106: ViewData["DateSortParm"] = sortOrder == "Date" ? "date_desc" : "Date";
107:
108: if (searchString != null)
109: {
110: page = 1;
111: }
112: else
113: {
114: searchString = currentFilter;
115: }
116:
117: ViewData["CurrentFilter"] = searchString;
118:
119: var students = from s in _context.Students
120: select s;
121: if (!String.IsNullOrEmpty(searchString))
122: {
123: students = students.Where(s => s.LastName.Contains(searchString)
124: || s.FirstMidName.Contains(searchString));
125: }
126: switch (sortOrder)
127: {
128: case "name_desc":
129: students = students.OrderByDescending(s => s.LastName);
130: break;
131: case "Date":
132: students = students.OrderBy(s => s.EnrollmentDate);
133: break;
134: case "date_desc":
135: students = students.OrderByDescending(s => s.EnrollmentDate);
136: break;
137: default:
138: students = students.OrderBy(s => s.LastName);
139: break;
140: }
141:
142: int pageSize = 3;
143: return View(await PaginatedList<Student>.CreateAsync(students.AsNoTracking(), page ?? 1, pageSize));
144: }
145: #endregion
146: #elif (DynamicLinq)
147: #region snippet_DynamicLinq
148: public async Task<IActionResult> Index(
149: string sortOrder,
150: string currentFilter,
151: string searchString,
152: int? page)
153: {
154: ViewData["CurrentSort"] = sortOrder;
155: ViewData["NameSortParm"] =
156: String.IsNullOrEmpty(sortOrder) ? "LastName_desc" : "";
157: ViewData["DateSortParm"] =
158: sortOrder == "EnrollmentDate" ? "EnrollmentDate_desc" : "EnrollmentDate";
159:
160: if (searchString != null)
161: {
162: page = 1;
163: }
164: else
165: {
166: searchString = currentFilter;
167: }
168:
169: ViewData["CurrentFilter"] = searchString;
170:
171: var students = from s in _context.Students
172: select s;
173:
174: if (!String.IsNullOrEmpty(searchString))
175: {
176: students = students.Where(s => s.LastName.Contains(searchString)
177: || s.FirstMidName.Contains(searchString));
178: }
179:
180: if (string.IsNullOrEmpty(sortOrder))
181: {
182: sortOrder = "LastName";
183: }
184:
185: bool descending = false;
186: if (sortOrder.EndsWith("_desc"))
187: {
188: sortOrder = sortOrder.Substring(0, sortOrder.Length - 5);
189: descending = true;
190: }
191:
192: if (descending)
193: {
194: students = students.OrderByDescending(e => EF.Property<object>(e, sortOrder));
195: }
196: else
197: {
198: students = students.OrderBy(e => EF.Property<object>(e, sortOrder));
199: }
200:
201: int pageSize = 3;
202: return View(await PaginatedList<Student>.CreateAsync(students.AsNoTracking(),
203: page ?? 1, pageSize));
204: }
205: #endregion
206: #endif
207:
208: // GET: Students/Details/5
209: #region snippet_Details
210: public async Task<IActionResult> Details(int? id)
211: {
212: if (id == null)
213: {
214: return NotFound();
215: }
216:
217: var student = await _context.Students
218: .Include(s => s.Enrollments)
219: .ThenInclude(e => e.Course)
220: .AsNoTracking()
221: .SingleOrDefaultAsync(m => m.ID == id);
222:
223: if (student == null)
224: {
225: return NotFound();
226: }
227:
228: return View(student);
229: }
230: #endregion
231:
232: // GET: Students/Create
233: public IActionResult Create()
234: {
235: return View();
236: }
237:
238: // POST: Students/Create
239: #region snippet_Create
240: [HttpPost]
241: [ValidateAntiForgeryToken]
242: public async Task<IActionResult> Create(
243: [Bind("EnrollmentDate,FirstMidName,LastName")] Student student)
244: {
245: try
246: {
247: if (ModelState.IsValid)
248: {
249: _context.Add(student);
250: await _context.SaveChangesAsync();
251: return RedirectToAction(nameof(Index));
252: }
253: }
254: catch (DbUpdateException /* ex */)
255: {
256: //Log the error (uncomment ex variable name and write a log.
257: ModelState.AddModelError("", "Unable to save changes. " +
258: "Try again, and if the problem persists " +
259: "see your system administrator.");
260: }
261: return View(student);
262: }
263: #endregion
264:
265: // GET: Students/Edit/5
266: public async Task<IActionResult> Edit(int? id)
267: {
268: if (id == null)
269: {
270: return NotFound();
271: }
272:
273: var student = await _context.Students
274: .AsNoTracking()
275: .SingleOrDefaultAsync(m => m.ID == id);
276: if (student == null)
277: {
278: return NotFound();
279: }
280: return View(student);
281: }
282:
283: // POST: Students/Edit/5
284: #if (CreateAndAttach)
285: #region snippet_CreateAndAttach
286: public async Task<IActionResult> Edit(int id, [Bind("ID,EnrollmentDate,FirstMidName,LastName")] Student student)
287: {
288: if (id != student.ID)
289: {
290: return NotFound();
291: }
292: if (ModelState.IsValid)
293: {
294: try
295: {
296: _context.Update(student);
297: await _context.SaveChangesAsync();
298: return RedirectToAction(nameof(Index));
299: }
300: catch (DbUpdateException /* ex */)
301: {
302: //Log the error (uncomment ex variable name and write a log.)
303: ModelState.AddModelError("", "Unable to save changes. " +
304: "Try again, and if the problem persists, " +
305: "see your system administrator.");
306: }
307: }
308: return View(student);
309: }
310: #endregion
311: #elif (ReadFirst)
312: #region snippet_ReadFirst
313: [HttpPost, ActionName("Edit")]
314: [ValidateAntiForgeryToken]
315: public async Task<IActionResult> EditPost(int? id)
316: {
317: if (id == null)
318: {
319: return NotFound();
320: }
321: var studentToUpdate = await _context.Students.SingleOrDefaultAsync(s => s.ID == id);
322: if (await TryUpdateModelAsync<Student>(
323: studentToUpdate,
324: "",
325: s => s.FirstMidName, s => s.LastName, s => s.EnrollmentDate))
326: {
327: try
328: {
329: await _context.SaveChangesAsync();
330: return RedirectToAction(nameof(Index));
331: }
332: catch (DbUpdateException /* ex */)
333: {
334: //Log the error (uncomment ex variable name and write a log.)
335: ModelState.AddModelError("", "Unable to save changes. " +
336: "Try again, and if the problem persists, " +
337: "see your system administrator.");
338: }
339: }
340: return View(studentToUpdate);
341: }
342: #endregion
343: #endif
344:
345: // GET: Students/Delete/5
346: #region snippet_DeleteGet
347: public async Task<IActionResult> Delete(int? id, bool? saveChangesError = false)
348: {
349: if (id == null)
350: {
351: return NotFound();
352: }
353:
354: var student = await _context.Students
355: .AsNoTracking()
356: .SingleOrDefaultAsync(m => m.ID == id);
357: if (student == null)
358: {
359: return NotFound();
360: }
361:
362: if (saveChangesError.GetValueOrDefault())
363: {
364: ViewData["ErrorMessage"] =
365: "Delete failed. Try again, and if the problem persists " +
366: "see your system administrator.";
367: }
368:
369: return View(student);
370: }
371: #endregion
372: // POST: Students/Delete/5
373: #if (DeleteWithReadFirst)
374: #region snippet_DeleteWithReadFirst
375: [HttpPost, ActionName("Delete")]
376: [ValidateAntiForgeryToken]
377: public async Task<IActionResult> DeleteConfirmed(int id)
378: {
379: var student = await _context.Students
380: .AsNoTracking()
381: .SingleOrDefaultAsync(m => m.ID == id);
382: if (student == null)
383: {
384: return RedirectToAction(nameof(Index));
385: }
386:
387: try
388: {
389: _context.Students.Remove(student);
390: await _context.SaveChangesAsync();
391: return RedirectToAction(nameof(Index));
392: }
393: catch (DbUpdateException /* ex */)
394: {
395: //Log the error (uncomment ex variable name and write a log.)
396: return RedirectToAction(nameof(Delete), new { id = id, saveChangesError = true });
397: }
398: }
399: #endregion
400: #elif (DeleteWithoutReadFirst)
401: #region snippet_DeleteWithoutReadFirst
402: [HttpPost]
403: [ValidateAntiForgeryToken]
404: public async Task<IActionResult> DeleteConfirmed(int id)
405: {
406: try
407: {
408: Student studentToDelete = new Student() { ID = id };
409: _context.Entry(studentToDelete).State = EntityState.Deleted;
410: await _context.SaveChangesAsync();
411: return RedirectToAction(nameof(Index));
412: }
413: catch (DbUpdateException /* ex */)
414: {
415: //Log the error (uncomment ex variable name and write a log.)
416: return RedirectToAction(nameof(Delete), new { id = id, saveChangesError = true });
417: }
418: }
419: #endregion
420: #endif
421: }
422: }
Next steps
This completes this series of tutorials on using the Entity Framework Core in an ASP.NET MVC application.
For more information about EF Core, see the Entity Framework Core documentation. A book is also available: Entity Framework Core in Action.
For information about how to deploy your web application after you’ve built it, see Publishing and deployment.
For information about other topics related to ASP.NET Core MVC, such as authentication and authorization, see the ASP.NET Core documentation.
Acknowledgments
Tom Dykstra and Rick Anderson (twitter @RickAndMSFT) wrote this tutorial. Rowan Miller, Diego Vega, and other members of the Entity Framework team assisted with code reviews and helped debug issues that arose while we were writing code for the tutorials.
Common errors
ContosoUniversity.dll used by another process
Error message:
Cannot open ‘…bin1.0.dll’ for writing – ‘The process cannot access the file’…1.0.dll’ because it is being used by another process.
Solution:
Stop the site in IIS Express. Go to the Windows System Tray, find IIS Express and right-click its icon, select the Contoso University site, and then click Stop Site.
Migration scaffolded with no code in Up and Down methods
Possible cause:
The EF CLI commands don’t automatically close and save code files. If you have unsaved changes when you run the migrations add command, EF won’t find your changes.
Solution:
Run the migrations remove command, save your code changes and rerun the migrations add command.
Errors while running database update
It’s possible to get other errors when making schema changes in a database that has existing data. If you get migration errors you can’t resolve, you can either change the database name in the connection string or delete the database. With a new database, there is no data to migrate, and the update-database command is much more likely to complete without errors.
The simplest approach is to rename the database in appsettings.json. The next time you run database update, a new database will be created.
To delete a database in SSOX, right-click the database, click Delete, and then in the Delete Database dialog box select Close existing connections and click OK.
To delete a database by using the CLI, run the database drop CLI command:
dotnet ef database dropError locating SQL Server instance
Error Message:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Solution:
Check the connection string. If you have manually deleted the database file, change the name of the database in the construction string to start over with a new database.
