ASP.NET MVC Razor render ListView by ID

Question

I'm new to Razor. The problem that I have is displaying a set of data by ID. I have 2 tables, Stores and Book.

Table Store:

ID_store	Name_store
1	Store 1
2	Store 2

Table Book:

ID	ID_store	ID_book	available
1	1	1	0
2	1	2	0
3	1	3	1
4	2	1	1
5	2	2	1

This is what I have right now

Models

public class FranchiseViewModel
{
    public int ID_franchise { get; set; }
    public List<Store> listStore { get; set; }
    public List<Book> listBook { get; set; }
}

public class Store
{
    public int ID_store { get; set; }
    public string Name_store { get; set; }
}

public class Book
{
    public int ID { get; set; }
    public int ID_store { get; set; }
    public int ID_book { get; set; }
    public string Name_book { get; set; }
    public int available { get; set; }
}

Controller:

public ActionResult Index()
{
     FranchiseViewModel ViewM = new FranchiseViewModel();         
     ViewM.listStore = Repositories.GetStores();
     ViewM.listBook = Repositories.GetBooks();
        
     return View(ViewM);
}

Repository: GetBooks() is basically the same

public static List<Store> GetStore()
{
    List<Store> ListStore = new List<Store>();

    using (var con = ConexionDAO.ObtenerConexion())
    {
        using (SqlCommand cmd = new SqlCommand("getStores_Sel", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            con.Open();

            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    Store objectStore = new Store();

                    objectStore.ID_store = reader.GetFieldValue<int>(0);
                    objectStore.Name_store = reader.GetFieldValue<string>(1);
                
                    ListStore.Add(objectStore);
                }
            }

            con.Close();
        }
    }

    return ListStore;
}

View:

<div>
    @if (Model.listStore.Count > 0)
    {
        var cnt = 0;
        foreach (var item in Model.listStore)
        {
            <div class="border border-dark rounded border-2" style="margin-top:1rem;">
                <div class="row" style="margin-left:.5rem;margin-top:.5rem;">
                    <div class="col-xs-12 col-sm-12 col-md-12 col-lg-5 col-xl-5 col-xxl-5">
                        <div class="input-group">
                            <div class="input-group-prepend align-self-center">
                                <label class="form-label labels" style="margin-right:1rem">Store:</label>
                            </div>
                            <input type="text" class="form-control"
                                   id="Store__[@cnt]"
                                   name="Store__[@cnt]"
                                   value="@item.Name_store">
                        </div>
                    </div>                   
                </div>
                <div class="row" style="margin-left:.5rem;margin-top:.5rem; margin-bottom:.5rem">
                    <div class="col-xs-12 col-sm-12 col-md-12 col-lg-12 col-xl-12 col-xxl-12">
                        <label class="form-label labels" style="margin-right:1rem">Books:</label>
                    </div>
                </div>
                <div class="row" style="margin-left: .5rem; margin-top: .5rem;">
                    <div class="col-xs-12 col-sm-12 col-md-12 col-lg-6 col-xl-6 col-xxl-6" style="margin-left:2rem">
                        <table class="table">
                            <thead>
                                <tr>
                                    <th scope="col" style="text-align: center">Book</th>
                                    <th scope="col" style="text-align: center">Available</th>
                                </tr>
                            </thead>
                            <tbody>
                                @if (Model.listBook.Count > 0)
                                {
                                    foreach (var item2 in Model.listBook)
                                    {

                                        <tr>
                                            <td>
                                                <span>@item2.Name_book</span>
                                                <input type="hidden" value="@item2.ID_book"/>
                                            </td>
                                            <td style=" text-align: center; ">
                                                @if (item2.available == 0)
                                                {
                                                    <span>No</span>
                                                }
                                                else
                                                {
                                                    <span>Yes</span>
                                                }
                                            </td>
                                        </tr>
                                    }
                                }
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
            cnt++;
        }
    }
</div>

With this the result is 2 boxes with each of the Stores with all the books inside the table, even if the book doesn't correspond to that store.

What is the best approach, so the end result is: unique main boxes (1 for each store), with the table within it, only containing the books that correspond with that store/ID

Answer 1

You print all the books inside each store, so you see the book corresponds to all the stores. The solution to this is to revise the logic of the nested foreach loop.

Note: Don't have to put if before nested foreach loop Here is the solution

foreach (var item2 in Model.listBook.Where(x => x.ID_store == item.ID_store).ToList())
{
    <tr>
        <td>
            <span>@item2.Name_book</span>
            <input type="hidden" value="@item2.ID_book"/>
        </td>
        <td style=" text-align: center; ">
            @if (item2.available == 0)
            {
                <span>No</span>
            }
            else
            {
                <span>Yes</span>
            }
        </td>
    </tr>
}