Hi, i am trying to write an expression for a select list which is supposed to concatenate an entity field of type string with an entity field of type date.

i have tried several different thing but cant get it working. any ideas?

-shane

What had you tried so far? and How would you write it in sql?

daelmo wrote:

What had you tried so far? and How would you write it in sql?

i have tried converting it into string using .tostring and convert.tostring

i would use convert in sql as in CONVERT(VARCHAR(19),GETDATE())

thanks -shane

In your approx Sql you are using GetDate which is not a field, but I guess you want a find in there, right? Also, as you said you want it in the select and not in the where, I guess you are using DynamicList. So I think an approx code would be:

ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(EmployeeFields.FirstName, 0, "FirstName", "Employee");
fields.DefineField(EmployeeFields.LastName, 1, "LastName", "Employee");
fields.DefineField(EmployeeFields.LastName, 2, "Birthday", "Employee");

fields[2].ExpressionToApply = new DbFunctionCall(
    "CONVERT(VARCHAR(19), {0})", new object[]{EmployeeFields.Birthday});

DataTable dynamicList = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, dynamicList, 0, null, null, null, true, null, null, 0, 0);

you are right, getdate was just to give an idea and yes i am using dynamic list. here is what i have tried and below is the error i am getting. what am i doing wrong now?

var fields = new ResultsetFields(4);
            fields.DefineField(UserFields.UserId, 0);
            fields.DefineField(UserFields.FullName, 1);
            fields.DefineField(UserFields.LastLoginDate, 2);
            fields.DefineField(new EntityField("UserAndLastLoginDate", UserFields.FullName + " - Last login: " + 
                new DbFunctionCall("CONVERT(VARCHAR(19), {0})", new object[] { UserFields.LastLoginDate })), 3);

and i am getting this error:

_creator isn't set so the function call 'CONVERT(VARCHAR(19), {0})' can't be converted to text.

You should try to have the string concatenation inside the DBFunctionCall.

Walaa wrote:

You should try to have the string concatenation inside the DBFunctionCall.

like how?

You have some options.

As Walaa sugested, you can just include your concatenation in the DBFunctionCall constant:

...
fields.DefineField(UserFields.FullName, 3);
fields[3].ExpressionToApply = 
     new DbFunctionCall("{0} + {1} + CONVERT(VARCHAR(19), {3})", new object[] {  
          UserFields.FullName, 
          " - Last login",
          UserFields.LastLoginDate });

You also can use nested expressions:

fields[3].ExpressionToApply = 
new Expression(
     UserFields.FullName,
     ExOp.Add,
     new Expression(
          " - Last login - ",
          ExOp.Add, 
          new DbFunctionCall("{0} + CONVERT(VARCHAR(19), {1})", new object[] {  
               UserFields.LastLoginDate })));

Also, I see all your fields are from the same entity and you are not doing any aggregate, so I guess you are using DynamicList just to do this expression. If that is so, I think you also can use an entity fetch and write your own custom property in a partial class that return this custom expression. Example:

public partial class UserEntity
{
     public string UserAndLastLoginDate
     {
          return string.Format("{0} - {1}- {2}", this.FullName, " - Last login - ", this.LastLoginDate);
     }
}

Then when you fetch the collection, you can access your custom property:

adapter.FetchEntityCollection(users);
var someLastLoginCustom = users.First().UserAndLastLoginDate;