question

ConstantFlamingo-5096 avatar image
0 Votes"
ConstantFlamingo-5096 asked ConstantFlamingo-5096 commented

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

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!




dotnet-aspnet-webpagesdotnet-aspnet-core-razor
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ConstantFlamingo-5096,

According to your description, I still couldn't understand your requirement clearly. What model you want to search from the dbcontext and return to the client side? downtimeJoinReasons ? It seems you want to convert the LINQ result to Ienumerable , have you tried to use this method .AsEnumerable()?

1 Vote 1 ·

Hi Brando, thank you for the response. Yes, I want to return downtimeJoinReasons, the new table I have created using LINQ, to the client side. I have tried using .AsEnumerable() but I continue to get error CS0266 telling me I

can't implicitly convert 'System.Collections.Generic.IEnumerable<<anonymous type:System.DateTime StartDowntime, System.DateTime? EndDowntime, int DowntimeCode, string DowntimeReason>>' to 'System.Collections.Generic.IEnumerable<(Website.Models.TblDowntime, Website.Models.TblDowntimeReason)>'

I call it using the above, then declaring downtimeJoinReasons = downtimeJoinReasons.AsEnumerable();


Is this how you're supposed to use it?

0 Votes 0 ·

1 Answer

BrandoZhang-MSFT avatar image
1 Vote"
BrandoZhang-MSFT answered ConstantFlamingo-5096 commented

Hi @ConstantFlamingo-5096,

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();

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Brando, with the above code I get "error CS1922: Cannot initialize type 'DowntimeJoinReasons' with a collection initializer because it does not implement 'System.Collection.IEnumerable'"

I have tried adding System.Collection.IEnumerable into my class declaration for DowntimeJoinReasons, but I am told it's redundant possibly because I already have System.Collection.Generic in it. What do you advise?

0 Votes 0 ·
BrandoZhang-MSFT avatar image BrandoZhang-MSFT ConstantFlamingo-5096 ·

Hi @ConstantFlamingo-5096,

Please try below codes, the error means the we should set the property value for each DowntimeJoinReasons model.

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


1 Vote 1 ·

Thank you Brando, it works correctly now!


I was able to call .ToList() on the above, and access it from my Views page

0 Votes 0 ·