Hi,

I would like to formulate following Query.

SELECT * FROM CompanyType WHERE ( CompanyTypeCode LIKE 'ABC%' OR CompanyTypeName LIKE 'ABC%' OR CompanyTypeCode + CompanyTypeName LIKE 'ABC%' ) AND (IsDeleted = 0)

        
public bool Select(string codeOrName, int pageNumer, int pageSize)
        {
            IPredicateExpression filter = new PredicateExpression();
            filter.AddWithOr(DAL.HelperClasses.CompanyTypeFields.CompanyTypeCode % (string.Format("{0}%", codeOrName)));
            filter.AddWithOr(DAL.HelperClasses.CompanyTypeFields.CompanyTypeName % (string.Format("{0}%", codeOrName)));
            
            IRelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.PredicateExpression.AddWithAnd(filter);
            bucket.PredicateExpression.AddWithAnd(DAL.HelperClasses.CompanyTypeFields.IsDeleted == false);

            ISortExpression sortClauses = new SortExpression();
            sortClauses.Add(DAL.HelperClasses.CompanyTypeFields.CompanyTypeCode | SortOperator.Ascending);

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchEntityCollection(this, bucket, pageSize, sortClauses, null, null, pageNumer, pageSize);
            }           

            return (this.Count > 0);
        }

LLBLGenPro 2.6 Final (latest release) Runtime Library: 2.6.9.903 Templates: Adapter .NET Framework: .NET 3.5 Database: SQL Server 2005

Hi Amit,

Here is an approximate Linq2LLBL code:

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    LinqMetaData metaData = new LinqMetaData(adapter);
    var q = from ct in metaData.CompanyType
            where (ct.CompanyTypeCode.StartsWith("ABC") ||
                c.CompanyTypeName.StartsWith("ABC"))
                && c. IsDeleted == false
            select ct;

    List<CompanyType> results = (List<CompanyType>)q.ToList();
}

The following line seems to be redundant:

CompanyTypeCode + CompanyTypeName LIKE 'ABC%'

Hope helpful

Thanks daelmo.

We are not using Linq2LLBL in this code.

Let's ignore the other two OR conditions and use this as a SQL.

SELECT * FROM CompanyType WHERE (CompanyTypeCode + CompanyTypeName LIKE 'ABC%') AND (IsDeleted = 0)

Thanks.

Sorry Amit, I misunderstand your question. So, you need to create an entityField with an expression (that concatenates the fields) and then use such created field into the filter. Here you go:

// create a field to concatenate the your two fields
EntityField2 concatenatedField = new EntityField2("conctField",
    new Expression(CompanyTypeFields.CompanyTypeCode, ExOp.Add,
        CompanyTypeFields.CompanyTypeName));

// build the filter
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(concatenatedField % "ABC%");

// fetch results
EntityCollection<CompanyType> results = new EntityCollection< CompanyType >();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(results, filter);
}

Thanks Daelmo.

Appreciate your help.

Cheers,