Home .NET Working with data from linked tables in ASP.NET MVC or development of Lookup component

Working with data from linked tables in ASP.NET MVC or development of Lookup component

by admin

The development of any business applications is somehow or other connected with the processing of a certain amount of data, building links between the data, as well as their convenient presentation. In this article we will consider how to work with inter-table communication in ASP.net MVC, as well as the possibilities of visualization of this interaction, try to develop a component, on the one hand, allowing easy selection of the desired data, on the other hand, easy to configure. We will use JqGrid to implement search, sorting and selection of related data. Let’s talk about forming dynamic predicates, let’s see how we can use metadata in the html helper, and finally let’s look at components that already exist in this class.
In the simplest example, which is probably known to every reader, we can use the usual DropDownList to display data from linked tables, but its use is quite limited and not always effective. In our case we had clear requirements describing a component with a built-in list, sorting, and related data search, and due to the fact that there were quite a few fields of this type, we decided to develop a corresponding component.
Let’s look at an example of two related tables: "User" and "Group

public class UserProfile{[Key]public int UserId { get; set; }public string UserName { get; set; }public int? UserGroupId { get; set; }public virtual UserGroup UserGroup { get; set; }}public class UserGroup{[Key]public int UserGroupId { get; set; }[DisplayName("Group Name")]public string GroupName { get; set; }[DisplayName("Group Description")]public string Description { get; set; }public virtual ICollection<UserProfile> Users { get; set; }}

We see that there can be N number of users in a group and that a user can correspond to a certain group. Now let’s look at the code that allows us to get this data and also visualize it. For a page with a list of records, it is pretty simple.

public ActionResult Index(){var userProfiles = _db.UserProfiles.Include(c =>c.UserGroup);return View(userProfiles.ToList());}

Actually in the controller code above we are requesting not only the user profile data but also the group associated with this profile. Then we display it in our View with the DisplayNameFor.

@Html.DisplayNameFor(model => model.UserGroup.GroupName)

If we only need to display the linked data to the user, that’s quite enough. For editing, as I said before, you can use DropDownList.However, in our case there is a need to create a more flexible control, and make it as easy to configure as possible, such as the above query for the linked table. The first thing we will start with is developing an Html helper, which will allow us to describe the use of our component in the view in a convenient way, and make it work.

1. Developing Html Helper for Lookup component

What is an Html Helper in ASP.net MVC? For the most part, they are just extension methods that allow you to call their parent class to create HTML content. To display our component, we will use the standard lookup controls, namely, a text box and a button. The record id will be stored in a hidden field.
Besides html content, html helper also allows to access metadata of models and fields that are used, so the first thing we will do is to create an attribute that would highlight our field in the model, and provide it with additional information necessary for the component to work correctly.
So the LookupAttribute code is as follows

[AttributeUsage(AttributeTargets.Property, AllowMultiple = false)]public sealed class LookupAttribute : Attribute{public Type Model { get; set; }public string NameField { get; set; }}

It’s simple, let’s keep the field we will use as a text description of the linked record and the model type we will be referring to. So our model code can be transformed a bit :

public class UserProfile{[Key]public int UserId { get; set; }public string UserName { get; set; }[Lookup(Model = typeof(UserGroup), NameField = "GroupName")]public int? UserGroupId { get; set; }public virtual UserGroup UserGroup { get; set; }}

Now you can see that we will be referring to the UserGroup model, the field for the GroupName text representation. However, in order for this attribute to be used in our HTML Helper we need to add it to the metadata collection of the view. To do this, we need to implement the DataAnnotationsModelMetadataProvider descendant class and register it accordingly.

public class LookupMetadataExtension : DataAnnotationsModelMetadataProvider{protected override ModelMetadata CreateMetadata(IEnumerable<Attribute> attributes, Type containerType, Func<object> modelAccessor, Type modelType, string propertyName){var metadata = base.CreateMetadata(attributes, containerType, modelAccessor, modelType, propertyName);var additionalValues = attributes.OfType<LookupAttribute> ().FirstOrDefault();if (additionalValues != null){metadata.AdditionalValues.Add(LookupConsts.LookupMetadata, additionalValues);}return metadata;}}

To be able to extend field metadata, you need to inherit from the DataAnnotationsModelMetadataProvider class and override the CreateMetadata method. The DataAnnotationsModelMetadataProvider class implements the default metadata model provider for ASP.NET MVC.
It’s simple enough. If the collection of passed attributes contains ours, we should add it to the AdditionalValues of the metadata collection, and then return the modified collection. For this class to work correctly, it needs to be registered. Go to Global.asax.cs and add the line :

ModelMetadataProviders.Current = new LookupMetadataExtension();

Now we are ready to continue designing our HTML helper. In general, the HTML helper function will look like this

