Automatic Lookup Columns in Grids

A common scenario in data binding is for data classes to contain references to other data classes. For example, a Product object may contain references to Category and Supplier objects.

In ADO.NET, the references usually appear as foreign keys that map to other tables (e.g., Product.CategoryID and Product.SupplierID).

In the Entity Framework, you still get the key columns, but you also get the actual objects. So you have Product.CategoryID (usually an integer) and Product.Category (an actual Category object).

Displaying foreign keys in a grid is not very useful, because it is unlikely that users will remember that category 12 is "Dairy Products" or that supplier 15 is "ACME Imports". Allowing users to edit these keys would be even worse. A common way to work around this problem is to remove the related entity columns from any bound grids and, optionally, to replace them with custom columns that use combo boxes for editing the values, so called lookups. The combo boxes have to be bound to the related tables and have their properties set so they display relevant values (e.g., Category.Name or Supplier.CompanyName) and so they are synchronized with the values being displayed on the grid. This is not terribly hard to do, but it is a tedious and error-prone task that makes projects harder to create and maintain.

C1DataSource can do this tedious work for the developer; it can automatically change related entity columns so that they show combo box lookups. It can do this for several types of data grids, those that it supports. Currently supported WinForms grids are: C1FlexGrid and Microsoft DataGridView. Here we will show how to do this for C1FlexGrid.

C1DataSource provides an extender property called ControlHandler. If you place a C1FlexGrid control on a form that contains a C1DataSource, the grid will get an additional ControlHandler property. A ControlHandler is an object containing (at present) a single boolean property AutoLookup. This property set to True causes the C1DataSource to configure grid columns that contain references to other entities so that they show lookup combos.

To see how it works, follow these steps:

Using the project used in Simple Binding, add a new form with a C1DataSource component using the same ContextType as before.
Create a ViewSource in the ViewSourceCollection editor, entering Products as the EntitySetName.
Add a C1FlexGrid to the form and set its C1DataSource property to the C1DataSource and its DataMember property to Products.
Save, build and run the application. It will look like this:

As you can see, the Category and Supplier columns are not useful at all. You could remove them or customize the grid by writing some code to create new columns, but there’s an easier way.
Select the grid in the designer and find the property called "ControlHandler on c1DataSource1" in the Properties window, as shown in the following picture:

Remember, this is an extender property and will be available only if there is a C1DataSource component on the form. Set the AutoLookup property there to True.
When you are done, run the project again and look at the Category and Supplier columns. Notice that now the grid shows the category name and supplier’s company name instead of the generic strings. Also notice that you can edit the product’s category and the product’s supplier by picking from a drop-down list, complete with auto search functionality.
Finally, click the column headers to sort the grid by Category or Supplier and notice that the sorting is performed based on the value displayed on the grid. This is what anyone would expect, but surprisingly it is not easy to achieve using regular data binding.

The string value (name) shown by the combo box is determined following these rules:

If the entity class overrides the ToString method, then the string representation of the entity is obtained using the overridden ToString method. This should return a string that uniquely represents the entity. For example, it could be the content of a CompanyName column, or a combination of FirstName, LastName, and EmployeeID. This is the preferred method because it is simple, flexible, and easy to implement using partial classes (so your implementation will not be affected if the entity model is regenerated).
If the entity class does not override the ToString method, but one of its properties has the DefaultProperty attribute, then that property is used as the string representation of the entity.
If the entity class does not override the ToString method and has no properties marked with the DefaultProperty attribute, then the first column that contains the string "Name" or "Description" in its name will be used as a string representation for the entities.
If none of the above applies, then no lookup is created for the given entity type.