Linq/Lambda查询 相关语法案例

Lambda表达式之查询篇

https://www.cnblogs.com/netlws/p/9490871.html

lambda表达式多条件查询

https://www.cnblogs.com/wy1992/p/6904442.html

C# LINQ语法详解

https://www.cnblogs.com/sxjljj/p/11348652.html

CTE 递归查询全解

https://www.cnblogs.com/ljhdo/p/4580347.html

CTE在Oracle和Sqlserver中使用的差异

https://blog.csdn.net/zouqingfang/article/details/9771681

http://vlambda.com/wz_5lBZKpN7dM7.html

如何在EF中实现left join（左联接）查询

https://www.cnblogs.com/beyond1983/p/5743445.html

Linq分组及排序，取前N条记录

https://blog.csdn.net/lqh4188/article/details/51444094

https://blog.csdn.net/make1828/article/details/54632786

Linq日期差值计算,Linq日期比较方法

https://blog.csdn.net/ningxi_/article/details/72828741

C# 递归构造树状数据结构（泛型），如何构造？如何查询？

https://www.cnblogs.com/chenwolong/p/asWith.html

WITH org("Id","No","Name","ParentId","LeaderId","Leader","Comment","Sort","OrgTypeId","OrgType","CreateUser","CreateTime","UpdateUser","UpdateTime") AS
(
    SELECT "Id","No","Name","ParentId","LeaderId","Leader","Comment","Sort","OrgTypeId","OrgType","CreateUser","CreateTime","UpdateUser","UpdateTime" FROM "ZTHROrganization" WHERE "Id"='20191008-1736-2676-7000-2608445F6318'
    UNION ALL
    SELECT z."Id",z."No",z."Name",z."ParentId",z."LeaderId",z."Leader",z."Comment",z."Sort",z."OrgTypeId",z."OrgType",
        z."CreateUser",z."CreateTime",z."UpdateUser",z."UpdateTime" FROM "ZTHROrganization" z 
    INNER JOIN org o ON z."ParentId" = o."Id"
)
SELECT * FROM org ORDER BY "ParentId"

public static void QueryCTESub(string id)
        {
            using (var db = Chaolj.DAL.ZTHR.ZTHRContext.Create())
            {
                string cteSql = @"WITH org(""Id"",""No"",""Name"",""ParentId"",""LeaderId"",""Leader"",""Comment"",""Sort"",""OrgTypeId"",""OrgType"",""CreateUser"",""CreateTime"",""UpdateUser"",""UpdateTime"") AS "
                                +"(select "
                                +@"""Id"",""No"",""Name"",""ParentId"",""LeaderId"",""Leader"",""Comment"",""Sort"",""OrgTypeId"",""OrgType"",""CreateUser"",""CreateTime"",""UpdateUser"",""UpdateTime"" FROM ""ZTHROrganization"" WHERE ""Id""='20191008-1736-2676-7000-2608445F6318' "
                                +@"UNION ALL "
                                +@"SELECT z.""Id"",z.""No"",z.""Name"",z.""ParentId"",z.""LeaderId"",z.""Leader"",z.""Comment"",z.""Sort"",z.""OrgTypeId"",z.""OrgType"",z.""CreateUser"",z.""CreateTime"",z.""UpdateUser"",z.""UpdateTime"" FROM ""ZTHROrganization"" z "
                                +@"INNER JOIN org o ON z.""ParentId"" = o.""Id"")"
                                +@"SELECT * FROM org ORDER BY ""ParentId""";
                string ctesql = @"WITH org AS( SELECT * FROM ""ZTHROrganization"")SELECT * FROM org";
                var list = db.ZTHROrganization.SqlQuery(cteSql).ToList();
            }
        }

分组多字段求和

query.GroupBy(q => new { q.Year, q.Month })
    .Select(q => new
    {
        Year = q.Key.Year,
        Month = q.Key.Month,
        BuildAmount = q.Sum(i => i.BuildAmount),
        RecAmount = q.Sum(i => i.RecAmount),
        Amount = q.Sum(i => i.Amount),
        RealAmount = q.Sum(i => i.RealAmount)
    });

