How to display only specific records from a database table in MVC?

Question

I'm new with MVC. I'm trying to develop a car reservation application. I have a Table named Reservations and one named Cars. I have to display the available cars for the users by the given Location, Time interval(cars that are not reserved at the moment).

My idea is to make a view to get the data from the user(location, time interval) and after he submits that data, he get's redirected to the page that displays those records(car details). The problem is that I don't really know how to bind the Views and how to display from the list.

Here's what I tried:

My controller:

public ActionResult DisplayCars()
        {
            return View();
        }

Here I've tried to make a list and add the resulted records from the database

 [HttpPost]
        public ActionResult AvailableCars([Bind(Include = "StartDate,EndDate,Location")] Reservations reservation)
        {
            List<Cars> carList = null;

            if (ModelState.IsValid)
            {
                if (reservation.StartDate != null && reservation.EndDate != null && reservation.Location != null)
                {
                    carList = db.Database.SqlQuery<Cars>("Select * from Cars WHERE Location = @location AND CarID NOT IN" +
                         "(Select CarID FROM Reservations WHERE NOT (StartDate > @endDate) OR (EndDate < @startDate))",
                         new SqlParameter("location", reservation.Location), new SqlParameter("endDate", reservation.EndDate), new SqlParameter("startDate", reservation.StartDate)).ToList<Cars>();
                }
                else if(reservation.StartDate == null && reservation.EndDate == null && reservation.Location != null)
                {
                    carList = db.Database.SqlQuery<Cars>("Select * from Cars WHERE Location = @location",
                         new SqlParameter("location", reservation.Location)).ToList<Cars>();
                }
                else if(reservation.StartDate != null && reservation.EndDate != null && reservation.Location == null)
                {
                    carList = db.Database.SqlQuery<Cars>("Select * from Cars WHERE CarID NOT IN" +
                        "(Select CarID FROM Reservations WHERE NOT (StartDate > @endDate) OR (EndDate < @startDate))",
                        new SqlParameter("endDate", reservation.EndDate), new SqlParameter("startDate", reservation.StartDate)).ToList<Cars>();
                }
            }

            if(carList == null)
            {
                ModelState.AddModelError("", "No available cars");
            }

            return View(carList);
        }

Here's my View for getting the input from the user:

@model RentC.UI.Models.Reservations

@{
    ViewBag.Title = "DetailsAvailableCars";
}

<h2>DetailsAvailableCars</h2>

@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })

        <div class="form-group">
            @Html.LabelFor(model => model.StartDate, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.StartDate, new { htmlAttributes = new { @class = "form-control date-picker" } })
                @Html.ValidationMessageFor(model => model.StartDate, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.EndDate, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.EndDate, new { htmlAttributes = new { @class = "form-control date-picker" } })
                @Html.ValidationMessageFor(model => model.EndDate, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Location, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Location, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Location, "", new { @class = "text-danger" })
            </div>
        </div>


        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Search" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "AvailableCars")
</div>

This is the View for displaying the records

@model IEnumerable<RentC.UI.Models.Cars>

@{
    ViewBag.Title = "Available Cars List";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.Plate)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Manufacturer)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Model)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.PricePerDay)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Location)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.Plate)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Manufacturer)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Model)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.PricePerDay)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Location)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { id=item.CarID }) |
            @Html.ActionLink("Details", "Details", new { id=item.CarID }) |
            @Html.ActionLink("Delete", "Delete", new { id=item.CarID })
        </td>
    </tr>
}

</table>

This is my Car Model

 public partial class Cars
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Cars()
        {
            this.Reservations = new HashSet<Reservations>();
        }

        public int CarID { get; set; }
        [Display(Name = "Cart Plate")]
        public string Plate { get; set; }
        public string Manufacturer { get; set; }
        public string Model { get; set; }
        public decimal PricePerDay { get; set; }
        public string Location { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<Reservations> Reservations { get; set; }
    }
}

This is the model for reservations

public partial class Reservations
    {
        public int ReservationID { get; set; }
        public int CarID { get; set; }
        public int CustomerID { get; set; }
        public System.DateTime StartDate { get; set; }
        public System.DateTime EndDate { get; set; }

        public virtual Cars Cars { get; set; }
    }

Please help me with some tips or ideas. Thank you!

Looking at your code in terms of structure, why do you choose to use raw SQL query over entity framework? — Formula12
– Formula12, Commented May 28, 2020 at 2:03
because of lack of knowledge, I just started with entity framework and I don't know how to use it yet — Ion cojocaru
– Ion cojocaru, Commented May 28, 2020 at 17:22

