LINQ to SQL Reflection in Select

Question

I've got the following problem. I have table in a SQL database which looks like this:

So, I want to get the data from this table in a for loop.

I've created the class

public class Attribute{
    public int Id {get; set;}
    public string Description {get; set}
}

And now i want to get all the attributes from table into one list

List<Attribute> attributeList = new List<Attribute>();
for(int i=0; i<6;i++)
{
    attributeList.Add((from a in db.Attributes
                  select new Attribute{
                      Id = a.Id,
                      Description = a.attr0}).First(); //How to use here i instead a fixed column name?
}

Is it possible to use i and get all attributes with one loop?

I was trying to use reflection but the compiler gives me an error, the code for that was:

for(int i=0; i<6;i++)
{
    attributeList.Add((from a in db.Attributes
                  select new Attribute{
                      Id = a.Id,
                      Description = a.GetType().GetProperty("attr"+i).GetValue(a,null))}).First();

UPDATE: The error is:

LINQ to Entities does not recognize the method 'System.Object GetValue(System.Object, System.Object[])' method, and this method cannot be translated into a store expression.

The `GetProperty("Attr" + i)` should be `GetProperty("attr" + i)` (small letter `a`) because the column name is starting with a lowercase `a`. I don't know if that's the error but it looks suspicious. — Twometer, Oct 19 '17 at 07:13
I think application design should support it's logic. Your logic requires iteration of attributes - so save it in the database that iteration will be most easy and efficient. — Fabio, Oct 19 '17 at 07:14
@Twometer nice catch. However the OP is talking about a "Compiler" error, whereas the one you mentioned could be a runtime error. — Pac0, Oct 19 '17 at 07:16
The problem is probably because it is part of your linq statement, and it cannot be translated into a db query. Get all the attributes first, and then do reflection on the resultset — Icepickle, Oct 19 '17 at 07:26
You know that this is going to execute a new query every loop right? Does your table only has 1 row??? Its somewhat weird what you want to do. You want to create 6 Attribute objects from 1 row? — Anestis Kivranoglou, Oct 19 '17 at 07:27

score 2 · Accepted Answer · edited Oct 19 '17 at 08:38

2

You are trying to use reflection on the "Database" level and LinqToEntities cannot translate that to sql.

If it could that would result in running your query 6 times for the same row.

Try Instantiating your db row class first and then using reflection on that object

List<Attribute> attributeList = new List<Attribute>();
DbAttribute attrFirst = db.attributes.First();
for (int i = 0; i < 6; i++)
{
    attributeList.Add(new Attribute {
        Id = attrFirst.Id,
        Description =(string)attrFirst.GetType().GetProperty("attr" + i).GetValue(attrFirst)
    });
}

edited Oct 19 '17 at 08:38

Salah Akbari

39,330
10
79
109

answered Oct 19 '17 at 07:41

Anestis Kivranoglou

7,728
5
44
47

Excellent! That works for me as I wanted! Thank YOU! – SBarkovskis Oct 19 '17 at 10:39

Salah Akbari · Answer 2 · 2017-10-19T11:36:15.363

The GetValue couldn't be translated to T-SQL, Linq to Entities couldn't recognize it. You can check this answer for more details https://stackoverflow.com/a/41802414/2946329.

Try this solution instead. This will give you what you're looking for:

var first = (from a in db.Attributes select a).FirstOrDefault();
List<Attribute> t = first?.GetType().GetProperties()
  .Select(c => new Attribute { Id = first.Id ,  Description = c.GetValue(first).ToString()})
  .ToList();

With this approach any further operation is performed using Linq to Objects, on the data already in memory and you don't get this error anymore. Also by using LINQ you don't need your for loop anymore.

LINQ to SQL Reflection in Select

2 Answers2