Sorting ExpandoObject / Dynamic Object Lists in c#

Sometimes I find myself using lists of ExpandoObjects to quickly create lists of dynamic objects. They are super fast to create, and so flexible. You may however want to sort the list using one of the dynamic fields you added. Below is an easy way I use:

yourlist.OrderBy(x => ((IDictionary<string, object>)x)[“yourfield”])

Below is a complete example, making use of a couple Windows versions for some fun sample data.

using System;
using System.Collections.Generic;
using System.Dynamic;
using System.Linq;

namespace ExpandoObjectSort
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            IList<ExpandoObject> windows = new List<ExpandoObject>();

            //add some data
            dynamic win31 = new ExpandoObject();
            win31.codename = "Janus";
            win31.windows = "Windows 3.1";
            win31.released = new DateTime(1992, 4, 6);
            windows.Insert(windows.Count, win31);

            dynamic win95 = new ExpandoObject();
            win95.codename = "Chicago";
            win95.windows = "Windows 95";
            win95.released = new DateTime(1995, 8, 24);
            windows.Insert(windows.Count, win95);

            dynamic winXP = new ExpandoObject();
            winXP.codename = "Whisler";
            winXP.windows = "Windows XP";
            winXP.released = new DateTime(2001, 10, 25);
            windows.Insert(windows.Count, winXP);

            dynamic win8 = new ExpandoObject();
            win8.codename = "Blue";
            win8.windows = "Windows 8";
            win8.released = new DateTime(2012, 10, 26);
            windows.Insert(windows.Count, win8);

            //loop through the list:
            Console.WriteLine("[default]");
            foreach (dynamic win in windows)
            {
                Console.WriteLine(win.windows + " - Codename:" + win.codename + " - Released: " + ((DateTime)win.released).ToShortDateString());
            }

            //sort via codename
            Console.WriteLine("[codename]");
            foreach (dynamic win in windows.OrderBy(x => ((IDictionary<string, object>)x)["codename"]))
            {
                Console.WriteLine(win.windows + " - Codename:" + win.codename + " - Released: " + ((DateTime)win.released).ToShortDateString());
            }

            //sort via date
            Console.WriteLine("[date desc]");
            foreach (dynamic win in windows.OrderByDescending(x => ((IDictionary<string, object>)x)["released"]))
            {
                Console.WriteLine(win.windows + " - Codename:" + win.codename + " - Released: " + ((DateTime)win.released).ToShortDateString());
            }

            Console.ReadLine();
        }
    }
}
Sorting ExpandoObject / Dynamic Object Lists in c#

Parsing JSON data in C# (JSON.NET, Linq, HttpClient)

Parsing JSON in C# is pretty simple. I’m a fan of using Newtonsoft.Json (known as JSON.NET) – I’ve found this to be the fastest, easiest JSON parser available.

