Left join a queryable on multiple property on a non-generic queryable using Dynamic.Linq

Question

I have 2 queryables containing the following entities:

class Entity
{
    int Id { get;set; }
}

class ExtraField
{
   int EntityId { get;set; }
   string Key { get;set; }
   string Value {get;set; }
}

Resulting in 2 queryables

IQueryable entities;
IQueryable extraFields;

An entity can have multiple extra fields. Not all entities contain the same number of extra fields. Because of that, a left join is required. The end result of the queryable should lead the following result:

Entity Id	Extra field 1	Extra field 2	Extra field 3
1	value	value	value
2	value	NULL	NULL
3	NULL	NULL	NULL

In SQL, I would like to create some kind of PIVOT to create the result above. However, I would like to achieve this with linq.

Because an entity can have x extra fields, I would need x number of joins on the extra fields table. Because the field will be not always there, i need a LEFT join.

I have spent some hours on stackoverflow and the Dynamic Linq documentation, but was not able to find an answer on how to build up the query using dynamic linq with string syntax.

I came this far:

entities.GroupJoin(extraFields, "Id", "EntityId", "new(outer.Id as Id, inner as ExtraFields)").SelectMany("ExtraFields.DefaultIfEmpty()", "new( what do i need to put here??  )");

With a generic non-dynamic linq i got this working. But what is the Dynamic Linq equivalent of this?

var result = from entity in entities
             from extraField in extraFields.Where(ef => ef.EntityId == entity.Id && ef.Key = "ExtraField1").DefaultIfEmpty()
             select new
             {
                 EntityId = entity.Id,
                 ExtraField = extraField.Value
             };

Answer 1

A possible solution would be to split the query in two parts:

var entities = new List<Entity> { new Entity { Id = 1 } }.AsQueryable();
var extraFields = new List<ExtraField>
{
    new ExtraField { EntityId = 1, Key = "ExtraField1", Value = "a" },
    new ExtraField { EntityId = 1, Key = "ExtraField2", Value = "b" }
}.AsQueryable();

// 1. Join
var dynamicJoin = entities.Join(extraFields, outerKeySelector: "Id", innerKeySelector: "EntityId", "inner");

// 2, Extra Where-statement
var dynamicJoined = dynamicJoin.Where("Key == \"ExtraField1\"").Select("new(EntityId as EntityId, Value as ExtraField)");

// Get the result
var dynamicResult = dynamicJoined.ToDynamicArray();

Result:

Answer 2

i guess you are looking how to join. its not clear what you expect to see

example:

void Main()
{
    var entities = new List<Entity>() { new Entity() {Id = 1}};
    var extraFields = new List<ExtraField>() { 
        new ExtraField() {EntityId = 1, Key = "ExtraField1", Value="a"},
        new ExtraField() {EntityId = 1, Key = "ExtraField2", Value="b"}
        };
    
    var result = from entity in entities
                 from extraField in extraFields.Where(ef => ef.EntityId == entity.Id && ef.Key == "ExtraField1").DefaultIfEmpty()
                 select new
                 {
                     EntityId = entity.Id,
                     ExtraField = extraField.Value
                 };
                result.Dump("original");
    
    entities
        .GroupJoin(
                extraFields, 
                en => en.Id,
                ext => ext.EntityId,
                (en, ext) => new { entities = en, ExtraField = ext }
            )
            .Dump("join");
            
                
}

public class Entity
{
    public int Id { get; set; }
}

public class ExtraField
{
    public int EntityId { get; set; }
    public string Key { get; set; }
    public string Value { get; set; }
}