Indirection with linq : Change query expression "Select" according users parameters

Question

I use a table "TLanguage" to record lable results of my site. I have 4 columns in this table: French, English, German and Spanish.

In a MVC application I use this query:

 var req = (from TYP in context.TYP_TypeMission
                   join ML in context.TLanguage on TYP.IDTMultiLanguage equals ML.IDTMultiLanguage
                  where TYP.IDTFiliale == idFiliale
                  orderby TYP.LibTypeMission
                  select new SelectListItem
                  {
                      Selected = TYP.IdTypeMission == idTypeMission,
                      Value = SqlFunctions.StringConvert((double)TYP.IdTypeMission),
                      Text = ML.French
                  }).ToList();

How can I change ML.French by ML.English or ML.German in my query according the language of my site?

Is it possible to create an indirection in the query?

Gert Arnold · Answer 1 · 2013-04-30T12:05:14.913

It is possible to parametrize the mapping like this:

public class TLanguageMap : EntityTypeConfiguration<TLanguage>
{
    public TLanguageMap(string language)
    {
        this.HasKey(t => t.TLanguageId);
        this.Property(t => t.Translation).HasColumnName(language);
    }
}

In the context:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Configurations.Add(new TLanguageMap(this._language));
}

and its constructor:

public LocalizableContext(string language)
{
    this._language = language;
}

Now when constructing a context you can determine for which language it is:

var context = new LocalizableContext("French");

And the query will always be:

...
select new SelectListItem
{
    Selected = TYP.IdTypeMission == idTypeMission,
    Value = SqlFunctions.StringConvert((double)TYP.IdTypeMission),
    Text = ML.Translation
})

You may want to make it more robust by using an enum for the languages and a switch statement to get the database column names.

It's an interesting solution. Unfortunately, I use 'ObjectContext' and not 'DBContext' to create my context and I can't use 'OnModelCreating'. — Fabrice Mainguené, Apr 30 '13 at 10:10

score 0 · Answer 2 · answered Apr 29 '13 at 13:03

You can save the beginning of your query into variable and change only the last part later:

var query = from TYP in context.TYP_TypeMission
            join ML in context.TLanguage on TYP.IDTMultiLanguage equals ML.IDTMultiLanguage
            where TYP.IDTFiliale == idFiliale
            orderby TYP.LibTypeMission
            select new { ML, TYP };

List<SelectListItem> req;

if(Site.Lang == "DE")
{
    req = (from item in query
           select new SelectListItem
           {
               Selected = item.TYP.IdTypeMission == idTypeMission,
               Value = SqlFunctions.StringConvert((double)item.TYP.IdTypeMission),
               Text = item.ML.German 
           }).ToList();
}
else if(Site.Lang == "FR")
{
    req = (from item in query
           select new SelectListItem
           {
               Selected = item.TYP.IdTypeMission == idTypeMission,
               Value = SqlFunctions.StringConvert((double)item.TYP.IdTypeMission),
               Text = item.ML.French
           }).ToList();
}
else
{
    req = (from item in query
           select new SelectListItem
           {
               Selected = item.TYP.IdTypeMission == idTypeMission,
               Value = SqlFunctions.StringConvert((double)item.TYP.IdTypeMission),
               Text = item.ML.English
           }).ToList();
}

Query won't be executed until ToList() is called.

Thank you but I seek rather to create an indirection in the query. — Fabrice Mainguené, Apr 29 '13 at 14:55

Indirection with linq : Change query expression "Select" according users parameters

2 Answers2