Hi, Suppose I have customer table which has got two fields such as name and surname. How can I handle the below normal SQL query with LLBLGEN V2.0 Self Servicing mode.

SELECT * FROM TBLCUSTOMERS WHERE NAME+SURNAME LIKE '%sample customer%'

In the customer table there are two fields for a customer name, and surname respectively, But for example a simple search form, I want to use a simple text txtCustomer not two separate text boxes (txtName, txtSurname).

Best Regards Koksal

I don't see a simple way to do that.

I think you could create a view witch merge NAME and SURNAME in a single field and regenerate LLBL on that view.

Hi Koksal, I would use a FieldLikePredicate (Ref: Using generated code - SelfServicing - Filtering and Sorting - The predicate system - FieldLikePredicate) mixed with Expressions (Ref: Using generated code - Field expressions and aggregates).

Here is a example using _Northwind _database and _Customers _table:

// collection to retrieve
CustomersCollection customers = new CustomersCollection();

// set the concatenated expression. Note that doesn't matter the field you use because the IEntityField result will contain the expression assigned.
IEntityField contatenatedFields = CustomersFields.CustomerId.SetExpression(CustomersFields.CompanyName + ' ' + CustomersFields.ContactName);

// set the like filter
FieldLikePredicate likePredicate = new FieldLikePredicate(contatenatedFields, "%Alfreds Futterkiste Maria Anders%");

// add to the whole filter
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(likePredicate);

// retrieve data
customers.GetMulti(filter.PredicateExpression);

You must adapt that to your entity structure. Hope helpful.

Hi David, Thanks for your response. I understand your solution but I forgot to say that my database is Oracle I mean

for sql NAME+SURNAME LIKE '%test%' it is ok and your aproach produces this but for plsql NAME||SURNAME LIKE '%test%' is working. According to your aproach what sholud I do to use '||' instead of '+' when we consider tyhe below code IEntityField contatenatedFields = CustomerFields.Id.SetExpression(CustomerFields.Name + CustomerFields.Surname);

Best Regards Koksal

According to your aproach what sholud I do to use '||' instead of '+' when we consider tyhe below code IEntityField contatenatedFields = CustomerFields.Id.SetExpression(CustomerFields.Name + CustomerFields.Surname);

I can't reproduce that test with Oracle right now. As I see, if we speak at Entity level, the concatenate operator must be the same no matter the database implementation. In traduction (trs-sql, plsql, etc.) would be in charge DataAccesLayer, so I think the code would work. But you try and let me know.

I test your approach with minor change as follows IEntityField contatenatedFields = CustomerFields.Id.SetExpression(CustomerFields.Name + CustomerFields.SurnaName);

not CustomerFields.Name +' '+ CustomerFields.SurnaName

When I investigate the query produced the Where Part Like As Follows (TBLCUSTOMER.NAME+TBLCUSTOMER.SURNAME Like :id1)

This query is running in sql server query analyzer but I can run the same query in Oracle query analyzer only by changing the "+" symbol with then "||" symbol.

The question is what should i do to prodeduce the above sql as follows (TBLCUSTOMER.NAME||TBLCUSTOMER.SURNAME Like :id1)

Best Regards Koksal

Use the or operator instead of the '+'.

Hi Otis, Did you mean the below? I tried to use "||" instead of "+" as follows

IEntityField contatenatedFields = CustomerFields.Id.SetExpression(CustomerFields.Name || CustomerFields.SurnaName);

But I got the following compile error.

Error 1 Operator '||' cannot be applied to operands of type 'SD.LLBLGen.Pro.ORMSupportClasses.EntityField' and 'SD.LLBLGen.Pro.ORMSupportClasses.EntityField'

Best Regards Koksal

You could try: IEntityField contatenatedFields = CustomerFields.Id.SetExpression( new Expression(CustomerFields.Name, ExOp.Or, CustomerFields.Surname));

I think I could not define the solution that I need, clearly.

At the beginning I asked that How I can generate the forrlowing SQL query with Llblgenpro self servicing mode.

"SELECT * FROM TBLCUSTOMER WHERE NAME+SURNAME LIKE '%bla bla%' for MS SQL

Then Daelmo solved the problem correctly. Thanks again him.

I supposed that the simple query runs the oracle the same way. Unfortunately, I noticed that the following query is running in oracle successfully with the minor change of using '||' instead of '+' such as

"SELECT * FROM TBLCUSTOMER WHERE NAME||SURNAME LIKE '%bla bla%' for Oracle

