[ASP.Net Core EF] Using LINQ to join two tables and send the result as a model?

Question

I have two tables in my database that I want to join and send to my cshtml page, let's call them downtime and downtimeReasons because that is their names. Downtime has a column that has a number that corresponds to a number in downtimeReasons, which then has another column that gives me a detailed explanation. What I had before trying to join the table was something like

namespace Website.Pages.Downtime
{
    public class IndexModel : PageModel
    {
        //    private readonly ILogger<IndexModel> _logger;
        private readonly Website.Models.myContext _context;
        public IEnumerable<TblDowntime>? downtimes;
        public IndexModel(Website.Models.myContext context)
        {
            _context = context;
        }
        private int _pageSize = 50;

        public int NumPages = 1;
        public int PagingIndex;

        public IList<TblDowntime> TblDowntime { get; set; }

        public async Task OnGetAsync(int pagingIndex)
        {
            TblDowntime = await _context.TblDowntimes.ToListAsync();
            NumPages = await _context.TblDowntimes.CountAsync() / _pageSize;
            downtimes = await _context.TblDowntimes
            .OrderByDescending(Datetime => Datetime)
            .Skip(pagingIndex * _pageSize)
            .Take(_pageSize)
            .ToListAsync();

        }
    }
}

I've tried a couple of things to combine them, such as using LINQ to perform an inner join

but then I had the issue where I was no longer able to perform .ToListAsync() on it, since it was a query. I've also tried converting that query into an IEnumerable but I wasn't sure I was doing that right, and I've also tried doing the inner join inside the await _context part itself but couldn't figure out the syntax for that since I had something like

  downtimes = await _context.TblDowntimes
    (from d in _context.TblDowntimes 
                              join dr in _context.TblDowntimeReasons on d.DowntimeCode equals dr.DowntimeCode
                    select new
                    {
                        dr.DowntimeReason, 
                        d.StartDowntime,
                        d.EndDowntime
                    };)
    .OrderByDescending(Datetime => Datetime)
    .Skip(pagingIndex * _pageSize)
    .Take(_pageSize)
    .ToListAsync();

and kept getting red highlights in my code.

public async Task OnGetAsync(int pagingIndex)
{
    TblDowntime = await _context.TblDowntimes.ToListAsync();
    NumPages = await _context.TblDowntimes.CountAsync() / _pageSize;
    var downtimeJoinReasons = from d in _context.TblDowntimes 
                              join dr in _context.TblDowntimeReasons on d.DowntimeCode equals dr.DowntimeCode
                    select new
                    {
                        dr.DowntimeReason, 
                        d.StartDowntime,
                        d.EndDowntime
                    };
var downtimeEnumerable = new [] { downtimeJoinReasons};
    /*   foreach (var reason in downtimeJoinReasons)
       {
           Console.WriteLine(reason.StartDowntime + "\t\t" + reason.EndDowntime + "\t\t" + reason.DowntimeReason);
       }*/
    //  downtimes = await _context.downtimeJoinReasons
    downtimes = await _context.Downtimes
    .OrderByDescending(Datetime => Datetime)
    .Skip(pagingIndex * _pageSize)
    .Take(_pageSize)
    .ToListAsync();

}

I can currently print the data I need to the console terminal (see commented out part), but can't get it out on the .cshtml page which looks something like this now (with no reference to the downtimeReasons table)

> @foreach (var item in Model.downtimes) {

>         <tr>

> 

>                 @Html.DisplayFor(modelItem => item.StartDowntime)

>             </td>

>             <td>

>                 @Html.DisplayFor(modelItem => item.EndDowntime)

>             </td>

>             <td>

>                 @Html.DisplayFor(modelItem => item.DowntimeCode)

>             </td>

>         </tr>

Am I on the right track with any of these attempts? Any help would be appreciated!

Answer 1

Hi @ConstantFlamingo ,

According to the error message, you have use linq to select anonymous type not the Website.Models.TblDowntime, Website.Models.TblDowntimeReason, you should select the downtimeJoinReasonsthen you could convert it to the IEnumerable<downtimeJoinReasons>? downtimesreason;.

If you want to use this in the razor page, you should firstly create a new model which contains the StartDowntime, EndDowntime,DowntimeReason and add a new property inside the razor page, then you could use it. Like belwo:

Model:

    public class DowntimeJoinReasons  
    {  
        public DateTime? StartDowntime { get; set; }  
  
        public DateTime? EndDowntime { get; set; }  
  
        public string DowntimeReason { get; set; }  
    }  

Razor page:

 public IEnumerable<DowntimeJoinReasons>? downtimeJoinReasons;  

Usage:

  downtimeJoinReasons = (from d in _context.TblDowntimes   
                           join dr in _context.TblDowntimeReasons on d.DowntimeCode equals dr.DowntimeCode  
                 select new DowntimeJoinReasons  
                 {  
                     dr.DowntimeReason,   
                     d.StartDowntime,  
                     d.EndDowntime  
                 }).AsEnumerable();