单表查询有关问题

单表查询问题
表中有2011.04.27到2011.05.17的每天每个时段到各个店铺采购货物的表，表中有id，shopid(购货门店的id号)，time(购货的时间),cash（到这个门店的开销）,先在统计出，每天到各个们店的次数的linq语句

[解决办法]

C# code

//假设你的表名为table,与之关联的店铺表名为shop//如下语句经过测试通过var tmp = from p in db.table          group p by p.shopid           into g         select new { g.Key,                      count = g.Count(),                     name = g.Select(q=>q.shop.name).First()                     };
[解决办法]
SQL code
create database TestDbuse TestDbcreate table shop([id] int identity primary key,[name] nvarchar(20))create table buy([id] int identity primary key,[time] nvarchar(20),[cash] int,[shopid] int foreign key references shop(id))insert into shop values('门店1')insert into shop values('门店2')insert into shop values('门店3')insert into shop values('门店4')insert into shop values('门店5')insert into buy values('2010-10-1 12:22:43',20,1)insert into buy values('2010-10-1 12:22:43',20,1)insert into buy values('2010-10-1 12:22:43',20,2)insert into buy values('2010-10-1 12:22:43',20,3)insert into buy values('2010-10-1 12:22:43',20,4)insert into buy values('2010-10-2 12:22:43',20,1)insert into buy values('2010-10-2 12:22:43',20,1)insert into buy values('2010-10-2 12:22:43',20,3)insert into buy values('2010-10-4 12:22:43',20,1)
[解决办法]
C# code
我觉得6楼的LINQ语句还有优化的空间：protected void Page_Load(object sender, EventArgs e){ if(!IsPostBack){ DataClassesDataContext db = new DataClassesDataContext();    GridView1.DataSource = from p in db.buy                           group p by new                           {                              p.shopid,                              time = SqlMethods.DateDiffDay(Convert.ToDateTime(p.time), new DateTime(1900, 1, 1))                           } into g                           orderby g.Key.time                             select new { time=g.Key.time, name = g.FirstOrDefault(q => q.shop.name), count = g.Count() };    GridView1.DataBind();}}