public static MvcHtmlString LookupFor<TModel, TProperty> (this HtmlHelper<TModel> htmlHelper, Expression<Func<TModel, TProperty> > expression, string filterAction, Type modelType, String nameField, IDictionary<string, object> htmlAttributes){var fieldName = ExpressionHelper.GetExpressionText(expression);var commonMetadata = PrepareLookupCommonMetadata(ModelMetadata.FromLambdaExpression(expression, htmlHelper.ViewData), htmlHelper.ViewData.ModelMetadata, modelType, nameField);var lookupAttribute = commonMetadata.AdditionalValues[LookupConsts.LookupMetadata] as LookupAttribute;return LookupHtmlInternal(htmlHelper, commonMetadata, lookupAttribute, fieldName, filterAction, htmlAttributes);}

Note that we also give the user the ability to set the model type directly from the view. On the first line, we get the name of our field, then we call the PrepareLookupCommonMetadata function. This function will be discussed later, I will only say that it is used to handle metadata and access the data in the linked table through that metadata. The line ModelMetadata.FromLambdaExpression(expression, htmlHelper.ViewData) uses expression expression to get metadata of current field, our AdditionalValues. Then we get our lookupAttribute from the returned commonMetadata object and call the HTML generation function.
Now let’s turn to the PrepareLookupCommonMetadata metadata handling function.

private static ModelMetadata PrepareLookupCommonMetadata(ModelMetadata fieldMetadata, ModelMetadata modelMetadata , Type modelType, String nameField){LookupAttribute lookupMetadata;if (modelType != null nameField != null){lookupMetadata = new LookupAttribute { Model = modelType, NameField = nameField };if (fieldMetadata.AdditionalValues.ContainsKey(LookupConsts.LookupMetadata))fieldMetadata.AdditionalValues.Remove(LookupConsts.LookupMetadata);fieldMetadata.AdditionalValues.Add(LookupConsts.LookupMetadata, lookupMetadata);}

First, see if the user has set the type and model in the view, and if so, update the data in AdditionalValues. And then

if (fieldMetadata.AdditionalValues != null fieldMetadata.AdditionalValues.ContainsKey(LookupConsts.LookupMetadata)){lookupMetadata = fieldMetadata.AdditionalValues[LookupConsts.LookupMetadata] as LookupAttribute;if (lookupMetadata != null){var prop = lookupMetadata.Model.GetPropertyWithAttribute("KeyAttribute");var releatedTableKey = prop != null ? prop.Name : String.Format("{0}Id", lookupMetadata.Model.Name);fieldMetadata.AdditionalValues.Add("idField", releatedTableKey);var releatedTableMetadata =modelMetadata.Properties.FirstOrDefault(proper=> proper.PropertyName ==lookupMetadata.Model.Name);if (releatedTableMetadata != null){UpdateLookupColumnsInfo(releatedTableMetadata, fieldMetadata);UpdateNameFieldInfo(lookupMetadata.NameField, releatedTableMetadata, fieldMetadata);}else{throw new ModelValidationException(String.Format("Couldn't find data from releated table. Lookup failed for model {0}", lookupMetadata.Model.Name));}}}else{throw new ModelValidationException(String.Format("Couldn't find releated model type. Lookup field"));}return fieldMetadata;}

Check that AdditionalValues exists, then retrieve it from the metadata collection. Next, use the GetPropertyWithAttribute extension type method to retrieve a field with a Key attribute from the associated Model. This field will be used to identify our connection, i.e. this field is the primary key of the linked table. If we don’t find it, we will try to form it with the help of rule Model Name + Id = primary key. We add this value to AdditionalValues as idField. Then we try to get the metadata of the linked table by its name.
If we get it, we get the column information and the text definition of the linked table.
Now let’s elaborate on getting the column information. This list of fields will be used to display records in JqGrid. Let’s create another attribute to configure this list.

[AttributeUsage(AttributeTargets.Class, AllowMultiple = false)]public class LookupGridColumnsAttribute : Attribute{public string[] LookupColumns { get; set; }public LookupGridColumnsAttribute(params string[] values){LookupColumns = values;}}

Now let’s look at the modified view of the linked table. You don’t need to register LookupGridColumnsAttribute, you can access this type through LookupAttribute using the Model field, which describes the model type.

[LookupGridColumns(new[] { "Description" })]public class UserGroup{[Key]public int UserGroupId { get; set; }[DisplayName("Group Name")]public string GroupName { get; set; }[DisplayName("Group Description")]public string Description { get; set; }public virtual ICollection<UserProfile> Users { get; set; }}

In addition to the default GroupName, add Description to the list of columns. Now let’s go back to the function that prepares the column metadata.

