How to write pure SQL in asp.net mvc?

Question

I've created a controller class as below:

public class CelebrityController : Controller
{
    private MyDatabase db = new MyDatabase();

    public ActionResult Index()
    {
        var query = db.tableCelebrity.Include(t => t.tableMovie);
        return View(query.ToList());
    }
}

Now I want to do the same thing, but using a pure SQL query in the Index method. I am trying to replace code as below:

public class CelebrityController : Controller
    {
        private MyDatabase db = new MyDatabase();

        public ActionResult Index()
        {
            IEnumerable<tableCelebrity> results = db.ExecuteQuery<tableCelebrity>
            (@"SELECT c1.celebid as CelebrityID, t1.movieName as MovieName
                FROM tableCelebrity as c1, tableMovie as t1
                WHERE c1.celebid = t1.celebid");
        }
    }

But this gives me an error at the line db.ExecuteQuery<tableCelebrity>:

Error 'MyProject.Models.MyDatabase' does not contain a definition for 'ExecuteQuery' and no extension method 'ExecuteQuery' accepting a first argument of type 'MyProject.Models.MyDatabase' could be found

Is it possible with ASP.NET MVC?? If yes, then what changes I have to perform in the method?

Please help me to do that.

I am created Details method as below

public ActionResult ShortDetails(int? id, string tagname)
    {
        tblCelebrity tblcelebrity = db.tblCelebrities.SqlQuery<tblCelebrity>("SELECT * FROM dbo.tblCelebrity WHERE Celebrity_ID =" + id);

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

And Details View accepting type as

@model MyProject.Models.tblCelebrity

I also created tblCelebrity class in my project

But `tblCelebrity tblcelebrity = db.tblCelebrities.SqlQuery("SELECT * FROM dbo.tblCelebrity WHERE Celebrity_ID =" + id); this line gives error as The non-generic method 'System.Data.Entity.DbSet.SqlQuery(string, params object[])' cannot be used with type arguments

Solution

You want something similar to this :

using (var context = new BloggingContext())
{
    var blogs = context.Blogs.SqlQuery("SELECT * FROM dbo.Blogs").ToList();
}

You can find tutorial for that here : http://msdn.microsoft.com/en-us/data/jj592907.aspx

Comments

[asawyer]

Mvc has nothing to do with whatever database framework you choose to you.

[Radenko Zec]

You probably don't have using System.Linq on top

[MikeSW]

Use a micro-Orm, they are exactly what you want. Dapper.net, SqlFu, PetaPoco, ServiceStack.OrmLite etc. But you don't want to query the db in your controller. Create at least a repository for that.

[user3194155]

any good tutorial link for micro-Orm

[Simon Halsey]

It's not really MVC. Entity framework I think?

[Mike Perrenoud]

At a minimum include the relevant code snippets from the link for posterity--links as answers are not really acceptable here.

[user3194155]

sir but views are strongly typed view so when I am using "Select * from dbo.Blogs where ID='1'" that time var blogs contains string data but Details view expecting <Blogs> type data..how to solve this problem

[Radenko Zec]

You can create C# Class blogs that correspond to result of that view and than use that class for <Blogs>

[user3194155]

Error is : The model item passed into the dictionary is of type 'System.Data.Entity.Infrastructure.DbSqlQuery`1[MyProject.Mo‌​dels.tableCelebrity]‌​', but this dictionary requires a model item of type 'MyProject.Models.tblCelebrity'.

[user3194155]

I created tblCelebrity class and write query like tblcelebrity blog = db.tblCelebrities.SqlQuery<tblcelebrity>("SELECT * FROM dbo.tblCelebrity WHERE Celebrity_ID =" + id);

[Radenko Zec]

Try set correct model in your MVC view like @model IEnumerable<tblcelebrity>

[user3194155]

and error is : The non-generic method 'System.Data.Entity.DbSet<MyProject.Models.tblCelebrity>.Sql‌​Query(string, params object[])' cannot be used with type arguments

[user3194155]

Sir error is in action method that I sent you in previous message.