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>

Answer 1

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.