Linq2DB EF Core Join Tables In Loop

Question

I am using linq2db.EntityFrameworkCore

I need to join to DocumentMetadataValue table join to Document table dynamically.

Left joined tables can be like DocumentMetadataValue_1, DocumentMetadataValue_5, DocumentMetadataValue_9, DocumentMetadataValue_11 etc.

How do I achieve this using Linq2Db.

Please find below code. This is just to explain the problem. So it doesn't work as it is the question.

var query2 = from p in dbContext.Document

foreach (JObject childRule in queryRule.rules)
{

    ChildRule rule = childRule.ToObject<ChildRule>();
    string DocumentFieldTable = string.Format("DocumentField_{0}", rule.id);

    //Here I need left join to query2.
    from op in projectContext.Set<DocumentMetadataValue>().ToLinqToDBTable().TableName(DocumentFieldTable).LeftJoin(op => op.DocumentId == p.Id);        
}
 //After above selection I will apply where clause here and will select p from query2.
 select p;

Answer 1

If I understood what you want, you need to write something like that (pure linq2db, so don't forget to add ToLinqToDBTable calls):

// define typed projection to include all possible joined tables
// otherwise you will need to write complex logic to build expressions
class Projection
{
    public Document doc { get; set; }
    public DocumentField field1 { get; set; }
    public DocumentField field2 { get; set; }
    public DocumentField field3 { get; set; }
}

// initial query typed as IQueryable<Projection>
var query = db.GetTable<Document>().Select(d => new Projection() { doc = d });
// select required joins (replace it with your query rules logic)
var with1 = true;
var with2 = false;
var with3 = true;

// add requested joins, note how we copy records
// from previous query to new projection
if (with1)
    query = query.LeftJoin(
        db.GetTable<DocumentField>().TableName("field_1"),
        (d, f) => d.doc.Id == f.DocumentId,
        (d, f) => new Projection () { doc = d.doc, field1 = f });
if (with2)
    query = query.LeftJoin(
        db.GetTable<DocumentField>().TableName("field_2"),
        (d, f) => d.doc.Id == f.DocumentId,
        (d, f) => new Projection() { doc = d.doc, field1 = d.field1, field2 = f });
if (with3)
    query = query.LeftJoin(
        db.GetTable<DocumentField>().TableName("field_3"),
        (d, f) => d.doc.Id == f.DocumentId,
        (d, f) => new Projection() { doc = d.doc, field1 = d.field1, field2 = d.field2, field3 = f });

// add filters
if (with1)
    query = query.Where(r => r.field1.FilterMe == "test1");
if (with2)
    query = query.Where(r => r.field2.FilterMe == "test2");
if (with3)
    query = query.Where(r => r.field3.FilterMe == "test3");

// select only documents
query.Select(r => r.doc).ToArray();

result:

SELECT
    [d].[Id]
FROM
    [Document] [d]
        LEFT JOIN [field_1] [f_1] ON [d].[Id] = [f_1].[DocumentId]
        LEFT JOIN [field_3] [f_2] ON [d].[Id] = [f_2].[DocumentId]
WHERE
    [f_1].[FilterMe] = N'test1' AND [f_2].[FilterMe] = N'test3'