SQL & Linq To SQL Help

Question

I have a table which is some thing like below..

Date             ID

 2009-07-01            1
 2009-07-01            2
 2009-07-01            3
 2009-08-01            4
 2009-08-01            5
 2009-08-01            6
 2009-09-01            7
 2009-09-01            8
 2009-10-01            9
 2009-10-01            10
 2009-11-01            11

....

Now I need to write a query which will show a output like below.

Date               Start            End
2009-07              1               3
2009-08              4               6
2009-09              7               8

...

How can I do this.. Any help would be highly appreciated Thanking In Advance Johnny

Answer 1

    tableData.GroupBy(i => i.Date).Select(i => new
          {
              DateTime = i.Key,
              Start = i.Min(j => j.ID),
              End = i.Max(j => j.ID)
          });

Answer 2

hey buddy,

i am not an expert in this but you can try the following code

var yourRows = _entities.YourTable.Where(colid => ((colid.Id == 3) || (colid.Id == 6) || (colid.Id == 8)));
return View(yourRows);

hope it works for you!!

take care

Answer 3

This might be what you're looking for:

var a = from myObj in db.MyObjs
        group myObj by myObj.Date.ToString("yyyy-mm")
            into ymGroup
            select new { Date = ymGroup.Key, Start = ymGroup.Min(c => c.ID), End = ymGroup.Max(c => c.ID) };