Razor Pages - EF return values of 2 tables

Question

I have a database with 2 tables (clients and cars). The table cars have a column named client_id. In my Razor Pages project I have the models created for the 2 tables, but I need do a INNER JOIN to return the result to view side and do a foreach loop. So until now I used the IList and do this in .cshtml:

 @foreach (var item in Model.clients)
{
   <p>@Html.DisplayFor(x=> item.name)</p>
   <p>@Html.DisplayFor(x=> item.mail)</p>
}

And in code cshtm.cs

public IList<establishments> establishments;
IQueryable<establishments> establishments_filter;
establishments_filter = (from x in db.establishments where x.category == category select x);
establishments = establishments_filter.ToList();

Now the problem is that I cant do the same with a Inner Join or I don´t know how (most probably). I see in others posts that I can use a variable to receive the values like this:

var filter = (from x in db.cars join y in db.clients on x.id_client == y.id select new {
  mark = x.mark,
  model = x.model,
  name = y.name,
  mail = y.name
}.ToList();

But now, my real question... How I can do a foreach if the var filter is not acessible in cshtml?

Thanks

Answer 1

I think you can define a viewModel that contains the field in the filter object. Then you can foreach the viewModel in your page. A simple demo like below:

Model:

public class Car
{
    public int id { get; set; }
    public string mark { get; set; }
    public string model { get; set; }
    [ForeignKey("Client")]
    public int client_id { get; set; }
}

public class Client
{
    public int id { get; set; }
    public string name { get; set; }
    public string mail { get; set; }
}

ViewModel:

public class ViewModel
{
    public string Mark { get; set; }
    public string Model { get; set; }
    public string Name { get; set; }
    public string Mail { get; set; }
}

Index.cshtml:

@page
@model RazorPageApp.Pages.IndexModel
@{
    ViewData["Title"] = "Index";
}
<table>
    <thead>
        <tr>
            <th>Mark</th>
            <th>Model</th>
            <th>Name</th>
            <th>Mail</th>
        </tr>
    </thead>
    @foreach (var item in Model.filter)
    {
        <tr>
            <td>@Html.DisplayFor(x => item.Mark)</td>
            <td>@Html.DisplayFor(x => item.Model)</td>
            <td>@Html.DisplayFor(x => item.Name)</td>
            <td>@Html.DisplayFor(x => item.Mail)</td>
        </tr>
    }

</table>

Index.cshtml.cs:

public class IndexModel : PageModel
{
    private readonly ILogger<IndexModel> _logger;
    private readonly MyDbContext _db;
    public IndexModel(ILogger<IndexModel> logger, MyDbContext db)
    {
        _db = db;
        _logger = logger;
    }

    public List<ViewModel> filter { get; set; }

    public async Task OnGetAsync()
    {
        filter = await (from x in _db.cars
                    join y in _db.clients on x.client_id equals y.id
                    select new ViewModel
                    {
                        Mark = x.mark,
                        Model = x.model,
                        Name = y.name,
                        Mail = y.mail
                    }).ToListAsync();
    }

DataSource:

Result: