CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-entitiesouter-join
kaze
kaze

Reputation: 4359

Can't get outer join to work in EF

Never mind, I solved it with the join/into syntax instead. Perhaps that is necessary after all

I'm trying to get a Linq outer join to work against EntitiyFramework.

public List<OSCDagbokDTO> FillDataForOSCDagbokSO(List<OSCDagbokDTO> oscdagboklista)
{
    var kalla_idn = oscdagboklista.Select(k => k.Kalla_id.GetGuidOrNull()).Distinct().ToList();
    var kallaLista = (from k in _gemensamEntityContext.Kalla
                      where kalla_idn.Contains(k.Kalla_id)
                      select new KallaDTO()
                      {
                          Kalla_id = k.Kalla_id,
                          KallaText = k.KallaText
                      }).ToList();

    var nyOSCDagbokLista = (from o in oscdagboklista
                            from k in kallaLista.DefaultIfEmpty()
                            where o.Kalla_id.GetGuidOrNull() == k.Kalla_id
                            select new OSCDagbokDTO()
                            {
                                Id = o.id,
                                Datum = o.Datum,
                                Enhet_id = o.Enhet_id,
                                Handelse = o.Handelse,
                                Kalla = k,
                                Kalla_id = o.Kalla_id,
                            }).ToList();
    return nyOSCDagbokLista;
}

The first statement is getting a list of ids from a properpty in the incoming list.

The second, creating DTO objects from that list.

The third, using (as I thought) an outer join to get ALL items from the incoming list, joining with the DTO-list (kallaLista). I'm using DefaultIfEmpty(), which I thought was going to NOT filter out items from the incoming list, but it does!

I'm not using the "join" syntax, as I've understood it from various sites, the above syntax should be ok, and I like it better even though it's more old fashioned.

What am I missing, why does items from the incoming list gets filtered out when not in the DTO-list, when using DefaultIfEmpty()?

Upvotes: 2

Views: 214

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109109

Might I add that there is a very concise way of performing an outer join if there is a navigation property from a parent entity to a child collection?

Suppose you have an entity OSCDagbok (I'm just guessing the singular form) with a navigation property kallaLista. Then you could do:

from o in oscdagboklista
from k in o.kallaLista.DefaultIfEmpty() // mind the "o."
select new OSCDagbokDTO()
...

If not, you can follow your solution. The into turns a join into a GroupJoin which is the linq equivalent of an outer join.

Upvotes: 1

Related Questions