Entity Framework (Database First) Part 4: Using the LINQ and Projection To SELECT Columns From Entities

In the last blog we just selected the Products entities from the NorthwindEntities with this Linq Query.

var query = from prod in nwctx.Products
         select prod;

It gets the job done but, the GridView displays the CategoryID and SupplierID as integers, which is not very useful to your users. Plus, we don't want to display the ProductID.












In this blog we are going to refine the LINQ query so that the GridView only display the columns that we want to display with Projection and Anonymous Types.  Projection is to transform an object into a new form with only the properties that you want in the new form.  In our case we want to transform the Products DataSet to a new form that only returns the data that we want.

The resulting GridView after projection is looks like this.

Notice that the ID column is hidden and the Category and Supplier shows the actual Category name and the Supplier name.

Here are the steps:

1.  Open up the project that you've completed on the last blog.
2. Change the ID of the GridView to ProductsGrid 3.  Comment out the code in the Page_Load method
4.  In the Default.aspx.cs code behind file add the following method

        protected void BindProductsGrid()
        {
            using(NorthwindEntities ntcx = new NorthwindEntities())
            {
                var query = from prod in ntcx.Products
                            join cat in ntcx.Categories on prod.CategoryID equals cat.CategoryID
                            join sup in ntcx.Suppliers on prod.SupplierID equals sup.SupplierID
                            select new
                            {
                                ID = prod.ProductID,
                                Name = prod.ProductName,
                                Price = prod.UnitPrice,
                                QuantityPerUnit = prod.QuantityPerUnit,
                                Category = cat.CategoryName,
                                Supllier = sup.CompanyName
                            };

                string[] dataKeyNames = new string[] { "ID" };

                ProductsGrid.DataSource = query.ToList();
                ProductsGrid.DataKeyNames = dataKeyNames;
                ProductsGrid.DataBind();
            }


        }

The code above uses LINQ to query the Products entity, and then joins the Categories and the Suppliers entities to get the "Category Name", and Supplier's "Company Name" respectively. We assign the Products.DataKeyNames property in the GridView control to an array of string values with the "ID" field from the LINQ query. The "select new" part of the query creates a new anonymous type on the fly with only the columns that we want from the resulting query. Then we bind the query to the ProductsGrid.DataSource.

4. In the Page_Load method, call the BindProductsGrid() method

        protected void Page_Load(object sender, EventArgs e)
        {
            BindProductsGrid();
        }

5. Press Ctrl+F5 to run the application, you will see that ProductsGrid is populated by the fields in the projection type. However, since the ID field is part of the anonymous type it shows up in the ProductsGrid

6. To hide the "ID" column we have to create an event handler RowDataBound on the ProductsGrid to make the "ID" invisible while the ProductsGrid is binding the data to the rows. 7. To create the event handler RowDataBound click the ProductsGrid, then in the Properties window double click the event handler RowDataBound

8. A ProductsGrid_RowDataBound method will be created in the code behind, since the "ID" column is the first column we can just hide the first column of the ProductsGrid with the following code

 protected void ProductsGrid_RowDataBound(object sender, GridViewRowEventArgs e)
{
      e.Row.Cells[0].Visible = false;
}

9. Press Ctrl + F5 to run the application and you will see that the "ID" column is hidden

Blogs in the Entity Framework Series:

1. Installing Entity Framework 6.1.1 With NuGet
2. Creating Entity Model From an Existing Database Entity Framework 6.1.1
3. Using the Entity Framework Objects In ASP.NET Project
4. Entity Framework (Database First) Part 4: Using the LINQ and Projection To SELECT Columns From Entities