Working with data from linked tables in ASP.NET MVC or developing the Lookup component

The development of any business application is in one way or another connected with processing a certain amount of data, building relationships between these data, as well as their convenient presentation. In this article, we will consider working with inter-table interaction in ASP.net MVC, as well as the possibilities for visualizing this interaction, try to develop our own component, which allows you to conveniently select the necessary data, on the one hand, and easily configure on the other. We will use JqGrid to implement the search, sorting and selection of related data. We will touch upon the formation of dynamic predicates, see how metadata can be used in the html helper, and finally consider the existing components of this class.

In the simplest example, which every reader is surely aware of, we can use the usual DropDownList to display data from related tables, but its use is quite limited and not always effective. In our case, there were clear requirements describing the component, with a built-in list, sorting, search by related data, and since there were a lot of fields of this type, it was decided to develop the corresponding component.

Consider an example from 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 the group can have the Nth number of users, and the user, in turn, can correspond to a specific group. Now let's look at a code that will allow us to receive this data, as well as visualize it. For a page with a list of entries, this is quite simple.

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

Actually, in the controller code presented above, we request, in addition to the user profile data, a group associated with this profile. Next, display it in our View using DisplayNameFor.

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

If we only need to display the associated data to the user, then this is quite enough. For editing, as I already said, you can use DropDownList. However, in our case there is a need to create a more flexible control and make it as simple as possible to configure, such as the query to the linked table presented above. The first thing we will start with is the development of the Html helper, which will allow us to conveniently describe the use of our component in the view and ensure its functioning.

1. Development of Html Helper for Lookup component


What is the Html Helper in ASP.net MVC? For the most part, these are common extension methods that allow parents to access their class in order to create HTML content. To display our component, we will use the standard view for lookup controls, namely a text field and a button. id records will be stored in a hidden field.
In addition to html content, html helper also allows you to access the metadata of models and fields that are used, so the first thing we do is create an attribute that could highlight our field in the model, as well as provide it with additional information necessary for the component to work correctly.

So the LookupAttribute code is presented below

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

Everything is simple here, we will save the field that we will use as a text description of the related record, as well as the type of model to which we will refer. So the code of our model can be slightly transformed:

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 it’s clear that we will refer to the UserGroup model, a field for the textual representation of GroupName. However, in order for this attribute to be used in our HTML Helper, we need to add it to the collection of metadata for the view. To do this, we need to implement the class derived from DataAnnotationsModelMetadataProvider 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;
        }
    }

In order to be able to expand the field metadata, you must inherit from the DataAnnotationsModelMetadataProvider class and override the CreateMetadata method. The DataAnnotationsModelMetadataProvider class implements the default metadata model provider for ASP.NET MVC.
Everything is quite simple. If there is ours in the collection of transferred attributes, then we should add it to the AdditionalValues ​​metadata collection, after which we return the changed collection. For this class to work correctly, it must be registered. Go to Global.asax.cs and add the line:

ModelMetadataProviders.Current = new LookupMetadataExtension();

Now we are ready to continue developing 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);
        }

I note that we also give the user the opportunity to specify the type of model directly from the view. In the first line we get the name of our field, then we call the PrepareLookupCommonMetadata function. This function will be considered later, I’ll just say that it is used to process metadata and access the data of the linked table through this metadata. The line ModelMetadata.FromLambdaExpression (expression, htmlHelper.ViewData) using the expression expression gets the metadata of the current field, actually our AdditionalValues. Next, from the returned commonMetadata object, we get our lookupAttribute and call the function for generating HTML code.

Now let's turn to the PrepareLookupCommonMetadata metadata processing 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 specified the type and model in the view, if so, then update the data in AdditionalValues. Move on

  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 ​​has a place to be, then retrieve it from the metadata collection. Next, using the GetPropertyWithAttribute Type extension method, we get the field with the Key attribute from the associated Model. We will use this field to identify our relationship, that is, this field is the primary key of the associated table. If we do not find it, then we try to form ourselves using the rule- Model name + Id = primary key. Add this value to AdditionalValues ​​as idField. Next, we try to get the metadata of the linked table by its name.
If received, then we get the column information and the text definition of the linked table.
Now let's dwell on obtaining information about the columns. This list of fields will be used to display records in JqGrid. To configure this list, create another attribute.

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

        public LookupGridColumnsAttribute(params string[] values)
        {
            LookupColumns = values;
        }
    }

