Support many-to-many via array column instead of via a join table

[aeb-dev]

I am trying to generate the following sql with linq but no success so far:

SELECT
    t1.id,
    t2.id
FROM public.table1 t1
INNER JOIN public.table2 t2 ON ARRAY[t2.id]::bigint[] <@ t1.t2_ids;

Table1:

public class Table1
{
    public long Id { get;set; }
    public long[] T2Ids { get; set }
}

Table2:

public class Table2
{
    public long Id { get;set; }
}

Is this possible with LINQ?

[roji]

Try this:

from t1 in ctx.Table1
from t2 in ctx.Table2
where t1.T2Ids.Contains(t1.Id)
select t1

[aeb-dev]

It produces the following

SELECT t1.id, t1.t2_ids
FROM table1 AS t1
CROSS JOIN table2 AS t2
WHERE t1.t2_ids @> ARRAY[t1.id]::bigint[]

Also I meant linq with methods sry :)

I might have oversimplified the example:
There are no FK's between the tables because Table1 is partitioned on another field.

To replace the effect of FKs, I simply added the following code with respective fields

t1Entity.HasMany(d => d.Table2s)
            .WithMany(p => p.Table1s);

t2Entity.HasMany(d => d.Table1s)
            .WithMany(p => p.Table2s);

[roji]

@Mrmumu you're apparently trying to mix EF Core's many-to-many with array columns in some complicated way - that's not likely to work. EF Core manages many-to-many by having a third join table in the middle, with foreign keys to each side. If you need further help, please post a full, runnable code sample with the complete model and query you're trying to do.

[aeb-dev]

I will try to prepare an example

[aeb-dev]

Sorry for the late response

check this repo: https://github.com/MrMuMu/ef-postgres-array-join

Now the question is how do I generate the following query on linq method syntax:

SELECT
    t1.id,
    t2.id,
    t2.name
FROM public.table1 t1
INNER JOIN public.table2 t2 ON ARRAY[t2.id]::bigint[] <@ t1.table2_ids;

update: new field on table2

[roji]

This is definitely not something that EF Core can generate at this point.

Note dotnet/efcore#23523 which is about providing this for Cosmos (some info also in dotnet/efcore#16920 (comment)); the same work could be done for PostgreSQL as well. Keeping on the backlog.

[roji]

@Bouke that sentence simply talks about storing arbitrary data values (numbers, strings) and not for having EF Core automatically manage many-to-many via PG arrays (i.e. store foreign keys in the array).

[Bouke]

@roji I missed your reply (normally this view auto-updates) and have deleted my original comment as re-reading the comment indeed didn't suggest this exact use-case. Thank you for confirming the same.

[roji]

With the work on primitive collections in EF Core (dotnet/efcore#30731), this would be a general EF feature - in other databases which don't support arrays, a JSON array could hold the FKs of the related entities.

[roji]

Duplicate of dotnet/efcore#30551