Web API: Supporting data shaping

Usually while building high availability Web API’s, where you know that typically your business objects are quite complex with a lot of properties returned as a part of the object, to the client, one would ideally like to give the client the ability to be able to request a specific number of fields.

That’s understandable from the business point of view and also giving the client a little more control over what they want to get from the API. Although, from the technical side of things, it does pose a few questions:

1. How do you want to get the fields requested from the client
2. How to you manage the scenarios where the client requested some navigation properties (and only specific fields within the navigation property)
3. How to structure the result returned

I am going to try to address this functionality and these points through an example and for the sake of brevity my objects will be a lot simpler to demonstrate the use case in question.

Lets say we have two objects called Trip and Stop, that are defined as:

public class Trip
{
     public int Id { get; set; }
     public string Name { get; set; }
     public string Description { get; set; }
     public DateTime StartDate { get; set; }
     public DateTime? EndDate { get; set; }
     public virtual ICollection<Stop> Stops { get; set; }
}

public class Stop
{
	public int Id { get; set; }
	public string Name { get; set; }
	public DateTime ArrivalDate { get; set; }
	public DateTime? DepartureDate { get; set; }
	public decimal Latitude { get; set; }
	public decimal Longitude { get; set; }

	public virtual int TripId { get; set; }
	public virtual Trip Trip { get; set; }
}

And you have a REST endpoint that implements [HTTPGET] and returns a list of trips. Now the user might only be interested in getting the Name and a list of Stops within a trip for all the trips that are returned. So we need to tell the API the fields that the user wants to request.
Below is one way that this scenario can be addressed.

[HttpGet]
public IHttpActionResult Get(string fields="all")
{
	try
	{
		var results = _tripRepository.Get();
		if (results == null)
			return NotFound();
		// Getting the fields is an expensive operation, so the default is all,
		// in which case we will just return the results
		if (!string.Equals(fields, "all", StringComparison.OrdinalIgnoreCase))
		{
			var shapedResults = results.Select(x => GetShapedObject(x, fields));
			return Ok(shapedResults);
		}
		return Ok(results);
	}
	catch (Exception)
	{
		return InternalServerError();
	}
}

public object GetShapedObject<TParameter>(TParameter entity, string fields)
{
	if (string.IsNullOrEmpty(fields))
		return entity;
	Regex regex = new Regex(@"[^,()]+(\([^()]*\))?");
	var requestedFields = regex.Matches(fields).Cast<Match>().Select(m => m.Value).Distinct();
	ExpandoObject expando = new ExpandoObject();

	foreach (var field in requestedFields)
	{
		if (field.Contains("("))
		{
			var navField = field.Substring(0, field.IndexOf('('));

			IList navFieldValue = entity.GetType()
										?.GetProperty(navField, BindingFlags.IgnoreCase | BindingFlags.Instance | BindingFlags.Public)
										?.GetValue(entity, null) as IList;
			var regexMatch = Regex.Matches(field, @"\((.+?)\)");
			if (regexMatch?.Count > 0)
			{
				var propertiesString = regexMatch[0].Value?.Replace("(", string.Empty).Replace(")", string.Empty);
				if (!string.IsNullOrEmpty(propertiesString))
				{
					string[] navigationObjectProperties = propertiesString.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);

					List<object> list = new List<object>();
					foreach (var item in navFieldValue)
					{
						list.Add(GetShapedObject(item, navigationObjectProperties));
					}

					((IDictionary<string, object>)expando).Add(navField, list);
				}
			}
		}
		else
		{
			var value = entity.GetType()
							  ?.GetProperty(field, BindingFlags.IgnoreCase | BindingFlags.Instance | BindingFlags.Public)
							  ?.GetValue(entity, null);
			((IDictionary<string, object>)expando).Add(field, value);
		}
	}

	return expando;
}

///
<summary>
/// Creates an object with only the requested properties by the client
/// </summary>

/// <typeparam name="TParameter">Type of the result</typeparam>
/// <param name="entity">Original entity to get requested properties from</param>
/// <param name="fields">List of properties requested from the entity</param>
/// <returns>Dynamic object as result</returns>
private object GetShapedObject<TParameter>(TParameter entity, IEnumerable<string> fields)
{
	ExpandoObject expando = new ExpandoObject();
	foreach (var field in fields)
	{
		var value = entity.GetType()
						  ?.GetProperty(field, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance)
						  ?.GetValue(entity, null);
		((IDictionary<string, object>)expando).Add(field, value);
	}
	return expando;
}

So, this allows the user to pass in the query string, a comma separated list of strings that specifies the names of the fields that he wants to be returned such as:

http://localhost:2365/api/trips?fields=name,stops(name,latitude,longitude)

and that would just contain the requested fields (thanks to ExpandoObject class) that helps us construct the object and return the results back to the client as below:

{
	"totalCount": 2,
	"resultCount": 2,
	"results": [
		{
			"name": "Trip to Scandanavia",
			"stops": [
				{
					"name": "Denmark",
					"latitude": 73.2323,
					"longitude": 43.2323
				}
			]
		},
		{
			"name": "Trip to Europe",
			"stops": [
				{
					"name": "Germany",
					"latitude": 72.37657,
					"longitude": 42.37673
				},
				{
					"name": "France",
					"latitude": 72.22323,
					"longitude": 42.3434
				}
			]
		}
	]
}

And that’s all. You can of course build on this approach and add support for multiple nested navigation fields support. Happy coding!

C# dynamic and ExpandoObject to fetch data from Sql Server

A little while ago I was working on a simple application that would let the user define or upload an HTML template and then upload the data that would map to the template. The data could be csv or an excel file. The user could define multiple HTML templates and correspondingly upload data files for each template. The HTML templates and the data files were to be stored in the application for anytime use.

Obviously I had to figure out a mechanism to read the Excel sheet’s columns and corresponding data and get that into an SQL table. That was simple enough. Using the LinqToExcel library  I could read the excel data as simply as:

var excelFactory = new ExcelQueryFactory(fileName);                
var worksheet = excelFactory.GetWorksheetNames();
var columnNames = excelFactory.GetColumnNames(worksheet.FirstOrDefault());
                
var data = (from row in excelFactory.Worksheet(0)
            select row).ToList();

And then just simply store that list into the database.
But the issue was when I was reading that data back from the SQL database, I didn’t have any POCO classes to map that data to for my application to understand. So, this is what ended up doing in the end:

A simple function to read data from the database provided the table name

public IEnumerable<dynamic> GetData(string tableName)
{
    using (var connection = new SqlConnection(this.GetConnectionString()))
    {
        var commandText = "select * from " + tableName;
        using (var command = new SqlCommand(commandText, connection))
        {
            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    yield return GetDynamicData(reader);
                }
            }
            connection.Close();
        }
    }
}

private dynamic GetDynamicData(SqlDataReader reader)
{
    var expandoObject = new ExpandoObject() as IDictionary<string, object>;
    for (int i = 0; i < reader.FieldCount; i++)
    {
        expandoObject.Add(reader.GetName(i), reader[i]);
    }
    return expandoObject;
}

And then just cast each item in the returned

IEnumerable<dynamic>

to an

IDictionary<string, object>

and bind that to my view or do any processing with it.