个人感觉Linq实用灵活性很大，参考一篇大牛的文章LINQ查询返回DataTable类型
http://xuzhihong1987.blog.163.com/blog/static/26731587201101853740294/
附上自己写的一个测试程序源代码。
下载
//创建自定义DataTableString[] _sFiled =newString[] {"ID","PC","EPC","CRC","rssI","FREQANT","INVCOUNT"};
# region Linq写法//LinQ写法1：//var numbers_1 = from number in numbers where (number % 2 == 0) orderby number descending select number;//orderby number descending 这是对筛选出来的数值进行排序//foreach (var i in numbers_1)//{//Console.WriteLine(i);//}//LinQ写法2：//var numbers_1 = numbers.Where(i => i % 2 == 0).Select(i => i);//输出用写法1一样#endregionDataTable _dtAudit=CreateSelfDataTable(_sFiled);
_dtAudit.Rows.Add("a1","b1","c1","","","","");
_dtAudit.Rows.Add("a1","b2","c2","","","","");
_dtAudit.Rows.Add("a2","b3","c3","","","","");
_dtAudit.Rows.Add("a3","b4","c4","","","","");
_dtAudit.Rows.Add("a1","b5","c5","","","","");
_dtAudit.Rows.Add("a2","b6","c6","","","","");varquery1 = (fromcontactin_dtAudit.AsEnumerable()//查询orderbycontact.Field<string>("ID") descending//排序group contact by contact.Field<string>("PC") into g//分组selectnew{ID=g.FirstOrDefault().Field<string>("ID"),PC=g.FirstOrDefault().Field<string>("PC"),EPC=g.FirstOrDefault().Field<string>("EPC"),CRC=g.FirstOrDefault().Field<string>("CRC"),RSSI=g.FirstOrDefault(
).Field<string>("RSSI")}).ToList();
DataTable contacts1=ToDataTable(query1);
SetListView(listView1, contacts1);
}///<summary>///创建自定义列DataTable///</summary>///<param name="sList"></param>///<returns></returns>publicDataTable CreateSelfDataTable(String[] sList)
{
DataTable _dtSelf=newDataTable();foreach(String sinsList)
{
_dtSelf.Columns.Add(s);
}
_dtSelf.AcceptChanges();return_dtSelf;
}///<summary>///下面通过一个方法来实现返回DataTable类型///LINQ返回DataTable类型///</summary>///<typeparam name="T"></typeparam>///<param name="varlist"></param>///<returns></returns>publicstaticDataTable ToDataTable<T>(IEnumerable<T>varlist)
{
DataTable dtReturn=newDataTable();//column namesPRopertyInfo[] oProps =null;if(varlist ==null)returndtReturn;foreach(T recinvarlist)
{if(oProps ==null)
{
oProps=((Type)rec.GetType()).GetProperties();foreach(PropertyInfo piinoProps)
{
Type colType=pi.PropertyType;if((colType.IsGenericType) &&(colType.GetGenericTypeDefinition()==typeof(Nullable<>)))
{
colType= colType.GetGenericArguments()[0];
}
dtReturn.Columns.Add(newDataColumn(pi.Name, colType));
}
}
DataRow dr=dtReturn.NewRow();foreach(PropertyInfo piinoProps)
{
dr[pi.Name]= pi.GetValue(rec,null) ==null?DBNull.Value : pi.GetValue
(rec,null);
}
dtReturn.Rows.Add(dr);
}returndtReturn;
}///<summary>///绑定ListView///</summary>privatevoidSetListView(System.Windows.Forms.ListView listView, DataTable dt)
{if(listView.Items.Count >50)
{
listView.Items.Clear();
}if(dt !=null&& dt.Rows.Count >0)
{
ListViewItem lv=null;for(inti =0; i < dt.Rows.Count; i++)
{
lv=newListViewItem(dt.Rows[i][0].ToString());//创建一列的多行对象lv.Tag = dt.Rows[i][0].ToString();//该lv对象绑定多行一列值for(intj =1; j < dt.Columns.Count; j++)
{
lv.SubItems.Add(dt.Rows[i][j].ToString());//一行添加多列的值}
listView.Items.Add(lv);
}
}
}
View Code
LINQ基本语法及其示例 http://xuzhihong1987.blog.163.com/blog/static/26731587201111342756123/
说明：
1、在未作任何说明的情况下，以下用到的db变量都是DataContext的实例对象，声明语法类似如下：
using (BP_DataClassesDataContext db = new BP_DataClassesDataContext(ConnectionStrings.GetInstance("ZJ").Con))
{
//Code
}
2、默认q为Iqueryable类型变量
简单查询：
var q = from p in db.view_PreTestResultAuditList
select p;
简单条件查询：
方式一：直接使用where关键字
var q = from c in db.view_PrTestList
where c.FTestmanID.Equals(UserContext.CurrentUser.FID)
select c;
方式二：使用lambda表达式
var q = db.TOriInfoAutoMemory.Where(c => c.FSampleName == sampleName);
使用&&和||带代替And或Or关键字：
var entity = db.TSampleOriginalInfo.FirstOrDefault(c => c.FFromID.Equals(fromID) && c.FSampleID.Equals(sampleID));
like模糊条件查询：
//使用C#中的Contains关键字代替SQL中的like关键字
q = q.Where(c => c.FTaskCode.Contains(condition.Name));
In包含查询：
/// <param name="certificateIDs"> string[] </param>
var certi = (from c in db.TMSCertificate
where certificateIDs.ToList().Contains(c.FID.ToString())
select c).ToList();
等价于：select * from TMSCertificate where FID in /* certificateIDs */
Skip和Take实现分页查询：
var q = from c in db.view_PrTestList
where c.FTestmanID.Equals(UserContext.CurrentUser.FID)
select c;
twi.data = (q.Skip(paging.startIndex).Take(paging.pageSize)).ToList();
//其中startIndex：跳过的系列中指定的条数
// pageSize：每页显示条数（记录数）
Distinct方法去除重复：
var p = (from c in dc.TSampleOriginalInfo
where sampleIDs.Contains(c.FSampleID.ToString())
select new
{
c.FFromID,
c.FName
}).Distinct();
查询指定列使new构造集合：
var p = (from c in dc.TSampleOriginalInfo
where sampleIDs.Contains(c.FSampleID.ToString())
select new
{
c.FFromID,
c.FName
}).Distinct();
join连接查询：详见下面的【join查询实例】
//此处省略……
FirstOrDefault查询满足条件的第一条记录或不存在是返回null，不发生异常：
foreach (var fromID in fromIDs)
{
var entity = db.TSampleOriginalInfo.FirstOrDefault(c => c.FFromID.Equals(fromID) && c.FSampleID.Equals(sampleID));
if (entity != null)
{
entities.Add(entity);
}
}
LINQ to SQL的重要方法：SubmitChanges方法：
无论您对对象做了多少项更改，都只是在更改内存中的副本。您并未对数据库中的实际数据做任何更改。直到您对DataContext显式调用SubmitChanges方法，您所做的更改才会传输到服务器。
db.SubmitChanges();
新增/删除/修改后都需要显式的调用该方法！
以下是示例代码
///<summary>///添加///</summary>///<param name="varCustomer">要添加的对象</param>///<returns></returns>publicboolNew(TTrademark idc)
{using(CP_DataClassesDataContext db =newCP_DataClassesDataContext(GS.MODEL.ConnectionStrings.GetInstance("ZJ").Con))
{if(idc !=null)
{
db.TTrademark.InsertOnSubmit(idc);
db.SubmitChanges();returntrue;
}else{returnfalse;
}
}
}///<summary>///新增多个【使用InsertAllOnSubmit】///</summary>///<param name="model"></param>publicvoidNew(GS.MODEL.TItem model)
{using(CP_DataClassesDataContext db =newCP_DataClassesDataContext(GS.MODEL.ConnectionStrings.GetInstance("ZJ").Con))
{
List<GS.MODEL.TItem> entities =newList<GS.MODEL.TItem>();
GS.MODEL.TItem entity=newGS.MODEL.TItem()
{
FID=Guid.NewGuid(),
FName=model.FName ,
FParentName=model.FParentName,
FFastCode=model.FFastCode,
FRemark=model.FRemark,fsort=model.FSort
};
entities.Add(entity);
db.TItem.InsertAllOnSubmit(entities);
db.SubmitChanges();
}
}///<summary>///修改///</summary>///<param name="model"></param>publicvoidEdit(GS.MODEL.TItem model)
{using(CP_DataClassesDataContext db=newCP_DataClassesDataContext(GS.MODEL.ConnectionStrings.GetInstance("ZJ").Con))
{
GS.MODEL.TItem items= db.TItem.Single(c => c.FID ==model.FID);
items.FName=model.FName;
items.FParentName=model.FParentName;
items.FRemark=model.FRemark;
items.FSort=model.FSort;
items.FFastCode=model.FFastCode;
db.SubmitChanges();
}
}///<summary>///删除///</summary>///<param name="IDs"></param>publicvoidDelete(string[] ids)
{using(CP_DataClassesDataContext db =newCP_DataClassesDataContext(GS.MODEL.ConnectionStrings.GetInstance("ZJ").Con))
{
List<GS.MODEL.TItem> entities =newList<GS.MODEL.TItem>();foreach(varidinids)
{varentity = db.TItem.FirstOrDefault(c =>c.FID.Equals(id));if(entity !=null)
{
entities.Add(entity);
}
}
db.TItem.DeleteAllOnSubmit(entities);
db.SubmitChanges();
}
}///<summary>///【简单查询】获取指定计量标准考核证书下的计量标准器/检定规程/主要配套设备///</summary>///<param name="certifiacteID"></param>///<returns></returns>publicIList<view_CertificateOriginalInfo>GetViewByCertificateID(Guid certifiacteID)
{using(BPDataContext db =newBPDataContext(TCTC_ConnectionStrings.connStrJL))
{vard =fromcindb.view_CertificateOriginalInfowherec.FCertificateID ==certifiacteIDselectc;returnd.ToList();
}
}///<summary>///【综合查询】我的待检测结果任务单///</summary>///<param name="paging"></param>///<param name="condition"></param>///<returns></returns>publicTwiReturn GetMyWorkList(PagingCondition paging, ConditionModel condition)
{
TwiReturn twi=newTwiReturn();using(BP_DataClassesDataContext db =newBP_DataClassesDataContext(ConnectionStrings.GetInstance("ZJ").Con))
{varq =fromcindb.view_PrTestListwherec.FTestmanID.Equals(UserContext.CurrentUser.FID)selectc;if(condition !=null)
{if(condition.NameSign =="TaskCode"&& condition.Name !="")
{
q= q.Where(c =>c.FTaskCode.Contains(condition.Name));
}elseif(condition.NameSign =="TestItemName"&& condition.Name !="")
{
q= q.Where(c =>c.FTestItemName.Contains(condition.Name));
}
}
twi.totalCount=q.Count();if(paging.needPaging)
{
twi.data=(q.Skip(paging.startIndex).Take(paging.pageSize)).ToList();
}else{
twi.data=q.ToList();
}returntwi;
}
}///<summary>///选择计量标准考核证书///</summary>///<param name="sampleIDs"></param>///<param name="certificateIDs"></param>publicvoidSelectCertificate(string[] sampleIDs,string[] certificateIDs)
{
BPDataContext bpDC=newBPDataContext(TCTC_ConnectionStrings.connStrJL);
CPDataContext cpDc=newCPDataContext(TCTC_ConnectionStrings.connStrJL);varcerti = (fromcincpDc.TMSCertificatewherecertificateIDs.ToList().Contains(c.FID.ToString())selectc).ToList();
List<TSampleOriginalInfo> entities =newList<TSampleOriginalInfo>();foreach(varsampleIDinsampleIDs)
{foreach(varcincerti)
{
TSampleOriginalInfo entity=newTSampleOriginalInfo()
{
FID=Guid.NewGuid(),
FFromID=c.FID,
FType=1,
FSampleID=newGuid(sampleID),
FName=c.FCertificateName,
FCode=c.FCode,
FRange=c.FMeasurementRange,
FUncertainty=c.FLevel,
FCertificateNo= c.FCode,//c.FNumber,zjw修改FValidDateTo=c.FPeriodDate
};
entities.Add(entity);
}
}
bpDC.TSampleOriginalInfo.InsertAllOnSubmit(entities);
bpDC.SubmitChanges();
bpDC.Dispose();
cpDc.Dispose();
}///<summary>///获取样品之标准/计量标准器/主要配套设备/检定规程///</summary>///<param name="sampleIDs"></param>///<returns></returns>publicDataTable GetBySampleIDs(List<string>sampleIDs)
{using(BPDataContext dc =newBPDataContext(TCTC_ConnectionStrings.connStrJL))
{varp = (fromcindc.TSampleOriginalInfowheresampleIDs.Contains(c.FSampleID.ToString())selectnew{
c.FFromID,
c.FType,
c.FName,
c.FCode,
c.FRange,
c.FModel,
c.FUncertainty,
c.FCertificateNo,
c.FValidDateTo,
c.FManufacturer
}).Distinct();returnLinqToDataTable.ToDataTable(p.ToList());
}
}///<summary>///【join查询实例】获取带检测的器具信息///</summary>///<returns></returns>publicDataTable GetBySampleName(stringsampleName)
{using(BPDataContext dc =newBPDataContext(TCTC_ConnectionStrings.connStrJL))
{varsample =fromcindc.view_Sample_WithVCItemwherec.FEndTag ==1&& c.FTaskEndTag ==1&& c.FOutSourcingTag ==0&& c.FAssignTag ==1&& c.FTestCompleteTag ==0&& c.FIsOuter =="否"selectc;varr =fromsinsample
join vindc.view_GetSampleLeastOrginalRecord
on s.FID equals v.FSampleID into recsfromvinrecs.DefaultIfEmpty()
join tindc.TTask
on s.FTaskID equals t.FID into tsfromtintswhere(sampleName ==""?true: s.FName == sampleName) && (s.FReceiverID == UserContext.CurrentUser.FID || TCTC_ConnectionStrings.TestResultFilterByTester =="0")orderbys.FName, t.FCode, s.FCodeselectnew{
SampleID=s.FID,
SampleCode=s.FCode,
s.FName,
s.FAnotherName,
s.FTaskID,
s.FIsRepair,
s.FIsNeedOriginalRecord,
s.FTestProperty,
s.FRepairTag,
s.VCItemID,
s.CertiID,
s.CertiName,
s.TechID,
s.TechName,
s.TechCode,
s.FReTestTag,
s.FReTestFromTag,
v.FNumber,
v.FFileName,
v.FCertificateNo,
v.FOriginalRecordID,
v.FTag,
TaskCode=t.FCode,
t.FRemark,
t.FIsUrgent,
s.FTaskType,
s.FTaskTypeID,
s.FMeasurementRange,
s.FLevel,
s.FSigPrice
};
DataTable dt=LinqToDataTable.ToDataTable(r.ToList());returndt;
}
}