Khoanglang89
Bạn hãy đăng nhập hoặc đăng ký
Khoanglang89

NHẬN THIẾT KẾ WEBSITE/ SOFTWARE - LÀM ĐỒ ÁN TỐT NGHIỆP, ĐỒ ÁN CHUYÊN MÔN NGÀNH CÔNG NGHỆ THÔNG TIN


You are not connected. Please login or register

Xem chủ đề cũ hơn Xem chủ đề mới hơn Go down  Thông điệp [Trang 1 trong tổng số 1 trang]

Admin

avatar

Admin
Admin
Loading
In the previous tutorial you implemented table-per-hierarchy inheritance. This tutorial includes introduces several topics that are useful to be aware of when you go beyond the basics of developing ASP.NET web applications that use Entity Framework Code First. Step-by-step instructions walk you through the code and using Visual Studio for the following topics:



The tutorial introduces several topics with brief introductions followed by links to resources for more information:



The tutorial also includes the following sections:



For most of these topics, you'll work with pages that you already created. To use raw SQL to do bulk updates you'll create a new page that updates the number of credits of all courses in the database:



Performing Raw SQL Queries


The Entity Framework Code First API includes methods that enable you to pass SQL commands directly to the database. You have the following options:


  • Use the DbSet.SqlQuery method for queries that return entity types. The returned objects must be of the type expected by the
    Code:
    DbSet
    object, and they are automatically tracked by the database context unless you turn tracking off. (See the following section about the AsNoTracking method.)
  • Use the Database.SqlQuery method for queries that return types that aren't entities. The returned data isn't tracked by the database context, even if you use this method to retrieve entity types.
  • Use the Database.ExecuteSqlCommand for non-query commands.


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, and these methods make it possible for you to handle such exceptions.

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.

Calling a Query that Returns Entities


The DbSet class provides a method that you can use to execute a query that returns an entity of type
Code:
TEntity
. To see how this works you'll change the code in the
Code:
Details
method of the
Code:

Department
controller.

In DepartmentController.cs, in the
Code:
Details
method, replace the
Code:
db.Departments.FindAsync
method call with a
Code:
db.Departments.SqlQuery
method call, as shown in the following highlighted code:

public async Task Details(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}

// Commenting out original code to show how to use a raw SQL query.
//Department department = await db.Departments.FindAsync(id);

// Create and execute raw SQL query.
string query = "SELECT * FROM Department WHERE DepartmentID = @p0";
Department department = await db.Departments.SqlQuery(query, id).SingleOrDefaultAsync();

if (department == null)
{
return HttpNotFound();
}
return View(department);
}

To verify that the new code works correctly, select the Departments tab and then Details for one of the departments.



Calling a Query that Returns Other Types of Objects


Earlier you created a student statistics grid for the About page that showed the number of students for each enrollment date. The code that does this in HomeController.cs uses LINQ:

var data = from student in db.Students
group student by student.EnrollmentDate into dateGroup
select new EnrollmentDateGroup()
{
EnrollmentDate = dateGroup.Key,
StudentCount = dateGroup.Count()
};

Suppose you want to write the code that retrieves this data directly in SQL rather than using LINQ. To do that you need to run a query that returns something other than entity objects, which means you need to use the Database.SqlQuery method.

In HomeController.cs, replace the LINQ statement in the
Code:
About
method with a SQL statement, as shown in the following highlighted code:

public ActionResult About()
{
// Commenting out LINQ to show how to do the same thing in SQL.
//IQueryable = from student in db.Students
// group student by student.EnrollmentDate into dateGroup
// select new EnrollmentDateGroup()
// {
// EnrollmentDate = dateGroup.Key,
// StudentCount = dateGroup.Count()
// };

// SQL version of the above LINQ code.
string query = "SELECT EnrollmentDate, COUNT(*) AS StudentCount "
+ "FROM Person "
+ "WHERE Discriminator = 'Student' "
+ "GROUP BY EnrollmentDate";
IEnumerable data = db.Database.SqlQuery(query);

return View(data.ToList());
}

Run the About page. It displays the same data it did before.



Calling an Update Query


Suppose Contoso University administrators want to be able to perform bulk 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
Code:
UPDATE
statement. The web page will look like the following illustration:



In CourseContoller.cs, add
Code:
UpdateCourseCredits
methods for
Code:
HttpGet
and
Code:
HttpPost
:

public ActionResult UpdateCourseCredits()
{
return View();
}

[HttpPost]
public ActionResult UpdateCourseCredits(int? multiplier)
{
if (multiplier != null)
{
ViewBag.RowsAffected = db.Database.ExecuteSqlCommand("UPDATE Course SET Credits = Credits * {0}", multiplier);
}
return View();
}