private static void UpdateLookupColumnsInfo(ModelMetadata releatedTableMetadata, ModelMetadata metadata){IDictionary<string, string> columns = new Dictionary<string, string> ();var gridColumns = releatedTableMetadata.ModelType.GetCustomAttributeByType<LookupGridColumnsAttribute> ();if (gridColumns != null){foreach (var column in gridColumns.LookupColumns){var metadataField =releatedTableMetadata.Properties.FirstOrDefault(propt => propt.PropertyName == column);if (metadataField != null){columns.Add(column, metadataField.DisplayName);}else{throw new ModelValidationException(String.Format("Couldn't find column in releated table {0}", releatedTableMetadata.GetDisplayName()));}}metadata.AdditionalValues.Add("lookupColumns", columns);}}

The function takes as its arguments the metadata of the linked table, as well as the metadata of our field. In the linked table’s metadata, we try to find the attribute specified by LookupGridColumnsAttribute. We check that it’s not null and go through the list of columns requesting their metadata to get the DisplayName of the corresponding column we need. If no metadata is detected, throw an exception, otherwise add the data to the columns collection. Once the columns collection is built, add it to the field metadata as AdditionalValues, they will be useful later.
Well now it’s time to go back to our PrepareLookupCommonMetadata function and examine the last call, namely UpdateNameFieldInfo.

private static void UpdateNameFieldInfo(string nameField, ModelMetadata releatedTableMetadata, ModelMetadata commonMetadata){var nameFieldMetedata =releatedTableMetadata.Properties.FirstOrDefault(propt => propt.PropertyName == nameField);if (nameFieldMetedata != null){commonMetadata.AdditionalValues.Add("lookupFieldValue", nameFieldMetedata.SimpleDisplayText);commonMetadata.AdditionalValues.Add("lookupFieldDisplayValue", nameFieldMetedata.DisplayName);}else{throw new ModelValidationException(String.Format("Couldn't find name field in releated table {0}", releatedTableMetadata.GetDisplayName()));}}

This function takes all the information regarding the text representation of our relationship, namely, the very field we specified as "NameField = "GroupName"" in the Lookup attribute and adds this information to the AdditionalValues of our field metadata. nameFieldMetedata.SimpleDisplayText – the GroupName field value from the associated table. nameFieldMetedata.DisplayName – The name of the GroupName field from the linked table.
At this point, we can say that we have all the information we need to create the appropriate Html code. Let’s see how the LookupHtmlInternal function works and what it takes. Recall that its call comes from the LookupFor function discussed at the very beginning of the HtmlHelper section.

private static MvcHtmlString LookupHtmlInternal(HtmlHelper htmlHelper, ModelMetadata metadata, LookupAttribute lookupMetadata, string name, string action, IDictionary<string, object> htmlAttributes){if (string.IsNullOrEmpty(name)){throw new ArgumentException("Error", "htmlHelper");}var divBuilder = new TagBuilder("div");divBuilder.MergeAttribute("id", String.Format("{0}_{1}", name, "div"));divBuilder.MergeAttribute("class", "form-wrapper cf");divBuilder.MergeAttribute("type", lookupMetadata.Model.FullName);divBuilder.MergeAttribute("nameField", lookupMetadata.NameField);divBuilder.MergeAttribute("idField", metadata.AdditionalValues["idField"] as string);divBuilder.MergeAttribute("nameFieldDisplay", metadata.AdditionalValues["lookupFieldDisplayValue"] as string);divBuilder.MergeAttribute("action", action);

We take the following arguments. 1. htmlHelper – allows us to generate html code, 2. metadata – This is essentially metadata fields containing all the additional metadata obtained during the information gathering stages. 3. Separate lookupMetadata. 4. name – The name of our field, as in the view. 5 action – Specify the controller and method that will be used to query the data. 5 htmlAttributes – Additional html attributes defined by the programmer.
Next, we check that the name of the field is not null and construct a div containing the main parameters of our field. Let’s focus on the main parameters: type – the type of model, which we refer to, nameField – the name of the text field from the linked table, which identifies the relationship (in our case the name of the group), idField – the primary key of the linked table, nameFieldDisplay – the value of the text field from the linked table, which identifies the relationship and action – as I said before this controller and method, which will be used to request data.

var columnsDivBuilder = new TagBuilder("div");columnsDivBuilder.MergeAttribute("id", String.Format("{0}_{1}", name, "columns"));columnsDivBuilder.MergeAttribute("style", "display:none");if (metadata.AdditionalValues.ContainsKey("lookupColumns")){var columns = ((IDictionary<string, string> )metadata.AdditionalValues["lookupColumns"]);var columnString = String.Empty;foreach (var column in columns.Keys){var columnDiv = new TagBuilder("div");columnDiv.MergeAttribute("colName", column);columnDiv.MergeAttribute("displayName", columns[column]);columnString += columnDiv.ToString(TagRenderMode.SelfClosing);}columnsDivBuilder.InnerHtml = columnString;}

Then we use the same scheme to build a div containing all the columns from the linked table, which will be used to build a view for JqGrid.

var inputBuilder = new TagBuilder("input");inputBuilder.MergeAttributes(htmlAttributes);inputBuilder.MergeAttribute("type", "text");inputBuilder.MergeAttribute("class", "lookup", true);inputBuilder.MergeAttribute("id", String.Format("{0}_{1}", name, "lookup"), true);inputBuilder.MergeAttribute("value", metadata.AdditionalValues["lookupFieldValue"] as string, true);var hiddenInputBuilder = new TagBuilder("input");hiddenInputBuilder.MergeAttribute("type", "hidden");hiddenInputBuilder.MergeAttribute("name", name, true);hiddenInputBuilder.MergeAttribute("id", name, true);hiddenInputBuilder.MergeAttribute("value", metadata.SimpleDisplayText, true);var buttonBuilder = new TagBuilder("input");buttonBuilder.MergeAttribute("type", "button");buttonBuilder.MergeAttribute("value", "Lookup");buttonBuilder.MergeAttribute("class", "lookupbutton");buttonBuilder.MergeAttribute("id", String.Format("{0}_{1}", name, "lookupbtn"), true);

Form the rest of the attributes, namely the field containing the text representation of our relationship (nameField), a hidden field containing the id of our relationship, the button which will open the JqGrid with data from the linked table.
Note that we get the id of the currently selected record from the field metadata, using the following call metadata.SimpleDisplayText.

divBuilder.InnerHtml = String.Format(@"{0}{1}{2}{3}", inputBuilder.ToString(TagRenderMode.SelfClosing), hiddenInputBuilder.ToString(TagRenderMode.SelfClosing), buttonBuilder.ToString(TagRenderMode.SelfClosing), columnsDivBuilder.ToString(TagRenderMode.Normal));return new MvcHtmlString(divBuilder.ToString(TagRenderMode.Normal));}

