How to avoid a long case statement

Question

I have an entity (Entity Framework 6) which maps to a table which has 100 columns named col_1, col_2 etc. The table is an existing legacy table which is used to interface to another system, so it's very generic and will never change.

So the entity has 100 properties which map to those columns.

public class DataEntity{ 

public string Column1{get;set;}    

...
}

I then have data like this:

var columnNumber = 1;
var data = "The data";

How can I set col_1 to "The Data" without a long case statement?

var dataEntity = new DataEntity();

This is what I don't want to do:

switch(columnNumber)
{
    case 1:
    dataEntity.Column1 = data;
    break;
}

Note that it is not possible to change the structure of the entity.

How does the entity map to the table? Are you using an ORM (if so which one)? Also, 100 columns... really!? — Belogix, Feb 16 '15 at 14:38
you potentially could do it using reflection http://stackoverflow.com/questions/619767/set-object-property-using-reflection — Daniel A. White, Feb 16 '15 at 14:38
Can't you do something like `string colName = "col_" + columnNumber; row[colName] = data;`? — Matthew Watson, Feb 16 '15 at 14:38
@JonSkeet, I cannot change the structure of the table to use this. — Greg Gum, Feb 16 '15 at 14:45
Well why not? What other restrictions are there? It's unclear what you're trying to do, to be honest... Did you *actually* mean `var data = entity.Column1;`? If so, that would have been a much clearer example... And how are you trying to use these column numbers - in a query, or on an entity already fetched? (That will make a huge difference to the answer...) — Jon Skeet, Feb 16 '15 at 14:45
Hmm... I don't see any switch case code in here.... (P.S. i still don't have any cup of coffee today) — Adrian Salazar, Feb 16 '15 at 14:49
@JonSkeet, The existing table is a generic import table used by another system. I feed the other system through the import table. I updated the question to be more clear about exactly what I want to do. — Greg Gum, Feb 16 '15 at 14:52
Right, that clarification helps a lot. So you never need the column numbers in a query? If that's correct, then Ian's answer is probably all you need. — Jon Skeet, Feb 16 '15 at 14:54

score 2 · Accepted Answer · answered Feb 16 '15 at 14:41

2

If you absolutely cannot change the structure of the data and you do not want to use a switch statement -- I'd probably look into some implementation of a strategy pattern:

http://www.dofactory.com/net/strategy-design-pattern

If you do not want to go that route, you could use reflection to set the values of the entity by following a convention where the columns will always be named "col_1", etc., but this is extremely fragile.

var myEntity = new MyEntity();
var value = "The data";
var columnNumber = 1;

PropertyInfo propertyInfo = MyEntity.GetType().GetProperty(string.Format("Col_{0}", columnNumber));
propertyInfo.SetValue(myEntity, Convert.ChangeType(value, propertyInfo.PropertyType), null);

answered Feb 16 '15 at 14:41

Ian P

12,840
6
48
70

1

Note that in order to make *queries* work for this, the OP will need to build expression trees. – Jon Skeet Feb 16 '15 at 14:47
Oh, I didn't realize he was wanting to query, or do the inverse of what I typed up. Expression trees are fun, though! – Ian P Feb 16 '15 at 14:49
Well we don't know, unfortunately - the question isn't clear enough. We'll see whether we get any clarification... – Jon Skeet Feb 16 '15 at 14:50
@IanP No, I don't need to query, just set the data, then save it to the database. – Greg Gum Feb 16 '15 at 14:56
1

If that's the case, the *easiest* way (not best) is to use reflection as indicated above. – Ian P Feb 16 '15 at 15:00

score 1 · Answer 2 · edited Apr 13 '17 at 12:40

I would definitely use an ExpandoObject for pushing anything I want into a dummy object, then map it to a specific instance of your class...

The mapper code you can get it from this answer: https://codereview.stackexchange.com/questions/1002/mapping-expandoobject-to-another-object-type

Let's say you have a class representing your holy-mother-of-100columns-database-tables

class LegacyTable
{
    public string Column1 { get; set; }
    public string Column2 { get; set; }
    public string Column3 { get; set; }
    // More to come
}

Now you can just work easily on a dynamic object.

dynamic expando = new ExpandoObject();
expando.Column1 = "Data 1";
expando.Column2 = "Data 2";
// Etcetera

You could also cast the expando to a dictionary for fast access (specially if your properties follow a patter "Column" + number

var p = expando as IDictionary<String, object>;

p["Column" + 1] = "Data 1";
p["Column" + 2] = "Data 2";
p["Column" + 3] = "Data 3";

Now you can use the mapper to pas your props to an actual instance of the legacy class.

LegacyTable legacy = new LegacyTable();
Mapper<LegacyTable>.Map(expando, legacy);

It just works...

Magic

How to avoid a long case statement

2 Answers2