Linq 一些操作

Linq  Left join 实现方法

 var query = from a in CreateQuery<MusicCategory>()
                   join b in CreateQuery<MusicLib>() on a.MusicCategoryID equals b.MusicCategoryID into c
                   from d in c.DefaultIfEmpty()
                   where (string.IsNullOrEmpty(searchKey) || a.MusicCategoryName.Contains(searchKey))
                    select new MusicCategoryExtend{
                             MusicCategoryID=a.MusicCategoryID,
                             MusicCategoryName=a.MusicCategoryName,
                             MusicCategoryDesc=a.MusicCategoryDesc,
                             IsUsed=d.MusicID!=null
                   };
            if (pageInfo != null)
                query = query.Paging(pageInfo);

Linq 多表关联，Max, Min, Avg

 List<StressResultExtend> list=null;

            endDate = endDate.AddHours(23).AddMinutes(59).AddSeconds(59);

            var query = from sd in CreateQuery<SignalData>()
                        join sr in CreateQuery<StressResult>()
                        on sd.SDID equals sr.SDID
                        where sd.PatientID == patientID && sd.IsValid == 0 && 
                                sd.TestDate >= startDate && sd.TestDate <= endDate
                        select new StressResultExtend()
                        {
                            SRID = sr.SRID,
                            StressVal = sr.StressVal,
                            ANSBalance = sr.ANSBalance,
                            RenitentAbility=sr.RenitentAbility,
                            ANSActivity=sr.ANSActivity,
                            TestDate=sd.TestDate,
                            SignalType=sd.SignalType
                        };
            list = query.ToList(); 
         
            var stressMonitorList = query.Where(q => q.SignalType == 0);
            var bioFeedBackList = query.Where(q => q.SignalType == 1);

            statList.Add(stressMonitorList.Count().ToString());
            statList.Add(stressMonitorList.Select(q => q.StressVal > 110).Count().ToString());
            decimal? avgStressVal = stressMonitorList.Average(q => (decimal?)q.StressVal);
            decimal? maxStressVal = stressMonitorList.Max(q => (decimal?)q.StressVal);
            decimal? minStressVal = stressMonitorList.Min(q => (decimal?)q.StressVal);

Linq 取出某一天的第一条记录

 //select * from `rmsms_signaldata` a
//    where a.`TestDate`=
//            (select min(b.TestDate) from rmsms_signaldata b where DATE(a.TestDate)=DATE(b.TestDate))
//    order by a.`TestDate` 

var queryDistinctDate = query.Where(q => q.TestDate == (query.Where( 
                                                                      p => p.TestDate.Value.Year == q.TestDate.Value.Year && 
                                                                      p.TestDate.Value.Month == q.TestDate.Value.Month && 
                                                                      p.TestDate.Value.Day == q.TestDate.Value.Day)).Min(p=>p.TestDate)).OrderBy(q=>q.TestDate);
            

Linq 音乐类型下创建相应的音乐，按音乐类型名模糊查询相关的音乐类型，并得出此类型下面是否已经创建音乐

 var queryCategory = from a in CreateQuery<MusicCategory>()
                                where (string.IsNullOrEmpty(searchKey) || 
                                              a.MusicCategoryName.Contains(searchKey))
                                select a;
            var queryLib = from b in CreateQuery<MusicLib>()
                           group b by b.MusicCategoryID into g
                           select g;
                          
            var query = from p in queryCategory
                        join q in queryLib on p.MusicCategoryID equals q.Key into cc
                        from s in cc.DefaultIfEmpty()
                        select new MusicCategoryExtend
                       {
                           MusicCategoryID = p.MusicCategoryID,
                           MusicCategoryName = p.MusicCategoryName,
                           MusicCategoryDesc = p.MusicCategoryDesc,
                           IsUsed = (s.Key!=null)
                       };
            return query.ToList();