Linq 实用查询

1.DataTable表连接，两个DataTable的左连接

        private List<object> GetDataByExcel(string fileName)
        {
            //把刚上传的这个excel文件中的内容查询出来
            string connStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"", fileName);
            string sql = "select [主体帐簿名称],[科目名称],[客商辅助核算名称] from [Sheet1$]";
            DataTable dt = new SqlHelper().GetDataTable_ACCESS(connStr, sql);

            string sqlNC = @"SELECT sd1.Name AS ZhangTao,sd.Name AS KeMu,bn.NCName,bn.CustomerID FROM dbo.Biz_NCustomer AS bn
                INNER JOIN Sys_Dict AS sd ON bn.KemuID=sd.ID INNER JOIN Sys_Dict AS sd1 ON bn.ZhangtaoID=sd1.ID";
            DataTable dtNC = new SqlHelper().GetDataTable(sqlNC);

            var query = from qdt in dt.AsEnumerable().Where(x => x.Field<string>("客商辅助核算名称") != string.Empty)
                        join qdtNC in dtNC.AsEnumerable()
                        on new { zt = qdt.Field<string>("主体帐簿名称"), km = qdt.Field<string>("科目名称"), nc = qdt.Field<string>("客商辅助核算名称") }
                        equals new { zt = qdtNC.Field<string>("ZhangTao"), km = qdtNC.Field<string>("KeMu"), nc = qdtNC.Field<string>("NCName") }
                        into temp
                        from tt in temp.DefaultIfEmpty()
                        select new
                        {
                            CustomerID = tt == null ? 0 : tt.Field<int>("CustomerID"),
                            ZhangTao = qdt.Field<string>("主体帐簿名称"),
                            KeMu = qdt.Field<string>("科目名称"),
                            NCName = qdt.Field<string>("客商辅助核算名称")
                        };

            List<object> list = new List<object>();
            foreach (var obj in query)
            {
                list.Add(new
                {
                    CustomerID = obj.CustomerID,
                    Zhangtao = obj.ZhangTao,
                    Kemu = obj.KeMu,
                    NCName = obj.NCName
                });
            }
            return list;
        }

写法二

            var query1 = from qdt in dt.AsEnumerable().Where(x => x.Field<string>("客商辅助核算名称") != string.Empty)
                         from qdtNC in dtNC.AsEnumerable()
                         where qdt.Field<string>("主体帐簿名称") == qdtNC.Field<string>("ZhangTao")
                          && qdt.Field<string>("科目名称") == qdtNC.Field<string>("KeMu")
                          && qdt.Field<string>("客商辅助核算名称") == qdtNC.Field<string>("NCName")
                         select new
                         {
                             CustomerID = qdtNC.Field<int>("CustomerID"),
                             ZhangTao = qdt.Field<string>("主体帐簿名称"),
                             KeMu = qdt.Field<string>("科目名称"),
                             NCName = qdt.Field<string>("客商辅助核算名称")
                         };

 2.将相同类型的多个List<T>集合合并成一个集合，集合A中包含N和相同类型的子集合

var TJlbYclItems = TJlbList.SelectMany(x => x.TJlbYcl_Items).ToList();

写法二

var listOfList = new List<List<int>>() {
    new List<int>() { 1, 2 },
    new List<int>() { 3, 4 },
    new List<int>() { 5, 6 }
};
var list = new List<int> { 9, 9, 9 };
var result = list.Concat(listOfList.SelectMany(x => x));

foreach (var x in result) Console.WriteLine(x);

3.List分组，量求和

 

        /// <summary>
        /// 合并材料，量求和
        /// </summary>
        /// <param name="dataRows"></param>
        /// <returns></returns>
        public object SumTasksMaterial(List<Biz_Audit_TaskMaterialItemAmount_Mod> dataRows)
        {
            var result = from p in dataRows
                         group p by new { p.MaterialSpecID, p.MaterialName, p.MaterialSpec } into g
                         select new
                         {
                             g.Key.MaterialSpecID,
                             g.Key.MaterialName,
                             g.Key.MaterialSpec,
                             OutSum = g.Sum(p => p.OutSum)
                         };

            return result;
        }