C#•9mo ago

how can I run a raw sql query in asp.net mvc.

I know how to write the query, but dont know how to execute it and return the query data to the view. The query contains data from multiple tables. I specifically need help with displaying the results in the view.

18 Replies

Pobiega•9mo ago

ASP.NET doesnt talk with database on its own, so what are you using to communicate with the database? SqlConnection? EF? Dapper? and you say that you specifially need help with displaying the results, does that mean you've successfully managed to execute the query?

qwertyz•9mo ago

Ef i leanrt i could use a sql query string and then do from sql (string) i jsut dont know how to display on view

Pobiega•9mo ago

you really shouldnt be using raw sql queries with EF unless its unavoidable

qwertyz•9mo ago

ight how come should i use linq i jsut prefer sql is it not possible

Pobiega•9mo ago

Because you lose all kind of type safety and you bypass EF like, why bother using EF at all if you want to write your SQL yourself? use dapper then

qwertyz•9mo ago

true

Pobiega•9mo ago

absolutely nothing wrong with handwriting SQL, but if thats what you want, use the right tools for that which imho is dapper

qwertyz•9mo ago

but what i am sturgling with is i dont get to hwo to make a query in linq that returns mutiple results to a view

Pobiega•9mo ago

multiple results? as in just more than one row?

qwertyz•9mo ago

like a list say i wnat to selct level and subejcts yes like not jsut the sum of the total vlaues

Pobiega•9mo ago

yeah thats fine, you just use .ToListAsync()

qwertyz•9mo ago

yes but in the view what model do i base it off

Pobiega•9mo ago

var results = await context.Levels.Where(x => x.Something).ToListAsync(); boom done

qwertyz•9mo ago

do i need to create a sperate vm if i have data form diffrent tables

Pobiega•9mo ago

yeah, you should project your query results to a "DTO" model

qwertyz•9mo ago

oh ok that makes senes thank you very much enjoy the rest of your day

Pobiega•9mo ago

var results = await _context.Levels
    .Select(l => new LevelDto
    {
        Name = l.Name,
        Subjects = l.Subjects.Select(s => new SubjectDto
        {
            Name = s.Name
        })
    }).ToListAsync();

var results = await _context.Levels
    .Select(l => new LevelDto
    {
        Name = l.Name,
        Subjects = l.Subjects.Select(s => new SubjectDto
        {
            Name = s.Name
        })
    }).ToListAsync();

something like this

z0mb•9mo ago

Depending on your use case, Dapper might be a great option for you in the future if you find you still prefer sql over linq

Gaming

Programming

how can I run a raw sql query in asp.net mvc.