C#•10mo 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•10mo 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?

qwertyzOP•10mo 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•10mo ago

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

qwertyzOP•10mo ago

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

Pobiega•10mo 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

qwertyzOP•10mo ago

true

Pobiega•10mo ago

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

qwertyzOP•10mo 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•10mo ago

multiple results? as in just more than one row?

qwertyzOP•10mo ago

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

Pobiega•10mo ago

yeah thats fine, you just use .ToListAsync()

qwertyzOP•10mo ago

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

Pobiega•10mo ago

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

qwertyzOP•10mo ago

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

Pobiega•10mo ago

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

qwertyzOP•10mo ago

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

Pobiega•10mo 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•10mo 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.