Multiple group join lambda to expression tree

c# expression-trees lambda

Question

In our database we have a number of tables which have corresponding Translation tables, with language and region IDs (mapped to other tables) with language 1 being English and the default region of language 1 being UK. All tables which have a translation table have the following default columns (although no interface has been defined on the entity framework classes):

<EntityTableName>
EntityTableNameID INT PK
Reference NVARCHAR NULL
[Any other columns]

<EntityTableNameTranslation>
EntityTableNameID INT NOT NULL
LanguageID INT NOT NULL
RegionID INT NULL
Title NVARCHAR NOT NULL
Description NVARCHAR NULL

The naming is consistent throughout the database, so we could add interfaces if required, but for now I've been trying to do it without to save the effort.

The logic for determining which translation title & description to return is: 1) If there is an exact match for both the language and region, return it 2) If there is a match for the language, but not the region, return the "default" for that language (which is where the RegionID is null, and there will always be one for every language) 3) If there is no match for language, just return the system default (LanguageID = 1, RegionID IS NULL).

I know this might all sound weird and everyone has better ways of doing it, but this is the brief I have to work with. So this is the lambda group join function I created, which is using an entity in the database called "OrgGroup":

public static IEnumerable<TransViewModel> GetUserAreaOrgGroups(TransTestEntities context, int companyID, int languageID, int? regionID)
{
    var transFull = context.OrgGroupTranslations.Where(tr => tr.LanguageID == languageID && tr.RegionID == regionID);
    var transLang = context.OrgGroupTranslations.Where(tr => tr.LanguageID == languageID && !tr.RegionID.HasValue);
    var transDefault = context.OrgGroupTranslations.Where(tr => tr.LanguageID == 1 && !tr.RegionID.HasValue);

    var results = context.OrgGroups.Where(en => en.CompanyID == companyID)
            .GroupJoin(transFull, en => en.OrgGroupID, tr => tr.OrgGroupID,
                        (en, tr) => new TransJoin<OrgGroup, OrgGroupTranslation> { Entity = en, TransFull = tr.DefaultIfEmpty().FirstOrDefault(), TransLang = null, TransDefault = null})
            .GroupJoin(transLang, en => en.Entity.OrgGroupID, tr => tr.OrgGroupID,
                        (en, tr) => new TransJoin<OrgGroup, OrgGroupTranslation> { Entity = en.Entity, TransFull = en.TransFull, TransLang = tr.DefaultIfEmpty().FirstOrDefault(), TransDefault = null })
            .GroupJoin(transDefault, en => en.Entity.OrgGroupID, tr => tr.OrgGroupID,
                        (en, tr) => new TransJoin<OrgGroup, OrgGroupTranslation> { Entity = en.Entity, TransFull = en.TransFull, TransLang = en.TransLang, TransDefault = tr.DefaultIfEmpty().FirstOrDefault() })
            .Select(vm => new TransViewModel
                {
                    EntityID = vm.Entity.OrgGroupID,
                    Title = (vm.TransFull ?? vm.TransLang ?? vm.TransDefault).Title,
                    Description = (vm.TransFull ?? vm.TransLang ?? vm.TransDefault).Description
                });
    return results;
}

Which seems to work as expected, and now I'm trying to convert this into a function which will accept the two table types and use expression trees to create, execute, and return the equivalent query. I've got as far as:

