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
             };

Do write a PIVOT query in SQL and get the data using Dapper. Much easier. — Gert Arnold, Jul 29 '21 at 19:09
I am trying to create a pivot indeed. But I rather would like to make use of the linq capabilities I also need ordering and skip/taking — Richard Mosselveld, Aug 02 '21 at 19:39

score 0 · Answer 1 · answered Jul 29 '21 at 18:36

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; }
}

I am looking for the dynamic linq methods of the Dynamic.Linq.Core library since I am working with a non-generic IQueryable. Because of that I don't have access to typed properties — Richard Mosselveld, Aug 02 '21 at 19:40
https://stackoverflow.com/questions/7364436/how-do-i-do-a-left-outer-join-with-dynamic-linq — Power Mouse, Aug 03 '21 at 15:43
I found this article when looking for a solution. However the given answer gives an error. It says the variables "inner" and "outer" are not available in the SelectMany. What do i need to put there? That is where my question is about. — Richard Mosselveld, Aug 04 '21 at 18:34

score 0 · Answer 2 · answered May 16 '22 at 09:10

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:

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

2 Answers2