How to map data from a raw sql query to Model properties, in ASP.NET MVC?

Question

Referring to this thread How to use raw normal sql in ASP.NET MVC without linq?

I know how to use DbContext to map data to Model properties and use them in Razor View. But sometimes I do need to use complex raw SQL query to get data from existing DB tables. From the above link I think I got the idea. SQL to Model, which is exactly what I need. But, my question is, what do I need to write in the DbContext class?

This is from the above reference

static ArticlesDataContext dc = new 
ArticlesDataContext(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

And in the Controller(...I think),

var arts = dc.ExecuteQuery<Art>(@"Select * from articles");

I guess the ArticlesDataContext class is something like this

public class ArticlesDataContext : DbContext
{
    public ArticlesDataContext() : base("name=ConnectionString")
    {
    }

    public DbSet<Article> Articles { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        ...
        // something here to map the DB table columns
    }
}

If I need to run the raw SQL query in Controller to get data, what do I do with the DbContext class, which is supposed to get data with EF? I'm confused.

Oh yea, and, what does that "@" do before the SQL statement?

I'm very new to ASP.NET MVC, hope I've made my question clear. Thx.

@ before any string in C# makes it to where you don't need to escape any character except double quote. — Timothy Kanski
– Timothy Kanski, Commented Apr 27, 2016 at 18:54
@SteveGreene I read that post before, it has a BloggingContext() which I guess it's a DbContext object. So my question is like what do I write in that BloggingContext class? — Cody Hung
– Cody Hung, Commented Apr 27, 2016 at 19:09
If you go the stored procedure route, you need a model and DbSet that match your return data. The non-entity option can be used to return an anonymous collection with no changes to the context. (If Timothy has answered your question you should click the check mark) — Steve Greene
– Steve Greene, Commented Apr 27, 2016 at 19:26

Timothy Kanski · Accepted Answer · 2016-04-27 19:03:15Z

0

DBContext is used to map a data entity(your database table) with a model (some plain old c# object you have created). Even though you're using SQL, you're still going to want to make a model to contain the results from your SQL query. Right now you're defining "arts" to be your list of results from the Query. Therefore, arts is probably going to be type IEnumerable<art> where art is the name of the model you created. Now in your DBContext, do something like:

public DbSet<art> arts { get; set; }

answered Apr 27, 2016 at 19:03

Timothy Kanski

1,88816 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to map data from a raw sql query to Model properties, in ASP.NET MVC?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related