Hi,

I have searched through the forums and found how to do querys that concatenate 2 db fields into 1 result field using expressions. However i am a little stumped on how to create that single field.

One example in particular someone needed help for was:

SELECT FirstName + ' ' + LastName AS Name FROM Employee

and a reply from Otis was:

    
// first grab the fields from the base class' version
    IEntityFields fields = base.BuildResultset();

    // now we're going to add an expression to the FullName column. this expression will
    // concat the names.
    Expression surNameEx = new Expression(" ", ExOp.Add, fields["Surname"]);
    Expression fullNameEx = new Expression(
        fields["Firstname"], ExOp.Add, surNameEx);
    fields["Fullname"].ExpressionToUse = fullNameEx;

    return fields;

So basically my question is, If I am creating a dynamic list, how do I initially define the field "Fullname" ?

If someone could paste a full working example, possibly based on this:

I have a Vehicle table with Manufacturer and Model. The manufacturer is a seperate lookup table. I need to use Vehicle as a lookup in this situation. So for the vehicle combo I would like to present Manufacturer.Name + " " + Vehicle.Model AS VehicleFullname. So if I could change this code into what i want, that would be excellent

        public static DataTable GetVehicles(int entrantId, int seasonId)
        {
            // Fields
            ResultsetFields fields = new ResultsetFields(3);
            fields.DefineField(VehiclesFieldIndex.VehicleId, 0, "VehicleId");
            fields.DefineField(ManufacturersFieldIndex.Name, 1, "Name");
            fields.DefineField(VehiclesFieldIndex.Model, 2, "Model");

            // Relations
            RelationCollection rels = new RelationCollection();
            rels.Add(VehiclesEntity.Relations.ManufacturersEntityUsingManufacturerId);

            // Sorting
            ISortExpression sorter = new SortExpression();
            sorter.Add(SortClauseFactory.Create(ManufacturersFieldIndex.Name, SortOperator.Ascending));
            sorter.Add(SortClauseFactory.Create(VehiclesFieldIndex.Model, SortOperator.Ascending));

            // filtering
            IPredicateExpression filtExp = new PredicateExpression();
            if(entrantId != -1)
                filtExp.AddWithAnd(PredicateFactory.CompareValue(VehiclesFieldIndex.EntrantId, ComparisonOperator.Equal, entrantId));
            if(seasonId != -1)
                filtExp.AddWithAnd(PredicateFactory.CompareValue(VehiclesFieldIndex.SeasonId, ComparisonOperator.Equal, seasonId));

            TypedListDAO dao = new TypedListDAO();
            DataTable Dt = new DataTable();

            dao.GetMultiAsDataTable(fields, Dt, 0, sorter, filtExp, rels, true, null, null, 0, 0);

            return Dt;
        }

Thanks,

Matt

After some experimentation i figured it out. I dont know if there is a better way to do it, but i simply added a new field using an existing field index and then applied the expression:

            // Fields
            ResultsetFields fields = new ResultsetFields(4);
            fields.DefineField(VehiclesFieldIndex.VehicleId, 0, "VehicleId");
            fields.DefineField(ManufacturersFieldIndex.Name, 1, "Name");
            fields.DefineField(VehiclesFieldIndex.Model, 2, "Model");
            fields.DefineField(VehiclesFieldIndex.Model, 3, "VehicleFullname");

            Expression modelExp = new Expression(" ", ExOp.Add, fields[2]);
            Expression fullExp = new Expression(fields[1], ExOp.Add, modelExp);
            fields[3].ExpressionToApply = fullExp;

and in a little test console app I printed the results and field 3 reported exactly what i had intended.

Very cool stuff indeed!

My only concern is I am getting back more info than I need. I really dont want to return the concatenate and the individual fields. Any way around this?

Also If i could still get some feedback though on wether or not this is the right way to go.

Thanks,

Matt

And after yet some more experimentation i have this:

            // Fields
            ResultsetFields fields = new ResultsetFields(2);
            fields.DefineField(VehiclesFieldIndex.VehicleId, 0, "VehicleId");
            fields.DefineField(VehiclesFieldIndex.Model, 1, "VehicleFullname");

            Expression modelExp = new Expression(" ", ExOp.Add, EntityFieldFactory.Create(VehiclesFieldIndex.Model));
            Expression fullExp = new Expression(EntityFieldFactory.Create(ManufacturersFieldIndex.Name), ExOp.Add, modelExp);
            fields[1].ExpressionToApply = fullExp;

which alleviates the extra data prob.

Still would like to know if this is the correct way to concatenate.

Should i manually be creating an EntityField to match the result?

Thanks again,

Matt

Sorry for the flood of posts, howevere i think i figured out the correct solution. I can easily create an EntityField for the field that is the concatenate of teh two fields. This seems to work well.

Thanks again,

Matt

Hi Matt, I think you found the right solution. I admit it's a bit verbose, this will be addressed in the update currently in development, where operator overloading will make this much simpler

Hi, I'm trying to do the same thing but using Oracle but it doesn't work because oracle using || to concatenate strings in sql. So how can I do this...

select firstName || ' ' || lastName as FullName From employee

           Expression exp1 = new Expression(" ", ExOp.Or, fields[2]);
            Expression fullExp = new Expression(fields[3], ExOp.Or, exp1 );
            fields[6].ExpressionToApply = fullExp;