Now look at the modified view of the linked table. It is not necessary to register LookupGridColumnsAttribute, access to this type will be possible through LookupAttribute using the Model field, which describes the type of model.

  [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 the column list, in addition to the default GroupName already present there, we add Description. Now we return to the consideration of the function preparing the metadata by columns.

        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 arguments the metadata of the linked table, as well as the metadata of our field. In the metadata of the linked table, we try to find the specified LookupGridColumnsAttribute attribute. We see that it is not null and go through the list of columns along the way requesting their metadata to get the corresponding column for us to display DisplayName. If no metadata is found, throw an exception; otherwise, add the received data to the columns collection. After the collection of columns is formed, add it to the field metadata in the form of AdditionalValues, they will be useful to us further.

So now is the time to return to our PrepareLookupCommonMetadata function and consider 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 receives all the information regarding the textual representation of our connection, namely, the same field that we specified as “NameField =" GroupName "in the Lookup attribute and adds this information to the AdditionalValues ​​metadata of our field. nameFieldMetedata.SimpleDisplayText - the value of the GroupName field from the linked table. nameFieldMetedata.DisplayName - The name of the GroupName field from the associated table.

This can be said that we have all the information we need in order to create the appropriate Html code. Let's see how it works and what the LookupHtmlInternal function accepts. Let me remind you that its call comes from the LookupFor function, considered 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 accept the following arguments. 1. htmlHelper - allows us to generate html code; 2. metadata - In fact, this is the metadata of the field containing all the ext. metadata obtained at the stages of information collection. 3. Dedicated lookupMetadata separately. 4. name - The name of our field, as in the view. 5 action - Specify the controller and method that will be used to request data. 5 htmlAttributes - ext. html attributes defined by the programmer.
Next, we see that the field name is not null and build a div containing the main parameters of our field. Let us dwell on the main parameters: type - the type of the model we are referring to, nameField - the name of the text field from the linked table that 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 connection well and action - as I said, this is the controller and the method that 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;
            }

Further, according to the same scheme, we stand a div containing all the columns from the linked table that will be used to build the 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);

We form the rest of the attributes, namely the field containing the textual representation of our connection (nameField), the hidden field containing the id of our connection, the button by which we will open JqGrid with data from the linked table.
I note that we get the id of the currently selected record from the field metadata using the following call to 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));
        }

We pack everything that we generated in the root div and return the html string to the browser for display.

To use our html helper was simple, 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);
        }

In order to use our html helper, it is enough to call Html.LookupFor (model => model.UserGroupId) in the view.
In order for intellisense to work in the view, you need to add the namespace in which the class that implements your Html Helper is located in web.config in the system.web -> pages -> namespaces section, or simply place this class in one of the already defined namespaces let's say in System.Web.Helpers. Либо непосредственно в представлении указать <@using your.namespace>.

This can be said that the development of our HtmlHelper has come to an end and we move on to the second part.

2. Expression and the formation of dynamic predicates.


In order to create a set of basic queries that will allow the developer to easily start using our component in the “default” mode, we need to prepare predicates that allow us to create a query tree during the execution of our application. Consider the LinqExtensions class, which contains several methods that ultimately enable the formation of dynamic Linq. Let's start by implementing 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 is the data field from which we will compare, searchString is the string that we will compare, and the function from the String class that will be used to implement the comparison. Next, we will analyze everything in detail. We look that the string that was passed to us is not null. If all is well, then determine the type of Expression.Parameter (typeof (T)); to which we will turn, in fact it will be the type of model. The next line defines the type property, the field from the model, which we will use for comparison. Then we form a call to the compareFunction function from the string class with searchString arguments and the “property pointer” formed earlier. Next, we create a lambda and use the IQueryable context in order to apply Where to it with the newly created predicate. Return the generated IQueryable.

We 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");
        }

In the image and likeness, we implement 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;
         }

Here, by analogy, I will not dwell in detail.

We also need to be able to dynamically sort, so we 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;
        } 

By arguments: 1. Property - the field by which we will sort; 2.methodName - The method that we will use for sorting. Next, we form a set of parameters. MakeGenericType в нашем случае сформирует делегат Func, затем используем его для создания лямбды, которую передаем в качестве аргумента методу определенному как methodName и вызываем все это при помощи рефлексии.

Thus, we are now able to define dynamic calls to sorting methods 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 completes the implementation of the Linq helper class and proceeds to the next step.

3. ModelBinder and configuration of our component.


Due to the fact that the amount of configuration data transmitted to us is quite large, it would be nice to structure them and place them in an object that provides easy and understandable access to any settings. Let me remind you that we use jqgrid, which will provide us with data regarding sorting, searching, pagination and additional parameters, which we will independently determine if necessary. And so let's move on 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 will not dwell on the description of classes. Next, consider a piece of code that allows data received from jqGrid or a lookup to be converted to the corresponding instance of the 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 binding, we need to inherit from the IModelBinder class and implement the BindModel function, where controllerContext is the Context in which the controller operates. Context information 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 a model object, model name, model type, property filter, and value provider. We get the HttpRequestBase and use this object to get the data passed in the request. Next, we form the structure of the settings model and return the resulting class. In order for the binding to start working, you need to register it, so let's go to Global.asax.cs and add the corresponding call.

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


As a result, 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 in the controller, we can use the following entry to refer to arguments received from the lookup.

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


On this we finish the work with the configuration object and proceed to the next stage:

4. Implementation of a common MVC controller for Lookup control.


