注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

飞天心宏的博客

依稀旧梦似曾见,相逢只恨缘太迟

 
 
 

日志

 
 
关于我

出身数学,爱好文学,从事软件开发工作。一个阳光、幽默、热爱生活的男孩子,在追求理想的路上风雨无阻,勇往直前,崇尚“梦想有多远,我们就走多远”!

网易考拉推荐

LINQ基本语法及其示例  

2011-02-13 16:40:35|  分类: LINQ |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

说明:

1、在未作任何说明的情况下,以下用到的db变量都是DataContext的实例对象,声明语法类似如下:

using (BP_DataClassesDataContext db = new BP_DataClassesDataContext(ConnectionStrings.GetInstance("ZJ").Con))

{

    //Code

}

2、默认qIqueryable类型变量

 

 

简单查询:

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);

 

使用 &&|| 带代替AndOr关键字:

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 */

 

SkipTake实现分页查询:

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;

            }

        }

 

 

 

                                                                                                     飞天心宏   2011-02-13

  评论这张
 
阅读(2721)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018