CODEWITHSUNDEEP
c#linq
rkmorgan
rkmorgan

Reputation: 517

Getting Time Series for a certain item with LINQ

I will try to summarize my problem:

In my product table I have different products and in productPrice table there are prices changing over time for each product.

I am trying to get result into a class like the following:

class ProductDictionary
{
    public int ProductId { get; set; }
    public IDictionary<int,double> YearValues { get; set; }
}

Basically : I need something like: (productId, Year, Value)

Product1, Dictionary( {2000, 10} {2001, 11},{2002, 13},{2003, 14},{2004, 15}}

Product2, Dictionary( {2000, 9} {2001, 11},{2002, 18},{2003, 16},{2004, 17} }

I wrote the following however it doesn't give what I want

var result = (from x in products
              join y in yearValuesForProduct2 on x.Id equals y.ProductId
              select new ProductDictionary
              {
                 ProductId = x.Id,
                  YearValues = (from z in yearValuesForProduct2
                                where z.ProductId == x.Id
                                select new { Year = z.Year, Value = z.Value })
                                    .ToDictionary(k => k.Year, k => k.Value)
              }).Distinct().ToList();

This returns :

Product1, Dictionary( {2000, 10} {2001, 11},{2002, 13},{2003, 14},{2004, 15}}

Product1, Dictionary( {2000, 10} {2001, 11},{2002, 13},{2003, 14},{2004, 15}}

Product1, Dictionary( {2000, 10} {2001, 11},{2002, 13},{2003, 14},{2004, 15}}

Product1, Dictionary( {2000, 10} {2001, 11},{2002, 13},{2003, 14},{2004, 15}}

Product1, Dictionary( {2000, 10} {2001, 11},{2002, 13},{2003, 14},{2004, 15}}

In other words repeated same result as many as the second part of the join.

Any help appreciated:

All sample code

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class ProductYearValues
{
    public int ProductId { get; set; }
    public int Year { get; set; }
    public double Value { get; set; }
}

public class ProductDictionary
{
    public int ProductId { get; set; }
    public string Name { get; set; }
    public IDictionary<int, double> YearValues { get; set; }
}


static void Main(string[] args)
{
    var newProduct1 = new Product() { Id = 1, Name = "Product 1" };
    var newProduct2 = new Product() { Id = 2, Name = "Product 2" };
    var newProduct3 = new Product() { Id = 3, Name = "Product3" };

    List<Product> products = new List<Product>() { newProduct1 };

    List<ProductYearValues> yearValuesForProduct2 = new List<ProductYearValues>()
    {
        new ProductYearValues() {ProductId =newProduct2.Id, Year = 2005, Value = 20},
        new ProductYearValues() {ProductId =newProduct2.Id, Year = 2006, Value = 22},
        new ProductYearValues() {ProductId =newProduct2.Id, Year = 2007, Value = 22},
        new ProductYearValues() {ProductId =newProduct2.Id, Year = 2009, Value = 23},
        new ProductYearValues() {ProductId =newProduct2.Id, Year = 2010, Value = 24},
        new ProductYearValues() {ProductId =newProduct2.Id, Year = 2011, Value = 25}
    };


    var result = (from x in products
                  join y in yearValuesForProduct2 on x.Id equals y.ProductId
                  select new ProductDictionary
                  {
                      ProductId = x.Id,
                      Name = x.Name,
                      YearValues = (from z in yearValuesForProduct2
                                    where z.ProductId == x.Id
                                    select new { Year = z.Year, Value = z.Value })
                                    .ToDictionary(k => k.Year, k => k.Value)


                      }).Distinct().ToList();
}

Upvotes: 1

Views: 140

Answers (1)

Chris Sinclair
Chris Sinclair

Reputation: 23208

Your test code for one doesn't add newProduct2 or newProduct3 into the comparison list, nor does it put year-values in for the other products. So I changed the test code slightly to this:

List<Product> products = new List<Product>() { newProduct1, newProduct2, newProduct3 };

List<ProductYearValues> yearValuesForProduct2 = new List<ProductYearValues>()
{
    new ProductYearValues() {ProductId = newProduct1.Id, Year = 2005, Value = 99},
    new ProductYearValues() {ProductId = newProduct1.Id, Year = 2006, Value = 45},

    new ProductYearValues() {ProductId = newProduct2.Id, Year = 2005, Value = 20},
    new ProductYearValues() {ProductId = newProduct2.Id, Year = 2006, Value = 22},
    new ProductYearValues() {ProductId = newProduct2.Id, Year = 2007, Value = 22},
    new ProductYearValues() {ProductId = newProduct2.Id, Year = 2009, Value = 23},
    new ProductYearValues() {ProductId = newProduct2.Id, Year = 2010, Value = 24},
    new ProductYearValues() {ProductId = newProduct2.Id, Year = 2011, Value = 25},

    new ProductYearValues() {ProductId = newProduct3.Id, Year = 2005, Value = 100},
    new ProductYearValues() {ProductId = newProduct3.Id, Year = 2006, Value = 77},
};

Secondly, here's a LINQ query to get your result. I think the key is to group them by the same Product:

var r = products
    .Join(yearValuesForProduct2, p => p.Id, pyv => pyv.ProductId, (p, pyv) => new { Product = p, ProductYearValue = pyv })
    .GroupBy(p => p.Product, p => p.ProductYearValue)
    .Select(p => new ProductDictionary() { 
        ProductId = p.Key.Id,  
        Name = p.Key.Name,
        YearValues = p.ToDictionary(pyv => pyv.Year, pyv => pyv.Value)})
    .ToList();

Which products this result (screenshot from LINQPad):

Result

Upvotes: 1

Related Questions