Generic Lookup – API Architecture for general purpose

Often as developers, we come across situations where we need to implement a new drop-down with a new set of data, something as simple as a list of occupations with two properties (let’s call them Id and Description). The solution seems straight forward: A controller with a get method and at least a service to fetch the data from the database, transform to a response DTO, and return the list of occupations to the UI where the data is then bound to a control.

A few weeks later a team member receives a requirement to implement another drop-down, the developer briefly looks at the first solution and implements a new controller, method and service. Let call this drop-down Gender with properties Id and Description. The Occupation and Gender lists are considered static data.

At the same time, another team member implements a drop-down to display a list of leads. From the leads we need the properties Id and Name, leads are not considered static data. The developer uses an existing leads service and controller that probably fetches more data than required for the lookup. I am sure you know how the rest goes, after some time we have multiple requests, about four or five classes to maintain per drop down and sometimes even poor performance (considering other features on the screen as well).

The requirements present a common problem, solvable in a single manner by:

  • Reducing the components for key-value pair requests to a single solution.
  • Introduce a single entry point for all key-value pair requests
  • Introduce a standard output for all key-value pair requests
  • Add the ability to request data from any specified source in the system
  • Add the ability to extend the single solution for different request requirements (e.g. filtering or fuzzy search on specific lookups)

How can we request different sets of key-value pair data with a single, generic extendable solution?

Let’s look at a Unit Test for the described solution:

Unit Test:
public void Given_A_Lookup_Request()
    _dto = new GetLookupRequest(new[]
        new LookupRequest() {Type = "Gender"},
        new LookupRequest() {Type = "Occupation"},
        new LookupRequest() {Type = "Lead", Key = "Id", Value = "Name"},
        new LookupRequest() {Type = "AuthorizationGroup", Key = "Id", Value = "Name"},

public void When_Request_All_Lookups()
    _responseDto = _connector

public void Then_All_Lookups_Received()
    var lookups = _responseDto?.Result?.Lookups;

    Assert.IsTrue(lookups.Any(a => a.Type.Equals("Gender")));
    Assert.IsTrue(lookups.Any(a => a.Type.Equals("Occupation")));
    Assert.IsTrue(lookups.Any(a => a.Type.Equals("Lead")));
    Assert.IsTrue(lookups.Any(a => a.Type.Equals("AuthorizationGroup")));

public void And_No_Duplicates_Received()
            .GroupBy(x => new {x.Id, x.Description})
            .Any(a => a.Count() > 2));

The Given_A_Lookup_Request method constructs a single request containing the different types we want to bind to our drop-downs. We can override the default Key-Value settings for each type with different values. In the When_Request_All_Lookups method, the connector serializes the request dto and sends a single HTTP request to the API. Finally, the result is tested in the Then_All_Lookups_Received method, a check is done for each type requested as well as a check for any duplicates received.

Response DTO:
Controller Method:
public async Task<IApiResponseContextDto<GetLookupResponse>>
    Get([FromBodyGetLookupRequest request)
    return await Task.Run(
        () => _executionPlan.Execute<GetLookupRequestGetLookupResponse>(request)

When the API controller receives the request an Execution Pipeline resolves the authorization, validation and worker handlers associated with the request-response types.

Handlers Folder Structure:
Validation Handler:
public class LookupItemValidator : AbstractValidator<LookupRequest>
    public LookupItemValidator(IGetLookupRequestContext requestContext)
        RuleFor(x => x.Type).NotContainSpecialCharacters();
        RuleFor(x => x.Key).NotContainSpecialCharacters();
        RuleFor(x => x.Value).NotContainSpecialCharacters();

        RuleFor(x => x.Type)
            .Must(x => requestContext.Lookups.List.Select(a => a.Item1).Contains(x))
            .WithMessage(x => $"Invalid lookup {x.Type}");

The validation handler confirms that all types received in the request correspond with an existing class implementing the ILookup Interface. It also ensures no special characters are found in the input to prevent SQL injection.

Get Lookups Worker Handler:
protected override void Execute()
    //Find lookups only implementing ILookup
    var allowedLookups = _requestContext.Lookups.List
        .Where(a => (!a.Item2 && !a.Item3)).Select(a => a.Item1).ToList();
    var matched = Request.Lookups.Where(a => allowedLookups.Contains(a.Type)).ToList();

    if (!matched.Any())

    //Compile SQL
    var sql = matched.Aggregate(string.Empty,
        (current, dtoLookup) =>
            current +
            $"select [{dtoLookup.Key}] as [Id]," +
            $"[{dtoLookup.Value}] as [Description]," +
            $"'{dtoLookup.Type}' as [Type] " +
            $"from {dtoLookup.Type} UNION ");

    sql = sql.Substring(0, sql
              .LastIndexOf("UNION ", StringComparison.Ordinal))
          + " order by [Type]";

    //Execute and build result
    _repository.Sql(sql, CallBack);

When both the authorization and validation handlers pass, the worker handlers are executed in the order seen in the folder structure above. Each has a different responsibility. The first two apply filters to ensure the request conforms with Organization and Group restrictions: An organization may only see its own leads or specific lookup data. The last handler resolves all non-restricted data. Each worker finds the request types it is responsible for, compiles a result and adds it to the response context passed to all handlers

In this example, we are compiling and executing three different SQL queries. To improve on this, each handler can combine its compiled SQL and an additional handler can be implemented to union and execute the combined query once.

Now that we have a single solution we can get any key-value pair data we want to expose from our service. We save time writing, testing and implementing new key-value components and at the same time have the ability to extend when required. Each handler can read from a different source and do what it needs to compile its result leaving the other handlers unaffected.

On the road map for this component we have:

  • Redis integration to reduce database interaction.
  • Allow for fuzzy search on each type for type-a-head scenarios
  • Allow for custom filtering on a data source.