Replacing conditional operator in the linq select with dynamic property-CodePudding

This linq query with conditional operator:

public List<ClientDto> GetClients(string locale)
{
    var q = from c in _dbContext.Clients
    select new ClientDto
    {
        Id = c.Id,
        Name = locale == "en" ? v.Name_en : v.Name_de
    };
    return q.ToList()
}

will generate the correct SQL query, selecting just the fields I need, without extra:

SELECT c."Id", c."Name_en" AS "Name" FROM "Clients" AS c

How can I do the same and support multiple languages? A conditional operator seems to be out of the question. Something like:

select new ClientDto
{
    Id = c.Id,
    Name = getNameProp(language)
};

But without the side-effect of getting all fields from the database. As soon as I send c as an argument to another function, (getNameProp(language, c)), the generated query returns all fields from the database.

I can of course restructure the database and keep translatable strings in a separate table, but the point of the question is the linq part, the solution could be useful for other purposes.

CodePudding user response：

In Linq-To-Objects you could parametrize it with a different getterFunc for each language, so your query always is aware of just one field name, like

var nameGetter = NameGetter("en");
var clientList = GetClients(nameGetter);

public List<ClientDto> GetClients(Func<Client, string> nameGetter) {
    var q = from c in _dbContext.Clients
    select new ClientDto {
       Id = c.Id,
       Name = nameGetter(c)
    };
    return q.ToList()
}

public Func<Client, string> NameGetter(string language) {
     Func<Client, string> getter = language switch {
                   "en" => c => c.Name_en,
                   "de" => c => c.Name_de,
                   "fr" => c => c.Name_fr
                    ...
                   _ => default,
     };
    return getter;
}

But I am not sure if and how your Linq-To-myOrm-Provider implementation translates that into SQL...

CodePudding user response：

I would suggest to use LINQKit. It needs just configuring DbContextOptions:

builder
    .UseSqlServer(connectionString)
    .WithExpressionExpanding(); // enabling LINQKit extension

Define helper class:

public static class LanguageExtensions
{
    public static Expression<Func<T, string>> NameGetter<T>(string language) 
    {
        var param = Expression.Parameter(typeof(T), "e");

        // simple realization
        var propName = "Name_"   language;

        var body = Expression.PropertyOrField(param, propName);

        return Expression.Lambda<Func<T, string>>(body, param);
    }
}

Then you can use helper in the following way:

public List<ClientDto> GetClients(string locale)
{
    var q = from c in _dbContext.Clients
    select new ClientDto
    {
        Id = c.Id,
        Name = LanguageExtensions.NameGetter<Client>(language).Invoke(e)
    };
    return q.ToList()
}