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

If you have the appropriate Foreign Keys, you can potentially use .Include(). You most likely will not need to use `Join` unless you don't have FK's. I think you can do something similar to [this question](https://stackoverflow.com/questions/6761104/how-does-linq-expression-syntax-work-with-include-for-eager-loading). — Cardi DeMonaco Jr, Sep 14 '20 at 20:04
I understood that part, the problem is that I receive the data in a variable, so how I can access in view side? I can't do a foreach because the viarable is not accessible. — d3pod, Sep 14 '20 at 20:09

score 2 · Accepted Answer · answered Sep 15 '20 at 03:11

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:

Razor Pages - EF return values of 2 tables

1 Answers1