When the controller processes an
Code:
HttpGet
request, nothing is returned in the
Code:
ViewBag.RowsAffected
variable, 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
Code:
HttpPost
method is called, and
Code:
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 the
Code:
ViewBag.RowsAffected
variable. When the view gets a value in that variable, it displays the number of rows updated  instead of the text box and submit button, as shown in the following illustration:



In CourseController.cs, right-click one of the
Code:

UpdateCourseCredits
methods, and then click Add View.



In Views\Course\UpdateCourseCredits.cshtml, replace the template code with the following code:

@model ContosoUniversity.Models.Course

@{
ViewBag.Title = "UpdateCourseCredits";
}

Update Course Credits



@if (ViewBag.RowsAffected == null)
{
using (Html.BeginForm())
{


Enter a number to multiply every course's credits by: @Html.TextBox("multiplier")





}
}
@if (ViewBag.RowsAffected != null)
{


Number of rows updated: @ViewBag.RowsAffected


}

@Html.ActionLink("Back to List", "Index")


Run the
Code:
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:50205/Course/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.



For more information about raw SQL queries, see Raw SQL Queries on MSDN.

No-Tracking Queries


When a database context retrieves table rows and creates entity objects that represent them, by default it keeps track of whether the entities in memory are in sync with what's in the database. The data in memory acts as a cache and is used when you update an entity. This caching is often unnecessary in a web application because context instances are typically short-lived (a new one is created and disposed for each request) and the context that reads an entity is typically disposed before that entity is used again.

You can disable tracking of entity objects in memory by using the AsNoTracking method. Typical scenarios in which you might want to do that include the following:


  • A query retrieves such a large volume of data that turning off tracking might noticeably enhance performance.
  • You want to attach an entity in order to update it, but you earlier retrieved the same entity for a different purpose. Because the entity is already being tracked by the database context, you can't attach the entity that you want to change. One way to handle this situation is to use the
    Code:
    AsNoTracking
    option with the earlier query.


For an example that demonstrates how to use the AsNoTracking method, see the earlier version of this tutorial. This version of the tutorial doesn't set the Modified flag on a model-binder-created entity in the Edit method, so it doesn't need
Code:
AsNoTracking
.

Examining SQL sent to the database


Sometimes it's helpful to be able to see the actual SQL queries that are sent to the database. In an earlier tutorial you saw how to do that in interceptor code; now you'll see some ways to do it without writing interceptor code. To try this out, you'll look at a simple query and then look at what happens to it as you add options such eager loading, filtering, and sorting.

In Controllers/CourseController, replace the
Code:
Index
method with the following code, in order to temporarily stop eager loading:

public ActionResult Index()
{
var courses = db.Courses;
var sql = courses.ToString();
return View(courses.ToList());
}

Now set a breakpoint on the
Code:
return
statement (F9 with the cursor on that line). Press F5 to run the project in debug mode, and select the Course Index page. When the code reaches the breakpoint, examine the
Code:
sql
variable. You see the query that's sent to SQL Server. It's a simple
Code:
Select
statement.

{SELECT
[Extent1].[CourseID] AS [CourseID],
[Extent1].[Title] AS [Title],
[Extent1].[Credits] AS [Credits],
[Extent1].[DepartmentID] AS [DepartmentID]
FROM [Course] AS [Extent1]}

Click the magnifying class to see the query in the Text Visualizer.



Now you'll add a drop-down list to the Courses Index page so that users can filter for a particular department. You'll sort the courses by title, and you'll specify eager loading for the
Code:
Department
navigation property.

In CourseController.cs, replace the
Code:
Index
method with the following code:

public ActionResult Index(int? SelectedDepartment)
{
var departments = db.Departments.OrderBy(q => q.Name).ToList();
ViewBag.SelectedDepartment = new SelectList(departments, "DepartmentID", "Name", SelectedDepartment);
int departmentID = SelectedDepartment.GetValueOrDefault();

IQueryable courses = db.Courses
.Where(c => !SelectedDepartment.HasValue || c.DepartmentID == departmentID)
.OrderBy(d => d.CourseID)
.Include(d => d.Department);
var sql = courses.ToString();
return View(courses.ToList());
}

Restore the breakpoint on the
Code:
return
statement.

The method receives the selected value of the drop-down list in the
Code:
SelectedDepartment
parameter. If nothing is selected, this parameter will be null.

A
Code:
SelectList
collection containing all departments is passed to the view for the drop-down list. The parameters passed to the
Code:
SelectList
constructor specify the value field name, the text field name, and the selected item.

For the
Code:
Get
method of the
Code:
Course
repository, the code specifies a filter expression, a sort order, and eager loading for the
Code:
Department
navigation property. The filter expression always returns
Code:
true
if nothing is selected in the drop-down list (that is,
Code:
SelectedDepartment
is null).

