Long title - short intro? Well - I recently came across my own stupidity and wanted to display that to the world. Basically, how to dynamically selecting a column with Entity Framework.
The problem
Imagine you have a database table with many columns. And the user has an option to select which of those columns he wants to "see" (like in a chart or so). How would you approach such a problem? I will show you my overcomplicated way and how I solved it with Entity Framework - even though I probably shouldn't have. But hey, this blog is also a source and log of my silly mistakes.
Oversimplified example
I will have a very oversimplified example here. Imagine the following domain object (as always the code at the end of the post):
public record BlogPost
{
public required int Id { get; set; }
public required string Title { get; set; }
public required string Content { get; set; }
public required string Author { get; set; }
public required DateTime Created { get; set; }
public required DateTime Updated { get; set; }
public required List<string> Tags { get; set; }
}
So we have a object with many properties. Of course there is the whole yadda yadda yadda of setting up Entity Framework. If you are interested in that, check the source at the end, I am not showcasing this.
Anyway, the user now wants to have only Title for example or Content among some shared properties (in my example only the Id). So something like:
Console.WriteLine("What column would you like to return?");
var column = Console.ReadLine();
var query = dbContext.BlogPosts.Select(s => new ReturnColumn
{
Id = s.Id,
Column = ??????
});
public class ReturnColumn
{
public int Id { get; set; }
public string Column { get; set; }
}
Now how would you get Column? Before you scroll down or continue reading, you can think a few seconds on how you would approach it.
In this oversimplified example, of course you can make a switch-case, that is deterministic at compile time. But you (in this case: I) are lazy and there are many shared columns so you would have big blocks of repetitive code, you don't want to have. So you want to solve that dynamically. (Well I wanted for sure).
Expressions to the win!?
My overcomplicated brain thought: "Hey, let's use expressions for that!". Basically expressions are a way to represent code as data. So you can create a lambda expression and then compile it to a delegate and execute it. So you can create a lambda expression that selects the column you want and then execute
That will leave you with this beauty:
var columnSelector = GenerateColumnSelector(column).Compile();
var query = dbContext.BlogPosts.Select(s => new ReturnColumn
{
Id = s.Id,
Column = columnSelector.Invoke(s)
});
Expression<Func<BlogPost, string>> GenerateColumnSelector(string? column)
{
return column switch
{
"Title" => s => s.Title,
"Content" => s => s.Content,
"Author" => s => s.Author,
_ => throw new ArgumentException("Invalid column name")
};
}
You have to compile the expression outside the query, as GenerateColumnSelector is a local function. But that is a detail. Now the hope would be that the SQL towards the database would look like this, if the user selects Title:
SELECT Id, Title FROM BlogPosts
But of course, that doesn't happen. If we call:
var queryString = query.ToQueryString();
Console.WriteLine(queryString);
SELECT "b"."Id", "b"."Author", "b"."Content", "b"."Created", "b"."Tags", "b"."Title", "b"."Updated"
FROM "BlogPosts" AS "b"
So we drag the whole object from the database and then select the column in memory. The issue is that the selector is done client-side after the data was received. As Entity Framework doesn't know what columsn we need, it has to drag the whole object from the database.
System.Linq.Dynamic.Core to the rescue
So I was a bit disappointed that my overcomplicated solution didn't work. To solve this problem, I found the library System.Linq.Dynamic.Core and made it worse! I mean better - well it depends!
The library allows you to write dynamic queries with strings. So you can write something like:
var queryWithDynamic = dbContext
.BlogPosts
.Select<ReturnColumn>($"new(Id, {column} AS Column)");
var queryStringWithDynamic = queryWithDynamic.ToQueryString();
Console.WriteLine(queryStringWithDynamic);
As you can see it is kind of a mix between SQL and LINQ.
And the SQL will look like (if the user selects Title):
SELECT "b"."Id", "b"."Title" AS "Column"
Under the hood it also uses (Lambda-)Expressions - so you will have no compile-time support. And there is also a performance cost involved in translating the string to an expression. But it worked flawlessly for me.
There are many other things the library offers, but this one really helped me out in this case.
Conclusion
I am not sure whether or not you learned something here. Well, maybe: I would have been better off from the start if I would have constructed the query myself with long switch-cases or build up the SQL myself. Using libraries and frameworks outside of their intended use can lead to problems. But it was also fun!
