# Looping through columns in Entity Framework

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)
.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>();

• Why do you need to loop through each column? Feb 12, 2013 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. Feb 12, 2013 at 19:27
• That makes sense. Feb 12, 2013 at 19:28
• 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 Mar 9, 2013 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. Mar 10, 2013 at 0:36

// 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)
.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);

• The parameter was there to support anonymous types through the following syntax: CreateAccessors(query.FirstOrDefault()). Mar 10, 2013 at 0:39

Unless I'm really missing that point of your exercise, have you considered something akin to the following?

List<string> retVal = new List<string>();
foreach (object prop_loopVariable in new EFModelObj().GetType().GetProperties()) {
prop = prop_loopVariable;

Where EFMOdelObj is a model from your EF models. This will return a list of all the column/property names and can easily be modified to get the value as well (say in a dictionary<string, dynamic> where string is the column name and dynamic is the value.
There are, of course, probably more efficient ways to do that. Such as not using object in the iterator, etc.