MSMQ and SQL Server integration


At times we have long running SQL procedures and its not always advisable to have the application waiting on it.Especially if your application needs to perform certain set of tasks based on the result returned from the SQL operation.

There are a few approaches that one might consider to address this scenario and using the Service Broker is definitely one of them. But we are not going to look into Service Broker, rather look at how we can use Messaging Queuing with SQL Server to address this requirement.

untitled-diagram

SQL Server interaction with MSMQ

The following steps depicts the process involved.

  • The SQL Server runs a Scheduled Job that does the following
    • Execute the long running Stored procedure
    • Execute a stored procedure that sends a message to a remote Message Queue
  • Remote queue on the application server receives the message
  • Client application running on the application server polls for messages from the MSMQ. On receiving the message, the message is read from the MSMQ and application can resume its further processing

In this post we will cover all the configuration, components and environment settings including (ports, firewall rules, queue access, SQL configuration etc.) required to facilitate this setup.

.Net Class Library

This class library will simply send the message to a MSMQ. This defines a simple method that takes the queue name and the message to be delivered as parameters.

using System;
using System.Data.SqlTypes;
using System.Messaging;
using Microsoft.SqlServer.Server;

namespace SqlMsmq
{
    public class SqlToMsmqManager
    {
        /// <summary>
        /// Sends a message to the queue
        /// </summary>
        /// <param name="queueName">Full name of the queue</param>
        /// <param name="message">Message to send</param>
        [SqlProcedure]
        public static void Send(SqlString queueName, SqlString message)
        {
            if (queueName == null || string.IsNullOrEmpty(queueName.Value))
                throw new Exception("Message queue name need to be provided");

            var queue = queueName.Value;
            if (!MessageQueue.Exists(queue))
                throw new Exception("Message queue does not exist");
            try
            {
                using (var messageQueue = new MessageQueue(queue, QueueAccessMode.Send))
                {
                    messageQueue.Formatter = new XmlMessageFormatter(new Type[] { typeof(string) });
                    messageQueue.Send(message.Value, MessageQueueTransactionType.Single);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}

SQL Server

To be able to send a message to a Message Queue from SQL Server, first of all we will need to register the System.Messaging assembly and the assembly we created above with SQL Server. Below is the script that defines different steps required in SQL Server configuration

-- STEP1: Enable CLR integration in SQL Server
SP_CONFIGURE 'clr enable', 1
GO
RECONFIGURE
GO

USE [DatabaseName]
GO
ALTER DATABASE [DatabaseName] SET TRUSTWORTHY ON
GO

-- STEP 2: Add System.Messaging assembly to the database to enable Message Queuing component
CREATE ASSEMBLY Messaging
AUTHORIZATION dbo
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Messaging.dll' -- Path to the System.Messaging.dll assembly
WITH PERMISSION_SET = UNSAFE
GO

-- STEP 3: Add the external .Net assembly that will send the message to the queue
CREATE ASSEMBLY SqlToMsmq
AUTHORIZATION dbo
FROM 'C:\SqlToMsmq\SqlMsmq.dll' -- Path to the .Net class library
WITH PERMISSION_SET = UNSAFE
GO

-- STEP 4: Create procedure that will calls into the external .Net assembly to send the message
CREATE PROCEDURE [SendMsmqMessage]
	@queueName NVARCHAR(200),
	@message NVARCHAR(MAX)
AS
	EXTERNAL NAME SqlToMsmq.[SqlMsmq.SqlToMsmqManager].Send
GO

--Stored Procedure that needs to be called from the escowing SP at the end
EXEC SendMsmqMessage '<Full queue name to send the message after SP runs>', '<Message><Status>Stored procedure processed</Status></Message>'

Notes:

  • You will need to be an admin user to be able to  make the above configurations to SQL server
  • Sometimes when registering assemblies in SQL Server you would receive the error:
    "The database owner SID recorded in the master database differs from the database owner SID recorded in database.You should correct this situation by resetting the owner of database using the ALTER AUTHORIZATION statement."

    In this case run the following statement to alter the authorization on the database

ALTER AUTHORIZATION ON DATABASE::[DatabaseName] TO [LoginName]

MSMQ

Because in the class library we have instructed that the message queue is a transactional queue, we need to create a transactional queue on the application server

Environment Configuration

MSMQ communicates over ports 135, 1801, 2103/2105

  • Sending Messages

So, since our database server will be pushing the message to MSMQ, we need to have port 1801 on the database server. MSMQ uses this port to establish a network session and then push the message to the destination

  • Receiving Messages

MSMQ uses RPC for pulling messages, which requires ports 135, 2103 or 2105 open on the application server.

Environment Testing

To test that you have the required level of connectivity between your database and application server for sending MSMQ messages you should at least be able to telnet from your database server to your application server on port 1801

telnet 1801

And lastly

To send the message from your SQL Server to the MSMQ queue as defined above all we have to do is:


EXEC SendMsmqMessage 'FormatName:Direct=OS:ServerName\Private$\QueueName', '<Message>Any message</Message>'

Notes:

  • Since our queue is a private queue we need to specify the message queue name in complete format.
    • We can use either OS or TCP format specifier

And that’s it. The message should appear in the message queue.

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!

COM Interop without referencing COM assemblies using Dynamic C#


Dynamics is a very strong yet quite under utilised feature of C# which came with C# version 4.0. The primary premise for its usage is that the “object type or data structure is not known at compile time”. In these cases the using the dynamic keyword basically tells the C# compiler to defer it evaluating the object type/ data structure to run time instead of compile time. This functionality or capability comes from another language runtime that sits on top of the CLR (Common Language Runtime) called the DLR (Dynamic Language Runtime).

Dynamics has a lot of use cases in the .Net Framework, one of which is interacting with COM components without having to actually add references to the Primary COM Interop assemblies. Below I just wanted to show a little use case where we can use dynamic to create an Excel document without referencing the following Excel Interop assembly:

  • Microsoft.Office.Interop.Excel

Now ofcourse, you would have to have Excel installed on the system where the code would run, but we will eliminate the need to reference the COM interop assemblies to our project. Also with use of dynamics one would need to have knowledge of the library since we do not get any intellisense support in Visual Studio once an object is declared as dynamic. This is solely because of the fact that the compiler does not know the type of the object until its evaluated at run time. So you would only see the base object methods on the intellisense.

My intent here is not to create a fully featured application, rather to just show the use case of how we can use dynamics to interact with COM Interop assemblies without actually having to reference them in our projects.

I am going to create a simple Console application that will launch an Excel, open a worksheet and add some information to the rows and columns. The sample code for the simple application is available on github.

I am going to create a simple Person class, the data for which we will add to Excel.

public class Person
{
    public string Name { get; set; }
    public int Age { get; set; }

    public Person(string name, int age)
    {
        Name = name;
        Age = age;
    }
}

Create a Console application and add a reference to the following assembly

AddReferenceInteropExcel

This example opens Excel WITH the primary interop assembly reference

class Program
{
    static List<Person> persons = new List<Person>();
    static Program()
    {
        persons.Add(new Person("Frank", 25));
        persons.Add(new Person("Joe", 24));
    }
 
    static void Main(string[] args)
    {
        var excelType = new Microsoft.Office.Interop.Excel.Application();
        excelType.Visible = true;
 
        excelType.Workbooks.Add();
        Worksheet workSheet = excelType.ActiveSheet;
 
        workSheet.Cells[1, 1] = "Names";
        workSheet.Cells[1, 2] = "Age";
 
        int rowIndex = 1;
        foreach (var person in persons)
        {
            rowIndex++;
            workSheet.Cells[rowIndex, 1] = person.Name;
            workSheet.Cells[rowIndex, 2] = person.Age;
        }
    }
}

And the same example WITHOUT using the Excel interop assembly:


class Program
{
    static List<Person> persons = new List<Person>();
    static Program()
    {
        persons.Add(new Person("Frank", 25));
        persons.Add(new Person("Joe", 24));
    }

    static void Main(string[] args)
    {
        dynamic excelType = Type.GetTypeFromProgID("Excel.Application");
        excelType.Visible = true;

        excelType.Workbooks.Add();
        dynamic workSheet = excelType.ActiveSheet;

        workSheet.Cells[1, 1] = "Names";
        workSheet.Cells[1, 2] = "Age";

        int rowIndex = 1;
        foreach (var person in persons)
        {
            rowIndex++;
            workSheet.Cells[rowIndex, 1] = person.Name;
            workSheet.Cells[rowIndex, 2] = person.Age;
        }
    }
}

We get the type of the Excel application using the Type.GetTypeFromProgID into a dynamic variable. Now we can program assuming that at Runtime the variable excelType will be evaluated to Excel.Application. As long as that happens during the run time our program will run just fine. However, its noteworthy that in case the type doesn’t evaluate to Excel.Application at runtime a RunTimeBinderException will be thrown when we try to access any properties or methods on the excelType variable.

So as we can see, Dynamic C# provides a very powerful mechanism that compliments the statically defined C# bindings and can also be used to interact with other dynamic langauages like Iron Python etc without much code clutter.

Integrating Rakuten API with Quartz.Net for Scheduling jobs using Windows Service


Recently one of my clients started using Rakuten E-commerce market place and wanted to develop a solution to run scheduled jobs at regular intervals for accessing the Rakuten API for tasks like updating stock information, fetching orders, updating shipment status for the orders etc.
The API is fairly well documented and is REST based, simple to understand. So I am going to show how we can work with Rakuten E-commerce market place API along side Quartz.Net for building Scheduled Jobs that will run inside a windows service.

Setup:
To start using Rakuten, You must request a license to use the Rakuten Marketplace Web Services Development Platform. Contact Rakuten Support to request your license. Rakuten assigns an authentication key to you containing your encoded credentials. This value must be used in each of your HTTP Request Headers to authorize your requests. Some of the other settings that we require to access the Rakuten API are:

I have used Visual Studio 2015 to develop this Windows Service.

Creating a HTTPClient with Authorization header to make API requests:
We will be using this HttpClient to make Rest based API requests to the Rakuten API

private HttpClient GetHttpClient()
{
    var client = new HttpClient();
    var authenticationKey = ConfigurationHelper.GetValue("AuthenticationKey");
    client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("ESA", authenticationKey);
    client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
    return client;
}

Helper classed used:

namespace RakutenIntegrationService.Helpers
{
    public static class CronHelper
    {
        public static string GetCronExpression(double interval)
        {
            return string.Format("0 0/{0} * 1/1 * ? *", interval);
            //return "0 43 16 ? * *";
        }
    }
}

using System;
using System.Configuration;

namespace RakutenIntegrationService.Helpers
{
    public static class ConfigurationHelper
    {
        public static TResult GetValue<TResult>(string key)
        {
            var setting = ConfigurationManager.AppSettings[key];
            if (string.IsNullOrEmpty(setting))
                return default(TResult);
            var result = Convert.ChangeType(setting, typeof(TResult));
            return (TResult)result;
        }
    }
}

And then define a RestService class that contains methods to make GET and POST requests using this HttpClient

using System;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using Common.Logging;
using Newtonsoft.Json;
using Newtonsoft.Json.Serialization;
using RakutenIntegrationService.Helpers;

namespace RakutenIntegrationService.Services
{
    public class RestService : IRestService
    {
        #region Fields

        private ILog Log = LogManager.GetLogger<RestService>();

        #endregion

        #region IRestService members

        public TResult Get<TResult>(string uriString) where TResult: class
        {
            var uri = new Uri(uriString);
            using (var client = GetHttpClient())
            {
                HttpResponseMessage response = client.GetAsync(uri).Result;
                if (response.StatusCode != HttpStatusCode.OK)
                {
                    Log.Error(response.ReasonPhrase);
                    return default(TResult);
                }
                var json = response.Content.ReadAsStringAsync().Result;
                return JsonConvert.DeserializeObject<TResult>(json, new JsonSerializerSettings { ContractResolver = new CamelCasePropertyNamesContractResolver() });
            }
        }

        public TResult Post<TResult, TInput>(string uriString, TInput payload = null) where TInput : class
        {
            var uri = new Uri(uriString);
            using (var client = GetHttpClient())
            {
                var jsonContent = JsonConvert.SerializeObject(payload, Formatting.Indented, new JsonSerializerSettings { ContractResolver = new CamelCasePropertyNamesContractResolver()});
                HttpResponseMessage response = client.PostAsync(uri, new StringContent(jsonContent, Encoding.UTF8, "application/json")).Result;
                if (response.StatusCode != HttpStatusCode.OK)
                {
                    Log.Error(response.ReasonPhrase);
                    return default(TResult);
                }
                var json = response.Content.ReadAsStringAsync().Result;
                return JsonConvert.DeserializeObject<TResult>(json);
            }
        } 

        #endregion
    }
}

Configuring Quartz.Net to create Scheduled jobs for API operations
So I installed Quartz.Net using nuget package manager: https://www.nuget.org/packages/Quartz/. Afterwards I created a class called TaskScheduler that basically configures the Scheduled Jobs creation and is responsible for Running/ Stopping the Quartz scheduler

using Quartz;
using RakutenIntegrationService.Helpers;
using RakutenIntegrationService.Jobs;

namespace RakutenIntegrationService.Scheduler
{
    public class TaskScheduler : ITaskScheduler
    {
        #region Private fields

        private readonly IScheduler _scheduler;

        #endregion

        #region Constructors

        public TaskScheduler(IScheduler scheduler)
        {
            _scheduler = scheduler;
        }

        #endregion

        #region ITaskScheduler members

        public string Name
        {
            get { return this.GetType().Name; }
        }

        public void Run()
        {
            ScheduleGetOrdersJob();
            ScheduleStockUpdateJob();
            ScheduleShipmentUpdateJob();

            _scheduler.Start();
        }

        public void Stop()
        {
            if (_scheduler != null) _scheduler.Shutdown(true);
        }

        #endregion

        #region Private methods

        private void ScheduleGetOrdersJob()
        {
            var jobDetails = JobBuilder.Create<GetOrdersJob>()
                                       .WithIdentity("GetOrdersJob")
                                       .Build();
            var trigger = TriggerBuilder.Create()
                                        .StartNow()
                                        .WithCronSchedule(CronHelper.GetCronExpression(ConfigurationHelper.GetValue<double>("GetOrdersInterval")))
                                        .Build();
            _scheduler.ScheduleJob(jobDetails, trigger);
        }

        private void ScheduleStockUpdateJob()
        {
            var jobDetails = JobBuilder.Create<StockUpdateJob>()
                                       .WithIdentity("StockUpdateJob")
                                       .Build();
            var trigger = TriggerBuilder.Create()
                                        .StartNow()
                                        .WithCronSchedule(CronHelper.GetCronExpression(ConfigurationHelper.GetValue<double>("StockUpdateInterval")))
                                        .Build();
            _scheduler.ScheduleJob(jobDetails, trigger);
        }

        private void ScheduleShipmentUpdateJob()
        {
            var jobDetails = JobBuilder.Create<ShipmentUpdateJob>()
                                       .WithIdentity("ShipmentUpdateJob")
                                       .Build();
            var trigger = TriggerBuilder.Create()
                                        .StartNow()
                                        .WithCronSchedule(CronHelper.GetCronExpression(ConfigurationHelper.GetValue<double>("ShipmentUpdateInterval")))
                                        .Build();
            _scheduler.ScheduleJob(jobDetails, trigger);
        }

        #endregion
    }
}

The jobs classes can then be created to do concrete specific work. I am giving an example of the GetOrdersJob

using System.Linq;
using Quartz;
using RakutenIntegrationService.Helpers;
using RakutenIntegrationService.Models.Response;
using RakutenIntegrationService.Services;

namespace RakutenIntegrationService.Jobs
{
    public class GetOrdersJob : IJob
    {
        #region Fields

        private readonly IRestService _restService;

        #endregion

        #region Constructors

        public GetOrdersJob(IRestService restService)
        {
            _restService = restService;
        }

        #endregion

        #region IJob members

        public void Execute(IJobExecutionContext context)
        {
            var uri = string.Concat(DataServiceConstants.BaseEndpointAddress, "order/list", RequestBuilder.ConstructListOrdersRequestParams());
            var response = _restService.Get<OrderResponse>(uri);
            if (response != null && response.Orders != null && response.Orders.Any())
            {
                var ordersToProcess = response.GetOrdersToProcess();
                if (ordersToProcess != null && ordersToProcess.Any())
                    OrderProcessor.ProcessOrders(ordersToProcess);
            }
        }

        #endregion
    }
}

And then define a Windows Service class that provides methods to Start and Stop the Scheduler

using System.ServiceProcess;
using Common.Logging;
using RakutenIntegrationService.Scheduler;

namespace RakutenIntegrationService
{
    public partial class RakutenService : ServiceBase
    {
        #region Fields

        private static ILog Log = LogManager.GetLogger<RakutenService>();
        private readonly ITaskScheduler _taskScheduler;

        #endregion

        public RakutenService(ITaskScheduler taskScheduler)
        {
            InitializeComponent();
            _taskScheduler = taskScheduler;
        }

        protected override void OnStart(string[] args)
        {
            Log.Info("Starting Rakuten Scheduler service.");
            _taskScheduler.Run();
        }

        protected override void OnStop()
        {
            Log.Info("Stopping Rakuten Scheduler service.");
            _taskScheduler.Stop();
        }
    }
}

And that’s pretty much about it. You can add as much custom functionality in the individual jobs classes depending what work you want the Scheduled Job to perform.

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.