Pack everything that was generated into the root div and return the html string to the browser for display.
To make it easy to use our html helper, we also implement LookupFor method overloads

public static MvcHtmlString LookupFor<TModel, TProperty> (this HtmlHelper<TModel> htmlHelper, Expression<Func<TModel, TProperty> > expression){var urlHelper = new UrlHelper(htmlHelper.ViewContext.RequestContext);return LookupFor(htmlHelper, expression, urlHelper.Action("LookupData"), null, null, null);}public static MvcHtmlString LookupFor<TModel, TProperty> (this HtmlHelper<TModel> htmlHelper, Expression<Func<TModel, TProperty> > expression, string filterAction){return LookupFor(htmlHelper, expression, filterAction, null, null, null);}

To use our html helper, just call Html.LookupFor(model => model.UserGroupId) in the view.
For intellisense to work in the view, you need to add namespaces in web.config under system.web -> pages -> namespaces in which the class implementing your Html Helper is located, or just place this class in one of the already defined namespaces, say in System.Web.Helpers. Or directly in the view specify <@using your.namespace> .
At this point we can say that the development of our HtmlHelper has come to an end and we are moving on to the second part.

2. Expression and formation of dynamic predicates.

To create a set of basic queries, that will allow the developer to easily start using our component in "default" mode, we need to prepare predicates, that allow forming a query tree at runtime of our application. Let’s consider the LinqExtensions class, which contains several methods that eventually allow forming dynamic Linq. Let’s begin with the implementation of the Where method.

public static IQueryable<T> Where<T> (this IQueryable<T> source, string fieldName, string searchString, string compareFunction){if (searchString == null) searchString = String.Empty;var param = Expression.Parameter(typeof(T));var prop = Expression.Property(param, fieldName);var methodcall = Expression.Call(prop, typeof(String).GetMethod(compareFunction, new[] { typeof(string) }), Expression.Constant(value: searchString));var lambda = Expression.Lambda<Func<T, bool> > (methodcall, param);var request = source.Where(lambda);return request;}

So fieldName – the field whose data will be compared, searchString – the string to be compared, and the function from the String class which will be used to implement the comparison. Let’s analyze it in detail. Let’s check if the string passed to us is not null. If everything is ok, define the Expression.Parameter(typeof(T)); it will be essentially a type of model. On the next line define a property of the type, a field from the model that we will use for comparison. Then we form a call to compareFunction from the string class with the searchString arguments and the previously formed "property pointer". Then we form a lambda and use the IQueryable context to apply the Where to it with the just formed predicate. Return the generated IQueryable.
Let’s implement several functions, with a predefined string comparison function

public static IQueryable<T> WhereStartsWith<T> (this IQueryable<T> source, string fieldName, string searchString){return Where(source, fieldName, searchString, "StartsWith");}public static IQueryable<T> WhereContains<T> (this IQueryable<T> source, string fieldName, string searchString){return Where(source, fieldName, searchString, "Contains");}

