Exercise 3: Querying the Database with Parameters

In this exercise, you will learn how to query the database using parameters and how to use the Query Result Shaping, a feature that reduces the number of accesses to the database to retrieve data in a more efficient way.

Task 1 – Modifying StoreController to Retrieve Albums from Database

In this task, you will change the StoreController class to access the database to retrieve albums from a specific genre.

1. If not already open, start Microsoft Visual Web Developer 2010 Express from Start | All Programs | Microsoft Visual Studio 2010 Express | Microsoft Visual Web Developer 2010 Express.
2. In the File menu, choose Open Project. In the Open Project dialog, browse to Source\Ex03-QueryingTheDatabaseWithParametersDBFirst\Begin (or Ex03-QueryingTheDatabaseWithParametersCodeFirst\Begin if you want to use a Code First approach), select MvcMusicStore.sln and click Open. Alternatively, you may continue with the solution that you obtained after completing any of the previous exercises.
3. Open the StoreController class to change the Browse action method. To do this, in the Solution Explorer, expand the Controllers folder and double-click StoreController.cs.

4. Change the Browse action method to retrieve albums for a specific genre. To do this, replace the following code:

   (Code Snippet – ASP.NET MVC Models and Data Access – Ex3 StoreController BrowseMethod – CSharp)

   C#

   //

   FakePre-f943ab5af8f14b7ba79bddfba0ff3be3-277d056f389748d88f85f16b2ff56861FakePre-799bf745c558464b93307046c6422b3c-6d485be878084a648eacfe7f80d774c2FakePre-e2f214fcd0cd4ce68344a96147190802-db8dba1bc9254c4faf16ec10a061c919FakePre-ccbeb5a758e2457588e6b594dcaee8bb-42a2f043e9504c9b92738132209f4f66 // Retrieve Genre and its Associated Albums from database var genreModel = storeDB.Genres.Include("Albums") .Single(g => g.Name == genre); var viewModel = new StoreBrowseViewModel() { Genre = genreModel, Albums = genreModel.Albums.ToList() };FakePre-d37ecf33bf844ec89b5a5224ebc1b5f4-84d15a2664194630b287811c3837daa9FakePre-37c7d431ba994457b3136a491347e116-66e873cffc6c4aa9a301b912df5626c5FakePre-c101903e75f74c8aacfb33f1c9b257b1-894bab26bb684cc7901101e520391031FakePre-9cf458e1148a4170966936dfcd0cadd9-8f41e2eda2ba4d3390ec452427b4d515FakePre-d6023ba6675d47fb913a2f6cfff1d382-4787c61d124945b2a58cdc54bb141b5d
   Note:

   You can use the .Single() extension in LINQ because in this case only one genre is expected for an album. The Single() method takes a Lambda expression as a parameter, which in this case specifies a single Genre object such that its name matches the value defined.
   Note:

   You will take advantage of a feature that allows you to indicate other related entities you want loaded as well when the Genre object is retrieved. This feature is called Query Result Shaping, and enables you to reduce the number of times needed to access the database to retrieve information. In this scenario, you will want to pre-fetch the Albums for the Genre you retrieve.

   The query includes Genres.Include(“Albums”) to indicate that you want related albums as well. This will result in a more efficient application, since it will retrieve both Genre and Album data in a single database request.

Task 2 – Running the Application

In this task, you will try out the Application in a web browser and obtain albums for a specific genre from the database.

1. Press F5 to run the Application.

2. The project starts in the Home page. Change the URL to /Store/Browse?genre=Jazz to verify that the results are being pulled from the database.

   Figure 40

   Browsing /Store/Browse?genre=Jazz

Task 3 – Accessing Albums by Id

In this task, you will repeat the previous procedure, in this case, to obtain albums by Id.

1. Close the browser if needed, to return to Visual Studio. Open the StoreController class to change the Details action method. To do this, in the Solution Explorer, expand the Controllers folder and double-click StoreController.cs.

2. Change the Details action method to retrieve albums details based on their Id. To do this, replace the following code:

   (Code Snippet – ASP.NET MVC Models and Data Access – Ex3 StoreController DetailsMethod – CSharp)

   C#

   //

   FakePre-2238077fdc4f4fbeab2de145309b6e83-0fa59afec29f4b2796ef48b822c3bc37FakePre-4972c205292f4a3c9502f6606fabd788-af6739c6150d4817a659732223c1b1b0FakePre-de3f3608eca24849b8ab8a798217cbd5-2f281e0e2f194e44be5e55486fb80709FakePre-c46d6e913e5f4989ae75e2cfc2ef9bba-fd9ee937e7c240f3b7ec01950e4f10cf var album = storeDB.Albums.Single(a => a.AlbumId == id);FakePre-9e9773b2f544439dac4b604c391658ec-6b3543a14b344ab3972836d2cf54c677FakePre-62cb0f0c19194f08994c27baab23ab12-db62ca1a22e14846bb670e6beea13982FakePre-d6cd769ac5144fb5a230ed60159a6ac5-8d9326c70786409e97c8efa44bfa3d28

Task 4 – Running the Application

In this task, you will try out the Application in a web browser and obtain album details based on its Id.

1. Press F5 to run the Application.

2. The project starts in the Home page. Change the URL to /Store/Details/500 to verify that the results are being pulled from the database.

   Figure 41

   Browsing /Store/Details/500

Next Step

Summary