C# Entity Framework Raw SQL Query

Question

Trying to tackle a simple web application, I've created a project in Visual Studio 2017 that works fine when I'm using models that correspond with tables (I followed the tutorial at: https://msdn.microsoft.com/en-us/library/jj206878(v=vs.113).aspx ).

I'd like to just run a raw sql query so I can work with the results, manipulate and write back into another table, or just display the results

In my controller I have:

    public ActionResult RunQuery001()
    {
        string query = "SELECT @@VERSION";
        return View(db.Servers.SqlQuery(query).ToArray());
    }

And I created a view that looks like:

    @model IEnumerable<omfgshootmenow.Server>

    @{
        ViewBag.Title = "RunQuery001";
    }

    @foreach (var item in Model)
    {
        @Html.DisplayFor(model => item.ToString());
    }

The code builds but when I execute I get an exception:

Message=The data reader is incompatible with the specified (model here) A member of the type, 'id', does not have a corresponding column in the data reader with the same name.

I keep thinking that the view is completely wrong (or the whole thing is) since the exception is referencing the key column of the model.

any help is appreciated.

@AnilKumar same error. I had tried that earlier from reading some other posts but no dice. — digitalohm, Mar 08 '17 at 06:22
if your question is all about EF, why mix and confuse with mvc? consider use console application code? — Lei Yang, Mar 08 '17 at 06:23
@LeiYang I'm trying to learn how to create a web application using mvc — digitalohm, Mar 08 '17 at 06:25
then does your problem has anything to do with mvc? and why not say mvc in the title? — Lei Yang, Mar 08 '17 at 06:26
can you show us what the db type is, how you are declaring this , db.Servers? — Anil, Mar 08 '17 at 06:31
@dbassassin I think Lei Yang was saying that this problem has nothing to do with mvc... I don't think you should tag it as such or use MVC in the title, in order to avoid confusion that this problem is specific to MVC (which it is not) — Mick, Mar 08 '17 at 06:36
@mick ok removed mvc from the title. I guess my question is getting down to, can I run a raw sql query as an action in a controller...and if so how can I return the results. I'm not sure how to associate @@servername or @@version with a model? — digitalohm, Mar 08 '17 at 06:40
@dbassassin, the tutorial you mentioned is for EF5 and you have tagged EF6 — Anil, Mar 08 '17 at 06:41

score 0 · Answer 1 · answered Mar 08 '17 at 06:43

I'm rather sure this is because the shape of your data doesn't match the type of Server.

When you are querying db.Servers you must return something that matches a Server.

If you want to return any non-entity type you should use context.Database.SqlQuery instead. See here: https://msdn.microsoft.com/en-us/library/jj592907(v=vs.113).aspx

Anil · Accepted Answer · 2017-03-08T07:00:02.967

0

To get a string, following should be used, refer section Writing SQL queries for non-entity types here

var query = db.Database.SqlQuery<string>("SELECT @@VERSION").FirstOrDefault(); 
ViewBag.SQLDBVersion= query; // viewbag is dynamic

On View

@MvcHtmlString.Create(ViewBag.SQLDBVersion)

edited Mar 08 '17 at 07:00

answered Mar 08 '17 at 06:47

Anil

3,722
2
24
49

thanks, after that, how does it get displayed in a view? – digitalohm Mar 08 '17 at 06:55
there are many ways refer http://www.c-sharpcorner.com/UploadFile/rohatash/options-for-passing-data-between-controller-to-view-in-mvc3/ and http://stackoverflow.com/questions/11296634/pass-a-simple-string-from-controller-to-a-view-mvc3 , one I added to answer. – Anil Mar 08 '17 at 07:01

score 0 · Answer 3 · answered Mar 08 '17 at 06:55

First of all make sure your model is IDENTICAL: I mean if any field is nullable, it have to be nullable in your model(update your model). Another reason, try to ommit the .ToArray(), save result in variable and check ii.Maybe, for test reasons, try to get date using other approach(eg https://msdn.microsoft.com/en-us/library/fksx3b4f.aspx)

C# Entity Framework Raw SQL Query

3 Answers3