Geek Answers Handbook

Multiple groups attach lambda to expression tree

There are several tables in our database that have corresponding translation tables, with language and region identifiers (compared with other tables), with language 1 being English and the default area of ​​language 1 being Great Britain. All tables that have a translation table have the following columns by default (although the interface was not defined in entity 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

Naming is consistent across the entire database, so we could add interfaces if necessary, but at the moment I was trying to do this without trying to save.

The logic of determining the name and description of the translation for the return: 1) If there is an exact match for both the language and the region, return it 2) If it matches the language, but not the region, return the "default" for this language (where RegionID is NULL , and there will always be one for each language) 3) If there is no language match, just return the system default setting (LanguageID = 1, RegionID IS NULL).

I know this may seem strange, and everyone has better ways to do this, but this is a short one that I have to work with. So, this is the lambda group aggregation function I created that uses an object in a 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;
}

, , , , , . :

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

, groupjoin IEnumerable TTrans, , , , , , .

, - , - ?

+4
2

node, , MemberInitExpression, , new { } .

, -, :

public class KV
{
    public int Key;
    public string Value;
}

new :

Type tKV = typeof(KV);
MemberInfo miKey = tKV.GetMember("Key")[0];
MemberInfo miValue = tKV.GetMember("Value")[0];

Expression meminit = 
    Expression.MemberInit(
        Expression.New(tKV),
        Expression.Bind(miKey, Expression.Constant(1)),
        Expression.Bind(miValue, Expression.Constant("Some Value"))
    );

, -, :

public Expression<Func<int, string, KV>> InitKV()
{
    var pK = Expression.Parameter(typeof(int), "k");
    var pV = Expression.Parameter(typeof(string), "v");

    Type tKV = typeof(KV);
    MemberInfo miKey = tKV.GetMember("Key")[0];
    MemberInfo miValue = tKV.GetMember("Value")[0];

    Expression meminit = 
        Expression.MemberInit(
            Expression.New(tKV),
            Expression.Bind(miKey, pK),
            Expression.Bind(miValue, pV)
        );

    return (Expression<Func<int, string, KV>>)Expression.Lambda(meminit, pK, pV);       
}

Bind.

0

# 2... : P

, .., GroupJoin, .

:

// 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);

... , .

, GroupJoin, Expression<Func<TEntity, IEnumerable<TTrans>, TransJoin<TEntity, TTrans>>>. TEntity TTrans (as IEnumerable<TTrans>), . IEnumerable<TTrans> , .

, GroupJoin, Join?

LINQPad . > PasteBin <, .

, LINQPad Dump . Expression<Func<....>>, Dump, , . , , .

0
source

More articles:


All Articles