Execute raw SQL query in ASP.NET MVC, database first mode

Question

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.

This is how I do:

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

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?

Saltern

tell us what the SQL is and we can probably provide the Linq or Lambda along with an explanation. – Colquitt

is the query the same each time you're calling it or is something different in the where clause? I would run SQL Profiler on it to see exactly what is being run on the server. From there you can paste the query into SSMS and run it with show execution plan enabled and diagnose what comes back from that. if there are missing indices the execution plan should show that as well. – Colquitt

I switch back to local server and it works fine. Thanks. Now my problem is my sql query is like: "select * from Table_A inner join Table_B ...", which means I will get a return of data from two table, and then return these data to the view. But the execution of sql query: "db.Database.SqlQuery<Table_A>(query)" can only return data from table "Table_A". What should I do if I want data from both "Table_A" and "Table_B"? – Saltern

It doesn't sound to me like EF is a good fit for what you're trying to do, are you only trying to get counts from a table? EF is for rehydrating objects from data in the database. – Colquitt

Yes I'm only trying to get counts. I worked around by create a public class which contains all the fields from the joined table that I'm gonna need and use the class as return element. What do you mean for rehydrating objects? – Saltern

Answer 1

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

Calling a Query that Returns Entities:

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);
}

Calling a Query that Returns Other Types of Objects:

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());
}

Calling an Update Query:

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

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

Gerry

Do you mean only the code first API provides this facility or your procedure can also be applied in the case of database first approach. – Madelinemadella

No, you can use this facility with the help of EF even if you do not use Code-First. – Tula

@MuratYıldız This response should be marked as an answer. Thank you for providing very detailed information and very informative link at the end. I got tremendous help out of it in doing my work since my background is also with database first approach. – Sideslip

@Sideslip You are welcome, I am happy that is was helpful for you :) – Tula

@Sideslip And also please do not forget vote up as the answer helpful for you ;) – Tula

@MuratYıldız Sure, you deserve it. I just did it. Thank you for helping readers (like me) of your response. – Sideslip