As an example, I have a Product entity, a ProductViewModel and a Label entity. Two of my product properties correspond to a label code rather than an actual value. So for example a Product's Name is "code1234" which corresponds to a Label having "code1234" as a Code and "Milk" as a Value. The label is not joined as a foreign key. We are using AutoMapper for projections.

```cs
public class Product{
    public int ProductId {get; set;}
    public string Name {get; set;}
    public string Description {get; set;}
}

public class ProductViewModel{
    public int ProductId {get; set;}
    public string Name {get; set;}
    public string Description {get; set;}
}

public class Label{
    public int LabelId {get; set;}
    public string Code {get; set;}
    public string Value {get; set;}
    public int LanguageId {get; set}
}

```

I am looking for a way to replace 

```cs
var currentLanguageId = 1; //As an example
IQueryable<Product> queryFromDb;

var Products = queryFromDb
    .ProjectTo<ProductViewModel>().AsEnumerable();

foreach(var Product in Products) {
    Product.Name = db.Labels.Where(x => x.Code == Product.Name && x.LanguageId == currentLanguageId).Single().Value;
    Product.Description = db.Labels.Where(x => x.Code == Product.Description && x.LanguageId == currentLanguageId).Single().Value;
}

```

With code that would **keep the query deferred** since filtering and sorting is done on the Label Value corresponding to the Product's Name and Description, not on the Name and Description themselves which are codes with no meaning.

Then I would also need a way to make the whole thing generic, since many entities in our database have properties which are label codes.

What I have until now : 

```cs
var result = scope.GetRepository<Product>().GetAll() //returns Queryable<Product>
                .ProjectTo<ProductViewModel>(_mapper.ConfigurationProvider) //returns Queryable<ProductViewModel>
                .WithLabels(_mapper, scope, x => x.Name, x => x.Description) //returns Queryable with columns replaced with a query
                .ToDataResult(request); //sorts filters takes skips, etc.

```

```cs
public static IQueryable<T> WithLabels<T>(this IQueryable<T> instance,
    IMapper mapper,
    IBaseReadContextScope scope,
    params Expression<Func<T, string>>[] expressions) where T : class
{
    var currentLanguage = 1; //as an example
    var labels = scope.GetRepository<Label>().GetAll(x => x.Language == currentLanguageId);
    foreach (var expression in expressions)
    {
        var query = instance
                .GroupJoin(
                    labels,
                    expression,
                    label => label.Code,
                    (x, y) => new { Obj = x, Label = y })
                .SelectMany(
                    xy => xy.Label.DefaultIfEmpty(),
                    (x, y) => new { Obj = x.Obj, Label = y })
                .Select(s => new ObjectWithLabel<T>()
                {
                    Object = s.Obj,
                    Label = s.Label
                });
        instance = mapper.ProjectTo<T>(query, new { propertyName = ExpressionUtilities.PropertyName(expression) });
    }

    return instance;
}

```

```cs
CreateMap<ObjectWithLabel<ProductViewModel>, ProductViewModel>()
    .ForMember(x => x.Name, m =>
    {
        m.Condition(x => propertyName == nameof(ProductViewModel.Name));
        m.MapFrom(x => x.Label.Value);
    })
    .ForMember(x => x.Name, m =>
    {
        m.Condition(x => propertyName != nameof(ProductViewModel.Name));
        m.MapFrom(x => x.Object.Name);
    })
    .ForMember(x => x.Description, m =>
    {
        m.Condition(x => propertyName == nameof(ProductViewModel.Description));
        m.MapFrom(x => x.Label.Value);
    })
    .ForMember(x => x.Description, m =>
    {
        m.Condition(x => propertyName != nameof(ProductViewModel.Description));
        m.MapFrom(x => x.Object.Description);
    });

```

Which actually works for a single property, but not for multiple. On top of that, having to project back and forth from ProductViewModel and ObjectWithLabel is not great. The reason I am using .Condition instead of a simple ternary operator is that ProjectTo doesn't support expressions and I am having trouble getting UseAsDataSource() to work (which would translate that expression for us)

Ideas so far : 

1. Use Query Interceptors. With AutoMapper, we would build a string like INTERCEPTME\_ColumnName\_Code\_Language and replace that string with a query (either written in LINQ or in the form of an SQL function). This seems like it would work but has the downside of not working for unit tests with NOSQL(probably), needing to check every incoming query (unless there is a way to flag a query as "interceptable".
2. Use a method similar to my current WithLabels method, but build a single join with multiple columns inside of it, then project only once from ObjectWithLabel**s** to ProductViewModel. (No clue how a generic join on an unknown number of columns would look like)
3. Find a way to directly replace a column without having to use an intermediary object/projection, by building queries and sending them as a parameter to AutoMapper, quick illustration of the basic idea:

```cs
Dictionary<string, IQueryable<string>> dictionary = null;

CreateMap<Product, ProductViewModel>()
    .ForMember(x => x.Name, m => m.MapFrom(x => 
        dictionary["Name"]))
    .ForMember(x => x.Description, m => m.MapFrom(x => 
        dictionary["Description"])));

```

Where dictionary would be built with queries which return the Label.Value corresponding to the code and language desired.

I would appreciate any input on the base problem which is to replace a column of an object in a query, without executing that query, as well as on any of the potential solutions I have mentioned.

Thank you