Linq2DB EF Core Join Tables In Loop

Question

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;

score 2 · Accepted Answer · answered Jul 14 '19 at 08:08

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'

this solves my problem. so just want to confirm, there is no way wtihout projection class right? — Anonymous Creator, Jul 14 '19 at 10:22
Role of `Projection` class it to ensure that query has same type for all build steps. You can replace it with anonymous type, if you don't want to add new class explicitly: `initial select: new { doc = d, field1 = (DocumentField)null, field2 = (DocumentField)null, field3 = (DocumentField)null }`, `select in join: new { d.doc, field1 = f, d.field2, d.field3 }`. If you don't do it - you will be doomed to write generation logic for all expressions in your query manually. — , Jul 14 '19 at 10:47

score 0 · Answer 2 · answered Jul 13 '19 at 22:40

0

I believe u actually need Inner join, below u can use

/* DocumentMetadataValue_1, DocumentMetadataValue_5, DocumentMetadataValue_9, DocumentMetadataValue_11 */

var query2 = (from doc1 in dbContext.Document.DocumentMetadataValue_1 
join doc5 in dbContext.Document.DocumentMetadataValue_5
on doc1.id equals doc5.id
join doc9 in dbContext.Document.DocumentMetadataValue_9
on doc5.id equals doc9.id
join doc11 in dbContext.Document.DocumentMetadataValue_11
on doc5.id equals doc11.id).ToList();

answered Jul 13 '19 at 22:40

Vatan Soni

540
2
11
23

I need these joins in loop. because it is not fixed which joins will be there. so these joins can be less or more. 1,5,9,11 are just example tables. they can be anything. – Anonymous Creator Jul 14 '19 at 06:28

Linq2DB EF Core Join Tables In Loop

2 Answers2