thanks

You can create your own IExpression implemenatation, by copying the Expression sourcecode from the runtime lib sourcecode into your own Expression class for string concatenation (like 'OracleConcatExpression') and you change the ToQueryText method in such a way that you emit '||' where the operator is required.

In your code you then create an instance of your own expression object when you want to concat strings

hum still doesn't work... I'm not sure I understand what to do here...

can you help?

I got this....

    // Selfservicing constructor.All other constructors are hidden, we only work on a single field. 
        public OracleConcatanateExpression(IEntityField field, IEntityField field2) : base(field)
    {
        _field2 = field2;
    }   
    
    public override string ToQueryText(ref int uniqueMarker, bool inHavingClause)
    {
        string query = string.Empty;
        int uniqueMarker2 = 0;

        if(_field2 != null)
        {
            Expression exp2 = new Expression(_field2);
            exp2.DatabaseSpecificCreator = new SD.LLBLGen.Pro.DQE.Oracle.OracleSpecificCreator();
            string s = exp2.ToQueryText(ref uniqueMarker2);
            query = string.Format("{0} || {1}", base.ToQueryText(ref uniqueMarker, inHavingClause), s);
        }
        else
        {
            query = base.ToQueryText(ref uniqueMarker, inHavingClause);
        }

        return query;
    }

and I'm using it like that...

...
          fields.DefineField(SecurityUsersFieldIndex.FirstName, 2, "FirstName", "U");
            fields.DefineField(SecurityUsersFieldIndex.LastName, 3, "LastName", "U");
...
BLL.Utilities.OracleConcatanateExpression ex = new AMS.BLL.Utilities.OracleConcatanateExpression(fields[2], fields[3]);
            fields[3].ExpressionToApply = ex;

It's better to start with the Expression sourcecode than to override the ToQueryText as you need the private routine which actually produces the field/operand text.

Sorry to ask but where is the expression source code?

Banane wrote:

Sorry to ask but where is the expression source code?

In the llblgen pro installation folder, or you can also obtain the latest source from the runtimelibraries section in the customer area

Otis wrote:

Hi Matt, I think you found the right solution. I admit it's a bit verbose, this will be addressed in the update currently in development, where operator overloading will make this much simpler

Hi, I don't want to seem like I'm hijacking an existing thread, but after searching for a clear answer to this in the forums, it seems that it might be well suited to have the answer here, so others searching for this might see the answer. What is the less verbose way of concatenating two fields in LLBL 2.5?

I'm building a simple TypedList with a select that's basically:

select text_field_one + ' ' + text_field_two as ConcatenatedField from ...

I understand the usage of the ResultFields and DefineField methods. I just need the simple IExpression that does the concatenation.

I'm looking in the "Generated code - field expressions and aggregates" section and thought I'd a simple concatenation example. This section of the documentation was great when I needed to define a field with a large CASE statement.

Thanks in advance!

Use a DbFunctionCall, with 2 parameters (the fields you want to connect to eachother) and the function snippet is: "{0} + {1}"

Otis wrote:

Use a DbFunctionCall, with 2 parameters (the fields you want to connect to eachother) and the function snippet is: "{0} + {1}"

Excellent, exactly what I was looking for, thanks! The exact syntax I used (to perhaps benefit others):

fields[1].ExpressionToApply = new DbFunctionCall("{0} + ' ' + {1}", new object[] { MyTableFields.Field1, MyTableFields.Field2 });

Very new to LLBLGen Pro. I have read and understand your post from Posted on: 09-Aug-2008 10:58:35. My code is below. I am not understanding why or how to bind my new Resultsfield to my listbox. Can you advise what I am missing or if I did not understand your post?

using (EnhancedPcnCaptureMgrCollection captmgrColl = new EnhancedPcnCaptureMgrCollection()) { //SortExpression sorterCapName = new SortExpression(); //sorterCapName.Add(EnhancedPcnCaptureMgrFields.Caplname | SortOperator.Ascending); //captmgrColl.GetMulti(null, 0, sorterCapName); lstApprover.DataSource = captmgrColl; //lstApprover.DataTextField = EnhancedPcnCaptureMgrFieldIndex.Capfname.ToString(); //lstApprover.DataTextField = EnhancedPcnCaptureMgrFieldIndex.Caplname.ToString(); //lstApprover.DataValueField = EnhancedPcnCaptureMgrFieldIndex.CaptureManagerId.ToString();

                    ResultsetFields fields = new ResultsetFields(2);
                    fields.DefineField(EnhancedPcnCaptureMgrFieldIndex.Capfname, 0, "Fname");
                    fields.DefineField(EnhancedPcnCaptureMgrFieldIndex.Caplname, 1, "Lname");
                    fields[1].ExpressionToApply = new DbFunctionCall("{0} + ' ' + {1}", new object[] { 
                    EnhancedPcnCaptureMgrFieldIndex.Capfname, EnhancedPcnCaptureMgrFieldIndex.Caplname });          
                    lstApprover.DataBind();
                }

Thanks

It's not clear to me what you are trying to do, bind to an entityCollection or a DynamicList (ResultSetFields).

Please start a new thread (don't re-open threads) and describe your case in more details.