Hi all,

I was wondering if theres a way to have a predicate that compare a value against n fields.

For example:

new RelationPredicateBucket((ClientFields.Firstname + " " + ClientFields.Lastname) % name);

Please try the following code (db=Northwind)

EntityCollection emps = new EntityCollection(new EmployeesEntityFactory());
            RelationPredicateBucket bucket = new RelationPredicateBucket();

            IExpression firstNameExp = new Expression(EmployeesFields.FirstName, ExOp.Add, " ");
            IExpression fullNameExp = new Expression(firstNameExp, ExOp.Add, EmployeesFields.LastName);
            EntityField2 field = EmployeesFields.FirstName.SetExpression(fullNameExp);
            bucket.PredicateExpression.Add(new FieldLikePredicate(field, null, "%Nancy Davolio%"));
            
            DataAccessAdapter adapter = new DataAccessAdapter();
            adapter.FetchEntityCollection(emps, bucket);

Hi~

I tried using similar code, but no results were returned with this query. I know I must've done something wrong, but I couldn't figure out...=(

string code = "HA262";
            
IExpression leftSubOp = new Expression(" ", ExOp.Add, ProductsFields.Contents);
IExpression leftOperand = new Expression(leftSubOp, ExOp.Add, " ");

string rightOperand = "% " + code + " %";

EntityField ContentsField = ProductsFields.Contents.SetExpression(leftOperand);

IPredicate filter = new FieldLikePredicate(ContentsField, rightOperand);
            
ProductsCollection proCol = new ProductsCollection();
proCol.GetMulti(filter);

The query I'm attempting to do is: (returns 4 results)

SELECT [Products].* 
FROM [Products] 
WHERE " "+[Products].[Contents]+" " LIKE "* HA262 *";

When I copy the queryExecuted use by the GetMulti() to MS Access, replacing the wildcard character from % to *, the query returns 4 results as well. But when I execute the code in my function, no results were returned...I'm not sure where I've done wrong. Thanks for your help!

So this was created by your code

SELECT [Products].*
FROM [Products]
WHERE " "+[Products].[Contents]+" " LIKE "% HA262 %";

Which returned 0 results. You changed the query to this and it returned 4.

SELECT [Products].*
FROM [Products]
WHERE " "+[Products].[Contents]+" " LIKE "* HA262 *";

I would suggest changing your code to this and see what the results are then.

string code = "HA262";
            
IExpression leftSubOp = new Expression(" ", ExOp.Add, ProductsFields.Contents);
IExpression leftOperand = new Expression(leftSubOp, ExOp.Add, " ");

string rightOperand = "* " + code + " *";

EntityField ContentsField = ProductsFields.Contents.SetExpression(leftOperand);

IPredicate filter = new FieldLikePredicate(ContentsField, rightOperand);
            
ProductsCollection proCol = new ProductsCollection();
proCol.GetMulti(filter);

Hi~

Thank you for your suggestion, I tried changing the wild card character from % to * in rightOperand, but still returns 0 result. I also tried couple of other cases, it seems like whenever I use a more complex IExpression, it returns no result:

At first I thought I just can't use expression for some reason. But if my expression consists of a simple field, it would work okay:

string code = "HA262";
            
IExpression leftOperand = new Expression(ProductsFields.Contents);

string rightOperand = "%" + code + "%";

EntityField ContentsField = ProductsFields.Contents.SetExpression(leftOperand);
IPredicate filter = new FieldLikePredicate(ContentsField, rightOperand);
            
ProductsCollection proCol = new ProductsCollection();
proCol.GetMulti(filter);

However, whenever I use a more complex expression, even if it's only adding a trailing space:

IExpression leftOperand = new Expression(ProductsFields.Contents, ExOp.Add, " ");

the query would not return any result.

So I suspect there must be something that I miss when I'm creating a complex expression, I just don't know what it is...=(

At first you asked about the following query:

new RelationPredicateBucket((ClientFields.Firstname + " " + ClientFields.Lastname) % name);

Together with the title of the thread implies that you want to concatenate 2 fields in your query.

Am I correct in this conclusion? That's what exactly the code I've posted earlier should do, it was tested against SQL database (Northwind). If you are using another database type like access you have to make sure you have generated your code against the Access database. This might help in the (% -> *) situation.

Then your next code contained only One field "ProductsFields.Contents" There were no 2 concatenated fields.

Would you please tell me why you are using spaces in your query?

Wouldn't the following query give the same results? (all spaces are removed)

SELECT [Products].*
FROM [Products]
WHERE [Products].[Contents] LIKE "%HA262%";

Which is a very simple query using FieldLikePredicate without any expressions.

Hi~

Sorry, I probably should've start a new thread, but I figure the idea of concatenation would be similar, so I posted on this thread instead, sorry!

I'm actually trying to concatenate leading and trailing space to a field (Products.Contents), rather than concatenating 2 fields. The code is generated against the MS Access database, so as you suggested, the wildcard character % -> * was okay.

And I know it's weird/bad practice to include space in the query, the reason was being that we wanted to try to do a very quick simple keyword search. Contents field contains something like for example "HA262 is a laptop with foil". But when people search the word "oil" or "apt", we don't really want this field to be returned because part of the keyword matches with the search word. So we figure if we use whole word " % oil %" rather than "%oil%" would return better results. However, with this method, the first and the last word of the Content field would never be found because they either don't have leading or trailing space. So that's why we try concatenate the Contents field with leading and trailing space, so that every word could be found. Probably a very bad idea..=(

The strangest part is if I copy and paste the queryExecuted from getMulti() into Access, it will execute and return the results I want (wildcard character aside). But your suggestion shed some light...perhaps it's in the way I generated the code? Contents field is generated as a nullable string type, maybe that's the problem?

Again I apologize for not starting a new thread, and thanks for any of your help!~

Just so we can get back on the same page can you post your current code and what it generates?

Hi, sorry for the late response, the current code I have is as follows:

string code = "HA262";

IExpression leftSubOp = new Expression(" ", ExOp.Add, ProductsFields.Contents);
IExpression leftOperand = new Expression(leftSubOp, ExOp.Add, " ");

string rightOperand = "% " + code + " %";
EntityField ContentsField = ProductsFields.Contents.SetExpression(leftOperand);

IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldLikePredicate(ContentsField, rightOperand));

ProductsCollection proCol = new ProductsCollection();
try
{
     proCol.GetMulti(filter);
}
catch (ORMQueryExecutionException ex)
{
     System.Console.WriteLine(ex.QueryExecuted);
}

Above code will execute successfully without exception, but with 0 result return. The queryExecuted by the codes above is as follows:

[_Products].* FROM [_Products] WHERE ( ( (@LO25f14a92 + [_Products].[Contents]) + @LO2b6
a1ca3 LIKE @Contents1))
        Parameter: @Contents1 : String. Length: 9. Precision: 0. Scale: 0. Direc
tion: Input. Value: "% HA262 %".
        Parameter: @LO25f14a92 : String. Length: 1. Precision: 0. Scale: 0. Dire
ction: Input. Value: " ".
        Parameter: @LO2b6a1ca3 : String. Length: 1. Precision: 0. Scale: 0. Dire
ction: Input. Value: " ".

The actual queryExecuted actually lists out all of the fields individually, I used .* instead since there are a lot of fields...

Then I use a direct query in MS Access 2003:

SELECT *
FROM _products
WHERE ' '+contents+' ' like "* HA262 *"

returns 4 results

Thanks for all of your help once again!

This is odd. I setup the same thing and I get the behavior that you were expecting. Here's my simple test db. If you could try this with this code and tell me what your behavior is it would help.

            string code = "HA262";

            IExpression leftSubOp = new Expression(" ", ExOp.Add, TestFields.TestName);
            IExpression leftOperand = new Expression(leftSubOp, ExOp.Add, " ");

            string rightOperand = "% " + code + " %";
            EntityField ContentsField = TestFields.TestName.SetExpression(leftOperand);

            IPredicateExpression filter = new PredicateExpression();
            filter.Add(new FieldLikePredicate(ContentsField, rightOperand));

            TestCollection proCol = new TestCollection();
            try
            {
                proCol.GetMulti(filter);
            }
            catch (ORMQueryExecutionException ex)
            {
                System.Console.WriteLine(ex.QueryExecuted);
            }

shouldn't: string rightOperand = "% " + code + " %"; be: string rightOperand = "* " + code + " *";

?

The FieldLikePredicate doesn't mangle the pattern, so if you want to specify '' as wildcard, you have to do that. Access uses '' as wildcard instead of the rest of the world which uses '%'