LINQ基本语法及其示例
2011-02-13 16:40:35| 分类: LINQ | 标签:linq 查询语法 in linq实现分页 |字号大中小 订阅
说明:
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>
public bool New(TTrademark idc)
{
using (CP_DataClassesDataContext db = new CP_DataClassesDataContext(GS.MODEL.ConnectionStrings.GetInstance("ZJ").Con))
{
if (idc != null)
{
db.TTrademark.InsertOnSubmit(idc);
db.SubmitChanges();
return true;
}
else
{
return false;
}
}
}
/// <summary>
/// 新增多个【使用InsertAllOnSubmit】
/// </summary>
/// <param name="model"></param>
public void New(GS.MODEL.TItem model)
{
using (CP_DataClassesDataContext db = new CP_DataClassesDataContext(GS.MODEL.ConnectionStrings.GetInstance("ZJ").Con))
{
List<GS.MODEL.TItem> entities = new List<GS.MODEL.TItem>();
GS.MODEL.TItem entity = new GS.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>
public void Edit(GS.MODEL.TItem model)
{
using(CP_DataClassesDataContext db=new CP_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>
public void Delete(string[] ids)
{
using (CP_DataClassesDataContext db = new CP_DataClassesDataContext(GS.MODEL.ConnectionStrings.GetInstance("ZJ").Con))
{
List<GS.MODEL.TItem> entities = new List<GS.MODEL.TItem>();
foreach (var id in ids)
{
var entity = 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>
public IList<view_CertificateOriginalInfo> GetViewByCertificateID(Guid certifiacteID)
{
using (BPDataContext db = new BPDataContext(TCTC_ConnectionStrings.connStrJL))
{
var d = from c in db.view_CertificateOriginalInfo
where c.FCertificateID == certifiacteID
select c;
return d.ToList();
}
}
/// <summary>
/// 【综合查询】我的待检测结果任务单
/// </summary>
/// <param name="paging"></param>
/// <param name="condition"></param>
/// <returns></returns>
public TwiReturn GetMyWorkList(PagingCondition paging, ConditionModel condition)
{
TwiReturn twi = new TwiReturn();
using (BP_DataClassesDataContext db = new BP_DataClassesDataContext(ConnectionStrings.GetInstance("ZJ").Con))
{
var q = from c in db.view_PrTestList
where c.FTestmanID.Equals(UserContext.CurrentUser.FID)
select c;
if (condition != null)
{
if (condition.NameSign == "TaskCode" && condition.Name != "")
{
q = q.Where(c => c.FTaskCode.Contains(condition.Name));
}
else if (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();
}
return twi;
}
}
/// <summary>
/// 选择计量标准考核证书
/// </summary>
/// <param name="sampleIDs"></param>
/// <param name="certificateIDs"></param>
public void SelectCertificate(string[] sampleIDs, string[] certificateIDs)
{
BPDataContext bpDC = new BPDataContext(TCTC_ConnectionStrings.connStrJL);
CPDataContext cpDc = new CPDataContext(TCTC_ConnectionStrings.connStrJL);
var certi = (from c in cpDc.TMSCertificate
where certificateIDs.ToList().Contains(c.FID.ToString())
select c).ToList();
List<TSampleOriginalInfo> entities = new List<TSampleOriginalInfo>();
foreach (var sampleID in sampleIDs)
{
foreach (var c in certi)
{
TSampleOriginalInfo entity = new TSampleOriginalInfo()
{
FID = Guid.NewGuid(),
FFromID = c.FID,
FType = 1,
FSampleID = new Guid(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>
public DataTable GetBySampleIDs(List<string> sampleIDs)
{
using (BPDataContext dc = new BPDataContext(TCTC_ConnectionStrings.connStrJL))
{
var p = (from c in dc.TSampleOriginalInfo
where sampleIDs.Contains(c.FSampleID.ToString())
select new
{
c.FFromID,
c.FType,
c.FName,
c.FCode,
c.FRange,
c.FModel,
c.FUncertainty,
c.FCertificateNo,
c.FValidDateTo,
c.FManufacturer
}).Distinct();
return LinqToDataTable.ToDataTable(p.ToList());
}
}
/// <summary>
/// 【join查询实例】获取带检测的器具信息
/// </summary>
/// <returns></returns>
public DataTable GetBySampleName(string sampleName)
{
using (BPDataContext dc = new BPDataContext(TCTC_ConnectionStrings.connStrJL))
{
var sample = from c in dc.view_Sample_WithVCItem
where c.FEndTag == 1 && c.FTaskEndTag == 1 && c.FOutSourcingTag == 0 && c.FAssignTag == 1 && c.FTestCompleteTag == 0 && c.FIsOuter == "否"
select c;
var r = from s in sample
join v in dc.view_GetSampleLeastOrginalRecord
on s.FID equals v.FSampleID into recs
from v in recs.DefaultIfEmpty()
join t in dc.TTask
on s.FTaskID equals t.FID into ts
from t in ts
where (sampleName == "" ? true : s.FName == sampleName) && (s.FReceiverID == UserContext.CurrentUser.FID || TCTC_ConnectionStrings.TestResultFilterByTester == "0")
orderby s.FName, t.FCode, s.FCode
select new
{
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());
return dt;
}
}