public static IEnumerable<TransViewModel> GetUserAreaTranslations<TEntity, TTrans>(TransTestEntities context, int companyID, int languageID, int? regionID)
{
    // Get types
    Type entityType = typeof(TEntity);
    Type transType = typeof(TTrans);

    string entityName = entityType.Name;
    string transName = transType.Name;

    // Parameters
    var entityParam = Expression.Parameter(entityType, "en");
    var transParam = Expression.Parameter(transType, "tr");
    var combinedParam = new ParameterExpression[] { entityParam, transParam };

    // Properties
    var CompanyIDProp = Expression.Property(entityParam, "CompanyID");
    var entityIDProp = Expression.Property(entityParam, entityName + "ID");
    var transIDProp = Expression.Property(transParam, entityName + "ID");
    var transLanProp = Expression.Property(transParam, "LanguageID");
    var transRegProp = Expression.Property(transParam, "RegionID");
    var transTitleProp = Expression.Property(transParam, "Title");
    var transDescProp = Expression.Property(transParam, "Description");

    // Tables
    //TODO: Better way of finding pluralised table names
    var entityTable = Expression.PropertyOrField(Expression.Constant(context), entityName + "s");
    var transTable = Expression.PropertyOrField(Expression.Constant(context), transName + "s");

    // Build translation subqueries
    //e.g. context.OrgGroupTranslations.Where(tr => tr.LanguageID == languageID && tr.RegionID == regionID);

    MethodCallExpression fullTranWhereLambda = Expression.Call(typeof(Queryable),
                                    "Where",
                                    new Type[] { transType },
                                    new Expression[]
                                    {
                                        transTable,
                                        Expression.Quote
                                            (
                                                Expression.Lambda
                                                    (
                                                        Expression.AndAlso
                                                            (
                                                                Expression.Equal(transLanProp, Expression.Constant(languageID)),
                                                                Expression.Equal(transRegProp, Expression.Convert(Expression.Constant(languageID), transRegProp.Type))
                                                            ), transParam
                                                    )
                                            )
                                    });

    MethodCallExpression lanTranWhereLambda = Expression.Call(typeof(Queryable),
                                    "Where",
                                    new Type[] { transType },
                                    new Expression[]
                                    {
                                        transTable,
                                        Expression.Quote
                                            (
                                                Expression.Lambda
                                                    (
                                                        Expression.AndAlso
                                                            (
                                                                Expression.Equal(transLanProp, Expression.Constant(languageID)),
                                                                Expression.IsFalse(MemberExpression.Property(transRegProp, "HasValue"))
                                                            ), transParam
                                                    )
                                            )
                                    });

    MethodCallExpression defaultTranWhereLambda = Expression.Call(typeof(Queryable),
                                    "Where",
                                    new Type[] { transType },
                                    new Expression[]
                                    {
                                        transTable,
                                        Expression.Quote
                                            (
                                                Expression.Lambda
                                                    (
                                                        Expression.AndAlso
                                                            (
                                                                Expression.Equal(transLanProp, Expression.Constant(1)),
                                                                Expression.IsFalse(MemberExpression.Property(transRegProp, "HasValue"))
                                                            ), transParam
                                                    )
                                            )
                                    });

    MethodCallExpression entityWhereLambda = Expression.Call(typeof(Queryable),
                                                "Where",
                                                new Type[] { entityType },
                                                new Expression[]
                                                {
                                                    entityTable,
                                                    Expression.Quote(
                                                        Expression.Lambda
                                                        (
                                                            Expression.Equal(CompanyIDProp, Expression.Convert(Expression.Constant(companyID), CompanyIDProp.Type))
                                                            , entityParam
                                                        )
                                                    )
                                                });

    // Create the "left join" call:
    // tr.DefaultIfEmpty().FirstOrDefault()
    var joinType = typeof(TransJoin<TEntity, TTrans>);
    var joinParam = Expression.Parameter(joinType, "tr");
    var leftJoinMethods =
        Expression.Call(
            typeof(Enumerable),
            "FirstOrDefault",
            new Type[] { transType },
            Expression.Call(
                typeof(Enumerable),
                "DefaultIfEmpty",
                new Type[] { transType },
                Expression.Parameter(typeof(IEnumerable<TTrans>), "tr"))
        );

    // Create the return bindings
    var emptyTrans = Expression.Constant(null, typeof(TTrans));
    //var emptyTrans = Expression.Constant(null);
    var fullBindings = new List<MemberBinding>();
    fullBindings.Add(Expression.Bind(joinType.GetProperty("Entity"), entityParam));
    fullBindings.Add(Expression.Bind(joinType.GetProperty("TransFull"), leftJoinMethods));
    fullBindings.Add(Expression.Bind(joinType.GetProperty("TransLang"), emptyTrans));
    fullBindings.Add(Expression.Bind(joinType.GetProperty("TransDefault"), emptyTrans));
    // Create an object initialiser which also sets the properties
    Expression fullInitialiser = Expression.MemberInit(Expression.New(joinType), fullBindings);
    // Create the lambda expression, which represents the complete delegate
    Expression<Func<TEntity, TTrans, TransJoin<TEntity, TTrans>>> fullResultSelector =
        Expression.Lambda <Func<TEntity, TTrans, TransJoin<TEntity, TTrans>>>(fullInitialiser, combinedParam);

    // Create first group join
    var fullJoin = Expression.Call(
        typeof(Queryable),
        "GroupJoin",
        new Type[]
        {
            typeof (TEntity),       // TOuter,
            typeof (TTrans),        // TInner,
            typeof (int),           // TKey,
            typeof (TransJoin<TEntity, TTrans>) // TResult
        },
        new Expression[]
        {
            entityWhereLambda,
            fullTranWhereLambda,
            Expression.Lambda<Func<TEntity, int>>(entityIDProp, entityParam),
            Expression.Lambda<Func<TTrans, int>>(transIDProp, transParam),
            fullResultSelector
        }
    );

The problem is that groupjoin is expecting to return an IEnumerable of TTrans, which I don't seem to be able to bind, and I can't change it to a standard join because I won't be able to use the coalesce in the projection as no result will be returned.

I'm sure I'm doing something very dumb, so can someone help me get my group joins working please?

Popular Answer

Answer #2... this time with more actual answer :P

The problem appears to be that the types of the lambdas and so on that you are passing to the GroupJoin method are wrong.

Specifically:

// Create the lambda expression, which represents the complete delegate
Expression<Func<TEntity, TTrans, TransJoin<TEntity, TTrans>>> fullResultSelector =
    Expression.Lambda<Func<TEntity, TTrans, TransJoin<TEntity, TTrans>>>(fullInitialiser, combinedParam);

...although some of the others also look a bit iffy, but that could just be me.

The selector expression expected by GroupJoin is of type Expression<Func<TEntity, IEnumerable<TTrans>, TransJoin<TEntity, TTrans>>>. It's going to pass in a single TEntity and a group of TTrans (as IEnumerable<TTrans>) even if there's only one instance in that group. Your expression tree needs to deal with that IEnumerable<TTrans> correctly, which it currently doesn't.

Are you sure you wanted a GroupJoin and not a Join here?

I wrote some code in LINQPad to test the concept. It's over at >PasteBin< if you want to look into it.


Incidentally, using LINQPad's Dump extension on an expression will give you a full breakout of how the expression is constructed. Assign a lambda to a variable of an appropriate Expression<Func<....>> type and then call Dump to see how it is constructed. Helps ferret out the usages and show what you need to do to construct it.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why