Support for EntityFramework .NET integration

[nandor23]

What idea are you proposing?

Hello,
I am proposing an EF extension library for pg_search. It would expose all the functions through a static class, similar to how EF.Functions is used. Index creation should not be supported, as that would require overriding the EF migration builder, and creating a BM25 index manually inside a migration file is possible with raw SQL: https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/managing?tabs=dotnet-core-cli#adding-raw-sql

I have already started working on an extension library, and am implementing paradedb.match. Here is an example of what the LINQ query would look like and how it would translate.

public class Product
{
    public Guid Id { get; set; }
    public string Description { get; set; }
    public double Rating { get; set; }
    public string Category { get; set; }
}
    
_dbContext.Products
  .Where(p => PgSearch.Match(p.Id, p.Description, "running shoes"))
  .Select(p => new { Description = p.Description, Rating = p.Rating, Category = p.Category })
  .ToList();

SELECT description, rating, category
FROM products
WHERE id @@@ paradedb.match('description', 'running shoes');

With that in mind, would it be possible to simplify the syntax in ParadeDB without specifying the key field. I am not a database expert, but wouldn’t it be possible to get the key field under the hood for the specified table we are selecting from? The syntax would become simpler, and easier to understand.

SELECT description, rating, category
FROM products
WHERE description @@@ paradedb.match('running shoes');

Why are you proposing this idea?

To provide an extension library for Npgsql.EntityFrameworkCore.PostgreSQL that adds pg_search integration.

Full Name:

Nandor Krizbai

Affiliation:

None

[philippemnoel]

Hi @nandor23! Thank you for being here, this is a really exciting project and one we'd love to support you with. A few thoughts:

• We're currently making changes to our API for a "v2" that will have a much simpler interface. You can see some parts of it here: https://docs.paradedb.com/v2/overview. The second wave of work, mostly improvements at the index creation/tokenizer level, is here: https://github.com/paradedb/paradedb/tree/eebbrr/sql-ux-tokenizers. I'd highly recommend that you base your work off of that, as it's what's we're going to be supporting moving forward (and don't expect to change it again)

• re: key_field. There's an issue someone else raised about this: Automatic key column detection on index creation #2372. We were planning to discuss it after completing the tokenizer work. I'll ask the team for their thoughts.

[philippemnoel]

We are working on a "tokenizer as types" feature, which will pretty much round up the V2 API interface. You can see the upcoming changes in this branch: https://github.com/paradedb/paradedb/tree/eebbrr/sql-ux-tokenizers.

[eeeebbbbrrrr]

@nandor23 if there's any specific questions I can answer around what our "v2" SQL API looks like (or will look like when we're done), please ask.

ORM support, across as many languages/frameworks as possible, is important to us. We don't necessarily have the expertise (or bandwidth) to do these integrations ourselves, so anything we can do to help you on this project we want to do.

[nandor23]

I was able to implement the following:

