Get a list of tuples using Linq

Question

I've got a list of time-related entites that are point-of-time-centric. E.g. each object PricesAtTimeX contains information about price of apples, price of banans for one specific point of time X.

Those I would like to transform to a JSON, more timeline-centric format using linq. E.g. an object BananaPrices that basically consists of a list of [date, value].

The concrete starting point PricesAtTimeX-class

public class PricesAtTimeX
{
    public int ID { get; set; }
    public DateTime Date { get; set; }
    public int BananaPrice{ get; set; }
    public int ApplePrice{ get; set; }
}

What I would love to make out of it:

[
    {
        "key": "BananaPrices",
        "values": [[date1, bananaPrice1], [date2, bananaPrice2] ... ],
    },
    {
        "key": "ApplePrices",
        "values": [[date1, applePrice1], [date2, applePrice2] ... ],
    }
]

My question is: How to get a list of (int, int) with Linq ?

My first attempt was to define a class that could be used to shell the data:

public class dataLine
{
    public string key { get; set; }
    // first tuple-int would be date converted to javascript datetime
    public List<Tuple<int, int>> values { get; set; }
}

.. and then try to fill that with Linq:

var result = from x in db.Prices
             select new List<dataLine>
             {
                 new dataLine() {
                     key = "ApplePrices",
                     values = ???
                 }
             };
return Json(result, JsonRequestBehavior.AllowGet);

Another approach would be to pack all values into separate Lists..

var xValues = from x in db.Prices select new List<DateTime>() { x.Date };
var yBananas = from x in db.Prices select new List<int>() { x.BananaPrice};
var yApples = from x in db.Prices select new List<int>() { x.ApplePrice};

.. and then use the .Zip-method to merge each of the Prices-Lists with the Date-List

public List<Tuple<int, int>> TupleMe(List<int> list1, List<int> list2)
{
    return list1.Zip(list2, Tuple.Create).ToList();
}

I'd be thankful for any suggestions, hints or ideas!

You are better off creating a simple class like public class ItemPrice { public string Desc{get;set;} public double Price{get;set;}} and then in your PricesAtTimeX class create a public List<ItemPrice> Items{ get; set; } then simply do linq like var banana = PricesAtTimeX.Items.Find(i=> i.Desc =="Banana"); — Franck
– Franck, Commented Apr 9, 2014 at 11:32

sloth · Accepted Answer · 2014-04-09 11:58:54Z

1

A simple Select and using an object[] should do what you want.

Usually you would use a custom type, a KeyValuePair or a Tuple to group Date and Banana-/ApplePrice together (for typesafety), but since you're going to create a JSON string anyway, using anonymous types and a simple object[] is the easiest way to go.

var items = new []
{
    new PricesAtTimeX
    {
        ID = 1,
        Date = DateTime.Now.AddDays(-3),
        ApplePrice = 10,
        BananaPrice = 20
    },
    new PricesAtTimeX
    {
        ID = 1,
        Date = DateTime.Now.AddDays(-2),
        ApplePrice = 12,
        BananaPrice = 20
    },
    new PricesAtTimeX
    {
        ID = 1,
        Date = DateTime.Now.AddDays(-1),
        ApplePrice = 14,
        BananaPrice = 10
    },
    new PricesAtTimeX
    {
        ID = 1,
        Date = DateTime.Now,
        ApplePrice = 17,
        BananaPrice = 7
    },
};

// maybe cache 'items' if you're running LINQ against a database
// and if you're not wanting to hit the database multiple times.
var result = new[]
{
    new 
    {
        key = "BananaPrices",
        values = items.Select(i => new object[]{i.Date, i.BananaPrice})
    },
    new 
    {
        key = "ApplePrices",
        values = items.Select(i => new object[]{i.Date, i.ApplePrice})
    },
};

var json = JsonConvert.SerializeObject(result);

json is now (formatted for readability):

[
 {"key":"BananaPrices","values":[["2014-04-06T13:39:01.109062+02:00",20],["2014-04-07T13:39:01.109062+02:00",20],["2014-04-08T13:39:01.109062+02:00",10],["2014-04-09T13:39:01.109062+02:00", 7]]},
 {"key":"ApplePrices" ,"values":[["2014-04-06T13:39:01.109062+02:00",10],["2014-04-07T13:39:01.109062+02:00",12],["2014-04-08T13:39:01.109062+02:00",14],["2014-04-09T13:39:01.109062+02:00",17]]}
]

edited Apr 9, 2014 at 11:58

answered Apr 9, 2014 at 11:40

sloth

101k21 gold badges182 silver badges224 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

peter Over a year ago

This looks perfect, it just gives me "Unable to cast type 'System.DateTime' to type 'SystemObject'. LINQ to Entities only supports casting EDM primitive or enumeration types."

sloth Over a year ago

Yes, this works only with LINQ-to-objects as it currently is. Since you query all items from the table, fetch and cache all items before doing the select. Something like var items = db.Prices.ToList(). This also makes sure that the database is queried only once.

peter Over a year ago

It took a while, but I got it :o) Thank you VERY much, Dominic!

Felipe Oriani · Accepted Answer · 2014-04-09 11:43:39Z

1

Try using the let to separete the logic of query in a sub-query and apply it on the result, for sample:

var result = from x in db.Prices
             let t = (from p in db.Prices select new { x.Date, x.BananaPrice, x.ApplePrice }
             select new List<dataLine>
             {
                 new dataLine() {
                     key = "ApplePrices",
                     values = t1.Select(t => Tuple.Create(t.Date, t.BananaPrice))
                 }
             };

answered Apr 9, 2014 at 11:43

Felipe Oriani

38.7k20 gold badges141 silver badges203 bronze badges

Collectives™ on Stack Overflow

Get a list of tuples using Linq

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related