Below are some steps to get parsing on your own (or jump down to the full source code). You can also grab this on GitHub. I’m just using a simple console app for this example. My JSON is also coming from a Google Custom Search (via http get). You’ll need to have your own JSON url (or maybe get a test url here).

  1. Start a new project > console application.
  2. In Package Manager Console, run: Install-Package Newtonsoft.Json
  3. Add the below to the top of your code file (Program.cs):
    using Newtonsoft.Json.Linq;
    using System;
    using System.Linq;
    using System.Net;
    using System.Net.Http;
    using System.Threading.Tasks;
  4. Create a task to get some JSON data from http:public static async Task<string> httpResponse(string url)
    {
    IWebProxy iprox = WebProxy.GetDefaultProxy();
    iprox.Credentials = CredentialCache.DefaultCredentials;HttpClientHandler httpHandler = new HttpClientHandler()
    {
    UseProxy = true,
    Proxy = iprox,
    PreAuthenticate = true,
    UseDefaultCredentials = true,
    Credentials = CredentialCache.DefaultCredentials
    };using (var httpClient = new HttpClient(httpHandler))
    return await httpClient.GetStringAsync(url);
    }
  5. Now get some data:
    JObject jData = JObject.Parse(httpResponse(@”https://www.googleapis.com/customsearch/v1?youneedtocreateyourownlink&#8221;).Result);
  6. Read some simple data:
    string sampleValue = (string)jData[“url”][“type”];
  7. Read array of data:
    foreach (var sampleItem in jData[“items”])
    {
    string sampleItemValue = (string)sampleItem[“link”];
    Console.WriteLine(sampleItemValue);
    }

The full source:

using Newtonsoft.Json.Linq;
using System;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;

namespace parsingJSON
{
    internal class Program
    {
        public static async Task<string> httpResponse(string url)
        {
            //i'm using a proxy, you could remove this if needed.
            IWebProxy iprox = WebProxy.GetDefaultProxy();
            iprox.Credentials = CredentialCache.DefaultCredentials;

            HttpClientHandler httpHandler = new HttpClientHandler()
            {
                UseProxy = true,
                Proxy = iprox,
                PreAuthenticate = true,
                UseDefaultCredentials = true,
                Credentials = CredentialCache.DefaultCredentials
            };

            using (var httpClient = new HttpClient(httpHandler))
                return await httpClient.GetStringAsync(url);
        }

        private static void Main(string[] args)
        {
            //get some JSON data
            JObject jData = JObject.Parse(httpResponse(@"https://yourJSONlinkhere").Result);

            string sampleValue = (string)jData["url"]["type"];

            Console.WriteLine(sampleValue);

            //loop through array in JSON
            foreach (var sampleItem in jData["items"])
            {
                string sampleItemValue = (string)sampleItem["link"];
                Console.WriteLine(sampleItemValue);
            }

            //filter w/ linq:

            var sampleArray =
              from p in jData["items"]
              select p;

            foreach (var sampleItem in sampleArray.Where(p => ((string)p["link"]).Contains("https")))
            {
                string sampleItemValue = (string)sampleItem["link"];
                Console.WriteLine(sampleItemValue);
            }

            Console.ReadLine();
        }
    }
}

View on GitHub

Parsing JSON data in C# (JSON.NET, Linq, HttpClient)

Reading Excel Files in .net Using LinqToExcel

planetsI’m sure at some point you’ve needed to read some Excel files in an application. You’ve probably even used the Office Interop libraries (using something like: new Excel.Application();). Below I’ll show a much easier way that makes use of a data model to read an Excel file using LinqToExcel. If you’re looking for the complete code, scroll to the end of the page.

  1. Start a new solutions / project. In my example I’m just using a console app.
  2. Make sure you save your solution before running step 3, or you’ll get a “The current environment doesn’t have a solution open.” error.
  3. In the Package Manager Console, at the “PM>” prompt, enter “Install-Package LinqToExcel” and press enter. When complete, it should read: “Successfully added ‘LinqToExcel 1.10.1’ to…”
  4. Add these lines to the very top of your code (if not already there):
    using System;
    using System.Linq;
    using LinqToExcel;

    using LinqToExcel.Attributes;
  5. Add your data model. I’m using an Excel spreadsheet that contains the planets sizes and masses (taken from this sweet NASA page). Why? Because it’s really cool.
    xls1
    My model looks like:

    internal class Planet
     {
     [ExcelColumn("Planetary Body")]
     public string Name { get; set; }
    
    [ExcelColumn("Mass (1024kg)")]
     public double Mass { get; set; }
    
    [ExcelColumn("Diameter (km)")]
     public int Diameter { get; set; }
     }
  6. Notice how the ExcelColumn attribute contains the formatted name of the column (with spaces, etc.). Make sure these match to your Excel columns.
  7. Now in your Main method, let’s add some code to read the Excel file (replacing the below w/ your file path, model name and sheet name (“Planets” in the below):
    var excel = new ExcelQueryFactory(@"F:\cbitting\Planets.xlsx")
    {
     DatabaseEngine = LinqToExcel.Domain.DatabaseEngine.Ace,
     TrimSpaces = LinqToExcel.Query.TrimSpacesType.Both,
     UsePersistentConnection = true,
     ReadOnly = true
     };
    var planets = from p in excel.Worksheet<Planet>("Planets")
     select p;
  8. Now let’s add something to see what data this retrieved:
    foreach (Planet planet in planets)
     {
     Console.WriteLine(planet.Name + " Mass: " + planet.Mass);
     }
    Console.ReadLine();
  9. Your results should be the data in your Excel file.

You can filter the data in the Linq statement using something like:

var planets = from p in excel.Worksheet<Planet>("Planets")
 where p.Diameter > 5000
 select p;

Or order the data using:

 foreach (Planet planet in planets.OrderByDescending(p => p.Diameter))

This is a very simple example of this powerful method of reading Excel spreadsheets. https://github.com/paulyoder/LinqToExcel has many more examples and documentation.

If you’re looking to use this on a machine without Office installed, I believe you need https://www.microsoft.com/en-au/download/details.aspx?id=13255 to enable ACE.

Below is the entire code from this example:

using System;
using System.Linq;
using LinqToExcel;
using LinqToExcel.Attributes;

namespace cSharpExcelRead
{
    internal class Planet
    {
        [ExcelColumn("Planetary Body")]
        public string Name { get; set; }

        [ExcelColumn("Mass (1024kg)")]
        public double Mass { get; set; }

        [ExcelColumn("Diameter (km)")]
        public int Diameter { get; set; }
    }

    internal class Program
    {
        private static void Main(string[] args)
        {
            var excel = new ExcelQueryFactory(@"F:\cbitting\Planets.xlsx")

            {
                DatabaseEngine = LinqToExcel.Domain.DatabaseEngine.Ace,
                TrimSpaces = LinqToExcel.Query.TrimSpacesType.Both,
                UsePersistentConnection = true,
                ReadOnly = true
            };

            var planets = from p in excel.Worksheet<Planet>("Planets")
                          select p;

            foreach (Planet planet in planets.OrderByDescending(p => p.Diameter))
            {
                Console.WriteLine(planet.Name + " Mass: " + planet.Mass);
            }

            Console.ReadLine();
        }
    }
}
Reading Excel Files in .net Using LinqToExcel

Using SortedSet as a Sorted List with Custom Comparer – c#

Sometimes you have a list of objects you want to stay sorted. You can usually use an IList and then linq: orderby to sort the list – but other times it helps to have the list always sorted. In those times, SortedSet works very well. In my below example, a sorted set contains some people – and whenever you look at the list in the SortedSet – they are already sorted (by age, name & location).

I was concerned about the performance of the SortedSet vs. a List, so after running some tests, as expected – the SortedSet needs some initial time when adding items (to compare) vs. the List which will is fast to write to – but needs a little time when sorting the list after. Below is some really fast numbers (data in milliseconds) I’ve compiled based on adding / reading 25000 records:

sortedSet-performance

Below is the sample using SortedSet and an IComparer:

using System;
using System.Collections.Generic;

namespace sortedSet
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            //create the SortedSet
            SortedSet<Person> people = new SortedSet<Person>(new PersonComparer());

            //add some random folks:
            Random rnd = new Random();
            for (int i = 1; i <= 25; i++)
            {
                //new person
                Person person = new Person();
                person.name = "Bob " + i.ToString();
                person.location = "Miami" + i.ToString();

                //random age
                int r = rnd.Next(1, 99);
                person.age = r;

                //add person to set
                people.Add(person);
            }

            //show our list:
            foreach (Person person in people)
            {
                Console.WriteLine(person.name + " - Age: " + person.age.ToString());
            }

            Console.ReadLine();
        }
    }

    //create comparer
    internal class PersonComparer : IComparer<Person>
    {
        public int Compare(Person x, Person y)
        {
            //first by age
            int result = x.age.CompareTo(y.age);

            //then name
            if (result == 0)
                result = x.name.CompareTo(y.name);

            //a third sort
            if (result == 0)
                result = x.location.CompareTo(y.location);

            return result;
        }
    }

    internal class Person
    {
        public string name { get; set; }

        public string location { get; set; }

        public int age { get; set; }
    }
}

