LINQ to SQL用法总结

假设dbml名称为SHSunMisDataClasses.dbml，那么对应的Context为SHSunMisDataClassesDataContext,以Customer表为例:

SHSunMisDataClassesDataContext sdcd=new SHSunMisDataClassesDataContext();

 

1.增加数据:

var cus=new Customer{

UserID=1,

CreateTime=DateTime.Now,

EditTime=DateTime.Now

};

sdcd.Customer.InsertOnSubmit(cus);

sdcd.SubmitChanges();//真正往数据库插入数据

 

2.删除数据：

Customer cus=sdcd.Customer.SingleOrDefault(p=>p.CustomerID==1);

if(cus!=null)

{

sdcd.Customer.DeleteOnSubmit(cus);

sdcd.SubmitChanges();

}

 

3.修改数据

Customer cus=sdcd.Customer.SingleOrDefault(p=>p.CustomerID==1);

if(cus!=null)

{

cus.UserID=2;

cus.EditTime=DateTime.Now

}

sdcd.SubmitChanges();

 

4.查询数据：

a.获取表对象；

 Customer cus=sdcd.Customer.SingleOrDefault(p=>p.CustomerID==1);

b.获取集合：

List<Customer> list=sdcd.Customer.OrderBy(p=>p.CustomerID).Where(p=>p.UserID==1).ToList();

          获取集合中某个字段的所有值：

               List<int> CustomerIDs = list.Select(s => s.CustomerID).ToList();//获取所有的CustomerID

                        在另外一张表(PlanOfSysObj)里找到所以符合上述ID的记录

                   List<string> CustomerIDStrs = CustomerIDs.ConvertAll<string>(new Converter<int, string>(m => m.ToString())).ToList();
                        List<PlanOfSysObj> planlist = sdcd.PlanOfSysObj.Where(p => p.SysObjectID == 1 && CustomerIDStrs.Contains(p.RealtionPK)).ToList();

 

c.获取满足条件的记录数量：

int count=sdcd.Customer.OrderBy(p=>p.CustomerID).Where(p=>p.UserID==1).Count();

 

5,where条件

Expression<Func<Customer, bool>> expr = p => true;

//and条件

  expr = expr.And(p=>p.CreateTime>DateTime.Parse('2013-06-07'));

//or条件

  expr = expr.Or(p=>p.EditTime<=DateTime.Parse('2013-06-07'));

 

6.分页：

  Grid1.DataSource = GetPagedDataTable(Grid1.PageIndex, Grid1.PageSize, sort);
        Grid1.DataBind();

public List<Customer> GetPagedDataTable(int pageIndex, int pageSize)

{

return   sdcd.Customer.OrderBy(p=>p.CustomerID).Where(p=>p.UserID==1).Skip(pageIndex * pageSize).Take(pageSize).OrderBy(p => p.CustomerID).ToList();

 

}

转自：http://blog.csdn.net/zhenzhenzhao12/article/details/9044721