Linq 和 lambd

这里说的不是二者的区别，而是二者的联合应用，因为好多函数在sql拼接的时候不支持，所以检索数据需要联合使用。

今天不去详细的阐述理论，只是放置一个我项目中的实例，以备自己以后查阅，也给有需要的朋友一个参考机会。

如下代码可靠可行：

 var datable = db.SalerTransaction.WhereIf(o => o.saler_username == ventity.saler_username.Trim(), !string.IsNullOrWhiteSpace(ventity.saler_username))
                    .WhereIf(o => o.cus_username == ventity.cus_username.Trim(), !string.IsNullOrWhiteSpace(ventity.cus_username))
                    .WhereIf(o => o.saler_type == ventity.saler_type, !string.IsNullOrWhiteSpace(ventity.saler_type))
                    .WhereIf(o => o.cur_versionno == ventity.cur_versionno, !string.IsNullOrWhiteSpace(ventity.cur_versionno))
                    .WhereIf(o => o.to_versionno == ventity.to_versionno, !string.IsNullOrWhiteSpace(ventity.to_versionno))
                    .WhereIf(o => o.created > ventity.startTime.Value, ventity.startTime != null)
                    .WhereIf(o => o.created < ventity.endTime.Value, ventity.endTime != null)
                    .WhereIf(o => o.type == ventity.type, !string.IsNullOrWhiteSpace(ventity.type));

                IQueryable<int> tempable = null;
                if (ventity.paymentstate == 2) {//钱全部结清的记录
                    tempable = from a in datable
                                   join b in db.SalerTransactionLog.Where(o => o.state == 2) on a.id equals b.st_id 
                                   group new { a, b } by new { a.id, a.init_price, a.discount_price } into x
                                   where x.Sum(o => o.b.money) >= x.Key.init_price-x.Key.discount_price
                                   select x.Key.id;         
                } else if (ventity.paymentstate == 3) {//定金且钱未补够的记录
                    tempable = from a in datable
                                   join b in db.SalerTransactionLog.Where(o => o.state == 2) on a.id equals b.st_id
                                   group new { a, b } by new { a.id, a.init_price,a.discount_price } into x
                                   where x.Sum(o => o.b.money) < x.Key.init_price-x.Key.discount_price
                                   select x.Key.id;
                }
                if (tempable != null) {
                    page.record_count = tempable.Count();
                    if (page.record_count == 0) {
                        return new ServiceResult(new List<VSalerTransaction>(), 0);
                    }
                    datable = datable.Where(o => tempable.Contains(o.id));
                } else page.record_count = datable.Count();

                datable = datable.OrderByDescending(o => o.id).Skip(page.page_size * (page.page_index - 1)).Take(page.page_size);

后来我发现这样做有点绕弯弯，所以做了修改

 var datable = db.SalerTransaction.WhereIf(o => o.saler_username == ventity.saler_username.Trim(), !string.IsNullOrWhiteSpace(ventity.saler_username))
                    .WhereIf(o => o.cus_username == ventity.cus_username.Trim(), !string.IsNullOrWhiteSpace(ventity.cus_username))
                    .WhereIf(o => o.saler_type == ventity.saler_type, !string.IsNullOrWhiteSpace(ventity.saler_type))
                    .WhereIf(o => o.cur_versionno == ventity.cur_versionno, !string.IsNullOrWhiteSpace(ventity.cur_versionno))
                    .WhereIf(o => o.to_versionno == ventity.to_versionno, !string.IsNullOrWhiteSpace(ventity.to_versionno))
                    .WhereIf(o => o.created > ventity.startTime.Value, ventity.startTime != null)
                    .WhereIf(o => o.created < ventity.endTime.Value, ventity.endTime != null)
                    .WhereIf(o => o.type == ventity.type, !string.IsNullOrWhiteSpace(ventity.type));

                if (ventity.paymentstate == 2) {//全部结清的记录
                    datable = from a in datable
                                   join b in db.SalerTransactionLog.Where(o => o.state == 2) on a.id equals b.st_id 
                                   group new { a, b } by new { a } into x
                                   where x.Sum(o => o.b.money) >= x.Key.a.init_price-x.Key.a.discount_price
                                   select x.Key.a;         
                } else if (ventity.paymentstate == 3) {//定金且钱未补够的记录
                    datable = from a in datable
                                   join b in db.SalerTransactionLog.Where(o => o.state == 2) on a.id equals b.st_id
                                   group new { a, b } by new { a } into x
                                   where x.Sum(o => o.b.money) < x.Key.a.init_price-x.Key.a.discount_price
                                   select x.Key.a;
                }
                page.record_count = datable.Count();
                datable = datable.OrderByDescending(o => o.id).Skip(page.page_size * (page.page_index - 1)).Take(page.page_size);