Join and subtract values from 2 lists using linq

Question

I have 2 lists that have objects of { DT (date), Value (double) }.

I want to join on date and subtract the 2 values. However, sometimes one list won't have any records for a given DT in which case I'd want to just use the value from the list that does. However, because I'm joining what ends up happening is I get no record at all for that DT. Is there any way to represent this using sql like linq?

I know I could loop over 1 list myself and search for that date in the other, but if I could do it all in 1 linq line it just seems cleaner.

Could you post some code? What did you try? what models are you using? — Felipe Oriani, Apr 01 '14 at 17:29
How do you know which value to subtract from which? Summing them would be straight forward, but value1 - value2 could be a different result from value2 - value1. — itsme86, Apr 01 '14 at 17:31
Take a look at http://stackoverflow.com/questions/5489987/linq-full-outer-join, this should give you enough to solve your problem — Jeff Hornby, Apr 01 '14 at 17:31
@itsme86 I know based on the lists I create which direction to go. — user441521, Apr 01 '14 at 17:31
So no outerjoin then. Probably be just as much code to just foreach() this then. Bummer. — user441521, Apr 01 '14 at 17:35
Personally, trying to turn this logic in to a 1-liner doesn't seem clean to me at all, it might end up being practically unreadable - whereas some well-written code with a comment about what you're doing would be far better. LINQ is absolutely wonderfully, but don't try and shoehorn every problem in to its' domain! :) — Moo-Juice, Apr 01 '14 at 17:45
Well if linq had better syntax for this then it would have worked. I didn't know if it did, which is why I asked the question. Now that I know it looks like a nightmare, I'll do just that Moo :) — user441521, Apr 01 '14 at 17:51
LINQ has plenty of ways you could solve this, but that still doesn't mean it's the best way to go about it. You could left join the lists and return a POCO based on your logic. But forcing it all on one-line sounds like it would be more unreadable than @Moo-Juice's solution. — Maurice Reeves, Apr 01 '14 at 18:10

score 0 · Answer 1 · answered Apr 01 '14 at 18:36

I believe this is what you can do:

var result = (from x in list1 select new Item() { date = x.date, value = x.value - (from y in list2 where x.date.Equals(y.date) select y.value).FirstOrDefault() }).ToList();

Feel free to run the test ConsoleApp I wrote:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace StackOverFlowConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            List<Item> list1 = new List<Item>()
            {
                new Item(){date = DateTime.Today, value=100},
                new Item(){date = DateTime.Today.AddDays(-1), value=100}
            };
            List<Item> list2 = new List<Item>()
            {
                new Item(){date = DateTime.Today, value=50}               
            };

            var result = (from x in list1 select new Item() { date = x.date, value = x.value - (from y in list2 where x.date.Equals(y.date) select y.value).FirstOrDefault() }).ToList();

        }

        class Item
        {

            public DateTime date { get; set; }
            public double value { get; set; }
        }


    }
}

score 0 · Answer 2 · answered Apr 01 '14 at 18:36

Say your class is named Blub and looks something like this:

public class Blub
{
    public DateTime DT { get; set; }
    public double Value { get; set; }
}

And you have two lists of it:

var list1 = new List<Blub>();
var list2 = new List<Blub>();

Then you can find the difference for each date using this LINQ query:

var differences = from x1 in list1
                  join x2 in list2 on x1.DT equals x2.DT into temp
                  from x2 in temp.DefaultIfEmpty()
                  select new Blub
                  {
                      DT = x1.DT,
                      Value = x1.Value - (x2 != null ? x2.Value : 0.0)
                  };

The DefaultIfEmpty() method turns the join into an outer join, ensuring you get a join pair of (x1, null) if there is no matching x2 for any given DT.

PS: Surely a matter of personal taste, but I don't think that this isn't readable..

Join and subtract values from 2 lists using linq

2 Answers2