So I am asking again basically, in order to produce the above simple query for oracle, what should I do with Llblgenpro self servicing? How can I write a filter that will produce in the where part of sql similar to that "NAME||SURNAME LIKE '%bla bla%'

Best Ragards Koksal

Hi Otis, I think we post nearly at the same time for my previous post. I tried yours such as

IEntityField contatenatedFields = CustomerFields.Id.SetExpression( new Expression(CustomerFields.Name, ExOp.Or, CustomerFields.Surname));

But It produced the following SQL

..... WHERE ( ( "DIGIBIZ"."TBLCUSTOMER"."NAME" OR "DIGIBIZ"."TBLCUSTOMER"."SURNAME" LIKE :Id1))

"||" is the concatanation symbol in oracle like "+" is the same for SQL Server.

I am looking forward to having the solution.

Best Regards Koksal

Can you use the following simpler query?

SELECT * 
FROM TBLCUSTOMERS 
WHERE NAME LIKE '%sample%'
AND SURNAME LIKE '%customer%'

Instead of: WHERE NAME || SURNAME LIKE '%sample customer%'

There are many of solutions of course but I want to learn whether it is possible to do my way or will be possible to do in the future versions?

On the other hand, yours query and mine are very different when we consider the functionality and performance although they are looking similar at first sight.

By the way I really like to use Llblgenpro in my development platform.

Best Regards Koksal

Maybe you need to use the ExOp.Add instead of the ExOp.Or.

Check the following thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=8248

I'm not sure if Add will work, it will result in '+' which isn't what Oracle needs. I think in this particular case, you could derive a class from Expression and produce the SQL in the ToQueryText, but you could also create a function to concat the 2 strings inside Oracle and call that via a DbFunctionCall expression on the field.

Hi Otis, As I noticed, You mentioned two things to do. One is derive a class from Expression... And create a function to concat 2 strings inside oracle...

The second one is not necessary because as I stated before "||" is the concatanetion operator already.

The only thing that I should do is the first one "derived class".

I wish You would gave a sample code to produce just the fllowing where part

"NAME || SURNAME LIKE '%blabla%'"

I would try to do, but I don't want reinvent the wheel again.

Best regards Koksal

An example of IExpression implementation is posted here: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3829

usus wrote:

Hi Otis, As I noticed, You mentioned two things to do. One is derive a class from Expression... And create a function to concat 2 strings inside oracle...

No, that's the two things to do you have the choice to derive a class from Expression and perform the concatenation in an override of ToQueryText(). though that can be a bit problematic. Though you could implement IExpression in the way the thread linked by Walaa shows. It's pretty simple.

You could also create a function inside Oracle, which accepts two fields/values and concats them so your query becomes MyFunc(NAME, SURNAME) LIKE '%blabla%' when you call it by using DbFunctionCall("MyFunc", new object[] {MyEntityFields.NAME, MyEntityFields.SURNAME})

The second one is not necessary because as I stated before "||" is the concatanetion operator already.

The only thing that I should do is the first one "derived class".

I wish You would gave a sample code to produce just the fllowing where part

"NAME || SURNAME LIKE '%blabla%'"

I would try to do, but I don't want reinvent the wheel again.

I personally think a function inside oracle is the most simple solution. Though a simple IExpression implementation would also do. For reference you can also check the Expression sourcecode in the ormsupportclasses sourcecode.

Hi Otis and Wallaa,

Sory for late response I have a meeting.

After meeting I checked my e mail asap. Now, I clearly understand what I should do.

You are very helpful and doing your bussiness very well and LLBLGENPRO is really so good.

Best Regards Köksal

Hi Otis and Wallaa,

Again me

When I look at the link in Wallaa's post, I understood what it did for functions like Year, Month...

I tried but unfortunately, I could not adapt the given solution to my problem.

Please, could you do it for me ? I think It will be also a good sample to the others

Best Regards Köksal

Hi, I think I should implement "IPredicate" interface and genarate a predicate that would be a combination of "FieldBetweenPredicate" and "FieldLikePredicate" because there would be two entityfield parameters (such as Customer Entity Name and Customer Entity Surname), one concataneteoperator symbol (such as '+' for SQL, '||' for Oracle), and one value parameter (such as "%blabla")

In the ToQueryText it should produce like below for instance Oracle ("TBLCUSTOMER"."NAME" || "TBLCUSTOMER"."NAME" like '%blabla%')

I think up to this point I am correct ?, but I could not implement this interface because I am new in OOP I know What I should do but I don't know How I could do?