Using the Equal and NotEqual methods

public static IQueryable<T> Equal<T> (this IQueryable<T> source, string fieldName, string searchString){if (searchString == null) searchString = String.Empty;var param = Expression.Parameter(typeof(T));var prop = Expression.Property(param, fieldName);var methodcall = Expression.Equal(prop, Expression.Constant(searchString));var lambda = Expression.Lambda<Func<T, bool> > (methodcall, param);var request = source.Where(lambda);return request;}public static IQueryable<T> NotEqual<T> (this IQueryable<T> source, string fieldName, string searchString){if (searchString == null) searchString = String.Empty;var param = Expression.Parameter(typeof(T));var prop = Expression.Property(param, fieldName);var methodcall = Expression.NotEqual(prop, Expression.Constant(searchString));var lambda = Expression.Lambda<Func<T, bool> > (methodcall, param);var request = source.Where(lambda);return request;}

I will not dwell on this analogy in detail.
We also need to be able to sort dynamically, so let’s implement the ApplyOrder method

static IOrderedQueryable<T> ApplyOrder<T> (IQueryable<T> source, string property, string methodName){var type = typeof(T);var param = Expression.Parameter(type);var pr = type.GetProperty(prop);var expr = Expression.Property(param, type.GetProperty(prop));var ptype = pr.PropertyType;var delegateType = typeof(Func<, > ).MakeGenericType(type, ptype);var lambda = Expression.Lambda(delegateType, expr, param);var result = typeof(Queryable).GetMethods().Single(method => method.Name == methodName method.IsGenericMethodDefinition method.GetGenericArguments().Length == 2 method.GetParameters().Length == 2).MakeGenericMethod(type, ptype).Invoke(null, new object[] { source, lambda });return (IOrderedQueryable<T> )result;}

For the arguments: 1. Property – the field by which we will sort; 2.methodName – the method we will use for sorting. Then we form a set of parameters. MakeGenericType in our case will form the Func<T, string> delegate, then use it to create a lambda which we pass as an argument to a method defined as methodName and call it all with a reflexion.
So we are now able to define dynamic sorting method calls from Queryable.

public static IOrderedQueryable<T> OrderBy<T> (this IQueryable<T> source, bool desc , string property){return ApplyOrder(source, property, desc ? "OrderByDescending" : "OrderBy");}public static IOrderedQueryable<T> OrderBy<T> (this IQueryable<T> source, string property){return ApplyOrder(source, property, "OrderBy");}public static IOrderedQueryable<T> OrderByDescending<T> (this IQueryable<T> source, string property){return ApplyOrder(source, property, "OrderByDescending");}public static IOrderedQueryable<T> ThenBy<T> (this IOrderedQueryable<T> source, string property){return ApplyOrder(source, property, "ThenBy");}public static IOrderedQueryable<T> ThenByDescending<T> (this IOrderedQueryable<T> source, string property){return ApplyOrder(source, property, "ThenByDescending");}

This ends the implementation of the Linq helper class and moves on to the next step.

3. ModelBinder and configuration of our component.

Because the amount of configuration data we pass to us is quite large, it would be nice to structure it and put it in an object that provides easy and clear access to any settings. Recall that we use jqgrid, which will provide us with data relating to sorting, searching, pagination and additional parameters, which we will define by ourselves, if necessary. And so we go to the model :

public enum SearchOperator{Equal, NotEqual, Contains}public class FilterSettings{public string SearchString;public string SearchField;public SearchOperator Operator;}public class GridSettings{public bool IsSearch { get; set; }public int PageSize { get; set; }public int PageIndex { get; set; }public string SortColumn { get; set; }public bool Asc { get; set; }}public class LookupSettings{public Type Model { get; set; }public FilterSettings Filter { get; set; }public GridSettings GridSettings { get; set; }public string IdField { get; set; }public string NameField { get; set; }}

I won’t describe the classes in detail. Next, let’s look at the piece of code that allows you to convert data from a jqGrid or a lookup into an appropriate instance of a class.

