How to join results from two different sets in LINQ?

Question

Hi,

I get some data about customers in my database with this method:

    public List<KlientViewModel> GetListOfKlientViewModel()
    {
        List<KlientViewModel> list = _klientRepository.List().Select(k => new KlientViewModel
            {
                Id = k.Id,
                Imie = k.Imie,
                Nazwisko = k.Nazwisko,
                Nazwa = k.Nazwa,
                SposobPlatnosci = k.SposobPlatnosci,
            }).ToList();
        return list;
    }

but also I have another method which counts value for extra field in KlientViewModel - field called 'Naleznosci'.

I have another method which counts value for this field based on customers ids, it looks like this:

public Dictionary<int, decimal> GetNaleznosc(List<int> klientIds)
{
    return klientIds.ToDictionary(klientId => klientId, klientId => (from z in _zdarzenieRepository.List()
         from c in z.Klient.Cennik
         where z.TypZdarzenia == (int) TypyZdarzen.Sprzedaz && z.IdTowar == c.IdTowar && z.Sprzedaz.Data >= c.Od && (z.Sprzedaz.Data < c.Do || c.Do == null) && z.Klient.Id == klientId
         select z.Ilosc*(z.Kwota > 0 ? z.Kwota : c.Cena)).Sum() ?? 0);
}

So what I want to do is to join data from method GetNaleznosc with data generated in method GetListOfKlientViewModel. I call GetNaleznosc like this:

GetNaleznosc(list.Select(k => k.Id).ToList())

but don't know what to do next.

Answer 1

Having obtained the dictionary:

var dict = GetNaleznosc(list.Select(k => k.Id).ToList());

You can now efficiently look up the decimal value of Naleznosci for a given client:

foreach (var k in list)
    k.Naleznosci = dict[k.Id];

Now you have merged the values into the main list. Is this what you mean?

By the way, in your function that builds the dictionary, you make it accept a List<int>, but then all you do is call ToDictionary on it, which only requires IEnumerable<int>. So change the parameter type to that, and then you can call it:

var dict = GetNaleznosc(list.Select(k => k.Id));

This removes the call to ToList, which avoids making an unnecessary intermediate copy of the whole list of Ids. Probably won't make much difference in this case if you're hitting a database and then building up a large set of results in memory, but maybe worth bearing in mind for other uses of these operations.

Also, looking again at the helper function, there is no apparent advantage in building up the set of results in a dictionary for a list of ids, because each one is handled independently. You could simply put:

public decimal GetNaleznosc(int klientId)
{
    return (from z in _zdarzenieRepository.List()
      from c in z.Klient.Cennik
      where z.TypZdarzenia == (int) TypyZdarzen.Sprzedaz && z.IdTowar == c.IdTowar && z.Sprzedaz.Data >= c.Od && (z.Sprzedaz.Data < c.Do || c.Do == null) && z.Klient.Id == klientId
      select z.Ilosc*(z.Kwota > 0 ? z.Kwota : c.Cena)).Sum() ?? 0);
}

That is, provide a function that discovers just one value. Now you can directly build the right list:

public List<KlientViewModel> GetListOfKlientViewModel()
{
    return  _klientRepository.List().AsEnumerable().Select(k => new KlientViewModel
        {
            Id = k.Id,
            Imie = k.Imie,
            Nazwisko = k.Nazwisko,
            Nazwa = k.Nazwa,
            SposobPlatnosci = k.SposobPlatnosci,
            Naleznosci = GetNaleznosc(k.Id)
        }).ToList();
}