I would be happy if some one helps me?

Best Regards Köksal

You could do with a new predicate but as an expression already is able to refer to 2 fields, why not use that instead?

So as you requested some help, here we go.

Let's call this expression the ConcatenateFieldName expression. I'll re-use the Expression.cs sourcecode, and put it in a new class. You can then place this class in your own project.

[Serializable]
public class ConcatenateFieldNameExpression: IExpression, ISerializable
{
    #region Class Member Declarations
    private IExpressionElement  _leftOperand, _rightOperand;
    private ExOp _operator;

    [NonSerialized]
    private List<IDataParameter>    _parameters;
    [NonSerialized]
    private IDbSpecificCreator  _creator;
    #endregion

    #region Constructors
    /// <summary>
    /// CTor
    /// </summary>
    /// <remarks>Empty constructor, do not use, use one of the constructor overloads to create an expression instance.</remarks>
    public ConcatenateFieldNameExpression()
    {
        InitClass();
        _leftOperand = null;
        _rightOperand = null;
        _operator = ExOp.None;
    }
    
    /// <summary>
    /// CTor for (expression) operator (expression) expressions.
    /// </summary>
    /// <param name="leftOperand">The first field to concatenate</param>
    /// <param name="operatorToUse">operator to use in this expression. E.g. use for SqlServer ExOp.Add and for oracle ExOp.Or </param>
    /// <param name="rightOperand">The second field to concatenate.</param>
    public ConcatenateFieldNameExpression(IEntityFieldCore leftOperand, ExOp operatorToUse, IEntityFieldCore rightOperand)
    {
        InitClass();
        _leftOperand = new ExpressionFieldElement( ExpressionElementType.Field, leftOperand, leftOperand as IFieldPersistenceInfo );
        _rightOperand = new ExpressionFieldElement( ExpressionElementType.Field, rightOperand, rightOperand as IFieldPersistenceInfo );
        _operator = operatorToUse;
    }


    /// <summary>
    /// Initializes a new instance of the <see cref="Expression"/> class.
    /// </summary>
    /// <param name="info">Info.</param>
    /// <param name="context">Context.</param>
    protected ConcatenateFieldNameExpression(SerializationInfo info, StreamingContext context)
    {
        InitClass();
        _leftOperand = (IExpressionElement)info.GetValue("_leftOperand", typeof(IExpressionElement));
        _rightOperand = (IExpressionElement)info.GetValue("_rightOperand", typeof(IExpressionElement));
        _operator = (ExOp)info.GetValue("_operator", typeof(ExOp));
    }

    #endregion


    /// <summary>
    /// Populates a <see cref="T:System.Runtime.Serialization.SerializationInfo"></see> with the data needed to serialize the target object.
    /// </summary>
    /// <param name="info">The <see cref="T:System.Runtime.Serialization.SerializationInfo"></see> to populate with data.</param>
    /// <param name="context">The destination (see <see cref="T:System.Runtime.Serialization.StreamingContext"></see>) for this serialization.</param>
    /// <exception cref="T:System.Security.SecurityException">The caller does not have the required permission. </exception>
    public void GetObjectData(SerializationInfo info, StreamingContext context)
    {
        info.AddValue("_leftOperand", _leftOperand, typeof(IExpressionElement));
        info.AddValue("_rightOperand", _rightOperand, typeof(IExpressionElement));
        info.AddValue("_operator", _operator, typeof(ExOp));
    }


    /// <summary>
    /// Retrieves a ready to use text representation of the contained expression. 
    /// </summary>
    /// <param name="uniqueMarker">int counter which is appended to every parameter. The refcounter is increased by every parameter creation,
    /// making sure the parameter is unique in the expression and also in the expression(s) containing the expression.</param>
    /// <returns>The contained expression in textual format.</returns>
    /// <exception cref="System.ApplicationException">When IExpression.DatabaseSpecificCreator is not set to a valid value.</exception>
    public string ToQueryText(ref int uniqueMarker)
    {
        return ToQueryText(ref uniqueMarker, false);
    }

