Hi, I'm trying to use FullTextSearch with LLBLGen.

LLBLGen query code:

IPredicateExpression filter = new PredicateExpression(); filter.Add(new FieldFullTextSearchPredicate(Table1Fields.Value, FullTextSearchOperator.Contains, *input + "")); var collection = new Table1Collection(); collection.GetMulti(filter)

I'm doing a search by email: "firstname.lastname@domain.com"

Results is very strange: 1. Input pattern: "firstname." + "". LLBLGen result: success 2. Input pattern: "firstname.l + "". LLBLGen result: success 3. Input pattern: "firstname.las" + "". LLBLGen result: fail. Email "firstname.lastname@domain.com" not found. 4. Input pattern: "firstname.lastname" + "". LLBLGen result: success

Generated Sql query is: SELECT [Table1].[Id], [Table1].[Created], [Table1].[Value] FROM [Table1] WHERE ( Contains([Table1].[Value], @p1)) Parameter: @p1 : String. Length: 15. Precision: 0. Scale: 0. Direction: Input. Value: "firstname.las*".

If execute a query to the database, the email will find. Looks like LLBLGen is missing a character '*'

Could anyone help me?

I just tried your example on SQL Server (13.0.1745). SQL Server returned no rows for "firstname.las", or for any phrase containing part of the word.... e.g. "firstname.lastn or "firstname.lastnam*".

Which makes sense, since you are using Contains which matches full words and phrases, you should be using Freetext or Freetexttable

Walaa wrote:

I just tried your example on SQL Server (13.0.1745). SQL Server returned no rows

I'm completely disagree with you. Please, see my examples:

1. http://prntscr.com/lwedcw SELECT [Value] FROM [Contact] WHERE CONTAINS([Value], '"second.te"') Result: 0 rows

2. http://prntscr.com/lwe9hm SELECT [Value] FROM [Contact] WHERE CONTAINS([Value], '"second.te*"') Result: 2 rows

These two queries differ from each other only in one characters - *. It's perfectly works with SQL Server.

How can I write similar query (#2) with LLBLGen?

What version are you using? (build / runtime version)?

What exactly is the problem? As you post 4 inputs and 1 gives 0 results but the others work, with the same wildcard? We simply pass the input you give as parameter to the database, so there's little else we can do.

Otis wrote:

What exactly is the problem?

The problem is that I get different results:

Via LLBLGen - 0 rows

IPredicateExpression filter = new PredicateExpression(); filter.Add(new FieldFullTextSearchPredicate(ContactFields.Value, FullTextSearchOperator.Contains, input + "*")); var collection = new ContactCollection(); collection.GetMulti(filter)

Via SQL - 2 rows

SELECT [Value] FROM [Contact] WHERE CONTAINS([Value], '"second.te*"')

Please answer all my questions or this thread is going to be closed. That might sound rude, but otherwise it's going to be a lot of time for us to track down things.

For the record, this forum uses fulltextsearch using llblgen pro for its search since 2008 and works fine

You also posted 3 inputs which do give the right result. As we only pass on the input as a parameter to the database, there's little else we can do (and with 3 inputs that are passed as value in a parameter things work ok).

Otis wrote:

What version are you using? (build / runtime version)?

LLBLGen Version - 4.0 Final. Released on May 15th, 2013. SQL Server Version - 10.50.2550.0.

Otis wrote:

What exactly is the problem? As you post 4 inputs and 1 gives 0 results but the others work, with the same wildcard?

I can't find email = "firstname.lastname@domain.com" with LLBLGen's CONTAINS using input = "firstname.last*"

But it works with SQL: SELECT [Value] FROm [Contact] WHERE CAONTAINS([Value], '"firstname.last*"')

That's a pretty old version but there hasn't been a bugfix in that predicate since a long time (as it works fine) so your version should work. It does work in your code too, with 3 of the 4 inputs you specify.

Like I said: we simply pass on what you specify as input, so there's nothing else done: no processing internally which might bug, nothing, just the input is passed on as a parameter.

Anyway, you specify "" around your fragment when you query directly on the DB, but those aren't present when you pass the input as a parameter. See: https://docs.microsoft.com/en-us/sql/relational-databases/search/query-with-full-text-search?view=sql-server-2017 You have to specify these deliberately when passing on a wildcard. See https://github.com/FransBouma/HnD/blob/master/BL/Searcher.cs#L190 for the searcher code we use here on this forum to do that.

I.o.w.: if you specify a '*' you have to include "" into your input string.

So

filter.Add(new FieldFullTextSearchPredicate(Table1Fields.Value, FullTextSearchOperator.Contains, "\"" + input + "*\""));

should do it.

Thanks, that works fine