Entity Framework problem with reducing projection

Question

I've been working on improving performance for our .NET core API with EF 5.0.11 by reducing the projection of our queries, but I'm currently stuck with the following scenario:

I improved the projection of the queries like this:

var employeeEmailQuery = context.Employee
                .Where(e => e.Active == true)
                .Select(e => new EmployeeEmailView
                {
                    Name = e.FullName,
                    Email = e.Email
                });

This reduces the select query to just the two columns I need instead of a SELECT * on 80+ columns in the database.

In my database, I also have columns with translated descriptions. It looks like this:

What I would like to do is select the relevant translated description, based on the current culture, so I added the following code:

 var culture = CultureInfo.DefaultThreadCurrentUICulture;
 var employeeEmailQuery = context.Employee
            .Where(e => e.Active == true)
            .Select(e => new EmployeeEmailView
            {
                Name = e.FullName,
                Email = e.Email,
                this.SetDescription(e, culture);
            });

The SetDescription method checks the culture and picks the correct column to set a Description property in the EmployeeEmailView. However, by adding this code, the query is now once again doing a SELECT *, which I don't want.

Does anybody have an idea on how to dynamically include a select column using EF without rewriting everything into raw SQL?

Thanks in advance.

Answer 1

I think the only way is to use an Interceptor to modify the query, or dynamically generate the EF IQueryable with Expressions.