ansaurus

Question

LINQ Query Grabs Earliest Entry Per Other Value

Answer 1

+7 A:

var result = from tq in TableQuotes
group tq by new {tq.TimeStamp.Date, tq.Symbol} into g
select g.OrderBy(tq => tq.TimeStamp).First();

recursive 2010-07-23 21:43:36

you beat me to it ;)

Thomas Levesque 2010-07-23 21:48:18

Huh? This doesn't look like it will pull just one symbol per date.

Patrick Steele 2010-07-23 22:01:17

Won't this grab only the earliest quote for any company, not every company?

sgriffinusa 2010-07-23 22:02:40

Ahhh yes... It will work. I see how you're grouping/selecting now. Very neat and tidy. Upvoted!

Patrick Steele 2010-07-23 22:27:39

@sgriffinusa: no. it grabs the earliest record from each group, where the grouping criteria is date and symbol, so different symbol means different group.

recursive 2010-07-23 22:36:50

Thanks for explaining further. I see how it works now, pretty slick.

sgriffinusa 2010-07-23 22:50:11

The grouping takes an enormous amount of time. I appreciate the elegance of the query, but is there a faster way to do this?

Soo 2010-07-26 17:58:58

If performance is a concern, I'd make this query a view in the database, so you can get better control over the SQL.

recursive 2010-07-26 19:43:55

Answer 2

A:

This is a great resource for figuring out how to write the linq query you desire http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx

Here's a straight copy of the example they give for your situation

var categories =
    from p in products
    group p by p.Category into g
    select new { Category = g.Key, TotalUnitsInStock = g.Sum(p => p.UnitsInStock) };

JupiterP5 2010-07-23 21:47:55

What is this code pattern called: "p => p.UnitsInStock"

Soo 2010-07-23 21:49:08

That's called a lambda expression.

recursive 2010-07-23 21:50:17

anonymous delegate, using lambda syntax in this case. For a more complete answer go here -> http://stackoverflow.com/questions/978063/anonymous-delegates-in-c

JupiterP5 2010-07-23 21:52:52

Answer 3

A:

The following will return the requested solution, the earliest quote for each company on a given day.

var results = from q in quotes
              group q by new { q.Symbol, q.TimeStamp.Date } into c
              select new TableQuote()
              {
                  Symbol = c.Key.Symbol,
                  TimeStamp = c.Min(ct => ct.TimeStamp),
                  Quote = c.OrderBy(ct => ct.TimeStamp).First().Quote
              };

sgriffinusa 2010-07-23 22:14:34

ansaurus

tags:

views:

answers:

LINQ Query Grabs Earliest Entry Per Other Value

related questions