Dynamic projection in linq

user_194076 0
Viewed 496

I have the following dictionary of strings: 

FirstName | Col1
LastName  | Col2
Email     | Col3

My database table looks like this (Crazy, I know): 

Id   Col1   Col2    Col3
1    John   Doe     test@email.com

Now, I need to select a list of values in my c# code with aliases. It would be easy to do this with projection, like so (This would solve this task for me only if I could hardcode Property names and column names): 

ctx.myTable.Select(x=> new { FirstName = x.Col1, LastName = x.Col2, Email = x.Col3});

But unfortunatelly I only have column names and alises stored as strings in a dictionary. Looks like this is the job for expression trees or dynamic sql but I couldn't figure out how to achieve this. I tried to do it with Expando object but in return I'm getting a dictionary, while I need to have a list with columns FirstName, LastName, Email.

entity-framework sql-server linq c#
1 Answers

You can dynamically construct SQL query as simple string:

var dict = new Dictionary<string, string>
{
    ["FirstName"] = "Col1",
    ["LastName"] = "Col2",
    ["Email"] = "Col3"
};

var query = $"select {String.Join(", ", dict.Select(x => $"{x.Value} as {x.Key}"))} from myTable";

var items = ctx.Database.SqlQuery<object>(query).ToList();
user_56307780
Related