Select columns from table based on column names from another table

Question

At the beginning I am aware that there are similar questions, but mine is a little bit different.

I implemented a function that allows the user to select the columns he wants to see.

I've created a stored procedure that gets all column names from the UserColumns table, creates a dynamic sql query and then runs the exec (@command) query. The functionality described above works very well, but there are more requirements that I can't handle this way.

There is TasksViewModel:

public class TasksViewModel
{
    public List<Dictionary<List<string>, List<List<object>>>> Tasks { get; set; }

    public List<UserDefaultStatusesViewModel> UserStatuses { get; set; }

    public List<ZgloszenieStatus> TaskStatuses { get; set; }

    public TasksViewModel()
    {

    }
}

Tasks is filled by stored procedure that runs SELECT x,y,z... FROM table... query.

I'm using this method:

private static IEnumerable<Dictionary<List<string>, List<List<object>>>> Read(DbDataReader reader)
    {
        var dict = new Dictionary<List<string>, List<List<object>>>();

        var cols = new List<string>();

        for (int temp = 0; temp < reader.FieldCount; temp++)
        {
            cols.Add(reader.GetName(temp));
        }

        var items = new List<List<object>>();

        while (reader.Read())
        {
            var tmp = new List<object>();

            for (int i = 0; i < reader.FieldCount; i++)
            {
                tmp.Add(reader.GetValue(i));
            }

            items.Add(tmp);

        }

        dict.Add(cols, items);

        foreach (var item in dict)
        {

        }

        yield return dict;
    }

I find this very overcomplicated, but at the moment I have no idea if there is another way to do this.

I'm using Entity Framework in my application.

Imagine that I'm using List<Tasks> instead of List<Dictionary<List<string>, List<List<object>>>>. Tasks is database table.

public class Tasks
{
    public int ID { get; set; }

    public string Title { get; set; }

    public string Description { get; set; }

    public DateTime Date { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }
}

User wants to see only ID,Title,Description columns, so the UserColumns table looks like this:

UserId | ColumnName
  1    |      ID
  2    |    Title
  3    |    Description

Is there a way to select specific columns from List<Tasks> based on UserColumns table using Entity Framework ?

For EF to handle your custom selection list you need to build `Select(x=> new Task{Id=x.Id, Title=x.Title, Description =x.Description })` expression and apply it on your query you will get `List` as result with default values for all other columns — Rafal, Jul 04 '19 at 09:48
Possible duplicate of [Creating a LINQ Select expression dynamically from string column names](https://stackoverflow.com/questions/41676566/creating-a-linq-select-expression-dynamically-from-string-column-names) — Rafal, Jul 04 '19 at 09:50
@Rafal It's still manual way. There are many users, not just one. Every user can have different settings. — fanarek, Jul 04 '19 at 09:53
Yes and you create it once per user and cache it, invalidate cache if settings change. Other way would be to select all columns and just not display data, if there are actually 6 of them that not that big of a deal it can get costly if you have like 50 to choose from. There is a border count of columns when implementing this will provide benefit but I think its bigger than lower. — Rafal, Jul 04 '19 at 09:59

score 0 · Answer 1 · answered Jul 04 '19 at 10:34

You can create the lambda for Column List dynamically

static Func<Tasks, Tasks> CreateSelect(string[] columns)
{
    var parameterExpression = Expression.Parameter(typeof(Tasks), "p");
    var newExpression = Expression.New(typeof(Tasks));

    var bindings = columns.Select(o => o.Trim())
        .Select(o =>
        {
            var pi = typeof(Tasks).GetProperty(o);
            var memberExpression = Expression.Property(parameterExpression, pi);
            return Expression.Bind(pi, memberExpression);
        }
    );
    var memberInitExpression = Expression.MemberInit(newExpression, bindings);
    var lambda = Expression.Lambda<Func<Tasks, Tasks>>(memberInitExpression, parameterExpression);
    return lambda.Compile();
}

and create a LINQ query based on that lambda (columnNameList array is rows from UserColumns table)

static void Foo()
{
    var columnNameList = new string[] { "ID", "Title", "Description" };
    var tasksList = new List<Tasks>
    {
        new Tasks{ ID=1, Title="T1", FirstName="F1", LastName="L1", Description="D1", Date=DateTime.UtcNow },
        new Tasks{ ID=2, Title="T2", FirstName="F2", LastName="L2", Description="D2", Date=DateTime.UtcNow }
    };

    var tasks = tasksList.Select(CreateSelect(columnNameList)).FirstOrDefault();
}

I hope that answers your question.

Select columns from table based on column names from another table

1 Answers1