Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

Is there a cleaner/more efficient way to loop through the columns in EF implicitly than the way written below?

static void Main(string[] args) {
    using (var db = new someDbContext()) {
        var query = from p in db.someTable
                    select new {
                        column1 = p.column1
                        column2 = p.column2
                    };

        var columnAccessors = CreateAccessors(query.FirstOrDefault());

        foreach (var row in query) {
            foreach (var col in columnAccessors) {
                var val = col(row);
                //Do something with val here.
            }
        }
    }
}

static Func<T, object>[] CreateAccessors<T>(T source = default(T)) {
    var propertyAccessors = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public)
                .Where(p => p.CanRead)
                .Select((p, i) => new {
                    Index = i,
                    Property = p,
                    Accessor = CreatePropertyAccessor<T>(p)
                })
                .ToArray();

    return propertyAccessors.Select(p => p.Accessor).ToArray();
}

static Func<T, object> CreatePropertyAccessor<T>(PropertyInfo prop) {
    var param = Expression.Parameter(typeof(T), "input");
    var propertyAccess = Expression.Property(param, prop.GetGetMethod());
    var castAsObject = Expression.TypeAs(propertyAccess, typeof(object));
    var lambda = Expression.Lambda<Func<T, object>>(castAsObject, param);
    return lambda.Compile();
}

I've tried simply doing a foreach (var col in row) before but I know that it won't work because row doesn't contain a definition for GetEnumerator and I'm not sure how one would go about implementing a solution that would do so generically for something like this.

I was working on implementing a DataReader and came across this page: http://www.developerfusion.com/article/122498/using-sqlbulkcopy-for-high-performance-inserts/. The thought occurred to me that I could modify the CreatePropertyAccessors segments to loop through columns and so I came up with the above. Problem being I'm just not sure that it's a "good" solution.

Edit:

With one small change the following is possible as well:

var query = from p in db.someTable
            select new SomeModel {
                item1 = p.column1,
                item2 = p.column2
            };

var columnAccessors = CreateAccessors<SomeModel>();
share|improve this question
2  
Why do you need to loop through each column? –  Bobson Feb 12 '13 at 18:55
    
@Bobson Depends on the situation but for example: I could print out the value of each column to the console without having to do something like Console.WriteLine(row.column1 + ", " row.Column2); Which honestly isn't that bad with only two columns but it starts to get ridiculous once you pass 5 or so. –  Kittoes Feb 12 '13 at 19:27
    
That makes sense. –  Bobson Feb 12 '13 at 19:28
1  
Entity Framework is doing all its best for developer not to think about "columns" but rather entities. That's why you have properties rather than an array of columns. Each column has its own meaning, so if they are semantically equivalent then most likely you designed your DB incorrectly. Even if you do need to output all record values comma-separated, you would most likely want to apply certain formatting to each column –  almaz Mar 9 '13 at 9:36
    
@almaz If I was displaying the results to my users then absolutely. I'd probably start building the appropriate models in MVC and decorating them with whatever tags I needed and all that fancy stuff. However, when I'm developing stuff on the fly and just trying to randomly test things I often find myself wishing I had a quick way to display the raw information returned from queries. –  Kittoes Mar 10 '13 at 0:36
add comment

1 Answer

So I had a look and made a few modifications, see below with comments.

// Made the method generic, the constaint is required by DbSet
static void LoopThroughColumns<T>(Func<someDbContext, DbSet<T>> getTable)
    where T : class
{
    using (var db = new someDbContext())
    {
        // Selecting columns exlicitly was unnecessary
        var query = getTable(db);
        var columnAccessors = CreateAccessors<T>();

        foreach (var row in query)
        {
            foreach (var col in columnAccessors)
            {
                var val = col(row);
            }
        }
    }
}

// Parameter is unnecessary as you never used it
static Func<T, object>[] CreateAccessors<T>()
{
    // Index and Property values weren't being used
    // ToArray() was an unnecessary conversion
    var propertyAccessors = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public)
                .Where(p => p.CanRead)
                .Select((p, i) =>  CreatePropertyAccessor<T>(p));

    return propertyAccessors.Select(e => e).ToArray();
}

static Func<T, object> CreatePropertyAccessor<T>(PropertyInfo prop)
{
    var param = Expression.Parameter(typeof(T), "input");
    var propertyAccess = Expression.Property(param, prop.GetGetMethod());
    var castAsObject = Expression.TypeAs(propertyAccess, typeof(object));
    var lambda = Expression.Lambda<Func<T, object>>(castAsObject, param);
    return lambda.Compile();
}

Usage is a little different now, you would call it like this:

LoopThroughColumns(e => e.someTable);

To be extra-super cool (ie. generic) you could even pass in an Action<object> and have that define what is done with val, then derive different methods on that one. Below I'm creating a PrintColumnValues method which calls LoopThroughColumns with an specific Action<object> to perform.

static void LoopThroughColumns<T>(Func<someDbContext, DbSet<T>> getTable, Action<object> actionOnObject)
    where T : class
{
    using (var db = new someDbContext())
    {
        var query = getTable(db);
        var columnAccessors = CreateAccessors<T>();

        foreach (var row in query)
        {
            foreach (var col in columnAccessors)
            {
                actionOnObject(col(row));
            }
        }
    }
}

static void PrintColumnValues<T>(Func<someDbContext, DbSet<T>> getTable)
    where T : class
{
    LoopThroughColumns(getTable, 
        new Action<object>(e => 
        {
            Console.WriteLine(e);
        }));
}

Usage

PrintColumnValues(e => e.someTable);
share|improve this answer
    
The parameter was there to support anonymous types through the following syntax: CreateAccessors(query.FirstOrDefault()). –  Kittoes Mar 10 '13 at 0:39
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.