Linq left join adding data to left object

Question

I have 3 entities :

class Foo {
  public Guid? BarId { get; set; } //Has optional bar

  [NotMapped]
  public boolean? HasXyzzy { get; set; }
}

class Bar {
  ...
}

class Xyzzy {
  public Guid BarId { get; set; } //Always has Bar
}

I want to retrieve some Foo from database and fill their HasXyzzy property. I'm using LINQ to entities extension methods.

I was thinking of something like this :

var xyzzys = xyzzyRepository.GetAll(); //IQueryable

var foos = fooRepository.GetAll()
    .Where() //Some condition
    .SelectMany(
        foo => xyzzys.Where(xyzzy => xyzzy.BarId == foo.BarId).DefaultIfEmpty(),
        (foo, xyzzy) => new Foo {
            BarId = foo.BarId,
            HasXyzzy = xyzzy != null
        });

But this is quite tedious since my Foo class has a lot of properties.

What would be the correct way to do this ?

I'm pretty sure you should be ever calling `new Foo` when you are retrieving from a database. — NetMage, Feb 12 '20 at 19:10
Depends on the provider and whether you want to do this in memory or translate to a query. Check out this [link](https://learn.microsoft.com/en-us/ef/core/querying/complex-query-operators#left-join) — Oliver, Feb 12 '20 at 20:08

Roma Borodov · Accepted Answer · 2020-02-12T21:41:29.677

2

Your code:

 (foo, xyzzy) => new Foo {
                BarId = foo.BarId,
                HasXyzzy = xyzzy != null
            });

You already have a Foo as a parameter so you could change a delegate you pass inside into:

 (foo, xyzzy) =>
 { 
     foo.HasXyzzy = xyzzy;
     return foo;
 });

Correct me if I'm wrong, fooRepository brings List<Foo> and after left join you just wanna complete them with additional data from join.

UPDATE: Actually I was wrong, since we have here IQueryable which has Expression as param instead of delegate, and cannot convert lambda with a statement body into expression tree.

Now, another idea which is not the best since it will gonna use additional iterations, but still - is to convert to IEnumerable and execute the same trick.

    (foo, xyzzy) => new {
                   foo, xyzzy                   
                }).AsEnumerable()
.Select(x=> 
{ 
     foo.HasXyzzy = xyzzy;
     return foo;
});

edited Feb 12 '20 at 21:41

answered Feb 12 '20 at 17:52

Roma Borodov

596
4
10

I don't think that is going to work with LINQ to SQL/EF (6)/EF Core 2/EF Core 3. – NetMage Feb 12 '20 at 19:09
@NetMage yep, updated to AsEnumerable implementation, thanks for remarks. – Roma Borodov Feb 12 '20 at 21:45
If I'm not mistaken, AsEnumerable() will actually trigger the call to the database instead of just translating to a query, correct ? Any way to avoid this ? – Vinzz Feb 13 '20 at 14:58
Yes, everything before AsEnumerable() will be executed on db side, and everything after is on code side. Unfortunately, I dont seem to find more elegant solution. Since it suppose to be convertable to sql, it looks like its not possible in current versions of EF. – Roma Borodov Feb 13 '20 at 16:13

Linq left join adding data to left object

1 Answers1