In Views\Course\Index.cshtml, immediately before the opening
Code:
table
tag, add the following code to create the drop-down list and a submit button:

@using (Html.BeginForm())
{

Select Department: @Html.DropDownList("SelectedDepartment","All")


}

With the breakpoint still set, run the Course Index page. Continue through the first times that the code hits a breakpoint, so that the page is displayed in the browser. Select a department from the drop-down list and click Filter:



This time the first breakpoint will be for the departments query for the drop-down list. Skip that and view the
Code:
query
variable the next time the code reaches the breakpoint in order to see what the
Code:
Course
query now looks like. You'll see something like the following:

SELECT
[Project1].[CourseID] AS [CourseID],
[Project1].[Title] AS [Title],
[Project1].[Credits] AS [Credits],
[Project1].[DepartmentID] AS [DepartmentID],
[Project1].[DepartmentID1] AS [DepartmentID1],
[Project1].[Name] AS [Name],
[Project1].[Budget] AS [Budget],
[Project1].[StartDate] AS [StartDate],
[Project1].[InstructorID] AS [InstructorID],
[Project1].[RowVersion] AS [RowVersion]
FROM ( SELECT
[Extent1].[CourseID] AS [CourseID],
[Extent1].[Title] AS [Title],
[Extent1].[Credits] AS [Credits],
[Extent1].[DepartmentID] AS [DepartmentID],
[Extent2].[DepartmentID] AS [DepartmentID1],
[Extent2].[Name] AS [Name],
[Extent2].[Budget] AS [Budget],
[Extent2].[StartDate] AS [StartDate],
[Extent2].[InstructorID] AS [InstructorID],
[Extent2].[RowVersion] AS [RowVersion]
FROM [dbo].[Course] AS [Extent1]
INNER JOIN [dbo].[Department] AS [Extent2] ON [Extent1].[DepartmentID] = [Extent2].[DepartmentID]
WHERE @p__linq__0 IS NULL OR [Extent1].[DepartmentID] = @p__linq__1
) AS [Project1]
ORDER BY [Project1].[CourseID] ASC

You can see that the query is now a
Code:
JOIN
query that loads
Code:
Department
data along with the
Code:
Course
data, and that it includes a
Code:
WHERE
clause.

Remove the
Code:
var sql = courses.ToString()
line.

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.
  • Features introduced in Entity Framework 6 make it easier to implement TDD without writing repository code.


For more information about how to implement the repository and unit of work patterns, see the Entity Framework 5 version of this tutorial series. For information about ways to implement TDD in Entity Framework 6, see the following resources:



Proxy classes


When the Entity Framework creates entity instances (for example, when you execute a query), it often creates them as instances of a dynamically generated derived type that acts as a proxy for the entity. For example, see the following two debugger images. In the first image, you see that the
Code:
student
variable is the expected
Code:
Student
type immediately after you instantiate the entity. In the second image, after EF has been used to read a student entity from the database, you see the proxy class.





This proxy class overrides some virtual properties of the entity to insert hooks for performing actions automatically when the property is accessed. One function this mechanism is used for is lazy loading.

Most of the time you don't need to be aware of this use of proxies, but there are exceptions:


  • In some scenarios you might want to prevent the Entity Framework from creating proxy instances. For example, when you're serializing entities you generally want the POCO classes, not the proxy classes. One way to avoid serialization problems is to serialize data transfer objects (DTOs) instead of entity objects, as shown in the Using Web API with Entity Framework tutorial. Another way is to  disable proxy creation.
  • When you instantiate an entity class using the
    Code:
    new
    operator, you don't get a proxy instance. This means you don't get functionality such as lazy loading and automatic change tracking. This is typically okay; you generally don't need lazy loading, because you're creating a new entity that isn't in the database, and you generally don't need change tracking if you're explicitly marking the entity as
    Code:
    Added
    . However, if you do need lazy loading and you need change tracking, you can create new entity instances with proxies using the Create method of the
    Code:
    DbSet
    class.
  • You might want to get an actual entity type from a proxy type. You can use the GetObjectType method of the
    Code:
    ObjectContext
    class to get the actual entity type of a proxy type instance.


For more information, see Working with Proxies on MSDN.

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:


  • Code:
    DbSet.Find

  • Code:
    DbSet.Local

  • Code:
    DbSet.Remove

  • Code:
    DbSet.Add

  • Code:
    DbSet.Attach

  • Code:
    DbContext.SaveChanges

  • Code:
    DbContext.GetValidationErrors

  • Code:
    DbContext.Entry

  • Code:
    DbChangeTracker.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 AutoDetectChangesEnabled property. For more information, see Automatically Detecting Changes on MSDN.

Automatic validation