For most of the lookups that we use in our application, there is no need for any complicated configuration, filtering or sorting, so we will develop an object that implements basic sorting and search, regardless of the type of component that came from the component, and the controller uses this object to organize access to data in the "default" mode. Let's start with the LookupDataResolver class. This class will be responsible for search operations, sorting in the "default" mode. I note that in addition to selecting an element from the grid, our component must provide the resolution of the element according to the text value entered in the corresponding field.

Since the type is determined only in runtime, we implement a function that will typify our model as a generic argument and call the function corresponding to the request. So we can use the following code dbContext.Set (). AsQueryable (); to form a basic request.

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 in the current type for the methodName method. After that, using the MakeGenericMethod function, we prepare our model for use as a generic argument. We form the parameters: settings (the essence of the settings obtained from the lookup), dbContext (context for accessing the database), onAfterQueryPrepared (the delegate that will be called immediately after the formation of the basic request to the database. It is needed to add additional filters, if necessary). Next, create the corresponding lambda that will call our method, after which we compile it and call it.

We implement the functions that call the method corresponding to the request using the LookupMethodCall function. BasicLookup, to resolve the text entered by the user in the lookup, will access the generic LookupSearch function. BasicGrid will provide sorting and search in the grid; it calls 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 realize the functions that perform operations with the database and form the resulting data sets. These are 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 a typed Queryable from dbContext for the corresponding model, see if the delegate is defined, if so, then call it and use the query returned by it to further form the query. Then everything is simple, we use WhereStartsWith to form the request. We use the values ​​from the settings.Filter.SearchField, settings.Filter.SearchString entity of the settings, respectively, to determine the field and line by which filtering is performed. In conclusion, we form the resulting array using reflection to obtain data from the fields of the instance t by the type of model modelType.
We return only two columns: label - textual representation of the related record and id - primary key.
If there will be more than one value, then the text in the control will be gray, this will indicate that the recording permission failed and you need to turn to a more detailed view.

Next, we move on to the implementation of the LookupDataForGrid function, which will provide filtering and search capabilities for related 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 by analogy with LookupSearch, here we add the processing of pagination, basic sorting and search. The list of column values ​​is obtained using the GetDataFromColumns function. This function uses the LookupGridColumnsAttribute attribute to determine the list of columns that our grid expects. The following 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.LookupColumns 
                                   select model.GetProperty(column).GetValue(instance) 
                                   into val where val != null 
                                   select val.ToString());
            }
            return dataArray;
        }

The resulting array includes, by default, the primary key and a field containing the value of the textual description of the connection. Next, from the type of the model, we obtain the LookupGridColumnsAttribute attribute and using instance, using reflection, pull out the column values.

Now it is time to implement the basic controller, which will ensure the functioning of all lookup controls on the form in the "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 successor class, you need to redefine the database context, and if you plan to expand the default queries, then the LookupBaseQuery function. This function is used to call from LookupSearch and LookupDataForGrid when forming a basic query. I also note that the names of functions in the controller that JS accesses to obtain data can be determined during the configuration of the html helper. However, the name of the function that performs data acquisition for jqGrid is formed according to the following pattern: Name specified during configuration of html helper + Grid. By default, JS will access the LookupData and LookupDataGrid functions.

On this we can say that the development of the basic elements of the component is completed. In the source you can find the file lookup.js, which is responsible for the client part of our component’s work, I didn’t consider it here, since it is not of great interest.

5. Usage example


Consider the models that were described at the beginning of the article. Let's apply our component to communication.

    [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 a UserProfile in which we add the Lookup link to the UserGroup and determine which field we will use for the textual representation of this record. In the UserGroud table, add the LookupGridColumns attribute in which we specify additional. columns that you would like to see in the view. Actually that's all, now go to the controller.

 public class UserListController : LookupBasicController
    {
        private readonly DataBaseContext _db = new DataBaseContext();

        protected override DbContext GetDbContext
        {
            get { return _db; }
        }

We will inherit from LookupBasicController and redefine GetDbContext in order to give LookupBasicController access to the db 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 associated data from the UserGroup table.
This completes 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 extra. scripts like jqgrid, lookup, etc. You can consider the presentation in more detail using the source codes 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. An element in the table is selected by double-clicking on the desired element. It is too early to talk about some kind of completed control element, much remains to be realized. The code needs refactoring and optimization, however, as a whole, it functions and implements the basic functions laid down at the design stage.

It all looks like this:


6. Conclusion


In conclusion, I want to say, we spent some time looking for a component that meets our needs, and as a result we settled on the product ASP.net MVC Awesome 3.5. I note that the MVC Awesome Lookup component is quite flexible, and allows you to perform various kinds of settings, but since it was decided to develop everything from scratch, I can not recommend it, because I did not use it in work. View a usage example and code here: Awe Lookup . They also have multi-selection support.

The source code of the component and the test application discussed in the article can be downloaded here: TestApp.zip .

I hope the material was interesting to you!