不分组多字段求和（这样得到的就是对应字段的总的求和，其实还是利用了分组，不过给分组依据传个空，如果利用linq的话就是传个常数）

where.GroupBy(x => new { }).Select(q => new
                {
                    sumWeight = q.Sum(x => x.Weight),
                    sumQuantity = q.Sum(x => x.Quantity),
                    sumIncome = q.Sum(x => x.Income)
                }).FirstOrDefault();

 分组求和IQueryable

var materialModel = from m in qry
                    group m by new { m.ItemSubClass } into g
                    select new
                    {
                         ItemSubClass = g.Key.ItemSubClass,
                         Amount = g.Sum(m => m.Amount),
                         Qty = g.Sum(m => m.Qty)
                    };

 IQueryable.Join方法

model = db.ZTHREmployee.Join(db.ZTHREmployeeContacts, e => e.Id, c => c.EmployeeId, (e, c) => new { 
                    e.Id,
                    e.ArchivesNo,
                    e.CreateTime,
                    e.CreateUser,
                    e.Files,
                    e.IDCard,
                    e.IDCardVerifier,
                    e.IDCardVerifierId,
                    e.InsurancePaymentPlace,
                    e.LeaveDate,
                    e.LeaveReasons,
                    e.LeaveType,
                    e.LeaveTypeId,
                    e.Name,
                    e.No,
                    e.PositiveTime,
                    e.RecruitmentPlace,
                    e.RecruitmentWay,
                    e.RemainAnnualLeave,
                    e.SocialSecurityNo,
                    e.StartingTime,
                    e.Status,
                    e.Type,
                    e.TypeId,
                    e.UpdateTime,
                    e.UpdateUser,
                    e.WorkPlace,
                    c.EMail,
                    c.EmployeeId,
                    c.FirstContactPhone,
                    c.FirstEmergencyContact,
                    c.HomePhone,
                    ContactsId = c.Id,
                    c.MobileNo,
                    c.ParentAddress,
                    c.ParentalContact,
                    c.PresentAddress,
                    c.SecondContactPhone,
                    c.SecondEmergencyContact
                }).Where(p => p.Id == id).FirstOrDefault();
            }

 EF使用Group By 时生成的sql语句会产生APPLY语法，但Oracle11 c 不支持，此时只有使用sql语句解决

Oracle关于如何获取分组排序后的第一条数据

--方式1
SELECT t1.*,t2.nums FROM "PS_QualityMargin" t1,
(
    SELECT b."ProjectNo",b."SupplierNo", COUNT(*) nums, MAX("Date") "dt" FROM "PS_QualityMargin" b GROUP BY b."ProjectNo",b."SupplierNo"
) t2
WHERE t1."ProjectNo" = t2."ProjectNo" AND t1."Date" = t2."dt";



--方式2
SELECT * FROM 
(
    SELECT t.*, ROW_NUMBER() OVER(PARTITION BY "ProjectNo","SupplierNo" ORDER BY "Date" DESC) rn,COUNT(*) OVER(PARTITION BY "ProjectNo","SupplierNo") nums FROM "PS_QualityMargin" t
)
WHERE rn = 1;


---推荐使用方式2，row_number()是比rownum更强大的伪列。

Linq 分组（group by）求和（sum）并且按照分隔符（join）分割列数据

 https://www.cnblogs.com/zq281660880/archive/2012/09/26/2704836.html

用符号分隔

var query = from c  in t.AsEnumerable()
                        group c by new {
                            pingming = c.Field<string>("品名"),
                            guige = c.Field<string>("规格")
                                        }
                            into s
                            select new
                            {
                                pingming = s.Select(p => p.Field<string>("品名")).First(),
                                shuliang = s.Sum(p => Convert.ToInt32(p.Field<string>("数量"))),
                                guige = s.Select(p => p.Field<string>("规格")).First(),
                                biaohao = string.Join(";",s.Select(p => p.Field<string>("表号")))
                            };

             DataTable tbl = tableA1.Clone();
             query.ToList().ForEach(p => tbl.Rows.Add(p.pingming, p.guige,p.biaohao,p.shuliang));