Association on List - Join Table

[Dutch-0]

How can I define a ManyToMany / OneToMany association with Linq2Db, preferably using a intermediate (join) table?

[MaceWindu]

We support only OneToOne/OneToMany associations. You can check various examples here https://github.com/linq2db/linq2db/blob/master/Tests/Linq/Linq/AssociationTests.cs

Cardinality defined by type of association: TRecord or IEnumerable<TRecord>. You can also use other collection types, like IQueryable<T>, array, List<T>, but I personally prefer IEnumerable<T> as it doesn't insinuate additional functionality, not really supported here.

Join condition could be defined as set of key fields, custom sql or even custom sub-query (see tests for examples).

For ManyToMany association you will need to define intermediate table mapping wtih associations to both sides.

[Dutch-0]

For ManyToMany association you will need to define intermediate table mapping wtih associations to both sides.

Does that have to be an association like

class A 
{
    [Association(...)]
    List<IntermediateTableB> Bs { get; set; }
}

Or is there a way, maybe using static methods, to achieve a 'normal' mapping? Like:

class A 
{
    [Association(...)]
    List<C> Cs { get; set; }
}

[MaceWindu]

I personally use following extensions (Note that I don't use eager load, so I throw exceptions from association implementations)

[ExpressionMethod(nameof(GetCsExpression))]
public static IQueryable<C> GetCs(this A) => throw new InvalidOperationException();

private static Expression<Func<A, IQueryable<C>>> GetCsExpression() => a => a.B.Select(b=> b.C);

[ExpressionMethod(nameof(GetCsExpression))]
public static IQueryable<A> GetAs(this C) => throw new InvalidOperationException();

private static Expression<Func<C, IQueryable<A>>> GetAsExpression() => c => c.B.Select(b=> b.A);

class A
{
    [PrimaryKey] public int Id {get; set; }

    [Association(ThisKey=nameof(Id), OtherKey= nameof(B.AId), CanBeNull=true)]
    public IQueryable<B> Bs => throw new InvalidOperationException();
}

class B
{
    [PrimaryKey] public int AId {get; set; }
    [PrimaryKey] public int BId {get; set; }

    [Association(ThisKey=nameof(AId), OtherKey= nameof(A.Id), CanBeNull=false)]
    public A A => throw new InvalidOperationException();

    [Association(ThisKey=nameof(BId), OtherKey= nameof(B.Id), CanBeNull=false)]
    public B B => throw new InvalidOperationException();
}

class C
{
    [PrimaryKey] public int Id {get; set; }

    [Association(ThisKey=nameof(Id), OtherKey= nameof(B.CId), CanBeNull=true)]
    public IQueryable<B> Bs => throw new InvalidOperationException();
}

if you want them as properties on A and C they should look like that I think:

// class A
[ExpressionMethod(nameof(GetCsExpression))]
public IQueryable<C> Cs => throw new InvalidOperationException();

private static Expression<Func<A, IQueryable<C>>> GetCsExpression() => a => a.B.Select(b=> b.C);

// class C
[ExpressionMethod(nameof(GetCsExpression))]
public IQueryable<A> As => throw new InvalidOperationException();

private static Expression<Func<C, IQueryable<A>>> GetAsExpression() => c => c.B.Select(b=> b.A);

``

[Dutch-0]

That looks a bit more complex than I had anticipated 😄
Thank you very much 👍

[Dutch-0]

For future references and for others:

Thanks to the examples from @MaceWindu, I manged to implement the following solution the retrieve my ManyToMany entries:
(Note: I have a custom repository method to insert and map relations between tables)

[Association(QueryExpressionMethod = nameof(ACExpression))]
public ICollection<C> Cs { get; set; }
        
public static Expression<Func<A, IDataContext, IQueryable<C>>> ACExpression =>
        (a, db) => db.GetTable<C>()
            .InnerJoin(
                db.GetTable<B>(), 
                (c, b) => 
                    c.Id == b.CId && 
                    a.Id == b.AId,
               (c, b) => c);