62,046
社区成员
发帖
与我相关
我的任务
分享
//查询单条
var single = db.Queryable<Student>().Single(c => c.id == 1);
//查询单条根据主键
var singleByPk = db.Queryable<Student>().InSingle(1);
//查询单条没有记录返回空对象
var singleOrDefault = db.Queryable<Student>().SingleOrDefault(c => c.id == 11111111);
//查询单条没有记录返回空对象
var single2 = db.Queryable<Student>().Where(c => c.id == 1).SingleOrDefault();
//查询所有的Id
var singleFieldList = db.Queryable<Student>().Select<int>(it=>it.id).ToList();
//查询第一条
var first = db.Queryable<Student>().Where(c => c.id == 1).First();
var first2 = db.Queryable<Student>().Where(c => c.id == 1).FirstOrDefault();
//查询条数
var count = db.Queryable<Student>().Where(c => c.id > 10).Count();
//从第2条开始以后取所有
var skip = db.Queryable<Student>().Where(c => c.id > 10).OrderBy(it => it.id).Skip(2).ToList();
//取前2条
var take = db.Queryable<Student>().Where(c => c.id > 10).OrderBy(it => it.id).Take(2).ToList();
//Not like
string conval = "a";
var notLike = db.Queryable<Student>().Where(c => !c.name.Contains(conval.ToString())).ToList();
//Like
conval = "三";
var like = db.Queryable<Student>().Where(c => c.name.Contains(conval)).ToList();
//支持字符串Where 让你解决,更复杂的查询
var student12 = db.Queryable<Student>().Where(c => "a" == "a").Where("id>@id", new { id = 1 }).ToList();
var student13 = db.Queryable<Student>().Where(c => "a" == "a").Where("id>100 and id in( select 1)").ToList();
//存在记录反回true,则否返回false
bool isAny100 = db.Queryable<Student>().Any(c => c.id == 100);
bool isAny1 = db.Queryable<Student>().Any(c => c.id == 1);
//获取最大Id
object maxId = db.Queryable<Student>().Max(it => it.id);
int maxId1 = db.Queryable<Student>().Max(it => it.id).ObjToInt();//拉姆达
int maxId2 = db.Queryable<Student>().Max<int>("id"); //字符串写法
//获取最小
int minId1 = db.Queryable<Student>().Where(c => c.id > 0).Min(it => it.id).ObjToInt();//拉姆达
int minId2 = db.Queryable<Student>().Where(c => c.id > 0).Min<int>("id");//字符串写法
int pageCount = 0;
var page = db.Queryable<Student>().Where(c => c.id > 1).OrderBy(it => it.id).ToPageList(pageIndex,pageSize,ref pageCount);
var jList = db.Queryable<Student>()
.JoinTable<School>((s1, s2) => s1.sch_id == s2.id) //默认left join
.Where<School>((s1, s2) => s1.id == 1)
.Select("s1.*,s2.name as schName")
.ToDynamic();
db.Insert(GetInsertItem()); //插入一条记录 (有主键也好,没主键也好,有自增列也好都可以插进去)
db.InsertRange(GetInsertList()); //批量插入 支持(别名表等功能)
db.SqlBulkCopy(GetInsertList()); //批量插入 适合海量数据插入
db.DisableInsertColumns = new string[] { "sex" };//sex列将不会插入值
Student s = new Student()
{
name = "张" + new Random().Next(1, int.MaxValue),
sex = "gril"
};var id = db.Insert(s); //插入
//查询刚插入的sex是否有值
var sex = db.Queryable<Student>().Single(it => it.id == id.ObjToInt()).sex;//无值
var name = db.Queryable<Student>().Single(it => it.id == id.ObjToInt()).name;//有值
//SqlBulkCopy同样支持不插入列设置
db.SqlBulkCopy(GetInsertList());
//清空禁止插入列
db.DisableInsertColumns = null;
//添加禁止插入列
db.AddDisableInsertColumns("name","id
");db.AddDisableInsertColumns("UpdateTime");//禁止插入更新时间
db.AddDisableUpdateColumns("CreateTime");//禁止更新创建时间
db.InsertOrUpdate(GetInsertItem2());
指定列更新
db.Update<School>(new { name = "蓝翔14" }, it => it.id == 14); //只更新name列 条件 id=14
db.Update<School, int>(new { name = "蓝翔11 23 12", areaId = 2 }, 11, 23, 12);//更新name和areaId 根据主键
db.Update<School, string>(new { name = "蓝翔2" }, new string[] { "11", "21" });
db.Update<School>(new { name = "蓝翔2" }, it => it.id == 100);
var array=new int[]{1,2,3};
db.Update<School>(new { name = "蓝翔2" }, it => array.Contains(it.id));// id in 1,2,3
使用字典更新
var dic = new Dictionary<string, string>();
dic.Add("name", "第十三条");
dic.Add("areaId", "1");
db.Update<School, int>(dic, 13);
整个实体更新
db.Update(new School { id = 16, name = "蓝翔16", AreaId = 1 });
db.Update<School>(new School { id = 12, name = "蓝翔12", AreaId = 2 }, it => it.id == 18);
db.Update<School>(new School() { id = 11, name = "青鸟11" });
与指定列更新不同的是只要实体中有的字段都会更新,如果想不更新指定字段请用排除更新列
设置不更新列
db.DisableUpdateColumns = new string[] { "CreateTime" };//设置CreateTime不更新TestUpdateColumns updObj = new TestUpdateColumns()
{
VGUID = Guid.Parse("542b5a27-6984-47c7-a8ee-359e483c8470"),
Name = "xx",
Name2 = "xx2",
IdentityField = 0,
CreateTime = null
};
//CreateTime将不会被更新
db.Update(updObj);
//以前实现这种更新需要用指定列的方式实现,现在就简单多了。
//批量更新 数据量小时建议使用
var updateResult = db.UpdateRange(GetUpdateList());
//批量更新 数据量大时建议使用
var updateResult2 = db.SqlBulkReplace(GetUpdateList2());
//更新字符串
db.Update<Student>("sch_id=sch_id+1", it => it.id == 1);
//清空禁止更新列
db.DisableUpdateColumns = null;
//新语法添加禁止更新列
db.AddDisableUpdateColumn("id", "name");//添加禁止更新列
string moduleName = queryCondition.GetType().Name;
switch (moduleName)
{
case "ba_itemQueryModel":
#region 按条件查询
var condition = queryCondition as ba_itemQueryModel;
//拼接查询条件
if (!condition.ItemCode.IsNullOrEmpty())
{
query = query.Where(c => c.ItemCode.Contains(condition.ItemCode));
}
if (!condition.GoodsCode.IsNullOrEmpty())
{
query = query.Where(c => c.GoodsCode.Contains(condition.GoodsCode));
}
if (condition.Status != "All")
{
query = query.Where(c => c.Status == condition.Status);
}
if (!condition.WideSearch.IsNullOrEmpty())
{
List<string> list = condition.WideSearch.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries).ToList();
query = from q in query
join g in context.ba_goods on q.GoodsCode equals g.GoodsCode
where list.All(c => q.Description.Contains(c)) || list.All(c => g.Description.Contains(c))
|| list.All(c => g.Properties.Contains(c)) || list.All(c => q.OperUser == c)
select q;
}
break;
#endregion
default:
throw new CustomException(string.Format("系统不支持的查询模型{0}!", moduleName));
}
[/quote]
首先你的解决问题的方法是不对的, OR在SQL里面没有 AND 或者 WHERE 是不能直接写的
也就是说 Where id=1 or id=2 相当于 Where(id=1 or id=2)相当于LINQ where(it=>it.id=1||it.id==2)
OR只是AND或者WHERE中的一个成员, 在EF中 将SQL中的AND和WHERE都统称为 WHERE。
所以你认为不方便是你的想法违背SQL本意,换种思路就可以实现你需要的功能。[/quote]
这个,我当然是知道的。
比如,如果能提供query.Or()方法,那不是挺好的?[/quote]
query.where().or().where() 这样写代码会很乱,没有办法用语法实现 OR外面的(),所以微软也就只能这么设计EF。string moduleName = queryCondition.GetType().Name;
switch (moduleName)
{
case "ba_itemQueryModel":
#region 按条件查询
var condition = queryCondition as ba_itemQueryModel;
//拼接查询条件
if (!condition.ItemCode.IsNullOrEmpty())
{
query = query.Where(c => c.ItemCode.Contains(condition.ItemCode));
}
if (!condition.GoodsCode.IsNullOrEmpty())
{
query = query.Where(c => c.GoodsCode.Contains(condition.GoodsCode));
}
if (condition.Status != "All")
{
query = query.Where(c => c.Status == condition.Status);
}
if (!condition.WideSearch.IsNullOrEmpty())
{
List<string> list = condition.WideSearch.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries).ToList();
query = from q in query
join g in context.ba_goods on q.GoodsCode equals g.GoodsCode
where list.All(c => q.Description.Contains(c)) || list.All(c => g.Description.Contains(c))
|| list.All(c => g.Properties.Contains(c)) || list.All(c => q.OperUser == c)
select q;
}
break;
#endregion
default:
throw new CustomException(string.Format("系统不支持的查询模型{0}!", moduleName));
}
[/quote]
首先你的解决问题的方法是不对的, OR在SQL里面没有 AND 或者 WHERE 是不能直接写的
也就是说 Where id=1 or id=2 相当于 Where(id=1 or id=2)相当于LINQ where(it=>it.id=1||it.id==2)
OR只是AND或者WHERE中的一个成员, 在EF中 将SQL中的AND和WHERE都统称为 WHERE。
所以你认为不方便是你的想法违背SQL本意,换种思路就可以实现你需要的功能。[/quote]
这个,我当然是知道的。
比如,如果能提供query.Or()方法,那不是挺好的?string moduleName = queryCondition.GetType().Name;
switch (moduleName)
{
case "ba_itemQueryModel":
#region 按条件查询
var condition = queryCondition as ba_itemQueryModel;
//拼接查询条件
if (!condition.ItemCode.IsNullOrEmpty())
{
query = query.Where(c => c.ItemCode.Contains(condition.ItemCode));
}
if (!condition.GoodsCode.IsNullOrEmpty())
{
query = query.Where(c => c.GoodsCode.Contains(condition.GoodsCode));
}
if (condition.Status != "All")
{
query = query.Where(c => c.Status == condition.Status);
}
if (!condition.WideSearch.IsNullOrEmpty())
{
List<string> list = condition.WideSearch.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries).ToList();
query = from q in query
join g in context.ba_goods on q.GoodsCode equals g.GoodsCode
where list.All(c => q.Description.Contains(c)) || list.All(c => g.Description.Contains(c))
|| list.All(c => g.Properties.Contains(c)) || list.All(c => q.OperUser == c)
select q;
}
break;
#endregion
default:
throw new CustomException(string.Format("系统不支持的查询模型{0}!", moduleName));
}
[/quote]
首先你的解决问题的方法是不对的, OR在SQL里面没有 AND 或者 WHERE 是不能直接写的
也就是说 Where id=1 or id=2 相当于 Where(id=1 or id=2)相当于LINQ where(it=>it.id=1||it.id==2)
OR只是AND或者WHERE中的一个成员, 在EF中 将SQL中的AND和WHERE都统称为 WHERE。
所以你认为不方便是你的想法违背SQL本意,换种思路就可以实现你需要的功能。string moduleName = queryCondition.GetType().Name;
switch (moduleName)
{
case "ba_itemQueryModel":
#region 按条件查询
var condition = queryCondition as ba_itemQueryModel;
//拼接查询条件
if (!condition.ItemCode.IsNullOrEmpty())
{
query = query.Where(c => c.ItemCode.Contains(condition.ItemCode));
}
if (!condition.GoodsCode.IsNullOrEmpty())
{
query = query.Where(c => c.GoodsCode.Contains(condition.GoodsCode));
}
if (condition.Status != "All")
{
query = query.Where(c => c.Status == condition.Status);
}
if (!condition.WideSearch.IsNullOrEmpty())
{
List<string> list = condition.WideSearch.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries).ToList();
query = from q in query
join g in context.ba_goods on q.GoodsCode equals g.GoodsCode
where list.All(c => q.Description.Contains(c)) || list.All(c => g.Description.Contains(c))
|| list.All(c => g.Properties.Contains(c)) || list.All(c => q.OperUser == c)
select q;
}
break;
#endregion
default:
throw new CustomException(string.Format("系统不支持的查询模型{0}!", moduleName));
}