-If you don’t have a need to sort your lists – don’t forget about HashSet (very fast).

Using SortedSet as a Sorted List with Custom Comparer – c#

MongoDB – Linking Records / Documents Using MongoDBRef

If you’re using MongoDB, you know as a document database it doesn’t provide the “join” feature you’ve come to rely on in standard relation databases (sql server, mySql, postgres). In many instances, you’re not using Mongo as a replacement for your existing data structure / methods – but it would be nice to relate some documents (without embedding everything [potentially duplicating data] into a single document.

I am a big fan of MongoDB and the .net / c# driver. Below is a method I’ve found that works well for relating different documents using MongoDBRef. In my scenario – which I’m sure many have better methods – I’m using a property to keep a list of related documents – then a method to retrieve the related documents if needed.

I’m my below example, I’m creating trains, train cars, and passengers – then relating them. In short: I’m using IList<MongoDBRef> to store the list, and FetchDBRefAs to get the documents again.

This works really well and is flexible. I’ve tested this against many records and have found that if you’re looking to get thousands of records with their related thousands of records – you won’t experience the same performance as a standard relational db join. But for a few records (hundreds in my testing) it’s fast and efficient. I’m also using .AsParallel() on the query – this gained me about 10%+ performance.

One note: if you’re allowing Mongo to generated the document id on insertion, queuing up records in a bulk operation (ie: InitializeOrderedBulkOperation) you’ll soon realize there isn’t an id yet to use for the MongoDBRef.

Comment if you’ve found a better method you prefer for linking documents.

My example:

using MongoDB.Bson;
using MongoDB.Driver;
using MongoDB.Driver.Linq;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;

namespace mongoDBlinked
{
    public class passenger
    {
        public ObjectId Id { get; set; }

        public string Name { get; set; }
    }

    public class trainCar
    {
        public ObjectId Id { get; set; }

        public string carNo { get; set; }

        public string serial { get; set; }

        public string note { get; set; }

        public IList<MongoDBRef> passengers { get; set; }

        public IList<passenger> GetPassengers(MongoDatabase db)
        {
            if (passengers.Count == 0)
                return new List<passenger>();

            IList<passenger> tpass = new List<passenger>();
            foreach (var related in passengers)
            {
                tpass.Add(db.FetchDBRefAs<passenger>(related));
            }

            return tpass;
        }
    }

    public class train
    {
        public ObjectId Id { get; set; }

        public string trainNo { get; set; }

        public string note { get; set; }

        public IList<MongoDBRef> trainCars { get; set; }

        public IList<trainCar> relTrainCards { get; set; }

        public IList<trainCar> GetTraincars(MongoDatabase db)
        {
            if (trainCars.Count == 0)
            {
                return new List<trainCar>();
            }

            IList<trainCar> tcars = new List<trainCar>();
            foreach (var related in trainCars)
            {
                tcars.Add(db.FetchDBRefAs<trainCar>(related));
            }

            return tcars;
        }
    }

    internal class Program
    {
        private static void Main(string[] args)
        {
            
            var server = new MongoClient("mongodb://localhost").GetServer();
            var database = server.GetDatabase("testmongodb");

            var collection = database.GetCollection<trainCar>("trainCars");
            var trainCollection = database.GetCollection<train>("trains");
            var peopleCollection = database.GetCollection<passenger>("passengers");
            

            //add some data
            for (int t = 0; t < 20; t++)
            {
                train newTrain = new train();
                newTrain.note = "nyc" + t.ToString();
                newTrain.trainNo = "345";
                newTrain.trainCars = new List<MongoDBRef>();

               

                //add some cars:

                for (int i = 0; i < 50; i++)
                {
                    trainCar tcar = new trainCar();

                    tcar.carNo = "0" + i.ToString();
                    tcar.note = "Needs new brakes";
                    tcar.serial = "1234";
                    tcar.passengers = new List<MongoDBRef>();
                    for (int p = 0; p < 3; p++)
                    {
                        passenger pass = new passenger();
                        pass.Name = "name" + p.ToString();
                        peopleCollection.Insert(pass);
                        tcar.passengers.Add(new MongoDBRef(peopleCollection.Name, pass.Id));
                    }

                    collection.Insert(tcar);
                 
                    newTrain.trainCars.Add(new MongoDBRef(collection.Name, tcar.Id));
                }

                

                trainCollection.Insert(newTrain);
            }
            Console.WriteLine("saved");
           

            Stopwatch stopWatch = new Stopwatch();
            stopWatch.Start();

            //get the data
            //asparallel gained me about 15% of qry time
            var query = from v in trainCollection.AsQueryable<train>().AsParallel()
                        select v;

            foreach (train aTrain in query)
            {
                Console.WriteLine("train: " + aTrain.note);
                foreach (trainCar tcar2 in aTrain.GetTraincars(database))
                {
                    if (tcar2 != null)
                    {
                        Console.WriteLine("car: " + tcar2.carNo);
                        foreach (passenger pass in tcar2.GetPassengers(database))
                        {
                            Console.WriteLine("car: " + tcar2.carNo + " pass: " + pass.Name);
                        }
                    }
                }

               
            }
            Console.WriteLine("trains loaded");

            stopWatch.Stop();           
            TimeSpan ts = stopWatch.Elapsed;

            
            string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
                ts.Hours, ts.Minutes, ts.Seconds,
                ts.Milliseconds / 10);
            Console.WriteLine("RunTime " + elapsedTime);

            

            Console.ReadLine();
        }
    }
}
MongoDB – Linking Records / Documents Using MongoDBRef