• Disjunction (|||), conjunction (&&&), phrase (###), and term (===) operators
• BM25 scoring, fuzziness and boost support where applicable
• Highlighting, but only paradedb.snippet(<column>)

I'll focus on these operations for now and need to add both translation and functional tests. Implementing Byte Offsets is not possible because multi-dimensional arrays are not yet supported in EF: npgsql/efcore.pg#314.

Apart from this, I am not able to add support for Proximity. This might require someone with more experience, or someone who is more familiar with the internal EF API. The 'sleek' ## 1 ## 'shoes' operation has 2 operators, and with the current API I am only able to construct ('sleek' ## 1) ## 'shoes'. I'm not sure if it means the same, but I haven't tested it yet.

At first glance, I am able to implement the query builder functions. I'll wait until the newer tokenizers are documented, as I am not familiar with Rust.

I have a few questions as well:

1. For now I treated the search value as a string type. Do the operations support integer (or other types of) values, e.g., WHERE description ||| 1234?
2. Using named notation when calling functions is not possible with EF, only positional notation is supported. This means I am forced to pass default values for optional parameters. Supporting the Match function is only possible if the tokenizer parameter takes in null as default value. I believe named notation is not supported in other ORMs either. What is the default value of tokenizer?

[eeeebbbbrrrr]

with the current API I am only able to construct ('sleek' ## 1) ## 'shoes'. I'm not sure if it means the same, but I haven't tested it yet.

That should be fine. Looks funny, but it's okay.

Do the operations support integer (or other types of) values

They currently do not. Strictly text for now. We may expand it in the future for certain types where we think it makes sense

This means I am forced to pass default values for optional parameters.

Can you provide specific examples, in SQL, of what you'd like to do v/s what you're required to do? There should be no need for "named notation" and it's not clear to my why you're required to pass default values.

[nandor23]

Can you provide specific examples, in SQL, of what you'd like to do v/s what you're required to do? There should be no need for "named notation" and it's not clear to my why you're required to pass default values.

An example would be the paradedb.snippet() function. With SQL, it's possible to write paradedb.snippet(description, max_num_chars => 100). The method can accept 4 parameters, but max_num_chars is the last parameter in the function call.

The above in .NET is possible with function overloads, if only the max_num_chars parameter needs to be specified:

EF.Functions.Snippet(p.Description, 100)

Since EF only supports positional parameter function calls, the following SQL is generated: paradedb.snippet(description, '<b>', '</b>', 100). In short, the 2nd and 3rd parameters are explicitly provided under the hood.

[eeeebbbbrrrr]

current API I am only able to construct ('sleek' ## 1) ## 'shoes'

If that works (and it should), I don't see any problems with it. You're making a framework to machine generate the SQL so it doesn't need to be quite as "pretty" as if one were to hand-write it.

Do the operations support integer (or other types of) values, e.g., WHERE description ||| 1234?

They currently do not, but it's possible we could do that if it's a blocker for you. The &&&, ===, ||| and ## operators were originally envisioned as strictly for text.

Supporting the Match function is only possible if the tokenizer parameter takes in null as default value.

I'm not sure I follow what you're getting at here?

What is the default value of tokenizer?

For the pdb.match() function itself the default tokenizer value is the SQL NULL, which internally translates to whatever tokenizer was used for the field during indexing.

[nandor23]

Thanks @eeeebbbbrrrr for answering all my questions.

They currently do not, but it's possible we could do that if it's a blocker for you. The &&&, ===, ||| and ## operators were originally envisioned as strictly for text.

It's not a blocker. I was just curious whether other types might be supported.

I just tested the ('sleek' ## 1) ## 'shoes' expression, and it works perfectly.

[nandor23]

I've implemented everything from Full Text Search except Byte Offsets and Proximity Array. Unit and integration tests are complete as well. Once the README is ready, I'll release version v0.1.0.

There's a DX issue: paradedb.score(<key_field>) returns null when the WHERE clause is missing. Wouldn't it be better to default to 0? I've treated the return value in .NET as non-nullable, so currently an InvalidOperationException is thrown if the WHERE clause is missing, because it tries to map NULL to float.

// Works without any issues
var products = dbContext
    .Products
    .Where(p => EF.Functions.MatchConjunction(p.Description, "with"))
    .Select(p => new { p.Id, Score = EF.Functions.Score(p.Id) })
    .ToList();
    
// translates to
SELECT p.id AS "Id", paradedb.score(p.id) AS "Score"
FROM products AS p
WHERE p.description &&& 'with'

// Exception is thrown
var products = dbContext
    .Products
    .Select(p => new { p.Id, Score = EF.Functions.Score(p.Id) })
    .ToList();
        
// translates to
SELECT p.id AS "Id", paradedb.score(p.id) AS "Score"
FROM products AS p

I see 2 options:

1. This stays as is. Making the result of the Score() function nullable means it can have both 0 and null values. Every arithmetic operation with the score would then require a null check, which is bad DX. Sure, Score() should be used in combination with &&&, ===, etc., but developers can make mistakes.

2. ParadeDB could return 0 instead of NULL. This way .NET could always map the result to the float type.

[eeeebbbbrrrr]

paradedb.score(<key_field>) returns null when the WHERE clause is missing. Wouldn't it be better to default to 0?

No. An SQL NULL means "we don't know what the value is". Returning zero would be us lying about the score. The intent is that users can use COALESCE(...) if they'd like to substitute in some kind of default score.

so currently an InvalidOperationException is thrown

Does .NET have some kind of Optional<T> wrapper? How does the library otherwise handle possible NULL values from a SELECT's target list? I think you'd want to treat the return value from paradedb.score() the same way.

[nandor23]

Yes, it has Nullable<T> type.

How does the library otherwise handle possible NULL values from a SELECT's target list?
In short EF handles it the following way:

• If the property is nullable type (float?, int?, etc.), SQL NULL is mapped to null in .NET.

• If the property is a non-nullable value type (float, int, etc.), EF assumes the column or expression never yields NULL. If the database does return NULL, EF will throw a runtime exception. (This is how Score() works currently)

Will paradedb.score(<key_field>) ever return NULL when the WHERE clause is missing? If the answer is no, the current implementation might be acceptable, otherwise, it's definitely not acceptable.

Making the result nullable means every operation on the .NET property holding the result requires a null check:

// SELECT p.id AS "Id", paradedb.score(p.id) AS "Score"
// FROM products AS p
// WHERE p.description &&& 'with'
// LIMIT 1

// suppose Score is nullable
var product = dbContext
    .Products.Where(p => EF.Functions.MatchConjunction(p.Description, "with"))
    .Select(p => new { p.Id, Score = EF.Functions.Score(p.Id) })
    .FirstOrDefault();

if (product is not null)
{
    float res = 5;
    
    if (product.Score is not null)
    {
        res += product.Score.Value;
    }
}

I'll leave it up to you to decide how the Score() function should behave:

1. Score() expects a non-null value, but throws an exception when the SQL function returns NULL (current implementation)
2. Make the value nullable in .NET, so additional null checks are required in the code when performing arithmetic operations or comparisons
3. Expect a non-null value in .NET, with the generated SQL including COALESCE, so the default value becomes 0 in .NET

Options 2 and 3 differ in terms of .NET DX and the generated SQL.

[eeeebbbbrrrr]

As the ORM tool author I think it's a defensible decision for you to always write paradedb.score() as COALESCE(paradedb.score(key_field), 0.0).

I don't, however, think it's a good idea for us to do that directly in pg_search.

[nandor23]

I see, the translated SQL now uses COALESCE.

[nandor23]

Just released v0.1.0 of the package on NuGet: https://www.nuget.org/packages/EFCore.ParadeDB.PgSearch
The package targets .NET 8 and above, since the required APIs were introduced in Npgsql.EntityFrameworkCore.PostgreSQL version 8.

Development can be followed at: https://github.com/nandor23/EFCore.ParadeDB.PgSearch

[philippemnoel]

That's super cool! Thanks for making it and for sharing. I'll highlight it on our social medias