1linq的左连接查询
var boundList = from x in text.S_Outbound
join y in text.S_Outbound_Per
on x.Shipment_ID equals y.Shipment_ID into temp
from y in temp.DefaultIfEmpty()
select new
{
Id = x.Id,
perID = y.id == null ? : ,
Shipment_ID = x.Shipment_ID,//订单编号
Order_Type = y.Order_Type,//订单来源
Actual_Ship_Date_Time = y.Actual_Ship_Date_Time,//返回时间
USER_DEF2 = y.USER_DEF2,//快递单号
Ship_To_Name = x.Ship_To_Name,
Ship_To_Attention_To = x.Ship_To_Attention_To,
Ship_To_Address1 = x.Ship_To_Address1 + x.Ship_To_Address2 + x.Ship_To_Address3,
Ship_To_Phone_Num = x.Ship_To_Phone_Num,
Carrier_Services = x.Carrier_Services,
USER_DEF1 = x.USER_DEF1,//下单时间
};
2linq实现分页查询
（1）创建BaseQuery用于存储总数、页码和每页显示数量
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks; namespace Model.Query
{
public class BaseQuery
{
public int PageIndex { get; set; }
public int PageSize { get; set; }
public int Total { get; set; }
}
}
（2）添加实体类的查询条件并继承BaseQuery
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks; namespace Model.Query
{
public class ProductQuery : BaseQuery
{
public string ProductName { get; set; } }
}
（3）在数据访问层实现查询的方法
public IQueryable<Hishop_Products> LoadSearchData(ProductQuery query)
{
DbContext dbContext = new ShopEntities();
dbContext.Configuration.ProxyCreationEnabled = false;
var temp = dbContext.Set<Hishop_Products>().Where(u => true);
if (!string.IsNullOrEmpty(query.ProductName))
{
temp = temp.Where<Hishop_Products>(u => u.ProductName.Contains(query.ProductName));
}
#region 其他类型查询
////模糊查询
//temp = temp.Where<Hishop_Products>(u => u.ProductName.Contains(query.ProductName));
////普通查询
//temp = temp.Where<Hishop_Products>(u => u.ProductName.Equals(query.ProductName));
////值类型查询
//temp = temp.Where<Hishop_Products>(u => u.ProductName == query.ProductName);
#endregion
query.Total = temp.Count();
return temp.OrderBy(u => u.ProductId).Skip(query.PageSize * (query.PageIndex - )).Take(query.PageSize);
}
3利用BaseController重新封装数据并返回Json数据
public ContentResult JsonDate(object Date)
{
var timeConverter = new IsoDateTimeConverter { DateTimeFormat = "yyyy-MM-dd" };
return Content(JsonConvert.SerializeObject(Date, Formatting.Indented, timeConverter));
}
4linq中自动求实体中某几个属性的累加和
var manager = from x in data
join y in text.SKUStock on x.skuId equals y.skuId into temp1
from y in temp1.DefaultIfEmpty()
select new
{
ProductId = x.ProductId,
CategoryId = x.CategoryId,
skuId = x.skuId,
sku = x.sku,
ProductName = x.ProductName,
ThumbnailUrl60 = x.ThumbnailUrl60,
stock = y != null ? y.F_Stock : ,
CostPrice = x.CostPrice,
SalePrice = x.SalePrice,
T_Stock = y != null ? y.T_Stock : ,
ValueStr = x.ValueStr,
All_stock = (y != null ? y.F_Stock : ) + (y != null ? y.T_Stock : ),
year = x.year
};
5屏蔽EF的自动导航用于解决EF+linq查出的数据循环引用的问题
text.Configuration.ProxyCreationEnabled = false;
text.Configuration.ValidateOnSaveEnabled = false;
6当数据库中字段太多并且想要将这些字段重新组合成一列显示的时候可以用以下方法实现
var ww = att.ToList().GroupBy(t => new { t.ProductId })
.Select(g => new
{
ProductId = g.Key.ProductId,
AttributeName = string.Join(",", g.Select(s => s.AttributeName).ToArray()),
ValueStr = string.Join(",", g.Select(s => s.ValueStr).ToArray())
});
7linq的条件查询+分页
/// <summary>
/// 分页
/// </summary>
/// <typeparam name="S">排序类型</typeparam>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">每页显示条数</param>
/// <param name="total">总数量</param>
/// <param name="whereLambda">lambda表达式（查询条件）</param>
/// <param name="isAsc">是否倒叙</param>
/// <param name="orderLambda">lambda表达式（排序条件）</param>
/// <returns></returns>
public IQueryable<T> PageList<S>(int pageIndex, int pageSize, out int total, Func<T, bool> whereLambda, bool isAsc, Func<T, S> orderLambda)
{
var temp = context.Set<T>().Where<T>(whereLambda);
total = temp.Count();
if (isAsc)
{
temp = temp.OrderBy<T, S>(orderLambda)
.Skip<T>(pageSize * (pageIndex - ))
.Take<T>(pageSize).AsQueryable();
}
else
{
temp = temp.OrderByDescending<T, S>(orderLambda)
.Skip<T>(pageSize * (pageIndex - ))
.Take<T>(pageSize).AsQueryable();
}
return temp.AsQueryable();
}
8linq+EF底层代码的实现
public T AddEntity(T model)
{
context.Set<T>().Attach(model);
context.Entry<T>(model).State = EntityState.Added;
dbSession.SaveChanges();
return model;
}
/// <summary>
/// 修改实体
/// </summary>
/// <param name="model">实体对象</param>
/// <returns>修改成功为true失败为false</returns>
public bool UpdateEntity(T model)
{
context.Set<T>().Attach(model);
context.Entry<T>(model).State = EntityState.Modified;
return dbSession.SaveChanges() > ;
}
/// <summary>
/// 删除实体
/// </summary>
/// <param name="model">实体对象</param>
/// <returns>修改成功为true失败为false</returns>
public bool DeleteEntity(T model)
{
context.Set<T>().Attach(model);
context.Entry<T>(model).State = EntityState.Deleted;
return dbSession.SaveChanges() > ;
} /// <summary>
/// 根据条件查询实体集合
/// </summary>
/// <param name="whereLambda">lambda表达式（查询条件）</param>
/// <returns>查询到的集合</returns>
public IQueryable<T> SelectByWhere(Func<T, bool> whereLambda)
{
return context.Set<T>().Where<T>(whereLambda).AsQueryable();
}