marc_s · Accepted Answer · 2020-06-06 08:02:55Z

Cannot tell what problem exactly do You have but I like the attempt so I create an example for You (link). Hope it will give you some ideas with all the comments.

In any case here are the main files:

HomeController.cs

using CarReservations.Models;
using CarReservations.ViewModels;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace CarReservations.Controllers
{
    [Route("[controller]")]
    public class HomeController : Controller
    {
        private readonly MyDbContext dbContext;

        public HomeController(MyDbContext dbContext)
        {
            this.dbContext = dbContext;
        }

        // 1. http://localhost:53436/Home/AvailableCars
        // ASP.NET Core cannot bind cars parameter neither [From*]  so it passes all Cars from our in-memory DB
        // We click <a asp-action="DisplayCars">Search for cars</a> in Home/AvailableCars.cshtml ----> DisplayCars action

        // 4. We got redirected from POST with model provided so cars are our result from POST not result from DB
        [HttpGet]
        // determinamtes URL: [Route("[controller]")]/[Route("AvailableCars")] -> Home/AvailableCars
        [Route("AvailableCars")]
        // determinates action name used by code (eg. new View()/asp-action): [ActionName("AvailableCars")] -> new View() = new View("AvailableCars")
        [ActionName("AvailableCars")]
        public async Task<IActionResult> AvailableCarsAsync(IEnumerable<Car> cars) => View(cars.Any()
            ? cars
            : await dbContext.Cars.AsNoTracking().ToListAsync());

        // 2. http://localhost:53436/Home/DisplayCars
        // ASP.NET Core cannot bind carSearch parameter neither [From*]  so it passes null
        // We submit a form ----> POST DisplayCars action (DisplayCarsPostAsync)
        [HttpGet]
        [Route("DisplayCars")]
        [ActionName("DisplayCars")]
        public IActionResult DisplayCarsGet(CarSearchViewModel carSearch) => View(carSearch);

        // 3. http://localhost:53436/Home/DisplayCars
        // got redirected from GET -> POST -> do stuff -> redirect back either to the same view (GET) on error or to some other GET action.
        [HttpPost]
        [Route("DisplayCars")]
        [ActionName("DisplayCars")]
        public async Task<IActionResult> DisplayCarsPostAsync(CarSearchViewModel carSearch)
        {
            if (ModelState.IsValid)
            {
                // liverage EF ORM
                // just build a query, hold on with execution
                IQueryable<Car> cars = dbContext.Cars
                    .Where(car => string.IsNullOrEmpty(carSearch.Location)
                        || car.Location.Equals(carSearch.Location));

                if (carSearch.StartDate.HasValue && carSearch.EndDate.HasValue)
                {
                    // hold on with execution
                    IQueryable<int> excludedCarsIds = dbContext.Reservations
                        .Where(reservation => reservation.EndDate < carSearch.StartDate.Value
                            && reservation.StartDate > carSearch.EndDate.Value)
                        .Select(reservation => reservation.CarID);

                    cars = cars.Where(car => !excludedCarsIds.Contains(car.CarID));
                }

                // ToListAsync will execute the query
                return View("AvailableCars", await cars.AsNoTracking().ToListAsync());
            }

            ModelState.AddModelError("", "No available cars");
            return View(carSearch);
        }
    }
}

CarSearchViewMode.cs

using System;

namespace CarReservations.ViewModels
{
    public class CarSearchViewModel
    {
        public string Location { get; set; }
        public DateTime? StartDate { get; set; }
        public DateTime? EndDate { get; set; }
    }
}

What I did:

replace raw SQL with EF's DbSet and LINQ. Find it way easier although SQL might not be translated as you wish.
replace HTML helper methods with asp tag helpers - just a mater of choice. You are new so I thought to show You ASP.NET Core tag helpers syntax
introduce a concept of ViewModels: models should stay related only to the DB entries, what we are displaying should be build from those models and be tailored specific to the view (thus they are called ViewModels) so the View can just display the date, not trying to shape it in some way.

In general how it is working (see comments from 1-4):

GET AvailableCars with all cars
-> GET DisplayCars (go to search form)
-> POST DisplayCars (submit form, validate, querying database, return results) -> GET AvailableCars with results only.

(3) is a Post/Redirect/Get pattern.

Good luck on a learning path.

Thank you very much for the help!!! I think I understood what I have to do!

Collectives™ on Stack Overflow

How to display only specific records from a database table in MVC?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related