How to replace columns in a Queryable<T> in a generic fashion without executing the Queryable

Question

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.

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

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 :

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.

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;
}

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 ObjectWithLabels 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:

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

Answer 1

I'm not sure if I got your problem statement right, and I think i went totally wrong direction with my LINQ in comments.

It appears you are trying to join two tables from the DB (and apply LanguageId filter on one of them). I believe with EF.Core 3 (I assume you use latest) you don't need to have FK defined to join tables:

var Products = db.Products.Join(
                    db.Labels.Where(l => l.LanguageId == 1), product => product.Name,
                    label => label.Code,
                    (p, l) => new {p, l})
                .Join(db.Labels.Where(l => l.LanguageId == 1), p => p.p.Description, l => l.Code,
                    (pp, l) => new ProductViewModel { Name = pp.l.Value, ProductId = pp.p.ProductId, Description = l.Value});

yields following SQL:

SELECT [t].[Value] AS [Name], [p].[ProductId], [t0].[Value] AS [Description]
FROM [Products] AS [p]
INNER JOIN (
    SELECT [l].[LabelId], [l].[Code], [l].[LanguageId], [l].[Value]
    FROM [Labels] AS [l]
    WHERE [l].[LanguageId] = 1
) AS [t] ON [p].[Name] = [t].[Code]
INNER JOIN (
    SELECT [l0].[LabelId], [l0].[Code], [l0].[LanguageId], [l0].[Value]
    FROM [Labels] AS [l0]
    WHERE [l0].[LanguageId] = 1
) AS [t0] ON [p].[Description] = [t0].[Code]

As you can see, this way it might be easier to just create ProductViewModel right inside the select statement.

Answer 2

The solution was to retrieve the label repository and pass it as a parameter when calling the mapper and to build the label query directly in the automapper profile.

Usage :

var labels = scope.GetRepository<Label>().GetAll().Where(x => x.LanguageId == 1)
var result = scope.GetRepository<Product>().GetAll() //returns Queryable<Product>
                .ProjectTo<ProductViewModel>(_mapper.ConfigurationProvider, new {labels})
                .Orderby(x => x.Description)
                .Take(10);

Mapping profile :

IQueryable<Label> labels = null;
CreateMap<Product, ProductViewModel>()
    .ForMember(x => x.Name, m =>
    {
        m.MapFrom(x => (from label in labels
                where label.Code == x.Name
                select label.Value).First());
    })
    .ForMember(x => x.Description, m =>
    {
        m.MapFrom(x => (from label in labels
                where label.Code == x.Description
                select label.Value).First());
    });

The MapFrom can probably be put into a MapFromLabel method which would avoid repeating code.