Using ViewModels and Linq in ASP.Net MVC 4

问题:

I'm learning MVC with a view to updating some of the old ASP/ASP.Net web apps I have to manage for my employer - and I'm starting to get my head around ViewModels (well trying - hence the post).

In my example to help me learn - I've got a Car Dealership. Each DealerShip has a number of cars that they hire out. The Cars are classed as CarTypes - and each CarType can have a number of Cars associated with it.

CarType.cs

//
// Holds types of car - this has a one to many relationship with Car class
//
[Table("tType")]
public class CarType
{
    [Key]
    public int type_id { get; set; }
    public int dealer_id { get; set; }
    public string type_name { get; set; }
    public string type_desc { get; set; }
    public virtual ICollection<Car> Car { get; set; }
}  


Car.cs

//
// Holds actual Cars
//

[Table("tCar")]
public class Car
{
    [Key]
    public int car_id { get; set; }
    public int dealer_id { get; set; }
    public int type_id { get; set; }
    public int car_order { get; set; }
    public string car_name { get; set; }
    public virtual ICollection<Rental> Rentals { get; set; }
    public virtual CarType CarType { get; set; }
}


Rental.cs

//
// This holds records of when cars are hired, and for which dealer, and for how long
//

[Table("tRental")]
public class Rental
{
    [Key]
    public int rental_id { get; set; }
    public int dealer_id { get; set; }
    public int car_id { get; set; }
    public DateTime hire_from { get; set; }
    public DateTime hire_to { get; set; }
    public virtual Car Car { get; set; }


}

The above classes map to my database. I've read that using Models like this is to be avoided, and instead you should use a ViewModel.

What I want to do in my example, is allow people to search a particular dealer, with a given date, and number of days hire they require.

I then want to populate the viewmodel with the following:

1. Date Car Required From
2. Number of Days Car is required
3. The DealerID (which is taken from the URL - eg. http://mycars.com/search/avail/dealerGlasgow )
4. A list of car types available
5. Number of each car type available

To do this, I came up with the ViewModel below:

SearchViewModel.cs

public class SearchViewModel
{
    [Required]
    public DateTime From { get; set; }
    [Required]
    public int DaysHire { get; set; }
    public int dealer_id { get; set; }
    public IQueryable<Car> Cars { get; set; }
}

Now I use this view model to display a Calendar and number of days search form in ASP.Net MVC - so I populate the dealer_id from a hidden field on the form (taken from the URL), and search the database, using the LINQ below in my controller.

The Search first looks for any rentals which overlap the dates the person is looking to hire for - it places that info into:

preBookedCars

It then searches for Cars by the dealer_id - and excludes any that have already been hired:

freeCars

The Controller code is:

    //
    // POST: /Search/Avail/DealerName

    [AllowAnonymous]
    [HttpPost]
    public ActionResult Avail(SearchViewModel model, string id)
    {
        if (ModelState.IsValid)
        {
            var dteFrom = model.From;
            var dteTo = model.From.AddDays(model.Days);

            // Search for any bookings for those dates, for the dealer specifed

            var prebookedCars = db.Cars
                .Where(car => car.Rentals.Any(rental =>
                    (model.dealer_id == rental.dealer_id && dteFrom >= rental.check_in && dteFrom < rental.check_out)
                    ||
                    (model.dealer_id == rental.dealer_id && dteTo > rental.check_in && dteTo <= rental.check_out)
                    ||
                    (model.dealer_id == rental.dealer_id && dteFrom <= rental.check_in && dteTo >= rental.check_out)
                ));

            // Search for Cars, include the Car Type (so we can have description etc), and exclude any cars that have already been hired

            var freeCars = db.Cars.Include("CarType")
                .Where(car => car.dealer_id == model.dealer_id)
                .Except(prebookedCars)
                .OrderBy(o => o.car_order);


            model.Cars = freeCars;

            return View(model);
        }
        else
        {
            return View(model);
        }


    }

I've a couple of questions:

a) Is my Linq for prebookedCars efficient? Is there a way I should be checking the:

model.dealer_id == rental.dealer_id

...just once, instead of 3 times (eg. by having 2 Where clauses for example)?

b) The query, when I'm stepping through the model in VS - for each Car in the model, I can click down in VS - ie. model.Cars.[Results View].Rentals - and see every Rental associated with that Car - even although I jut wanted to exclude any Cars which had already been booked. If I don't use that level of detail in my view, does it matter that this list is within the model? Or the fact that it's there, does that mean that I'm adding unnecessary overhead to my model/query?

c) I really wanted to end up, in my View, being able to show:

CarType (type_name) and Description (type_desc) - and in a drop down box, the number of cars of that type available.

What I've ended up with is the specific individual Cars - rather than being able to show:

model.CarType.type_name and model.CarType.count

How could I change my query, to return the number of car types available (or can I do it somehow with the model I have above?)

I know this is very long winded - but I thought showing the detail, would help - if anyone is able to help with any part of my query, I'd appreciate it.

thanks, Mark

回答1:

a) Is my Linq for prebookedCars efficient? Is there a way I should be checking the:

model.dealer_id == rental.dealer_id ...just once, instead of 3 times (eg. by having 2 Where clauses for example)?

You could alter your Statement to put the test for model.dealer_id first:

db.Cars.Where(car => car.Rentals.Any(rental =>
                    (model.dealer_id == rental.dealer_id) && (
                    (dteFrom >= rental.check_in && dteFrom < rental.check_out)
                    ||
                    (dteTo > rental.check_in && dteTo <= rental.check_out)
                    ||
                    (dteFrom <= rental.check_in && dteTo >= rental.check_out))
                ));

Regarding B: There are a few things that could be wrong. Are you using code first for this? If so, you may need to implement the Equals function, or it might be a casting linq thing. This article may be related

Regarding C: I'm not really sure I understand your question - but I think you're wanting to count the available cars for any given car type and display it? If so, since you already have an enumeration of available cars (assuming b works) as you're iterating through the list of car types you could get the number of available cars from your previous query:

 foreach (var cartype in db.CarTypes)
            {
                var numberAvailable = freeCars.Where(fc => fc.CarType == cartype);
            }

This is kind of pseudo-code as the loop should be in your view, and will probably be doing a number of other things, but I hope you get the idea.