How to use a SQL query in ASP.NET MVC and which to use?

Question

I am creating an ASP.NET MVC program about movies, series and actors. My goal for this problem is to add a parameter in my view that answers the questions "which actors participated in this movie/series?" and "This actor participated in which movies/series?" (Basically, when I click the details of the actor, I want it to appear on the movies that he participated and for movies I want to display which actors had participated in it).

For that I have created a table between the actor and the movie and the actor and the series, called Movies Participated and Series Participated

These are my tables:

--Movies Table
MoviesData (
MoviesID int PK,
MoviesName varchar(50),
MoviesDescription text,
MoviesCategory varchar(50),
MoviesYear varchar(4));

--Series Table
SeriesData (
SeriesID int PK,
SeriesName varchar(50),
SeriesDescription text,
SeriesCategory varchar(50),
SeriesYear varchar(4));

--Actors Table
ActorsData (
ActorID int PK,
ActorName varchar(50),
ActorAge varchar(3))

--SeasonsnEpisodes
SeasonsEpisodes (
SeasonsEpisodesId in PK,
SeriesID int FK,
SerieSeasons int,
SerieEpisodes int)

--MoviesParticipated
MoviesParticipated (
MoviesParticipationId int PK
ActorID int FK
MovieID varchar(50) FK )

--SeriesParticipated
SeriesParticipated (
SeriesParticipationId int PK
ActorID int FK
SeriesID varchar(50) FK)

But now i am stuck. I don't know whether i should do a query or something else.

EDIT:

Controller Details:

    public ActionResult Details()
        {
    PAPEntities db = new PAPEntities();
            SeriesViewModel[] SeriesVM = db.SeriesData.Select(serie => new SeriesViewModel
            {
                SerieID = serie.SerieID,
                SerieName = serie.SerieName,
                SerieCategory = serie.SerieCategory,
                SerieDescription = serie.SerieDescription,
                SerieYear = serie.SerieYear

            }).ToArray();


            return View(SeriesVM);
        }

ViewModel:

  public class SeriesViewModel
    {
        public int SerieID { get; set; }
        public string SerieName { get; set; }
        public string SerieCategory { get; set; }
        public string SerieDescription { get; set; }
        public string SerieYear { get; set; }
    }

Edit2 (forgot to add the view):

<table class="table table-bordered table-responsive table-hover">
       <tr>
        <td><h5><b>Id da Série </b></h5></td>
        <td><h5><b>Nome </b></h5></td>
        <td><h5><b>Categoria</b></h5></td>
        <td><h5><b>Descrição</b></h5></td>
        <td><h5><b>Ano de Lançamento</b></h5></td>
        <td><h5><b>Episódios</b></h5></td>
        </tr>
        @foreach (var item in Model)
        {
        <tr>
            <td>@item.SerieID</td>
            <td>@item.SerieName</td>
            <td>@item.SerieCategory
            <td>@item.SerieDescription</td>
            <td>@item.SerieYear</td>
            <td>@Html.ActionLink("Temporadas e Episódios", "DetailsSeasonsNEpisodes")</td>
        </tr>
        }
    </table>

What do you imagine the "something else" would be? If you want to retrieve data from a database, you use a query. Are you asking how the query should be done? — itsme86
– itsme86, Commented Jun 3, 2019 at 15:05
Hello @itsme86. The "something else" is an expression, im sorry. First, i wanted to know the query. I have tried a lot of them but I didnt really got into a good solution, Then, how I would implement it in MVC — Diogo Teixeira
– Diogo Teixeira, Commented Jun 3, 2019 at 15:12
Not sure if this fits as opinion based, but it is at the very least unclear what you are asking. How do we answer this without any follow-up questions? "Yes, do a query" — Mackan
– Mackan, Commented Jun 4, 2019 at 8:45
Hello @Mackan. I´ve been studying a query yesterday, the whole day, since im not the most experiencied programmer, as you already noticed. This is my query: SELECT m.*, a.* FROM Movie m INNER JOIN MovieParticipation mp ON mp.MovieID = m.MovieID INNER JOIN Actor a ON a.ActorID = mp.ActorID WHERE m.MovieID = 1. Can you check it so see if it correct? That query's goal is to list all the actors from a movie. If it is, can you explain to me how do I implement it in MVC in order to appear in a Details view? I will edit the question in order to explain to you what I´ve got in my view and view model — Diogo Teixeira
– Diogo Teixeira, Commented Jun 4, 2019 at 9:11
Yes, you want a query. Better use LINQ than plain SQL. Maybe the following tutorial can help you along: Accessing Your Model's Data from a Controller — Georg Patscheider
– Georg Patscheider, Commented Jun 4, 2019 at 9:57

Rhys Jones · Accepted Answer · 2019-06-05 13:09:04Z

There are various ways to approach what you want to do but sticking with what you have so far you could add the 'query' part in your controller code. Something like this;

public ActionResult Details(int actorId)
{
    PAPEntities db = new PAPEntities();

    SeriesViewModel[] SeriesVM = db.SeriesParticipated.Where(o => o.ActorId == actorId).Include(o => o.SeriesData).Select(o => new SeriesViewModel
    {
        SerieID = o.SeriesData.SerieID,
        SerieName = o.SeriesData.SerieName,
        SerieCategory = o.SeriesData.SerieCategory,
        SerieDescription = o.SeriesData.SerieDescription,
        SerieYear = o.SeriesData.SerieYear
    }).ToArray();

    return View(SeriesVM);

}

Without having your EF model I can't say whether this will build first time but it should get you going in the right direction.

Note that it's now starting with the SeriesParticipated data, filters it (Where) by ActorId, joins in (Include) the Series data, then selects the view models using references from SeriesParticipated.

Collectives™ on Stack Overflow

How to use a SQL query in ASP.NET MVC and which to use?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related