Reading SQL JSON Results into .NET Core

If you have a complex table structure in SQL that you want to map into corresponding classes in your .NET Core project it might be easier to output the result of your SQL query as JSON and then deserialize the results to your object model rather than trying to map it directly from table to class.

For example, I have a Lookup table in Azure Table Storage that I want to be able to load different kinds of data into, this is partitioned by the type of data and the row key is the ID of the data row, all other SQL fields will be stored as a dictionary of attributes.

public class LookupEntity : TableEntity
{
	public LookupEntity(string templateId, string lookupId)
	{
		this.PartitionKey = templateId;
		this.RowKey = lookupId;
	}

	[EntityJsonPropertyConverter]
	public Dictionary<string, string> Attributes { get; set; }

	public override IDictionary<string, EntityProperty> WriteEntity(OperationContext operationContext)
	{
		var results = base.WriteEntity(operationContext);
		EntityJsonPropertyConverter.Serialize(this, results);
		return results;
	}

	public override void ReadEntity(IDictionary<string, EntityProperty> properties, OperationContext operationContext)
	{
		base.ReadEntity(properties, operationContext);
		EntityJsonPropertyConverter.Deserialize(this, properties);
	}
}

This uses the EntityJsonPropertyConverterAttribute attribute to specify that the JSON in the Attributes filed should be deserialized into a Dictionary<string, string>.

[AttributeUsage(AttributeTargets.Property)]
public class EntityJsonPropertyConverterAttribute : Attribute
{
	public EntityJsonPropertyConverterAttribute()
	{
	}
}

public class EntityJsonPropertyConverter
{
	public static void Serialize<TEntity>(TEntity entity, IDictionary<string, EntityProperty> results)
	{
		entity.GetType().GetProperties()
			.Where(x => x.GetCustomAttributes(typeof(EntityJsonPropertyConverterAttribute), false).Count() > 0)
			.ToList()
			.ForEach(x => results.Add(x.Name, new EntityProperty(JsonConvert.SerializeObject(x.GetValue(entity)))));
	}

	public static void Deserialize<TEntity>(TEntity entity, IDictionary<string, EntityProperty> properties)
	{
		entity.GetType().GetProperties()
			.Where(x => x.GetCustomAttributes(typeof(EntityJsonPropertyConverterAttribute), false).Count() > 0)
			.ToList()
			.ForEach(x => x.SetValue(entity, JsonConvert.DeserializeObject(properties[x.Name].StringValue, x.PropertyType)));
	}
}

The product data in this lookup table comes from a tablewith the following structure.

CREATE TABLE [dbo].[email_content_product_lookup](
	[product_id] [int] NOT NULL,
	[short_desc] [varchar](255) NULL,
	[product_type] [varchar](255) NULL,
	[price] [numeric](8, 2) NULL,
	[was_price] [numeric](18, 2) NULL,
	[number_of_reviews] [int] NULL,
	[average_overall_rating] [numeric](5, 4) NULL
) ON [PRIMARY]
GO

This is then parsed into the required format by using the FOR JSON output modifier in SQL.

create procedure
	sp__get_lookup as
select
	cast('Product' as varchar(50)) as partitionKey,
	cast(product_id as varchar(50)) as rowKey,
	[product_name] as 'attributes.productName'
	,[range] as 'attributes.range'
	,[short_desc] as 'attributes.shortDesc'
	,[product_type] as 'attributes.productType'
	,[price] as'attributes.price'
	,[was_price] as 'attributes.wasPrice'
	,[product_url] as 'attributes.productUrl'
	,[thumbnail_image_url] as 'attributes.thumbnailImageUrl'
	,[image_url] as 'attributes.imageUrl'
	,[number_of_reviews] as 'attributes.numberOfReviews'
	,[average_overall_rating] as 'attributes.averageOverallRating'
from
	[dbo].[email_content_product_lookup]
for json path
go

This produces the following JSON.

[{
	"partitionKey": "Product",
	"rowKey": "18538",
	"attributes": {
		"shortDesc": "Tumbler 35cl",
		"productType": "Glassware",
		"price": 2.50,
		"wasPrice": 2.50,
		"numberOfReviews": 15,
		"averageOverallRating": 5.0000
	}
},
{
	"partitionKey": "Product",
	"rowKey": "18574",
	"attributes": {
		"shortDesc": "Small paper easy-to-fit ceiling shade",
		"productType": "Lampshade",
		"price": 5.00,
		"wasPrice": 5.00,
		"numberOfReviews": 130,
		"averageOverallRating": 4.8077
	}
}]

Because SQL JSON results can be pretty long you can’t just read the result in as a string field from a stored procedure, rather you need to build up a string using a StringBuilder which you can then parse.

public async Task<List<LookupEntity>> GetLookupsAsync()
{
	List<LookupEntity> lookupEntities = new List<LookupEntity>();

	using (SqlConnection connection = GetSqlConnection())
	{
		using (SqlCommand command = new SqlCommand("sp__get_lookups"))
		{
			command.CommandType = System.Data.CommandType.StoredProcedure;
			command.Connection = connection;

			await connection.OpenAsync();
			StringBuilder jsonResult = new StringBuilder();
			SqlDataReader reader = await command.ExecuteReaderAsync();

			if (!reader.HasRows)
			{
				jsonResult.Append("[]");
			}
			else
			{
				while (await reader.ReadAsync())
				{
					jsonResult.Append(reader.GetValue(0).ToString());
				}

				lookupEntities = JsonConvert.DeserializeObject<List<LookupEntity>>(jsonResult.ToString());
			}

			connection.Close();
		}
	}

	return lookupEntities;
}