When you call the
Code:
SaveChanges
method, by default the Entity Framework validates the data in all properties of all changed entities before updating the database. If you've updated a large number of entities and you've already validated the data, this work is unnecessary and you could make the process of saving the changes take less time by temporarily turning off validation. You can do that using the ValidateOnSaveEnabled property. For more information, see Validation on MSDN.

Entity Framework Power Tools


Entity Framework Power Tools is a Visual Studio add-in that was used to create the data model diagrams shown in these tutorials. The tools can also do other function such as generate entity classes based on the tables in an existing database so that you can use the database with Code First. After you install the tools, some additional options appear in context menus. For example, when you right-click your context class in Solution Explorer, you get an option to generate a diagram. When you're using Code First you can't change the data model in the diagram, but you can move things around to make it easier to understand.





Entity Framework source code


The source code for Entity Framework 6 is available at http://entityframework.codeplex.com/. Besides source code, you can get [url=https://entityframework.codeplex.com/wikipage?title=Nightly Builds] nightly builds[/url], issue tracking, feature specs, [url=http://entityframework.codeplex.com/wikipage?title=Design Meeting Notes] design meeting notes[/url], 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 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. 

Summary


This completes this series of tutorials on using the Entity Framework in an ASP.NET MVC application. For more information about how to work with data using the Entity Framework, see the EF documentation page on MSDN and ASP.NET Data Access - Recommended Resources.

For more information about how to deploy your web application after you've built it, see ASP.NET Web Deployment - Recommended Resources in the MSDN Library. 

For information about other topics related to MVC, such as authentication and authorization, see the ASP.NET MVC - Recommended Resources.

Acknowledgments



  • Tom Dykstra wrote the original version of this tutorial, co-authored the EF 5 update, and wrote the EF 6 update. Tom is a senior programming writer on the Microsoft Web Platform and Tools Content Team.
  • Rick Anderson (twitter @RickAndMSFT) did most of the work updating the tutorial for EF 5 and MVC 4 and co-authored the EF 6 update. Rick is a senior programming writer for Microsoft focusing on Azure and MVC.
  • Rowan Miller and other members of the Entity Framework team assisted with code reviews and helped debug many issues with migrations that arose while we were updating the tutorial for EF 5 and EF 6.


VB


When the tutorial was originally produced for EF 4.1, we provided both C# and VB versions of the completed download project. Due to time limitations and other priorities we have not done that for this version. If you build a VB project using these tutorials and would be willing to share that with others, please let us know.

Common errors, and solutions or workarounds for them


Cannot create/shadow copy


Error Message:

Cannot create/shadow copy '' when that file already exists.


Solution

 Wait a few seconds and refresh the page.

Update-Database not recognized


Error Message (from the
Code:
Update-Database
command in the PMC):

The term 'Update-Database' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.


Solution

 Exit Visual Studio. Reopen project and try again.

Validation failed


Error Message (from the
Code:
Update-Database
command in the PMC):

Validation failed for one or more entities. See 'EntityValidationErrors' property for more details.


Solution

One cause of this problem is validation errors when the
Code:
Seed
method runs.  See Seeding and Debugging Entity Framework (EF) DBs for tips on debugging the
Code:
Seed
method.

HTTP 500.19 error


Error Message:

HTTP Error 500.19 - Internal Server Error
The requested page cannot be accessed because the related configuration data for the page is invalid.


Solution

One way you can get this error is from having multiple copies of the solution, each of them using the same port number. You can usually solve this problem by exiting all instances of Visual Studio, then restarting the project you're working on. If that doesn't work, try changing the port number. Right click on the project file and then click properties. Select the Web tab and then change the port number in the Project Url text box.

Error 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, change the name of the database in the construction string.

This article was originally created on February 14, 2014


_________________
Có nỗi buồn triền miên, làm trái tim hoá đá
Có những dòng lệ nhỏ, khiến đá hoá thành tim.

-------------------------------------------------------------------------------------------------------
Program Skills:  ASP.Net MVC 3/4; C#; VB.Net/ VB 6.0; Java Applet/Swing; JS/JavaScript; Bootstrap/ AngularJS; HTML/CSS; Turbo C/ Turbo C++; Pascal...
Xem mẫu Phần mêm quản lý ở đây     -           Xem mẫu Bán hàng trực tuyến ở đây
Nguyễn Ích Hoàn
new_life02081989@yahoo.com
nguyenichhoan1989@gmail.com
Xem lý lịch thành viên http://khoanglang89.forumvi.com

Xem chủ đề cũ hơn Xem chủ đề mới hơn Về Đầu Trang  Thông điệp [Trang 1 trong tổng số 1 trang]

Bài viết mới cùng chuyên mục

      Permissions in this forum:
      Bạn không có quyền trả lời bài viết