Hi, people! I would appreciate if you look at my implementation of calculated fields idea and give me some advice. It is based on Otis' idea, which he showed in his blog, but I made some improvements.

Synopsis. This is a quite reasonable idea to have a calculated field in an entity. For example, I have an entity User with FirstName, SecondName and LastName fields (and some other, which are not interested us in this discussion). I want to combine *Name fields into FullName to obtain a field and have a possibility to see this new field in grids, sort on it, filter by it (using SQL, not client operations!) and so on. Our DBMS (Oracle 10g) does not allow to create calculated columns on tables and I refuse creating view on each table where I want to have calculated column nor storing this column physically caclucating it in trigger.

Solution. I know there is a possibility to use a TypedList for such purposes, but, as for me (and our team) it is more convenient to have caclulated properties in Entity class, because they are needed almost everywhere where entity is used. The decision I made is based on information, which I found in this forum and, especially, Frans' blog: 1) I created a new field definition in EntityFields class:

    public partial class UserFields
    {
        public static EntityField2 FullName
        {
            get
            {
                return new EntityField2("FullName",
                    new DbFunctionCall("{0} || ' ' || {1} || ' ' || {2}",
                        new object[] { UserFields.SurName, UserFields.FirstName, UserFields.SecondName }));
            }
        }
   }

2) I overrided method CreateFields in corresponding Factory's partial class:

public partial class UserEntityFactory
    {
        public override IEntityFields2 CreateFields()
        {
            IEntityFields2 toReturn = base.CreateFields();
            toReturn.Expand(1);
            toReturn.DefineField(UserFields.FullName, (int)UserFieldIndex.AmountOfFields + 0);
            return toReturn;
        }
    }

3) I defined a read-only property in entity partial class:

public partial class UserEntity
    {
        public string FullName
        {
            get { return (string)GetValue((int)UserFieldIndex.AmountOfFields + 0, true); }
        }
    }

The "(int)UserFieldIndex.AmountOfFields + 0" means, that there are another calculated fields, which relative indexes would be "+ 1", "+ 2" and so on. So, it all works as I expect: I can add new column to Users' grid and make sorting and filtering on it, and this operations are reflected in generated SQL statements. Perfectly. But this is only small part of issues which stand in real system. Much more widespread task the next one. Most of business entities have two fields: user, who created a record (CreateUser) and user, who modified a record (ModifyUser). Obviously, there are relations between UserEntity and business entities. And there is a requirement to see FullName of user, who created and user, who modified the record in business entities' registries. Sorting and filtering - a "must have", of course . Using this technology, I can still prefetch users by corresponding prefetch paths and access their FullName in data binding expressions. But what happened if, for example, sort on SomeBusinessEntity.CreateUser.FullName column and simultaneously filter by SomeBusinessEntity.ModifyUser.FullName column? Our framework will produce a RelationPredicateBucket with aliased relations SomeBusinessEntity.Relations.UserEntityUsingCreateUserId and SomeBusinessEntity.Relations.UserEntityUsingModifyUserId (alias is needed because we need to distinguish two usings of the same entity User). Then, PredicateExpression and SortExpression are built (aliases to EntityField2 objects are set by SetObjectAlias) and all of this is passed to Adapter.FetchEntityCollection. This works fine, if FullName is regular field. "Fine" - I mean, in the resulting SQL proper aliases are applied to each using of the field. But in case of calculated field, even if I apply aliases by SetObjectAlias, they are not affected the generated SQL at all (it just contains the name of user's table, regardless of ObjectAlias). From one point, I understand this behaviour, because SetObjectAlias just sets corresponding property of EntityField2 object and, of course, would not affect an Expression, which is already build when field object is created. My idea is to recalculate the Expression in EntityField2 object when it's ObjectAlias is changed. I wrote descendant class:

public delegate IExpression ExpressionBuilder(string objectAlias);

    public class CalculatedEntityField2 : EntityField2, IEntityField2
    {
        private ExpressionBuilder builder;

        public CalculatedEntityField2(string name, ExpressionBuilder builder)
            : base(name, builder(string.Empty))
        {
            this.builder = builder;
            SetExpression(builder(string.Empty));
        }

        public new EntityField2 SetObjectAlias(string objectAlias)
        {
            ObjectAlias = objectAlias;
            SetExpression(builder(objectAlias));
            return this;
        }

        #region IEntityField2 Members

        EntityField2 IEntityField2.SetObjectAlias(string objectAlias)
        {
            return this.SetObjectAlias(objectAlias);
        }

        #endregion
    }

The EntityField2 properties in UserFields class are such:

public static CalculatedEntityField2 FullName
        {
            get
            {
                return new CalculatedEntityField2("FullName",
                    delegate(string objectAlias)
                    {
                        return new DbFunctionCall("{0} || ' ' || {1} || ' ' || {2}",
                            new object[] { 
                                UserFields.SurName.SetObjectAlias(objectAlias), 
                                UserFields.FirstName.SetObjectAlias(objectAlias), 
                                UserFields.SecondName.SetObjectAlias(objectAlias) });
                    });
            }
        }

Now, wherever I use SetObjectAlias of this field, the contained expression is recalculated. It allowed the uniform using of regular and calculated fields in standard LLBLGen procedures (filtering, sorting and prefetching). Now questions :-)

• Are there any important things which I did not take into account in this mechanism?
• Have new LLBLGen v 2.6 any possibilities to allow me to reimplement this mechanism more natively, closer to original LLBLGen's architecture? (I've noticed a new property ActAsDerivedTableField in IEntityFieldCore, for example)
• When I tried to use an entity with calculated fields in LINQ query, the problem with aliases have risen again - they are not applied to field expression . I also override property IEntityFieldCore.ObjectAlias in the same way as IEntityField2.SetObjectAlias to recalculate an expression, but it didn't help . This is the most disappointing thing, because one of the main reasons to move to LLBLGen 2.6 was to dive into LINQ. Your help will be appreciated!

Would be nice to have full support for this - add fields in the designer, etc. That would be great. I have always wanted this and tried to implement - ran into issues. I wanted to return the result of function calls (sql server) in the entity directly. So, something like:

select blah, blah2, dbo.somefunction(somefield) as blah3

would be the resulting query. As I have it now, I call the function when the field is referenced instead of in the original select - not as optimal, but it works.

Wonder if I could implement that using what you have below...