Custom LINQ to SQL Query with dynamic result

madeinstein , February 29, 2012

[TestFixture]
public class TestDynamicLinq2Sql
{
    [Test]
    public void TestDynamicLinq()
    {
        var ctx = new MyDataContext("<connection string>");
        var result = ctx.ExecuteQuery("SELECT USER [User], SUSER_SNAME() [SysUser]");
        foreach (dynamic row in result)
        {
            Console.WriteLine("{0},{1}", row.User, row.SysUser);
        }
    }
    [Test]
    public void TestDynamicRow()
    {
        dynamic d = new DynamicRow();
        d.FirstName = "Albert";
        d["LastName"] = "Einstein";
        Console.WriteLine(d.FirstName);
        Console.WriteLine(d.LastName);
    }
}
public static class LinqExtensions
{
    public static IEnumerable<dynamic> ExecuteQuery(this DataContext ctx, string query)
    {            
        using (DbCommand cmd = ctx.Connection.CreateCommand())
        {
            cmd.CommandText = query;
            ctx.Connection.Open();
            using (DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                while (rdr.Read())
                {
                    dynamic row = new DynamicRow();                        
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {                      
                        row[rdr.GetName(i)] = rdr[i];
                    }
                    yield return row;
                }
            }
        }
    }
}
public class DynamicRow : DynamicObject
{
    private readonly Dictionary<string,object> _data = new Dictionary<string, object>();
    public object this[string propertyName]
    {
        get
        {
            object result = null;
            TryGetMember(propertyName, out result);
            return result;
        }
        set { TrySetMember(propertyName, value); }
    }
    public override bool TryGetMember(GetMemberBinder binder, out object result)
    {
        return TryGetMember(binder.Name, out result);
    }
    private bool TryGetMember(string propertyName, out object result)
    {
        return _data.TryGetValue(propertyName.ToLower(), out result); 
    }
    public override bool TrySetMember(SetMemberBinder binder, object value)
    {
        return TrySetMember(binder.Name, value);
    }
    private bool TrySetMember(string propertyName, object value)
    {
        _data[propertyName.ToLower()] = value;
        return true;
    }
}