Efcore.pg: Operation translation for List<T>

[roji]

We now support mapping CLR List<T> to PostgreSQL arrays (#392). We also have some nice operation translations for regular CLR arrays, but should also translate operations for List<T>, which is supported for mapping PostgreSQL arrays.

[austindrenski]

@roji Has anyone started working on this?

If not, I can start scoping out what we could translate in time for 2.1.

[roji]

Feel free to work on this, at least some things should be fairly easy. As a first goal it would be good to achieve party with what is already translated for arrays (length, indexing...)

[roji]

See some work done based on the old query pipeline in #541

[ryanelian]

Excuse me, does this mean that:

            var origins = new List<string> { origin };

            var results = DB.Clients
                .Where(Q => origins.All(item => Q.AllowedCorsOrigins.Contains(item)))
                .AsNoTracking()
                .AnyAsync();

Is now supported?

(Will it translate to:)

SELECT * FROM mytable WHERE myarray @> ARRAY['item'];

[roji]

@ryanelian yes, that should work - it's very similar to this test. Give it a try and please open a new issue if you run into trouble.

[JohnDitno]

Could someone confirm whether the following partial matching using Any and Contains is supported?

var partialOrigin = "http://";

var results = DB.Clients
.Where(Q => Q.origins.Any(item => item.Contains(partialOrigin)))
.AsNoTracking()
 .AnyAsync();

[roji]

@JohnDitno take a look at https://github.com/npgsql/efcore.pg/issues/395#issuecomment-590121075. I'd recommend trying to reference 5.0.0-rc1 and giving it a try to be sure.

[SommerEngineering]

@JohnDitno Unfortunately it does not work, at least in our case:

The LINQ expression 'DbSet<Entry>()
    .Where(e => e.IsLatestRevision)
    .Where(e => e.EditTime >= __request_ShowSince_0)
    .Where(e => !(e.IsArchived))
    .Where(e => !(e.IsDeleted))
    .OrderBy(e => e.Name)
    .Where(e => e.Competencies
        .Any(i => i.Contains(__searchTerm_1)))' could not be translated.

It does not matter if Entry.Competencies is List<string> or string[]. Both fails with the shown exception.

I used:

• .NET 5.0 RC2 and <TargetFramework>net5.0</TargetFramework>
• <PackageReference Include="Npgsql" Version="5.0.0-preview1" />
• <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="5.0.0-rc2" />
• PostgreSQL 12.2 server

@ryanelian Should I open a new issue for that?

[JohnDitno]

@SommerEngineering thanks for responding to my query.

That is a shame it doesn't seem to work still, hopefully it can be fixed as we have a similar scenario to your case.

[roji]

@SommerEngineering your query is very different from a simple Contains in a Where clause - can you please open a new issue?

If Competencies is a list (or array) of string, are you searching whether one of its strings contains a specific character? Posting a full code sample (including your model) could help clarify what you're trying to do.

[SommerEngineering]

Thanks @roji for your response. I created issue #1554 with a minimal repo to reproduce the case 🙂

@JohnDitno In my repo for issue #1554 you find different attempts to archive this functionality. No one works, if partial matching is necessary. Searching for exact matches in array columns works, though. This is might a workaround until we get the issue solved.