public class LookupModelBinder : IModelBinder{public object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext){HttpRequestBase request = controllerContext.HttpContext.Request;var lookupSettings = new LookupSettings{Model = Type.GetType(request["modelType"]), IdField = request["IdField"], NameField = request["NameField"], Filter = new FilterSettings{SearchString = request["searchString"] ?? String.Empty, SearchField = request["searchField"]}};if(request["searchOper"] != null){switch (request["searchOper"]){case "eq": lookupSettings.Filter.Operator = SearchOperator.Equal; break;case "ne": lookupSettings.Filter.Operator = SearchOperator.NotEqual; break;case "cn": lookupSettings.Filter.Operator = SearchOperator.Contains; break;}}lookupSettings.GridSettings = new GridSettings {Asc = request["sord"] == "asc"};if (request["_search"] != null) lookupSettings.GridSettings.IsSearch = Convert.ToBoolean(request["_search"]);if (request["page"] != null) lookupSettings.GridSettings.PageIndex = Convert.ToInt32(request["page"]);if (request["rows"] != null) lookupSettings.GridSettings.PageSize = Convert.ToInt32(request["rows"]);lookupSettings.GridSettings.SortColumn = request["sidx"];if (lookupSettings.Filter.SearchField == null) { lookupSettings.Filter.SearchField = request["NameField"];lookupSettings.Filter.Operator = SearchOperator.Contains;}return lookupSettings;}}

To implement bindings, we need to inherit from the IModelBinder class and implement the BindModel function, where controllerContext is the Context in which the controller functions. Information about the context includes information about the controller, HTTP content, request context and route data. bindingContext – The context in which the model is bound. The context contains information such as the model object, model name, model type, property filter, and value provider. We retrieve the HttpRequestBase and use this object to retrieve the data passed in the request. We then form the structure of the settings model and return the resulting class. For bindings to work it must be registered, so let’s go to Global.asax.cs and add the appropriate call.

ModelBinders.Binders.Add(typeof(LookupSettings), new LookupModelBinder());

In the end, after all the registrations, my Global.asax.cs looks like this :

protected void Application_Start(){AreaRegistration.RegisterAllAreas();ModelMetadataProviders.Current = new LookupMetadataExtension();ModelBinders.Binders.Add(typeof(LookupSettings), new LookupModelBinder());WebApiConfig.Register(GlobalConfiguration.Configuration);FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);RouteConfig.RegisterRoutes(RouteTable.Routes);BundleConfig.RegisterBundles(BundleTable.Bundles);AuthConfig.RegisterAuth();}

Now we can use the following entry in the controller to refer to the arguments coming from the lookup.

public virtual ActionResult LookupData([ModelBinder(typeof(LookupModelBinder))] LookupSettings settings)

This concludes our work with the configuration object and moves on to the next step:

4. Implementation of a generic MVC controller for a Lookup controller.

For most of the lookups we use in our application we don’t need any complex configuration, filtering or sorting, so let’s develop an object that implements basic sorting and searching regardless of the type coming from the component, and a controller that uses this object to organize access to the data in the "default" mode. Let’s start with the LookupDataResolver class. This class will be responsible for lookup and sorting operations in the "default" mode. Note that our component must provide resolution of the element by the textual value entered in the corresponding field, in addition to the selection of the element from the grid.
Since type is only defined in runtime, let’s implement a function that will type our model as a generic argument and call the function corresponding to the query. So we can use the following code dbContext.Set().AsQueryable(); to form a basic query.
Consider the LookupMethodCall function.

private static ActionResult LookupMethodCall(string methodName, LookupSettings settings, DbContext dbContext, OnAfterQueryPrepared onAfterQueryPrepared){var methodLookupCall = typeof(LookupDataResolver).GetMethod(methodName, BindingFlags.NonPublic | BindingFlags.Static);methodLookupCall = methodLookupCall.MakeGenericMethod(settings.Model);var lookupSettings = Expression.Parameter(typeof(LookupSettings), "settings");var dbCtx = Expression.Parameter(typeof(DbContext), "dbContext");var funct = Expression.Parameter(typeof(OnAfterQueryPrepared), "onAfterQueryPrepared");var lookupSearch = Expression.Lambda(Expression.Call(null, methodLookupCall, lookupSettings, dbCtx, funct), lookupSettings, dbCtx, funct);var lookupSearchDelegate = (Func<LookupSettings, DbContext, OnAfterQueryPrepared, JsonResult> )lookupSearch.Compile();return lookupSearchDelegate(settings, dbContext, onAfterQueryPrepared);}

First we look for methodName in the current type. Then we use the MakeGenericMethod function to prepare our model for use as a generic argument. We form the parameters: settings (the settings entity taken from the lookup), dbContext (context for accessing the database), onAfterQueryPrepared (the delegate which will be called immediately after forming the basic query to the database. It is necessary to add additional filters, if they are needed). Then we create a corresponding lambda, which will call our method, then compile it and call it.
Let’s implement the functions which call the method corresponding to the request with the LookupMethodCall function. BasicLookup will call the generic LookupSearch function to resolve the text entered by the user into Lookup. BasicGrid will provide sorting and searching in the grid, calling the generic function LookupDataForGrid.

public static ActionResult BasicLookup(LookupSettings settings, DbContext dbContext, OnAfterQueryPrepared onAfterQueryPrepared){return LookupMethodCall("LookupSearch", settings, dbContext, onAfterQueryPrepared);}public static ActionResult BasicGrid(LookupSettings settings, DbContext dbContext, OnAfterQueryPrepared onAfterQueryPrepared){return LookupMethodCall("LookupDataForGrid", settings, dbContext, onAfterQueryPrepared);}

