How to combine 2 tables using Entity Framework 6 and Linq in an MVC Project?

Question

I want to know how to get data from relationals tables.

I want to get the images from the table named "Noticias1" that is relationated with "Noticias" (Noticias is an spanish word that means News sorry but it is for my University).

here the Diagram Image

Here my "Noticias1" table that gets the images that will contain the news in table "Noticias"

Here my "Noticia" Table that only contain 1 "Noticia" that means News in english

Here the actual view IMG

As you can see it only shows "Noticias" Table that only have 1 News that is not the problem.

Now I want to get the all the Images from "Noticias1" to every News in the table "Noticias" to show it in my view. (the named 1_0 will be the featured img).

Here my Controller

public class NoticiasController : Controller
    {
        // GET: Noticias
        public ActionResult Index(int? page)
        {
            var entities = new Model.CobecaIntranetEntities();
            //where n.FeHasta < DateTime.Now && n.Activo


            var noticias = from n in entities.Noticias
                           where n.Activo && n.FeDesde <= DateTime.Now && DateTime.Now <= n.FeHasta
                           select n;
            var noticiasArray = noticias.ToArray();

            int pageSize = 10;
            int pageNumber = (page ?? 1);

            return View(noticiasArray.ToPagedList(pageNumber, pageSize));
        }
    }

Here my View

@model PagedList.IPagedList<IntranetCorporativa.Model.Noticias>
@using PagedList;
@using PagedList.Mvc;
@{
    var format = "dddd, MMMM dd, yyyy";
    ViewBag.Title = "Index";
    Layout = "~/Views/Shared/_LayoutPage.cshtml";
    string principalTitulo = Model[0].Titulo;
    string principalContenido = Model[0].Contenido;
    DateTime principalFechaDesde = Convert.ToDateTime(Model[0].FeDesde);
    DateTime principalFechaHasta = Convert.ToDateTime(Model[0].FeHasta);
}

<script type="text/javascript">
    function changeDisplay(e) {

        var principalTitulo = $(e).text();
        var principalContenido = $(e).siblings(".vNoticiaContenido:first").html();
        var principalFecha = $(e).siblings(".vNoticiaFecha:first").val();

        $("#currentprincipalTitulo").html(principalTitulo);
        $("#currentprincipalContenido").html(principalContenido);
        $("#currentprincipalFecha").html(principalFecha);
    }
</script>

<style>
    .uppercase {
        text-transform: uppercase;
    }

    .limit {
        text-overflow: ellipsis;
        word-wrap: break-word;
        overflow: hidden;
        max-height: 3em;
        line-height: 1.7em;
    }
</style>
<!-- CONTENIDO -->
<div class="col-md-12 main">

    <div class="header sec-title-hd">
        <div class="bg-calendar"></div>
        <div class="col-md-7">
            <h5 class="pull-left">NOTICIAS</h5>
            <div>
                <a href="dashboard.html" class="btn sky-blue n-radius-b"> <img src="slider/img/arrow-left.png"> VOLVER</a>
            </div>
        </div>
    </div>

    <div class="content-inter">
        <div class="container-fluid sec-title-hd-sub">
            <div class="row">
                <div class="col-md-7">
                    <div>
                        <figure class="img_N">
                            <img id="currentprincipalImagen" src="#" class="img-responsive" alt="Here Principal IMG" />
                            <figcaption>
                                <p id="currentprincipalImagenTitulo">Here Img Description</p>
                            </figcaption>
                        </figure>
                    </div>
                    <div class="textnota">
                        <br>
                        <h5 id="currentprincipalTitulo" class="titulo_N uppercase">@principalTitulo</h5>
                        <p class="time">FeDesde: @principalFechaDesde.ToString(format)</p>
                        <p class="time">FeHasta: @principalFechaHasta.ToString(format)</p>
                        <p class="time">Hoy: @DateTime.Now.ToString(format)</p>
                        <div class="noti_P">
                            <p id="currentprincipalContenido">@principalContenido</p>
                        </div>
                    </div>
                </div>
                <div class="col-md-5">
                    <!-- Lado Derecho -->
                    @foreach (IntranetCorporativa.Model.Noticias n in Model)
                    {
                        <blockquote class="blockquote-nopadding bg-calendar-border-left">
                            <p class="time_f principalTitulo">@n.FeDesde.ToString(format)</p>
                            <a href="#" onclick="changeDisplay(this)" class="titulo_N">@n.Titulo</a>
                            <p class="text-justify limit vNoticiaContenido">@n.Contenido</p>
                        </blockquote>
                    }
                    Págnia @(Model.PageCount < Model.PageNumber ? 0 : Model.PageNumber) de @Model.PageCount
                    @Html.PagedListPager(Model, page => Url.Action("Index", new { page }))
                    <div>

                    </div>

                </div>
            </div>
        </div>
    </div>

</div>

Thanks for everyhing.

Answer 1

First, do yourself a favor and use better names for your classes and properties. You can modify them in the edmx designer as you like, an update won't destroy the changes. Change Noticias1 into the Spanish equivalent of NewsImage and rename the navigation properties.

Secondly, use Include to get the news images:

var noticias = from n in entities.Noticias.Include(n => n.Noticias2) // TODO: rename!!!
               where n.Activo && n.FeDesde <= DateTime.Now && DateTime.Now <= n.FeHasta
               select n;

Then somewhere within the @foreach (IntranetCorporativa.Model.Noticias n in Model) you're going to need a @foreach (var image in n.Noticias2) to display the images.

Answer 2

you will need a view model like this:

internal class NewsImagesViewModel
{
    public string Title{ get; set; }

    public IEnumerable<Image> Images { get; set; }

    //... some other properties
}

In the Controller:

IList<NewsImagesViewModel> newsImagesList;

using (DbContext dbContext = new DbContext())
{
   newsImagesList = dbContext.News
       .Select(n => new NewsImagesViewModel
       {
           Title = n.Title,
           Images = n.Images,
           // ... some other properties you may need
       }
       .ToList();                                        
 }
 return View(newsImagesList);

In the View

@model IEnumerable<Your.Namespace.NewsImagesViewModel>
@foreach(var item in Model)
{
 //....
}