linq 使用示例

 

最近做项目用到linq 网上找示例东拼西凑 麻烦 刚好有段代码很多语法都用到了 就贴出来跟大家分享一下

  //这里得到将要查询的数据集 List DataTable 都行
List<Model.ProjectSaleInfo> psiList ;
List<Model.StoreSystem> ssList ;        
List<Model.City> cityList ;     
List<Model.ProjectSaleDetail> psdList;
List<Model.KeyValueList> kvlList;
//在进行linq查询之前 一定要先判断要查询集是否为null
            if (psiList == null || ssList == null || cityList==null||psdList==null||psdList==null||kvlList==null)
            {
                return;
            }
            try
            {
                //动态给linq添加where条件 由于只找到单张表动态条件的列子 所以在联合查询前先单张表过滤 先把问题解决了  以后找找看看多表的动态条件
              psiList = (from psi in psiList select psi).AsQueryable<Model.ProjectSaleInfo>().Where(psi => psi.ProjectID == ProjectID).ToList<Model.ProjectSaleInfo>();
            
                if (Framework.Utils.IsDateString(txtDate.Text))
                {
                    DateTime minDate = DateTime.Parse(txtDate.Text);
                    psiList = (from psi in psiList select psi).AsQueryable<Model.ProjectSaleInfo>().Where(psi => psi.WorkDate >= minDate).ToList<Model.ProjectSaleInfo>();
                }
                if (Framework.Utils.IsDateString(TextBox1.Text))
                {
                    DateTime maxDate = DateTime.Parse(TextBox1.Text);
                    psiList = (from psi in psiList select psi).AsQueryable<Model.ProjectSaleInfo>().Where(psi => psi.WorkDate <= maxDate).ToList<Model.ProjectSaleInfo>();
                }


                //多表左连接查询
                var data = (from psi in psiList
                            join ss in ssList on psi.ShopID equals ss.StoreSystemID into psiss
                            from s in psiss.DefaultIfEmpty(new Model.StoreSystem { StoreSystemName = null })
                            join city in cityList on s.CityID equals city.CityID into psicity
                            from c in psicity.DefaultIfEmpty(new Model.City { CityID = null, CityName = null })
                            join psd in psdList on new { a = psi.WorkDate.HasValue ? "" : psi.WorkDate.ToString(), b = Convert.ToInt32(psi.ShopID), c = psi.ProjectID } equals new { a = psd.WorkDate.HasValue ? "" : psd.WorkDate.ToString(), b = Convert.ToInt32(psd.ShopID), c = psd.ProjectID } into psipsd
                            from p in psipsd.DefaultIfEmpty(new Model.ProjectSaleDetail { GroupBuyCount = -1, IsOutStock = -1, ProductName = null })
                            join kvl in kvlList on psi.IsDisplay equals kvl.DataKey into psikvl
                            from k in psikvl.DefaultIfEmpty(new Model.KeyValueList { DataValue = null, KeyValueDesc = null })
                            orderby psi.WorkDate
                            select new
                            {
                                c.CityName,
                                s.StoreSystemName,
                                psi.WorkDate,
                                p.GroupBuyCount,
                                p.IsOutStock,
                                p.ProductName,
                                psi.IsDisplay,
                                k.DataValue,
                                psi.IsStack
                            });

                //循环组装table
                foreach (var d in data)
                {
                                   }

 一些有关的ling数据转换的方法

 

public static System.Data.DataTable LinqToDataTable<T>(IEnumerable<T> data)
        {
            var dt = new System.Data.DataTable();
            var ps = typeof(T).GetProperties().ToList();
            ps.ForEach(p => dt.Columns.Add(p.Name, p.PropertyType));//这里如果报“DataSet 不支 System.Nullable<>。”   的错误 就去掉p.PropertyType  
//写成 ps.ForEach(p => dt.Columns.Add(p.Name));
       foreach (T t in data)
            {
                var dr = dt.NewRow();
                var vs = from p in ps select p.GetValue(t, null);
                var ls = vs.ToList(); int i = 0; ls.ForEach(c => dr[i++] = c); dt.Rows.Add(dr);
            }
            return dt;
        }