在ASP.NET MVC中，第一个数据库模式中执行原始SQL查询 [英] Execute raw SQL query in ASP.NET MVC, database first mode

问题描述

我的项目模型数据库第一，并使用另一台服务器上数据库的远程访问。
我需要使用原始的SQL查询，因为我的查询是非常复杂的，我在SQL中感觉更舒服不LINQ。

The model of my project is database first, and uses remote access to database on another server. I need to use raw SQL query because my query is very complex and I feel more comfortable in SQl not LINQ.

这是我该怎么办：

        string query = "select * from Inquiry_TBL where ...";

        using (educationEntities db = new educationEntities())
        {
            var list = db.Database.SqlQuery<Inquiry_TBL>(query);
            ViewData["total"] = list.Count();
        }

问题是，有时我得到一秒钟内查询结果，有时它只是保持加载时间长，给我说，'呼唤'读取错误时，数据读取器是关闭的是不是一个有效的操作。

The problem is sometimes I get the query result within a second, sometimes it just keep loading for a long time and gives me an error that 'Calling 'Read' when the data reader is closed is not a valid operation.'

这是为什么？是不是有什么毛病我code，或者是因为我使用到另一台服务器的远程访问？在切换到本地服务器解决问题？

Why is that? Is there something wrong with my code, or because I'm using remote access to another server? Will switching to local server solve the problem?

推荐答案

实体框架code首先API包含的方法，使您能够直接通过SQL命令到数据库。您有以下选择：

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

•使用DbSet.SqlQuery方法返回实体类型的查询。返回的对象必须是由DbSet对象期望的类型，除非你跟踪转关会自动通过数据库上下文跟踪。 （请参阅关于AsNoTracking方法以下部分）。

• Use the DbSet.SqlQuery method for queries that return entity types. The returned objects must be of the type expected by the DbSet object, and they are automatically tracked by the database context unless you turn tracking off. (See the following section about the AsNoTracking method.)

•使用Database.SqlQuery方法返回不属于实体类型的查询。返回的数据不会被数据库上下文跟踪，即使您使用此方法来检索实体类型。

• 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.

•使用Database.ExecuteSqlCommand非查询命令。

• Use the Database.ExecuteSqlCommand for non-query commands.

调用返回实体的查询：

public async Task<ActionResult> 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);
}

调用返回的对象的其他类型的查询：

public ActionResult About()
{
    //Commenting out LINQ to show how to do the same thing in SQL.
    //IQueryable<EnrollmentDateGroup> = 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<EnrollmentDateGroup> data = db.Database.SqlQuery<EnrollmentDateGroup>(query);

    return View(data.ToList());
}

调用一个更新查询：

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

有关更多信息，请看看<一个href=\"http://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-using-mvc/advanced-entity-framework-scenarios-for-an-mvc-web-application#rawsql\"相对=nofollow>高级实体框架6方案为5 MVC Web应用程序（12 12）。希望这有助于...

For more information please have a look at Advanced Entity Framework 6 Scenarios for an MVC 5 Web Application (12 of 12). Hope this helps...

这篇关于在ASP.NET MVC中，第一个数据库模式中执行原始SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！