We implement functions that perform operations on the database and form the resulting data sets. These are the two generic functions whose calls are described above.

private static JsonResult LookupSearch<T> (LookupSettings settings, DbContext dbContext, OnAfterQueryPrepared onAfterQueryPrepared) where T : class{var modelType = typeof(T);var request = dbContext.Set<T> ().AsQueryable();if (onAfterQueryPrepared != null){var query = onAfterQueryPrepared(request, settings);if (query != null) request = query.Cast<T> ();}request = request.WhereStartsWith(settings.Filter.SearchField, settings.Filter.SearchString);return new JsonResult{Data = request.ToList().Select(t => new{label = modelType.GetProperty(settings.NameField).GetValue(t).ToString(), id = modelType.GetProperty(settings.IdField).GetValue(t).ToString()}).ToList(), ContentType = null, ContentEncoding = null, JsonRequestBehavior = JsonRequestBehavior.AllowGet};}

So we get the typed Queryable from dbContext for the corresponding model, see if the delegate is defined, if it is, we call it and use the returned query to further form the query. Next it’s simple, use WhereStartsWith to form query. We use the values from the settings.Filter.SearchField and settings.Filter.SearchString entities, respectively, to determine the field and string to filter by. Finally, we form the resulting array using reflection to retrieve data from the fields of the t instance by modelType.
We return only two columns: label, the textual representation of the associated record, and id, the primary key.
If there is more than one value, the text in the control will be grayed out, this will indicate that the record resolution failed and we need to refer to a more detailed representation.
Next, let’s move on to implementing the LookupDataForGrid function, which will provide filtering and search capabilities on the linked data.

private static JsonResult LookupDataForGrid<T> (LookupSettings settings, DbContext dbContext, OnAfterQueryPrepared onAfterQueryPrepared) where T : class{var modelType = typeof(T);var pageIndex = settings.GridSettings.PageIndex - 1;var pageSize = settings.GridSettings.PageSize;var request = dbContext.Set<T> ().AsQueryable();if (onAfterQueryPrepared != null){var query = onAfterQueryPrepared(request, settings);if (query != null) request = query.Cast<T> ();}if (settings.GridSettings.IsSearch){switch (settings.Filter.Operator){case SearchOperator.Equal:request = request.Equal(settings.Filter.SearchField, settings.Filter.SearchString); break;case SearchOperator.NotEqual:request = request.NotEqual(settings.Filter.SearchField, settings.Filter.SearchString); break;case SearchOperator.Contains:request = request.WhereContains(settings.Filter.SearchField, settings.Filter.SearchString); break;}}var totalRecords = request.Count();var totalPages = (int)Math.Ceiling(totalRecords / (float)pageSize);var userGroups = request.OrderBy(!settings.GridSettings.Asc, settings.GridSettings.SortColumn).Skip(pageIndex * pageSize).Take(pageSize);return new JsonResult{Data = new{total = totalPages, settings.GridSettings.PageIndex, records = totalRecords, rows = (userGroups.AsEnumerable().Select(t => new{id = modelType.GetProperty(settings.IdField).GetValue(t).ToString(), cell = GetDataFromColumns(modelType, settings, t)}).ToList())}, ContentType = null, ContentEncoding = null, JsonRequestBehavior = JsonRequestBehavior.AllowGet};}

The function is implemented similar to LookupSearch, here we add processing of page breakdown, basic sorting and search. The list of values by columns is obtained with GetDataFromColumns function. This function uses the LookupGridColumnsAttribute to define the list of columns our grid expects.Below is its code :

private static IEnumerable<string> GetDataFromColumns(Type model, LookupSettings settings, object instance){var dataArray = new List<string>{model.GetProperty(settings.IdField).GetValue(instance).ToString(), model.GetProperty(settings.NameField).GetValue(instance).ToString()};var gridColumns = model.GetCustomAttributeByType<LookupGridColumnsAttribute> ();if (gridColumns != null){dataArray.AddRange(from column in gridColumns.LookupColumnsselect model.GetProperty(column).GetValue(instance)into val where val != nullselect val.ToString());}return dataArray;}

The resulting array includes, by default, a primary key and a field containing the value of the textual description of the relationship. Next, we get the LookupGridColumnsAttribute from the model type and use instance, using reflection, to pull the values of the columns.
Now it’s time to implement a base controller, which ensures that all lookup controllers on the form work in "default" mode

