Query data with "OR" condition in many code rows

[Tralli_Luca]

I need to set some conditions in OR into an Aurelius Query.

The conditions are decided by the interface and can vary between 0 to n

I find the way to use the OR into one line of code like follow:

oCriteria.Where(((Linq['NameCombo'] = ss) OR (Linq['Description'] = ss)));

I'm don't understand how to split in more than one row of code (I need to insert the lines in a loop).

Something like the following code (It works in AND)

  oCriteria.add(Linq['NameCombo'] = ss);

  oCriteria.add(Linq['Description'] = ss);

I tried the following one but it does not work

  oCriteria.add(Linq['NameCombo'] = ss);

  oCriteria.Add(Linq.Sql(' OR '));

  oCriteria.add(Linq['Description'] = ss);

[Maierhofer_Bernd]

Additionally, as this is related to Lucas question: How can I combine two or more criterias with OR?

[wlandgraf (Wagner Landgraf)]

You can use something like this:

uses Aurelius.Criteria.Linq;


var
  Expr: TLinqExpression;
begin
  Expr := (Linq[‘A’] = 1) or (Linq[‘B’] = 2);
  if Condition then
    Expr := Expr or (Linq[‘C’] = 3);


  Criteria.Add(Expr);
end;

[Maierhofer_Bernd]

:- ) Great. Thanks.

One more:

Having a loop, I want to build up my expression:

  ExpressionA: TLinqExpression;
  s:           string;
begin
  for s in sSearch.Split([' ']) do begin
    if ExpressionA = nil then begin < ----  this does not work of course
      ExpressionA := Linq.like('Bezeichnung', '%' + s + '%');
    end else begin
      ExpressionA := ExpressionA and Linq.like('Bezeichnung', '%' + s + '%');
    end;

How can I test, if a TLinqExpression already has something assigned?

[wlandgraf (Wagner Landgraf)]

I think it's best you just use a flag to see if you have initialized it, or use a for statement using index and check if Index=0, for example.

[Maierhofer_Bernd]

Actually, this is, what I did.

tx for your answer!

[Tralli_Luca]

Thanks a Lot. I tried the following code and it works fine: 

function TframeXLookupProdotti.getCriteria: TCriteria;

var

  Expr: TLinqExpression;

begin

  Result := Manager.CreateCriteria(Manager.Explorer.Hierarchy.FindClassByName('TProducts'));

  Expr := Linq.Contains(TProjections.Upper('CodeProduct'), 'D_001');

  Expr := Expr or Linq.Contains('Description', '001');

  Expr := Expr and Linq.Eq('Disabled', False);

  Result.Add(Expr);

end;

I'ts only a simple example not dinamyc but can be useful.

[wlandgraf (Wagner Landgraf)]

Luca, be careful with mixing "or" and "and" in the same expression, you might get confused about the precedence.

If you feel like, use parenthesis or simply use different variables to properly organize the expressions.

[Tralli_Luca]

Thanks for the warning Wagner.

I found all the conditions in the Linq Expression except for the "<>" case (not equal).

I Tried with Not Linq.Eq but the results are not the same of "<>"

Result := Linq.Eq(sFieldName, sFind)

Result := Linq.GreaterThan(sFieldName, sFind);

Result := Linq.LessThan(sFieldName, sFind);

Result := Linq.GreaterOrEqual(sFieldName, sFind);

Result := Linq.LessOrEqual(sFieldName, sFind);

Result := Linq.StartsWith(sFieldName, sFind);

Result := Linq.Contains(sFieldName, sFind);

Result := Linq.IsNull(sFieldName);

Result := Linq.IsNotNull(sFieldName);

// Not working as needed!

Result := not Linq.Eq(sFieldName, sFind);

Can you tell me the function to use.?

[wlandgraf (Wagner Landgraf)]

Can you please provide more details? The following function compiles fine here:

function TForm4.GetLinq: TLinqExpression;
const
  sFieldName = 'foo';
  sFind = 'bar';
begin
  Result := Linq.Eq(sFieldName, sFind);
  Result := Linq.GreaterThan(sFieldName, sFind);
  Result := Linq.LessThan(sFieldName, sFind);
  Result := Linq.GreaterOrEqual(sFieldName, sFind);
  Result := Linq.LessOrEqual(sFieldName, sFind);
  Result := Linq.StartsWith(sFieldName, sFind);
  Result := Linq.Contains(sFieldName, sFind);
  Result := Linq.IsNull(sFieldName);
  Result := Linq.IsNotNull(sFieldName);


  Result := not Linq.Eq(sFieldName, sFind);
end;

Wagner R. Landgraf2020-01-16 16:06:18

[Tralli_Luca]

Sure, It's not a compilation problem but I'd like to reproduce this sql statment:

... Where ProductCode <> '001'

Imagining a table with the filed 'ProductCode" and the 3 following records:

'001'

'002'

'003'

I need to extract all records that are not equal to '001':

'002'

'003'

Using

Result := not Linq.Eq('ProductCode ','001')

I get:

'001'

'002'

'003'

[wlandgraf (Wagner Landgraf)]

It should work. Have you checked the generated SQL?

In any cause, you can use Linq.Neq function

[Tralli_Luca]

Ops, sorry,

It was a my mistake

Watching the SQL I understood the problem

Thanks for your time