Hi, Frans. Is it possible to build a concatenated field using the dynamic lists functionality of the code? I'm trying to accomplish something similar to:

SELECT FirstName + ' ' + LastName AS Name
FROM Employee

I looked at applying an expression to the defined ResultSetField, but it's more structured for mathematic formula than for doing any sort of string manipulation. Any other ideas? Thanks.

Jeff...

Yes you can do that. ExOp.Add will result in a '+' which concats 2 strings in T-SQL

you need 2 nested expressions, something like: (code copied from a support email to another customer)

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

Otis wrote:

Yes you can do that. ExOp.Add will result in a '+' which concats 2 strings in T-SQL

you need 2 nested expressions, something like: (code copied from a support email to another customer)

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

That'll do. I didn't make the connection that you could nest expressions. Thanks.

Jeff...

HI...

I've implemeted this concatenation example by extending a base class but one of my fields is a DateTime. The runtime exception is a System.Data.SqlClient.SqlException that shows a "Syntax error converting datetime from character string" merror message.

Is there support that would allow me to cast the datetime to a string before ExOp.Add concatenates it? If not perhaps there is another way.

public class AssignmentPeriodTankAssignmentListTypedListCustom : AssignmentPeriodTankAssignmentListTypedList
    {
        public AssignmentPeriodTankAssignmentListTypedListCustom()
        {
        }

        public override SD.LLBLGen.Pro.ORMSupportClasses.IEntityFields BuildResultset()
        {
            ResultsetFields fields = base.BuildResultset() as ResultsetFields;
            
            fields.Expand(1);
            fields.DefineField(AssignmentPeriodTankFieldIndex.DueDate, 3, "AssignmentTitleAndDueDate", "", AggregateFunction.None);
            Expression expAssignmentTitle = new Expression(" ", ExOp.Add, fields["AssignmentTitle"]);
            Expression expAssignmentTitleAndDueDate = new Expression(fields["DueDate"], ExOp.Add, expAssignmentTitle);
            fields["AssignmentTitleAndDueDate"].ExpressionToApply = expAssignmentTitleAndDueDate;
            
            return fields;
        }
    }

Can you possibly paste the desired SQL as an example?

You can cast it in 1.0.2005.1 only in the db by implementing IExpression in your own class to make it emit CAST(... ) as a function. To give you an idea how to do that, please check this thread:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3829

In v2 (now in beta) this is supported and build in.