Npgsql EF Core concatenation expression

Question

I have a customer table with columns firstname, lastname and a trigram index:

CREATE INDEX ix_customer_search_trgm ON customer USING gin ((firstname || lastname ) gin_trgm_ops);"

I'm trying to search customers by firstname or lastname using C#/EFCore but can't figure out how to specify a concatenation expression for firstname || lastname

I tried the following

1. dbContext.Customers.Where(q => EF.Functions.ILike('firstname || lastname', $"%{searchTerm}%")); This generates a query like below (notice $1 = 'f' which looks odd) and doesn't match the columns

2020-12-01 23:04:52.119 AEDT [38091] LOG:  execute <unnamed>: SELECT ...
        FROM customer AS c
        WHERE $1

2020-12-01 23:04:52.119 AEDT [38091] DETAIL:  parameters: $1 = 'f'

2. dbContext.Customers.Where(q => EF.Functions.ILike(q.firstname, $"%{searchTerm}%")); This generates the sort of query I expect but obviously filters only one of the columns in the expression.

2020-12-01 23:02:21.777 AEDT [38034] LOG: execute <unnamed>: SELECT ...
        FROM customer AS c
        WHERE c.customer ILIKE $1 ESCAPE ''
2020-12-01 23:02:21.777 AEDT [38034] DETAIL:  parameters: $1 = '%abc%'

How can I specify a concatenation expression for the above with EF Core?

Answer 1

Like all other EF Core LINQ methods, ILike doesn't accept a raw SQL string which gets integrated into the query, but rather a .NET expression which EF Core translates for you. So you should be able to achieve the above with:

dbContext.Customers.Where(q => EF.Functions.ILike(q.FirstName + q.LastName, $"%{searchTerm}%"));
~~~