    /// <summary>
    /// Retrieves a ready to use text representation of the contained expression. 
    /// </summary>
    /// <param name="uniqueMarker">int counter which is appended to every parameter. The refcounter is increased by every parameter creation,
    /// making sure the parameter is unique in the expression and also in the expression(s) containing the expression.</param>
    /// <param name="inHavingClause">if set to true, it will allow aggregate functions to be applied to fields.</param>
    /// <returns>The contained expression in textual format.</returns>
    /// <exception cref="System.ApplicationException">When IExpression.DatabaseSpecificCreator is not set to a valid value.</exception>
    public virtual string ToQueryText(ref int uniqueMarker, bool inHavingClause)
    {
        if(_creator==null)
        {
            throw new System.ApplicationException("DatabaseSpecificCreator object not set. Cannot create query part.");
        }

        StringBuilder queryText = new StringBuilder(128);
        _parameters.Clear();

        // we always have 2 fields and a string operator.
        OperandToText(ref queryText, _leftOperand, ref uniqueMarker, true, inHavingClause);
        switch(_operator)
        {
            case ExOp.Add:
                queryText.Append(" + ");
                break;
            case ExOp.Or:
                queryText.Append(" || ");
                break;
        }
        OperandToText(ref queryText, _rightOperand, ref uniqueMarker, false, inHavingClause);

        return queryText.ToString();
    }


    /// <summary>
    /// Converts the passed in operand to text, appended to queryText. parameters created are added to _selectParameters.
    /// </summary>
    /// <param name="queryText"></param>
    /// <param name="operand"></param>
    /// <param name="uniqueMarker"></param>
    /// <param name="isLeftOperand"></param>
    /// <param name="inHavingClause"></param>
    private void OperandToText(ref StringBuilder queryText, IExpressionElement operand, ref int uniqueMarker, bool isLeftOperand, bool inHavingClause)
    {
        // always a field.
        IExpressionFieldElement fieldElement = (IExpressionFieldElement)operand;
        IEntityFieldCore fieldCore = (IEntityFieldCore)fieldElement.Contents;
        queryText.AppendFormat(null, "{0}", _creator.CreateFieldName(fieldCore, fieldElement.PersistenceInfo, fieldCore.Name, fieldCore.ObjectAlias, 
                ref uniqueMarker, inHavingClause));
        if(fieldCore.ExpressionToApply!=null)
        {
            // add parameters to this expression's collection.
            _parameters.AddRange(fieldCore.ExpressionToApply.Parameters);
        }
    }


    /// <summary>
    /// inits members
    /// </summary>
    private void InitClass()
    {
        _parameters = new List<IDataParameter>();
        _creator = null;
    }


    #region Class Property Declarations
    /// <summary>
    /// The list of parameters created when the Expression was translated to text usable in a query. Only valid after a succesful call to ToQueryText
    /// </summary>
    public List<IDataParameter> Parameters 
    {
        get { return _parameters;}
    }
    
    /// <summary>
    /// Object which will be used to create valid parameter objects, field names, including prefix/postfix characters, 
    /// and conversion routines, and field names, including prefix/postfix characters. 
    /// Uses the strategy pattern so the generic code can work with more than one target database.
    /// </summary>
    public IDbSpecificCreator DatabaseSpecificCreator 
    {
        get { return _creator;}
        set {_creator = value;}
    }

    /// <summary>
    /// Gets the left expression operand. Set by the constructor used.
    /// </summary>
    public IExpressionElement LeftOperand 
    {
        get { return _leftOperand;}
    }

    /// <summary>
    /// Gets the right expression operand. Set by the constructor used.
    /// Can be null
    /// </summary>
    public IExpressionElement RightOperand 
    {
        get { return _rightOperand;}
    }
    /// <summary>
    /// Gets the operator of the expression. 
    /// </summary>
    public ExOp Operator 
    {
        get { return _operator;}
    }
    #endregion
}

Most code is comments, so the code is pretty small and straight forward.

You then use it like: IEntityField contatenatedFields = CustomersFields.CustomerId.SetExpression(new ConcatenateFieldNameExpression(CustomersFields.CompanyName, ExOp.Or, CustomersFields.ContactName));

Which will result in a field || field result.

If you're on sqlserver, use ExOp.Add.

Hi Otis Thank you very much ::D Best Regards Köksal

I had a similar problem like how to do a query like this "select *from tableName where fieldLastName+', '+fieldfirstName like 'Shildt, Herbert%' " in llblgen.

I solved it by creating a custom field like:

IRelationPredicateBucket filter = new RelationPredicateBucket();

IEntityField2 newField= TableFields.IdSomeTable.SetExpression(TableFields.fieldLastName + ", " + TableFields.fieldFirstName); filter.PredicateExpression.AddWithOr( (EntityField2)newField % "Shildt, Herbert%"));

EntityCollection<Table> tableList = new EntityCollection<Table> (); tableList.Find(filter);

I hope this helps by solving your problem.