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;

	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>.


public class EntityJsonPropertyConverterAttribute : Attribute
	public EntityJsonPropertyConverterAttribute()

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

	public static void Deserialize<TEntity>(TEntity entity, IDictionary<string, EntityProperty> properties)
			.Where(x => x.GetCustomAttributes(typeof(EntityJsonPropertyConverterAttribute), false).Count() > 0)
			.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

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


create procedure
	sp__get_lookup as
	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'
for json path

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)
				while (await reader.ReadAsync())

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


	return lookupEntities;


Srini · 4th June 2021 at 4:56 pm

What is EntityProperty?

Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *