How to write a query to use jsonb::text ilike?

[aropb]

My model:

public class DocumentTag
{
    public string Name { get; set; }
    public string Value { get; set; }
}

// Table
public class Document
{
    ...
    public List<DocumentTag> Tags { get; set; } = new(); // jsonb column type + GIN index
    ...
}

I need to use EF to write a query like this, how do I do this?

SELECT * FROM public."Document" WHERE "Tags"::text ilike '%234%'

It doesn't work: .Where(... && x.Tags.Any(y => y.Value.Contains("234"))

Thanks.

[roji]

WHERE "Tags"::text ilike '%234%'

This converts your json document to a text file, and does a text search on it - are you sure that's what you want to do?

It doesn't work: .Where(... && x.Tags.Any(y => y.Value.Contains("234"))

This LINQ query is indeed very different from the SQL you posted above, and generally makes much more sense. This should work just fine if you use the newer EF JSON column mapping rather than the legacy jsonb mapping - see the docs for more info.

[aropb]

I've tried different options. I need to select all entries where an element with Value containing '%234%' occurs in List of DocumentTag. What is the best way to do it then?

What should sql be like? It is important that I choose the value not strictly, but exactly like from the list of DocumentTag objects.

toJSON() - I can't create a GIN index for such a field. How to do it?

[roji]

@aropb you seem to be trying to find Documents who have a tag that matches a pattern (that's what your LINQ query expresses, in any case). If that's the case, I suggest you use POCO ToJson() mapping (docs) rather than "legacy POCO mapping" (documented just below). At that point, your LINQ query should just work as-is:

.Where(... && x.Tags.Any(y => y.Value.Contains("234"))

[aropb]

I tried toJSON() again.

Yes, this option works fine: x.Tags.Any(y => EF.Functions.ILike(y.Value, $"%{tags}%"))

SQL:

SELECT t0."ID", t0."DocumentId", t0."Name", t0."FileName", t0."FilePath", t0."FileSize", t0."Duration", t0."CreatedDate", t0."LoadedDate", t0.c, d0."ID", d0."Content", d0."DocumentID" FROM ( SELECT d."ID", d."CreatedDate", d."DocumentId", d."Duration", d."FileName", d."FilePath", d."FileSize", d."LoadedDate", d."Name", d."Tags" AS c FROM "Document" AS d WHERE d."LoadedDate" >= @start_0 AND d."LoadedDate" <= @__stop_1 AND (d."FilePath" ILIKE @Format_3 ESCAPE '' OR EXISTS ( SELECT 1 FROM ROWS FROM (jsonb_to_recordset(d."Tags") AS ( name text, value text )) WITH ORDINALITY AS t WHERE t.value ILIKE @Format_3_1 ESCAPE '')) ORDER BY d."LoadedDate" DESC, d."FileName" LIMIT @p_4 ) AS t0 LEFT JOIN "DocumentSummary" AS d0 ON t0."ID" = d0."DocumentID" ORDER BY t0."LoadedDate" DESC, t0."FileName"

Database context:

        modelBuilder.Entity<Document>()
            .OwnsMany(b => b.Tags, c => { c.ToJson(); });

But as soon as I add this an error occurs:

       modelBuilder.Entity<Document>()
            .HasIndex(b => b.Tags)
            .HasMethod("GIN");

The property or navigation 'Tags' cannot be added to the 'Document' type because a property or navigation with the same name already exists on the 'Document' type.

How do I create a GIN index for the Tags field?

[roji]

You're right - EF currently has a limitation, where it doesn't allow creating indexes over owned entity types (ToJson()) - that's tracked on the EF side by https://github.com/dotnet/efcore/issues/28605.

In the meantime, you can use custom SQL in your migration to manually do "CREATE INDEX" and add the GIN index - that would be the recommended at this stage. Note that this unfortunate state is temporary - EF 10 will very likely bring full complex type support, which I'm hopeful will also support defining indexes in a nicer way.

I'll go ahead and close this as a duplicate of https://github.com/dotnet/efcore/issues/28605, but feel free to post back here if you have further questions etc.

[roji]

Duplicate of https://github.com/dotnet/efcore/issues/28605