Dynamically Extracting SQL Fields in LinqToSQL Data Object

Problem:

I had a situation in which I get a SQL record using LinqToSQL then dynamically extract a value continued in the returned record by the field name.  For example, suppose you have a SQL Table that contains columns like "Description" or "Indication" and the caller would want to know what's contained in these field individually, also dynamically (so that in the future if a column is added, the code do not need to change.)

If you look at these database record objects that LinqToSQL generates, there is no "by-name" functions. 

Solution: 

As it turns out you can essentially "parse" break-down a C# object descriptions into arrays called MethodInfo and FieldInfo using System.Reflection. Once we know this the rest of of the idea is easy; just find the matching method or field names the user requests and dynamically access either the fields or methods.

In LinqToSQL database "row" class objects, the column values are implemented as {get; set; } methods, so instead of extracting the FieldInfo, you need to extract MethodInfo for the column field, and you get two methods get_ and set_. So if my Study table contains Description then the method to get the Description value in the column is _get_Description.

My code example below returns the value contained in the field. In order to make it easier for the user, I make the field name matching to be non-case sensitive by ToLower() the names. 

 private string FindStudyField(string Field, Guid? StudyGuidKey)
        {
            if (StudyGuidKey == null) return "";

            using (var ctx = MyLINQtoSQL.ContextFactory.NewMyDataConext())
            {

                var studies = from s in ctx.Studies
                              orderby s.StudyDateTime descending
                              where s.StudyGuidKey == StudyGuidKey
                              select s;

                if (studies.Count() == 0) return "";

                /*
                 * The following piece of code uses Reflection to get values "dynamically" 
                 * out of a LINQ data row object. In this case a Study record from a study table.
                 * LINQ values are implemented as {get; set;} methods, so we will need to 
                 * get them out as methods. This means that the field name needs to be 
                 * appended with a 'get_' to derive the proper method name that corresponds
                 * with the SQL field name.
                 * 
                 */

                Field = "get_" + Field.Trim().ToLower();

                try
                {
                    Study study = studies.First();
                    Type studyType = typeof(Study);
                    MethodInfo[] studyFields = studyType.GetMethods(BindingFlags.Public
                        | BindingFlags.Instance);

                    for (int i = 0; i < studyFields.Length; i++)
                    {
                        var fn = studyFields[i].Name.ToLower();
                        if (fn != Field) continue;
                        var v = studyFields[i].Invoke(study, null).ToString();
                        return "";
                    }
                }
                catch (Exception ex)
                {
                    return "";
                }
            }
            
            return "";
        }