public class LookupBasicController : Controller{protected virtual DbContext GetDbContext{get { throw new NotImplementedException("You have to implement this method to return correct db context"); }}protected virtual IQueryable LookupBaseQuery(IQueryable query, LookupSettings settings){return null;}public virtual ActionResult LookupData([ModelBinder(typeof(LookupModelBinder))] LookupSettings settings){return LookupDataResolver.BasicLookup(settings, GetDbContext, LookupBaseQuery);}public virtual ActionResult LookupDataGrid([ModelBinder(typeof(LookupModelBinder))] LookupSettings settings){return LookupDataResolver.BasicGrid(settings, GetDbContext, LookupBaseQuery);}

To work correctly in the descendant class, you need to override the database context and, if you plan to extend the default queries, the LookupBaseQuery function as well. This function is used to call from LookupSearch and LookupDataForGrid when forming the base query. I should also note that the names of the functions in the controller that are called by JS to retrieve data can be defined during the html helper configuration. However, the name of the function that fetches data for jqGrid is formed according to the following pattern : The name specified when configuring the html helper + Grid. By default, JS will call the LookupData and LookupDataGrid functions.
At this point we can say that the development of the basic component elements is complete. In the sources you will also find the file lookup.js, which is responsible for the client side of the component, I’m not going to consider it here, because it is not of great interest.

5. Example of use

Consider the models that were described at the beginning of this article. Let’s apply our component to the connection.

[Table("UserProfile")]public class UserProfile{[Key][DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]public int UserId { get; set; }public string UserName { get; set; }[Lookup(Model = typeof(UserGroup), NameField = "GroupName")]public int? UserGroupId { get; set; }public virtual UserGroup UserGroup { get; set; }}[LookupGridColumns(new[] { "Description" })]public class UserGroup{[Key]public int UserGroupId { get; set; }[DisplayName("Group Name")]public string GroupName { get; set; }[DisplayName("Group Description")]public string Description { get; set; }public virtual ICollection<UserProfile> Users { get; set; }}

So, we have UserProfile in which we add Lookup reference to UserGroup and determine which field we will use for textual representation of this record. In the table UserGroud we add attribute LookupGridColumns, in which we specify the additional columns, which we would like to see in representation. That’s all, now let’s move on to the controller.

public class UserListController : LookupBasicController{private readonly DataBaseContext _db = new DataBaseContext();protected override DbContext GetDbContext{get { return _db; }}

Inherit from LookupBasicController and override GetDbContext to give LookupBasicController access to the database context.

public ActionResult Edit(int id = 0){UserProfile userprofile = _db.UserProfiles.Include(c => c.UserGroup).SingleOrDefault(x => x.UserId == id);if (userprofile == null){return HttpNotFound();}return View(userprofile);}

Added a query to the linked data from the UserGroup table.
This ends the controller setup and we move on to the view.

@using TestApp.Models@model UserProfile@{ViewBag.Title = "Edit";}@Styles.Render("~/Content/JqGrid")<h2> Edit</h2>@using (Html.BeginForm()) {@Html.ValidationSummary(true)<fieldset><legend> UserProfile</legend>@Html.HiddenFor(model => model.UserId)<div class="editor-label">@Html.LabelFor(model => model.UserName)</div><div class="editor-field">@Html.EditorFor(model => model.UserName)@Html.ValidationMessageFor(model => model.UserName)</div><div class="editor-label">@Html.LabelFor(model => model.UserGroupId)</div><div class="editor-field">@Html.LookupFor(model => model.UserGroupId)@Html.ValidationMessageFor(model => model.UserGroupId )</div><p><input type="submit" value="Save" /></p></fieldset>}<div>@Html.ActionLink("Back to List", "Index")</div>@section Scripts {@Scripts.Render("~/bundles/lookup")@Scripts.Render("~/bundles/jqueryval")@Scripts.Render("~/bundles/jqueryui")@Scripts.Render("~/bundles/jqgrid")}

Here you need to remember to add more scripts like jqgrid, lookup, etc. You can take a closer look at the representation using the source code attached to the article.
As a result, you get a field with a button that allows you to conveniently search and sort data in related tables. Selecting an element in the table is done by double-clicking, on the desired element. It’s too early to talk about some kind of complete control right now, there’s still a lot to implement. The code needs refactoring and optimization, but in general it works and implements the main functions of the design phase.
It all looks like this :
Working with data from linked tables in ASP.NET MVC or development of Lookup component

6. Conclusion

In conclusion, I want to say that we spent some time looking for the appropriate component for our needs, in the end we settled on the ASP.net MVC Awesome 3.5 product. I should mention, that MVC Awesome Lookup component is pretty flexible and allows to do some customization, but since we decided to develop everything from scratch I can’t recommend it, because I didn’t use it in my work. I can see an example of how to use it and the code here: Awe Lookup They also have multi-selection support.
The source code of the component and the test application discussed in this article can be downloaded here : TestApp.zip
I hope the material was of interest to you!

You may also like