/// <summary> /// 批量更新 /// </summary> /// <param name="p"></param> /// <param name="db"></param> private void Update(Student2 p, SqlSugarClient db) { db.UpdateRange(StudentListUpd); var list = db.Queryable<Student>().ToList(); db.SqlBulkReplace(StudentListUpd2); list = db.Queryable<Student>().ToList(); }
public void Init() { SqlSugarClient db = new SqlSugarClient("server=.;uid=sa;pwd=sasa;database=SqlSugarTest", "server=localhost;uid=sa;pwd=sasa;database=SqlSugarTest"); var l1= db.Queryable<Student>().ToList();//走从 var d1= db.Queryable<Student>().ToDataTable();//走从 var t3 = db.Queryable<Student>().ToList();//走从 db.Insert<Student>(new Student() { name="主" }); db.BeginTran(); var l2 = db.Queryable<Student>().ToList();//走主 db.CommitTran(); var l3 = db.Queryable<Student>().ToList();//走从 db.Dispose(); }
private static void SqlSugar(int eachCount) { GC.Collect();//回收资源 System.Threading.Thread.Sleep(2000);//休息2秒 PerHelper.Execute(eachCount, "SqlSugar", () => { using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString)) { var list = conn.Queryable<Test>().OrderBy("id").ToPageList(1, 10); } }); }
protected void Page_Load(object sender, EventArgs e) { PerformanceTest pt = new PerformanceTest(); pt.SetCount(10000); int id = 1; using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString())) { //ado.GetDataTable pt.Execute(i => { db.GetDataTable("select * from Student WHERE ID>@id", new SqlParameter("@id", id)); }, m => { }, "ado.DateTable 纯SQL写法"); //dapper var conn = db.GetConnection(); pt.Execute(i => { conn.Query<Models.Student>("select * from Student where id>@id", new { id = id}).ToList(); }, m => { }, "dapper 纯SQL写法"); //sqlSugar pt.Execute(i => { db.Queryable<Models.Student>().Where(c => c.id == id).ToList(); }, m => { }, "sqlSugar 拉姆达"); } using (WebTest.TestLib.SqlSugarTestEntities db = new TestLib.SqlSugarTestEntities()) { //EF pt.Execute(i => { db.Student.Where(c => c.id == id).ToList(); }, m => { }, "EF4.0+sql05 拉姆达"); } GridView gv = new GridView(); gv.DataSource = pt.GetChartSource(); gv.DataBind(); Form.Controls.Add(gv); }
protected void Page_Load(object sender, EventArgs e) { PerformanceTest pt = new PerformanceTest(); pt.SetCount(10000); using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString())) { //ado.GetDataTable pt.Execute(i => { db.GetDataTable("select * from(select *,row_number() over(order by id) as r from Student where id>@id) t where t.r between @b and @e", new SqlParameter("@id","0"), new SqlParameter("@b", 11), new SqlParameter("@e", 20)); }, m => { }, "ado.DateTable 纯SQL写法"); //dapper var conn = db.GetConnection(); pt.Execute(i => { conn.Query<Models.Student>("select * from(select *,row_number() over(order by id) as r from Student where id>@id) t where t.r between @b and @e", new { id = 0,b=11,e=20 }).ToList(); }, m => { }, "dapper 纯SQL写法"); //sqlSugar pt.Execute(i => { db.Queryable<Models.Student>().Where(c => c.id > 0).OrderBy("id").ToPageList(2, 10); }, m => { }, "sqlSugar 拉姆达"); } using (WebTest.TestLib.SqlSugarTestEntities db = new TestLib.SqlSugarTestEntities()) { //EF pt.Execute(i => { db.Student.Where(c => c.id == 2).OrderBy(c=>c.id).Skip(10).Take(10).ToList(); }, m => { }, "EF4.0+sql05 拉姆达"); } GridView gv = new GridView(); gv.DataSource = pt.GetChartSource(); gv.DataBind(); Form.Controls.Add(gv); }
public WarmUp() { Console.WriteLine("开启预热"); //预热处理 for (int i = 0; i < 2; i++) { using (SqlConnection conn = new SqlConnection(PubConst.connectionString)) { var list = conn.QueryFirst<Test>("select top 1 * from Test"); conn.Update(new Test()); } using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString)) { var list = conn.Queryable<Test>().Where(it => 1 == 2).ToList(); conn.Update(new Test()); } } Console.WriteLine("预热完毕"); Console.WriteLine("----------------比赛开始-------------------"); }
/// <summary> /// 查询今日余刀 /// 用于查刀和催刀 /// </summary> /// <returns>余刀表</returns> public Dictionary <long, int> GetTodayAtkCount() { try { using SqlSugarClient dbClient = SugarUtils.CreateSqlSugarClient(DBPath); return(dbClient.Queryable <GuildBattle>() .AS(BattleTableName) .Where(attack => attack.Time > Utils.GetUpdateStamp()) .GroupBy(member => member.Uid) .Select(member => new { member.Uid, times = SqlFunc.AggregateCount(member.Uid) }) .ToList() .ToDictionary(member => member.Uid, member => member.times)); } catch (Exception e) { ConsoleLog.Error("Database error", ConsoleLog.ErrorLogBuilder(e)); return(null); } }
/// <summary> /// 通过code获取组织信息 /// </summary> /// <param name="orgCode"></param> /// <returns></returns> public OrgModel getOrgModelByCode(string orgCode) { using (SqlSugarClient db = BaseDB.GetClient()) { List <OrgModel> res = db.Queryable <Organization, OrganizationOrg>((o, oo) => new object[] { JoinType.Left, o.id == oo.orgid }) .Where((o, oo) => o.orgCode == orgCode) .Select((o, oo) => new OrgModel { organization = o, parentOrgId = oo.parentorgid }).ToList(); if (res.Count > 0) { return(res[0]); } else { return(null); } } }
/// <summary> /// 删除某一条角色数据(连同角色有关的权限全部删除掉) /// </summary> /// <param name="roleTypeVguid"></param> /// <returns></returns> public bool DeleteRoleType(string roleTypeVguid) { using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance()) { bool result = false; Guid vguid = Guid.Parse(roleTypeVguid); Sys_Role sysRole = _dbMsSql.Queryable <Sys_Role>().Where(i => i.Vguid == vguid).SingleOrDefault(); string sysRoleJson = JsonHelper.ModelToJson <Sys_Role>(sysRole); //保存日志 _logLogic.SaveLog(2, 30, Common.CurrentUser.GetCurrentUser().LoginName, "角色", sysRoleJson); try { result = _dbMsSql.Delete <Sys_Role>(i => i.Vguid == vguid); _dbMsSql.Delete <Sys_Role_Module>(i => i.RoleVGUID == vguid); } catch (Exception exp) { Common.LogHelper.LogHelper.WriteLog(exp.ToString()); } return(result); } }
/// <summary> /// 获取数据 /// </summary> /// <param name="orderFun">排序的表达式</param> /// <param name="ob">排序方式 默认为正序</param> /// <param name="PageIndex">页码 从1开始计</param> /// <param name="PageSize">页容量</param> /// <param name="where">条件</param> /// <param name="pars">参数</param> /// <returns></returns> public ISugarQueryable <T> GetData(Expression <Func <T, object> > orderFun = null, OrderByType ob = OrderByType.Asc, int PageIndex = 0, int PageSize = 0, string where = "1=1", object pars = null) { ISugarQueryable <T> Tempdb = _ssc.Queryable <T>().Where(where, pars); //需要分页 if (PageIndex > 0) { int count = Tempdb.Count(); int Skip = (PageIndex - 1) * PageSize; int SumPage = Convert.ToInt32(Math.Ceiling(count * 1.0 / PageSize)); //总页数 bool dxp = PageIndex > (SumPage / 2); if (dxp) //页码大于一半用倒序 { Tempdb.OrderBy(orderFun, (ob == OrderByType.Asc ? OrderByType.Desc : OrderByType.Asc)); int mod = count % PageSize; if (PageIndex * PageSize >= count) { Skip = 0; PageSize = mod == 0 ? PageSize : mod; } else { Skip = (SumPage - PageIndex - 1) * PageSize + mod; } } else { Tempdb.OrderBy(orderFun, ob);//升序 } Tempdb.Skip(Skip); Tempdb.Take(PageSize); } return(Tempdb); }
public CategoryCreateDTO GetModel(int id) { using (var db = new SqlSugarClient(Connection)) { CategoryCreateDTO result = null; var model = db.Queryable <R_Category>().InSingle(id); if (model != null) { result = new CategoryCreateDTO() { Id = model.Id, Name = model.Name, Description = model.Description, DiscountRate = model.DiscountRate, Pid = model.PId, IsDiscount = model.IsDiscount, Sorted = model.Sorted }; } return(result); } }
public ActionResult CancelhomestayOrder() { SqlSugarClient db = new SqlSugarClient( new ConnectionConfig() { ConnectionString = System.Web.Configuration.WebConfigurationManager.AppSettings["ConnectionString"], DbType = DbType.Oracle, //设置数据库类型 IsAutoCloseConnection = true, //自动释放数据务,如果存在事务,在事务结束后释放 InitKeyType = InitKeyType.Attribute //从实体特性中读取主键自增列信息 }); JObject isSuccess = new JObject(); try { //查询房源订单结果 var target = db.Queryable <HomestayOrder>().InSingle(Convert.ToInt32(Request.Form["orderID"])); if (target.status == -1) { target.status = -2; db.Updateable(target).UpdateColumns(it => new { it.status }).ExecuteCommand(); isSuccess.Add("isSuccess", true); } else { isSuccess.Add("isSuccess", false); isSuccess.Add("message", "你的房源订单不处在待出行状态,无法取消"); } } catch (Exception ex) { isSuccess.Add("isSuccess", false); isSuccess.Add("message", "操作失败"); return(Content(JsonConvert.SerializeObject(isSuccess, Formatting.Indented))); } return(Content(JsonConvert.SerializeObject(isSuccess, Formatting.Indented))); }
public ActionResult RemoveHomestay(FormCollection form) { SqlSugarClient db = new SqlSugarClient( new ConnectionConfig() { ConnectionString = System.Web.Configuration.WebConfigurationManager.AppSettings["ConnectionString"], DbType = DbType.Oracle, //设置数据库类型 IsAutoCloseConnection = true, //自动释放数据务,如果存在事务,在事务结束后释放 InitKeyType = InitKeyType.Attribute //从实体特性中读取主键自增列信息 }); JObject isSuccess = new JObject(); try { //检测房源是否有正处在活跃的订单 var checkif = db.Queryable <HomestayOrder>().Where(it => it.homestay_id == Convert.ToInt32(Request.Form["productID"]) && it.status <= 0).ToArray(); if (checkif.Length == 0) { db.Deleteable <SpecialPrice>(it => it.homestay_id == Convert.ToInt32(Request.Form["productID"])).ExecuteCommand(); db.Deleteable <Homestay>().In(Convert.ToInt32(Request.Form["productID"])).ExecuteCommand(); isSuccess.Add("isSuccess", true); } else { isSuccess.Add("isSuccess", false); isSuccess.Add("message", "不允许删除!你有房源订单正在处于活跃状态!"); } } catch (Exception ex) { isSuccess.Add("isSuccess", false); isSuccess.Add("message", "操作失败"); return(Content(JsonConvert.SerializeObject(isSuccess, Formatting.Indented))); } return(Content(JsonConvert.SerializeObject(isSuccess, Formatting.Indented))); }
/// <summary> /// 根据餐饮项目id集合获取餐饮项目集合 /// </summary> /// <param name="projectIdList">餐饮项目id集合</param> /// <returns>餐饮项目集合</returns> public List <ProjectJoinDetailDTO> GetProjectListBy(List <int> projectIdList) { using (var db = new SqlSugarClient(Connection)) { List <ProjectJoinDetailDTO> list = db.Queryable <R_Project>() .JoinTable <R_ProjectDetail>((s1, s2) => s1.Id == s2.R_Project_Id) .Where <R_ProjectDetail>((s1, s2) => projectIdList.Contains(s2.Id)) .Select <R_ProjectDetail, ProjectJoinDetailDTO>((s1, s2) => new ProjectJoinDetailDTO { Id = s2.Id, ProjectName = s1.Name, CategoryId = s1.R_Category_Id, CostPrice = s2.CostPrice, Description = s2.Description, Price = s2.Price, Property = s1.Property, R_Project_Id = s2.R_Project_Id, Unit = s2.Unit, UnitRate = s2.UnitRate }).ToList(); return(list); } }
public static void UpdateEsbDepartmentParentID(SqlSugarClient Ddb) { Ddb.CodeFirst.InitTables(typeof(DepartmentTrees)); var newlist = Ddb.Queryable <DepartmentTrees, DepartmentTrees>((st, sc) => new object[] { JoinType.Inner, st.ParentDepartmentId == sc.DepartmentId }) .Select ( (st, sc) => new DepartmentTrees { Id = st.Id, DD_Id = st.DD_Id, DD_ParentId = sc.DD_Id, DepartmentId = st.DepartmentId, FullName = st.FullName, ParentDepartmentId = st.ParentDepartmentId, CreateDate = st.CreateDate, level = st.level } ).ToList(); Ddb.Updateable <DepartmentTrees>(newlist).ExecuteCommand(); Console.Write("\r\n执行完成\r\n"); }
public ExtendCreateDTO GetModel(int id) { using (var db = new SqlSugarClient(Connection)) { ExtendCreateDTO model = null; var data = db.Queryable <R_ProjectExtend>().InSingle(id); if (data != null) { model = new ExtendCreateDTO() { Id = data.Id, CyxmKzType = data.CyxmKzType, Description = data.Description, Name = data.Name, Price = data.Price, Unit = data.Unit, ExtendType = data.R_ProjectExtendType_Id }; } return(model); } }
/// <summary> /// 分页查询习题信息列表 /// </summary> /// <param name="searchParam"></param> /// <param name="para"></param> /// <returns></returns> public JsonResultModel <V_Business_Exercises_Infomation> GetExerciseListBySearch(Business_Exercises_Infomation_Search searchParam, GridParams para) { using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance()) { JsonResultModel <V_Business_Exercises_Infomation> jsonResult = new JsonResultModel <V_Business_Exercises_Infomation>(); var query = _dbMsSql.Queryable <V_Business_Exercises_Infomation>().Where(i => i.Status == 1); if (!string.IsNullOrEmpty(searchParam.ExercisesName)) { query.Where(i => i.ExercisesName.Contains(searchParam.ExercisesName)); } //if (!string.IsNullOrEmpty(searchParam.Status)) //{ // int status = int.Parse(searchParam.Status); // query.Where(i => i.Status == status); //} if (!string.IsNullOrEmpty(searchParam.InputType)) { int inputType = int.Parse(searchParam.InputType); query.Where(i => i.InputType == inputType); } if (!string.IsNullOrEmpty(searchParam.EffectiveDate)) { DateTime effectiveDate = DateTime.Parse(searchParam.EffectiveDate); query.Where(i => i.EffectiveDate < effectiveDate); } query.OrderBy(para.sortdatafield + " " + para.sortorder); jsonResult.TotalRows = query.Count(); jsonResult.Rows = query.ToPageList(para.pagenum, para.pagesize); //存入操作日志表 string logData = JsonHelper.ModelToJson <JsonResultModel <V_Business_Exercises_Infomation> >(jsonResult); _ll.SaveLog(3, 7, Common.CurrentUser.GetCurrentUser().LoginName, "习题列表", logData); return(jsonResult); } }
protected void Page_Load(object sender, EventArgs e) { try { using (SqlSugarClient db = SugarDao.GetInstance()) //开启数据库连接 { db.IsGetPageParas = true; //使用无参模式直接将Requst中的ID传给@id无需在代码中写出来 var list = db.Queryable <STUDENT>().Where("id=:id").ToList(); //sqlable queryable sqlquery通用 //获取页面所有参数到键值对 //var kvs= SqlSugarTool.GetParameterDictionary(); //以前写法 //var xx = db.Queryable<Student>().Where("id=@id", new { id=Request["id"] }).ToList(); } } catch (Exception) { throw new Exception("请在当前页面URL地址后面加上参数?id=1"); } }
private static void MasterSlave() { Console.WriteLine(""); Console.WriteLine("#### MasterSlave Start ####"); SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { ConnectionString = Config.ConnectionString,//Master Connection DbType = DbType.SqlServer, InitKeyType = InitKeyType.Attribute, IsAutoCloseConnection = true, SlaveConnectionConfigs = new List <SlaveConnectionConfig>() { new SlaveConnectionConfig() { HitRate = 10, ConnectionString = Config.ConnectionString2 }, new SlaveConnectionConfig() { HitRate = 10, ConnectionString = Config.ConnectionString2 } } }); db.Aop.OnLogExecuted = (s, p) => { Console.WriteLine(db.Ado.Connection.ConnectionString); }; Console.WriteLine("Master:"); db.Insertable(new Order() { Name = "abc", CustomId = 1, CreateTime = DateTime.Now }).ExecuteCommand(); Console.WriteLine("Slave:"); db.Queryable <Order>().First(); Console.WriteLine("#### MasterSlave End ####"); }
/// <summary> /// 获取系统日志分页信息 /// </summary> /// <param name="logContent"></param> /// <param name="logType"></param> /// <param name="opUserName"></param> /// <param name="startTime"></param> /// <param name="endTime"></param> /// <param name="pagination"></param> /// <returns></returns> public List <Base_SysLog> GetLogList(string logContent, string logType, string opUserName, DateTime?startTime, DateTime?endTime, Pagination <Base_SysLog> pagination) { var whereExp = LinqHelper.True <Base_SysLog>(); if (!logContent.IsNullOrEmpty()) { whereExp = whereExp.And(x => x.LogContent.Contains(logContent)); } if (!logType.IsNullOrEmpty()) { whereExp = whereExp.And(x => x.LogType == logType); } if (!opUserName.IsNullOrEmpty()) { whereExp = whereExp.And(x => x.OpUserName.Contains(opUserName)); } if (!startTime.IsNullOrEmpty()) { whereExp = whereExp.And(x => x.OpTime >= startTime); } if (!endTime.IsNullOrEmpty()) { whereExp = whereExp.And(x => x.OpTime <= endTime); } // List<Base_SysLog> List = dbSqlClent.Queryable<Base_SysLog>() // .Where(whereExp).ToDataTablePage(pagination.page, pagination.rows); // pagination.records = source.Count(); // source = source.OrderBy($"{pagination.sidx} {pagination.sord}"); var total = 0; var List = dbSqlClent.Queryable <Base_SysLog>() .Where(whereExp).OrderBy(st => pagination.sidx, (SqlSugar.OrderByType)System.Enum.Parse(typeof(SqlSugar.OrderByType), pagination.sord.ToString())) .ToPageList(pagination.PageIndex, pagination.PageRows, ref total); pagination.records = total; return(List); }
public void AddNewestRecord(SqlSugarClient db, string groupId, string record) { var user = db.Queryable <NEWEST_CHAT_RECORD>().Where(c => c.GROUP_ID == groupId).ToList().FirstOrDefault(); if (user == null) { user = new NEWEST_CHAT_RECORD { ID = Guid.NewGuid().ToString("N").ToUpper(), DATETIME_CREATED = DateTime.Now, USER_CREATED = "SYS", STATE = "A", GROUP_ID = groupId, NEWEST_CHAR_RECORD = record }; db.Insertable(user).ExecuteCommand(); } else { user.NEWEST_CHAR_RECORD = record; user.DATETIME_MODIFIED = DateTime.Now; db.Updateable(user).ExecuteCommand(); } }
public static void Init() { SqlSugarClient Db = new SqlSugarClient(new ConnectionConfig() { ConnectionString = @"PORT=5433;DATABASE=x;HOST=localhost;PASSWORD=haosql;USER ID=postgres", DbType = DbType.PostgreSQL, IsAutoCloseConnection = true, InitKeyType = InitKeyType.Attribute, }); //调式代码 用来打印SQL Db.Aop.OnLogExecuting = (sql, pars) => { Console.WriteLine(sql); }; Db.CodeFirst.InitTables(typeof(testmmxxxmm121)); Db.Insertable(new testmmxxxmm121() { name = (float)0.01, name2 = 1 }).ExecuteCommand(); var list = Db.Queryable <testmmxxxmm121>().ToList(); }
/// <summary> /// 根据手术单号查询对应手术单号 /// </summary> /// <param name="pageDataApo">带分页的pageDataApo</param> /// <param name="totalCount">返回的总数</param> /// <returns></returns> public List <SurgeryOrderDto> GetSurgeryOrderDto(SurgeryOrderApo pageDataApo, out int totalCount) { totalCount = 0; List <SurgeryOrderDto> data; //查询语句 var queryable = Db.Queryable <SurgeryOrder>() .Where(it => it.code.StartsWith(pageDataApo.SurgeryOrderCode)) .Select <SurgeryOrderDto>(); //如果小于0,默认查全部 if (pageDataApo.PageSize > 0) { data = queryable.ToPageList(pageDataApo.PageIndex, pageDataApo.PageSize, ref totalCount); } else { data = queryable.ToList(); totalCount = data.Count(); } return(data); }
public void Excute(int menuAddressId, int languageId, string uiEngineDir) { _uiEngineDir = uiEngineDir; var data = _db.Queryable <SysBest_MenuAddress>().FirstOrDefault(it => it.Id == menuAddressId); _data = data; _languageId = languageId; string areaBoxDir = FileSugar.MergeUrl(_solutionDir, "Areas", data.AreaName); string areaDir = FileSugar.MergeUrl(_solutionDir, "Areas", data.AreaName); string viewsDir = FileSugar.MergeUrl(areaDir, "Views"); string areaRegistrationPath = FileSugar.MergeUrl(areaDir, "{0}AreaRegistration.cs".ToFormat(data.AreaName)); string controllerDir = FileSugar.MergeUrl(areaDir, "Controllers", data.ControllerName + "App"); string controllerDomainDir = FileSugar.MergeUrl(areaDir, "Controllers", data.ControllerName + "App", "Domain"); string controllerDomainPath = FileSugar.MergeUrl(areaDir, "Controllers", data.ControllerName + "App", "Domain", data.ControllerName + "Domain.cs"); string configPath = FileSugar.MergeUrl(viewsDir, "Web.config"); string viewPath = FileSugar.MergeUrl(viewsDir, _data.ControllerName, _data.ActionName + ".cshtml"); var lan = _db.Queryable <SysBest_Language>().FirstOrDefault(it => it.Id == languageId).Suffix; if (lan.IsValuable() && lan != "zh") { viewPath = FileSugar.MergeUrl(viewsDir, _data.ControllerName, _data.ActionName + $"_{lan}.cshtml"); } string controllerPath = FileSugar.MergeUrl(controllerDir, data.ControllerName + "Controller.cs"); string path = _solutionDir + @"\" + _projectName + ".csproj"; try { XDocument doc = XDocument.Load(path); BuildArea(areaDir, viewsDir, controllerDomainDir, areaRegistrationPath, configPath, doc); BuildController(controllerDir, controllerPath, controllerDomainPath, doc); BuildView(viewPath, doc); BuildDatabaseTableTable(); doc.Save(path); } catch (Exception ex) { throw ex; } }
private static void QueryConditions() { Console.WriteLine(""); Console.WriteLine("#### Query Conditions Start ####"); SqlSugarClient db = GetInstance(); /*** By expression***/ //id=@id var list = db.Queryable <Order>().Where(it => it.Id == 1).ToList(); //id=@id or name like '%'+@name+'%' var list2 = db.Queryable <Order>().Where(it => it.Id == 1 || it.Name.Contains("jack")).ToList(); //Create expression var exp = Expressionable.Create <Order>() .And(it => it.Id == 1) .Or(it => it.Name.Contains("jack")).ToExpression(); var list3 = db.Queryable <Order>().Where(exp).ToList(); /*** By sql***/ //id=@id var list4 = db.Queryable <Order>().Where("id=@id", new { id = 1 }).ToList(); //id=@id or name like '%'+@name+'%' var list5 = db.Queryable <Order>().Where("id=@id or name like @name ", new { id = 1, name = "%jack%" }).ToList(); /*** By dynamic***/ //id=1 var conModels = new List <IConditionalModel>(); conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1" }); //id=1 var student = db.Queryable <Order>().Where(conModels).ToList(); //Complex use case List <IConditionalModel> Order = new List <IConditionalModel>(); conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1" }); //id=1 conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Like, FieldValue = "1" }); // id like '%1%' conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.IsNullOrEmpty }); conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.In, FieldValue = "1,2,3" }); conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.NotIn, FieldValue = "1,2,3" }); conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.NoEqual, FieldValue = "1,2,3" }); conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.IsNot, FieldValue = null }); // id is not null conModels.Add(new ConditionalCollections() { ConditionalList = new List <KeyValuePair <WhereType, SqlSugar.ConditionalModel> >()// (id=1 or id=2 and id=1) { //new KeyValuePair<WhereType, ConditionalModel>( WhereType.And ,new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1" }), new KeyValuePair <WhereType, ConditionalModel> (WhereType.Or, new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "2" }), new KeyValuePair <WhereType, ConditionalModel> (WhereType.And, new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "2" }) } }); var list6 = db.Queryable <Order>().Where(conModels).ToList(); /*** Conditional builder ***/ // use whereif string name = ""; int id = 1; var query = db.Queryable <Order>() .WhereIF(!string.IsNullOrEmpty(name), it => it.Name.Contains(name)) .WhereIF(id > 0, it => it.Id == id).ToList(); //clone new Queryable var query2 = db.Queryable <Order>().Where(it => it.Id == 1); var list7 = query2.Clone().Where(it => it.Name == "jack").ToList(); //id=1 and name = jack var list8 = query2.Clone().Where(it => it.Name == "tom").ToList(); //id=1 and name = tom Console.WriteLine("#### Condition Screening End ####"); }
public override IEnumerable <T_Entity> Query() { var list = _sqlSugarClient.Queryable <T_Entity>().Take(QUERY_TAKEN).ToList(); return(list); }
public override Order Get(Guid key) { var order = _sqlClient.Queryable <Model.Order>().Where(x => x.Id == key).Single(); return(Order.Load(order.Id, order.TotalAmount, order.CreateTime)); }
public string PageList(Bootstrap.BootstrapParams bootstrap) { var totalNumber = 0; if (bootstrap.offset != 0) { bootstrap.offset = bootstrap.offset / bootstrap.limit + 1; } //var order = ExpressionExt.InitO<Sys_user>(bootstrap.sort); var query = _client.Queryable <Sys_user, Sys_user, Sys_user, Sys_dept, Sys_role> ((s, c, u, d, r) => new object[] { JoinType.Left, s.CreateBy == c.UserId, JoinType.Left, s.ModifiedBy == u.UserId, JoinType.Left, s.DeptId == d.DeptId, JoinType.Left, s.RoleId == r.RoleId }).Where((s, c, u, d, r) => s.IsDel == 1 && d.IsDel == 1 && r.IsDel == 1) .Select((s, c, u, d, r) => new { UserId = s.UserId.ToString(), s.UserName, s.UserNickname, d.DeptName, r.RoleName, s.Tel, s.Email, s.Sex, s.IsEabled, s.Remark, CName = c.UserNickname, s.CreateDate, UName = u.UserNickname, s.ModifiedDate }).MergeTable(); if (!bootstrap.search.IsEmpty()) { query.Where((s) => s.UserName == bootstrap.search || s.UserNickname == bootstrap.search); } if (!bootstrap.datemin.IsEmpty() && !bootstrap.datemax.IsEmpty()) { query.Where(s => s.CreateDate > bootstrap.datemin.ToDateTimeB() && s.CreateDate <= bootstrap.datemax.ToDateTimeE()); } if (bootstrap.order.Equals("desc", StringComparison.OrdinalIgnoreCase)) { query.OrderBy($"MergeTable.{bootstrap.sort} desc"); } else { query.OrderBy($"MergeTable.{bootstrap.sort} asc"); } var list = query.ToPageList(bootstrap.offset, bootstrap.limit, ref totalNumber); //var list = _client.Queryable("Sys_User", "s") // .AddJoinInfo("Sys_User", "c", "s.CreateBy=c.UserId") // .AddJoinInfo("Sys_User", "u", "s.ModifiedBy=u.UserId"); //if (!bootstrap.search.IsEmpty()) //{ // list.Where("s.IsDel=@id and s.UserName=@UserName or s.UserNickname=@UserName") // .AddParameters(new { id = 1, UserName = bootstrap.search }); //} //else //{ // list.Where("s.IsDel=@id ") // .AddParameters(new { id = 1 }); //} //if (bootstrap.offset != 0) //{ // bootstrap.offset = bootstrap.offset / bootstrap.limit + 1; //} //list.OrderBy($"s.{bootstrap.sort}") //.Select("s.*,c.UserNickname AS CNAME,u.UserNickname AS UNAME") //.ToPageList(bootstrap.offset, bootstrap.limit, ref totalNumber); return(Bootstrap.GridData(list, totalNumber).JilToJson()); }
private static void SubIdentity(SqlSugarClient db) { db.CodeFirst.InitTables <Country1, Province1, City1>(); db.DbMaintenance.TruncateTable("Country1"); db.DbMaintenance.TruncateTable("Province1"); db.DbMaintenance.TruncateTable("City1"); db.Insertable(new List <Country1>() { new Country1() { Id = 1, Name = "中国", Provinces = new List <Province1>() { new Province1 { Id = 1001, Name = "江苏", citys = new List <City1>() { new City1() { Id = 1001001, Name = "南通" }, new City1() { Id = 1001002, Name = "南京" } } }, new Province1 { Id = 1002, Name = "上海", citys = new List <City1>() { new City1() { Id = 1002001, Name = "徐汇" }, new City1() { Id = 1002002, Name = "普陀" } } }, new Province1 { Id = 1003, Name = "北京", citys = new List <City1>() { new City1() { Id = 1003001, Name = "北京A" }, new City1() { Id = 1003002, Name = "北京B" } } } } }, new Country1() { Name = "美国", Id = 2, Provinces = new List <Province1>() { new Province1() { Name = "美国小A", Id = 20001 }, new Province1() { Name = "美国小b", Id = 20002 } } }, new Country1() { Name = "英国", Id = 3 } }) .AddSubList(it => new SubInsertTree() { Expression = it.Provinces.First().CountryId, ChildExpression = new List <SubInsertTree>() { new SubInsertTree() { Expression = it.Provinces.First().citys.First().ProvinceId } } }) .ExecuteReturnPrimaryKey(); var list = db.Queryable <Country1>() .Mapper(it => it.Provinces, it => it.Provinces.First().CountryId) .Mapper(it => { foreach (var item in it.Provinces) { item.citys = db.Queryable <City1>().Where(y => y.ProvinceId == item.Id).ToList(); } }) .ToList(); }
public ISugarQueryable <DBCSTypeChange> LoadEntities(Expression <Func <DBCSTypeChange, bool> > whereLambda) { return(db.Queryable <DBCSTypeChange>().WhereIF(whereLambda != null, whereLambda)); }
protected void Page_Load(object sender, EventArgs e) { //连接字符串 string connStr = @"Server=.;uid=sa;pwd=sasa;database=SqlSugarTest"; using (SqlSugarClient db = new SqlSugarClient(connStr))//开启数据库连接 { //开启事务,可以不使用事务,也可以使用多个事务 db.BeginTran(); //db.CommitTran 提交事务会,在using结束前自动执行,可以不声名 //db.RollbackTran(); 事务回滚,catch中声名 //查询是允许脏读的,可以声名多个(默认值:不允许) db.IsNoLock = true; try { /************************************************************************************************************/ /*********************************************1、实体生成****************************************************/ /************************************************************************************************************/ //根据当前数据库生成所有表的实体类文件 (参数:SqlSugarClient ,文件目录,命名空间) //db.ClassGenerating.CreateClassFiles(db,Server.MapPath("~/Models"),"Models"); //根据表名生成实体类文件 //db.ClassGenerating.CreateClassFilesByTableNames(db, Server.MapPath("~/Models"), "Models" , "student","school"); //根据表名生成class字符串 var str = db.ClassGenerating.TableNameToClass(db, "Student"); //根据SQL语句生成class字符串 var str2 = db.ClassGenerating.SqlToClass(db, "select top 1 * from Student", "student"); /************************************************************************************************************/ /*********************************************2、查询********************************************************/ /************************************************************************************************************/ //---------Queryable<T>,扩展函数查询---------// //针对单表或者视图查询 //查询所有 var student = db.Queryable<Student>().ToList(); var stud = new Student() { id = 1 }; //查询单条 var single = db.Queryable<Student>().Single(c => c.id==stud.id); //取10-20条 var page1 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Skip(10).Take(20).ToList(); //上一句的简化写法,同样取10-20条 var page2 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").ToPageList(2, 10); //查询条数 var count = db.Queryable<Student>().Where(c => c.id > 10).Count(); //从第2条开始以后取所有 var skip = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Skip(2).ToList(); //取前2条 var take = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Take(2).ToList(); // Not like string conval = "a"; var notLike = db.Queryable<Student>().Where(c => !c.name.Contains(conval.ToString())).ToList(); // 可以在拉姆达使用 ToString和 Convert,比EF出色的地方 var convert1 = db.Queryable<Student>().Where(c => c.name == "a".ToString()).ToList(); var convert2 = db.Queryable<Student>().Where(c => c.id == Convert.ToInt32("1")).ToList();// var convert3 = db.Queryable<Student>().Where(c => DateTime.Now > Convert.ToDateTime("2015-1-1")).ToList(); var convert4 = db.Queryable<Student>().Where(c => DateTime.Now > DateTime.Now).ToList(); //支持字符串Where 让你解决,更复杂的查询 var student12 = db.Queryable<Student>().Where(c => "a" == "a").Where("id>100").ToList(); //存在记录反回true,则否返回false bool isAny100 = db.Queryable<Student>().Any(c => c.id == 100); bool isAny1 = db.Queryable<Student>().Any(c => c.id == 1); //---------Sqlable,创建多表查询---------// //多表查询 List<School> dataList = db.Sqlable() .Form("school", "s") .Join("student", "st", "st.id", "s.id", JoinType.INNER) .Join("student", "st2", "st2.id", "st.id", JoinType.LEFT).Where("s.id>100 and s.id<@id").SelectToList<School>("st.*", new { id = 1 }); //多表分页 List<School> dataPageList = db.Sqlable() .Form("school", "s") .Join("student", "st", "st.id", "s.id", JoinType.INNER) .Join("student", "st2", "st2.id", "st.id", JoinType.LEFT).Where("s.id>100 and s.id<100").SelectToPageList<School>("st.*", "s.id", 1, 10); //---------SqlQuery,根据SQL或者存储过程---------// //用于多用复杂语句查询 var School = db.SqlQuery<Student>("select * from Student"); //获取id var id = db.SqlQuery<int>("select top 1 id from Student").Single(); //存储过程 //var spResult = db.SqlQuery<school>("exec sp_school @p1,@p2", new { p1=1,p2=2 }); /************************************************************************************************************/ /*************************************************3、添加****************************************************/ /************************************************************************************************************/ School s = new School() { name = "蓝翔" }; //插入单条 var id2 = Convert.ToInt32(db.Insert(s)); //插入多条 List<School> sList = new List<School>(); sList.Add(s); var ids = db.InsertRange(sList); /************************************************************************************************************/ /*************************************************4、修改****************************************************/ /************************************************************************************************************/ //指定列更新 db.Update<School>(new { name = "蓝翔2" }, it => it.id == id); //整个实体更新,注意主键必需为实体类的第一个属性 db.Update<School>(new School { id = id, name = "蓝翔2" }, it => it.id == id); /************************************************************************************************************/ /*************************************************5、删除****************************************************/ /************************************************************************************************************/ db.Delete<School>(10);//注意主键必需为实体类的第一个属性 db.Delete<School>(it => it.id > 100); db.Delete<School>(new string[] { "100", "101", "102" }); //db.FalseDelete<school>("is_del", 100); //等同于 update school set is_del=0 where id in(100) //db.FalseDelete<school>("is_del", it=>it.id==100); /************************************************************************************************************/ /*************************************************6、基类****************************************************/ /************************************************************************************************************/ string sql = "select * from Student"; db.ExecuteCommand(sql); db.GetDataTable(sql); db.GetList<Student>(sql); db.GetSingle<Student>(sql + " where id=1"); using (SqlDataReader read = db.GetReader(sql)) { } //事务中一定要释放DataReader db.GetScalar(sql); db.GetString(sql); db.GetInt(sql); } catch (Exception ex) { //回滚事务 db.RollbackTran(); throw ex; } }//关闭数据库连接 }
/// <summary> /// 查询出刚插入的1000条数据 /// </summary> /// <returns></returns> private static List<Test> GetDeleteList() { using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString)) { conn.Delete<Test>(it => it.F_String == "Test"); //插入1000条 conn.SqlBulkCopy(GetList); //查询出插入的1000条 var list = conn.Queryable<Test>().Where(it => it.F_String == "Test").ToList(); return list; } }
/// <summary> /// 删除测试 /// </summary> /// <param name="p"></param> /// <param name="db"></param> private void Delete(Student2 p, SqlSugarClient db) { db.Delete<Student>(it => it.id == 1); db.Delete<Student>(new Student() { id=2}); db.Delete<Student,int>(3,4); db.Delete<Student, int>(it=>it.id,5,6); var list = db.Queryable<Student>().ToList(); }
private List <BaseIList> schData() { UserProc.CheckTime = ""; var exp = Expressionable.Create <BaseIList>().And((b) => b.leibie == "计划表"); if (checkMohu.Checked) { if (chkDanHao.chkSel) { exp.And((b) => b.dingdanhao.Contains(chkDanHao.txt.Text)); } if (chkKehu.chkSel) { exp.And((b) => b.kehu.Contains(chkKehu.cobodgv.Text)); } if (chkPinmin.chkSel) { exp.And((b) => b.pingmin.Contains(chkPinmin.cobodgv.Text)); } if (chkZPS.chkSel) { exp.And((b) => b.pishu.Contains(chkZPS.txt.Text)); } if (chkZZ.chkSel) { exp.And((b) => b.seming.Contains(chkZZ.cobodgv.Text)); } if (chkPS.chkSel) { exp.And((b) => b.sehao.Contains(chkPS.cobodgv.Text)); } if (chkTxt1.chkSel) { exp.And((b) => b.item0.Contains(chkTxt1.txt.Text)); } if (chkTxt2.chkSel) { exp.And((b) => b.zongliang.Contains(chkTxt2.txt.Text)); } } else { if (chkDanHao.chkSel) { exp.And((b) => b.dingdanhao == (chkDanHao.txt.Text)); } if (chkKehu.chkSel) { exp.And((b) => b.kehu == (chkKehu.cobodgv.Text)); } if (chkPinmin.chkSel) { exp.And((b) => b.pingmin == (chkPinmin.cobodgv.Text)); } if (chkZPS.chkSel) { exp.And((b) => b.pishu == (chkZPS.txt.Text)); } if (chkZZ.chkSel) { exp.And((b) => b.seming == (chkZZ.cobodgv.Text)); } if (chkPS.chkSel) { exp.And((b) => b.sehao == (chkPS.cobodgv.Text)); } if (chkTxt1.chkSel) { exp.And((b) => b.item0 == (chkTxt1.txt.Text)); } if (chkTxt2.chkSel) { exp.And((b) => b.zongliang == (chkTxt2.txt.Text)); } } DateTime dateTime; string str1 = "1970-01-01 00:00:00", str2 = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); if (this.chkDtime1.chkFsel) { dateTime = chkDtime1.dtimeF.Value; str1 = dateTime.ToString("yyyy-MM-dd HH:mm:ss"); } if (this.chkDtime1.chkTsel) { dateTime = chkDtime1.dtimeT.Value; str2 = dateTime.ToString("yyyy-MM-dd HH:mm:ss"); exp.And((b) => SqlFunc.Between(b.riqi, str1, str2)); UserProc.CheckTime = str1 + " - " + str2; } var getAll1 = db.Queryable <BaseIList>() .Where(exp.ToExpression()) .OrderBy((b) => b.riqi, OrderByType.Desc) .ToList(); return(getAll1); }
public static void Init() { Console.WriteLine(""); Console.WriteLine("#### DemoN_SplitTable Start ####"); SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { DbType = DbType.PostgreSQL, ConnectionString = Config.ConnectionString, InitKeyType = InitKeyType.Attribute, IsAutoCloseConnection = true }); db.Aop.OnLogExecuted = (s, p) => { Console.WriteLine(s); }; //初始化分表 db.CodeFirst.SplitTables().InitTables <OrderSpliteTest>(); Console.WriteLine(); //根据最近3个表进行查询 var list = db.Queryable <OrderSpliteTest>().Where(it => it.Pk == Guid.NewGuid()) .SplitTable(tabs => tabs.Take(3)) .Where(it => it.Time == DateTime.Now).ToOffsetPage(1, 2); Console.WriteLine(); //根据时间选出的表进行查询 var list2 = db.Queryable <OrderSpliteTest>().SplitTable(tabs => tabs.Where(it => it.Date >= DateTime.Now.AddYears(-2))).ToList(); Console.WriteLine(); //删除数据只在最近3张表执行操作 var x = db.Deleteable <OrderSpliteTest>().Where(it => it.Pk == Guid.NewGuid()).SplitTable(tabs => tabs.Take(3)).ExecuteCommand(); Console.WriteLine(); var tableName = db.SplitHelper <OrderSpliteTest>().GetTableName(DateTime.Now.AddDays(-1)); var tableName2 = db.SplitHelper(new OrderSpliteTest() { Time = DateTime.Now }).GetTableNames(); var tableName3 = db.SplitHelper(new List <OrderSpliteTest> { new OrderSpliteTest() { Time = DateTime.Now }, new OrderSpliteTest() { Time = DateTime.Now }, new OrderSpliteTest() { Time = DateTime.Now.AddMonths(-10) } }).GetTableNames(); var x2 = db.Updateable <OrderSpliteTest>() .SetColumns(it => it.Name == "a") .Where(it => it.Pk == Guid.NewGuid()) .SplitTable(tabs => tabs.InTableNames(tableName2)) .ExecuteCommand(); Console.WriteLine(); //按日分表 var x3 = db.Insertable(new OrderSpliteTest() { Name = "A" }).SplitTable().ExecuteCommand(); Console.WriteLine(); ////强制分表类型 var x4 = db.Insertable(new OrderSpliteTest() { Name = "A", Time = DateTime.Now.AddDays(-1) }).SplitTable().ExecuteCommand(); Console.WriteLine("#### CodeFirst end ####"); }
/// <summary> /// 测试select new /// </summary> /// <param name="p"></param> /// <param name="db"></param> private void SelectNew(Student2 p, SqlSugarClient db) { //测试用例 var queryable = db.Queryable <Student>().Where(c => c.id < 10) .Select <V_Student>(c => new V_Student { id = c.id, name = c.name, AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET" }); var list = queryable.ToList(); //多表操作将Student转换成V_Student var queryable2 = db.Queryable <Student>() .JoinTable <School>((s1, s2) => s1.sch_id == s2.id) .Where <School>((s1, s2) => s2.id < 10) .Select <School, V_Student>((s1, s2) => new V_Student() { id = s1.id, name = s1.name, AreaName = "默认地区", SchoolName = s2.name, SubjectName = "NET" }); //select new 目前只支持这种写法 var list2 = queryable2.ToList(); //select字符串 转换成V_Student var list3 = db.Queryable <Student>() .JoinTable <School>((s1, s2) => s1.sch_id == s2.id) .Where(s1 => s1.id <= 3) .Select <V_Student>("s1.*,s2.name SchoolName") .ToList(); //新容器转换函数的支持 只支持ObjToXXX和Convert.ToXXX var f1 = db.Queryable <InsertTest>().Select <Student>(it => new Student() { name = it.d1.ObjToString(), id = it.int1.ObjToInt() // 支持ObjToXXX 所有函数 }).ToList(); var f2 = db.Queryable <InsertTest>().Select <Student>(it => new Student() { name = Convert.ToString(it.d1),//支持Convet.ToXX所有函数 id = it.int1.ObjToInt(), sex = Convert.ToString(it.d1), }).ToList(); var f3 = db.Queryable <InsertTest>() .JoinTable <InsertTest>((i1, i2) => i1.id == i2.id) .Select <InsertTest, Student>((i1, i2) => new Student() { name = Convert.ToString(i1.d1), //多表查询例子 id = 1, sex = Convert.ToString(i2.d1), }).ToList(); //Select 外部参数用法 var f4 = db.Queryable <InsertTest>().Where("1=1", new { id = 100 }).Select <Student>(it => new Student() { id = "@id".ObjToInt(), //取的是 100 的值 name = "张三", //内部参数可以直接写 sex = it.txt, sch_id = it.id }).ToList(); var f6 = db.Queryable <InsertTest>() .JoinTable <InsertTest>((i1, i2) => i1.id == i2.id) .Where("1=1", new { id = 100, name = "张三", isOk = true }) //外部传参给@id .Select <InsertTest, Student>((i1, i2) => new Student() { name = "@name".ObjToString(), //多表查询例子 id = "@id".ObjToInt(), sex = i2.txt, sch_id = 1, isOk = "@isOk".ObjToBool() }).ToList(); try { //测试用例 db.Queryable <Student>().Where(c => c.id < 10) .Select <V_Student>(c => new V_Student { id = c.id, name = _p.name, AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET" }).ToList(); } catch (Exception ex) { Console.WriteLine(ex.Message); } try { //测试用例 db.Queryable <Student>().Where(c => c.id < 10) .Select <V_Student>(c => new V_Student { id = c.id, name = p.name, AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET" }).ToList(); } catch (Exception ex) { Console.WriteLine(ex.Message); } try { //测试用例 db.Queryable <Student>().Where(c => c.id < 10) .Select <V_Student>(c => new V_Student { id = c.id, name = Getp().name, AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET" }).ToList(); } catch (Exception ex) { Console.WriteLine(ex.Message); } try { //测试用例 db.Queryable <Student>().Where(c => c.id < 10) .Select <V_Student>(c => new V_Student { id = c.id, name = Getp2(), AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET" }).ToList(); } catch (Exception ex) { Console.WriteLine(ex.Message); } try { //测试用例 db.Queryable <InsertTest>() .JoinTable <InsertTest>((i1, i2) => i1.id == i2.id) .Select <InsertTest, Student>((i1, i2) => new Student() { name = Getp2(), //多表查询例子 id = i1.int1.ObjToInt(), sex = Convert.ToString(i2.d1), }).ToList(); } catch (Exception ex) { Console.WriteLine(ex.Message); } try { //测试用例 db.Queryable <InsertTest>() .JoinTable <InsertTest>((i1, i2) => i1.id == i2.id) .Select <InsertTest, Student>((i1, i2) => new Student() { name = Getp().name, //多表查询例子 id = i1.int1.ObjToInt(), sex = Convert.ToString(i2.d1), }).ToList(); } catch (Exception ex) { Console.WriteLine(ex.Message); } try { //测试用例 db.Queryable <InsertTest>() .JoinTable <InsertTest>((i1, i2) => i1.id == i2.id) .Select <InsertTest, Student>((i1, i2) => new Student() { name = Getp().name, //多表查询例子 id = i1.int1.ObjToInt(), sex = Convert.ToString(i2.d1), }).ToList(); } catch (Exception ex) { Console.WriteLine(ex.Message); } try { //测试用例 db.Queryable <InsertTest>() .JoinTable <InsertTest>((i1, i2) => i1.id == i2.id) .Select <InsertTest, Student>((i1, i2) => new Student() { name = Getp().name, //多表查询例子 id = i1.int1.ObjToInt() + 1, sex = Convert.ToString(i2.d1), }).ToList(); } catch (Exception ex) { Console.WriteLine(ex.Message); } try { //测试用例 db.Queryable <InsertTest>() .JoinTable <InsertTest>((i1, i2) => i1.id == i2.id) .Select <InsertTest, Student>((i1, i2) => new Student() { name = p.name, //多表查询例子 sex = Convert.ToString(i2.d1), }).ToList(); } catch (Exception ex) { Console.WriteLine(ex.Message); } }
public static void Init() { Console.WriteLine(""); Console.WriteLine("#### Insertable Start ####"); SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { DbType = DbType.SqlServer, ConnectionString = Config.ConnectionString, InitKeyType = InitKeyType.Attribute, IsAutoCloseConnection = true, AopEvents = new AopEvents { OnLogExecuting = (sql, p) => { Console.WriteLine(sql); Console.WriteLine(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value))); } } }); var insertObj = new Order() { Id = 1, Name = "order1", Price = 0 }; var insertObjs = new List <Order> { new Order() { Id = 11, Name = "order11", Price = 0 }, new Order() { Id = 12, Name = "order12", Price = 0 } }; //Ignore CreateTime db.Insertable(insertObj).IgnoreColumns(it => new { it.CreateTime }).ExecuteReturnIdentity();//get identity db.Insertable(insertObj).IgnoreColumns("CreateTime").ExecuteReturnIdentity(); //Only insert Name and Price db.Insertable(insertObj).InsertColumns(it => new { it.Name, it.Price }).ExecuteReturnIdentity(); db.Insertable(insertObj).InsertColumns("Name", "Price").ExecuteReturnIdentity(); //ignore null columns db.Insertable(insertObjs).ExecuteCommand();//get change row count //Use Lock db.Insertable(insertObj).With(SqlWith.UpdLock).ExecuteCommand(); insertObjs = new List <Order> { new Order() { Id = 11, Name = "order11", Price = 1 }, new Order() { Id = 12, Name = "order12", Price = 20, CreateTime = DateTime.Now, CustomId = 1 } }; db.Insertable(insertObjs).UseSqlServer().ExecuteBlueCopy(); var dt = db.Queryable <Order>().Take(5).ToDataTable(); dt.TableName = "Order"; db.Insertable(dt).UseSqlServer().ExecuteBlueCopy(); db.CodeFirst.InitTables <RootTable0, TwoItem, TwoItem2, TwoItem3>(); db.CodeFirst.InitTables <ThreeItem2>(); db.DbMaintenance.TruncateTable("RootTable0"); db.DbMaintenance.TruncateTable("TwoItem"); db.DbMaintenance.TruncateTable("TwoItem2"); db.DbMaintenance.TruncateTable("TwoItem3"); db.DbMaintenance.TruncateTable("ThreeItem2"); Console.WriteLine("SubInsert Start"); db.Insertable(new Order() { Name = "订单 1", CustomId = 1, Price = 100, CreateTime = DateTime.Now, Id = 0, Items = new List <OrderItem>() { new OrderItem() { CreateTime = DateTime.Now, OrderId = 0, Price = 1, ItemId = 1 }, new OrderItem() { CreateTime = DateTime.Now, OrderId = 0, Price = 2, ItemId = 2 } } }) .AddSubList(it => it.Items.First().OrderId).ExecuteReturnPrimaryKey(); db.Insertable(new List <RootTable0>() { new RootTable0() { Name = "aa", TwoItem2 = new TwoItem2() { Id = "1", ThreeItem2 = new List <ThreeItem2>() { new ThreeItem2() { Name = "a", TwoItem2Id = "1" }, new ThreeItem2() { Id = 2, Name = "a2", TwoItem2Id = "2" } } }, TwoItem = new TwoItem() { Name = "itema", RootId = 2 }, TwoItem3 = new List <TwoItem3>() { new TwoItem3() { Id = 0, Name = "a", Desc = "" }, } }, new RootTable0() { Name = "bb", TwoItem2 = new TwoItem2() { Id = "2" }, TwoItem = new TwoItem() { Name = "itemb", RootId = 2, }, TwoItem3 = new List <TwoItem3>() { new TwoItem3() { Id = 1, Name = "b", Desc = "" }, new TwoItem3() { Id = 2, Name = "b1", Desc = "1" }, } } }) .AddSubList(it => it.TwoItem.RootId) .AddSubList(it => new SubInsertTree() { Expression = it.TwoItem2.RootId, ChildExpression = new List <SubInsertTree>() { new SubInsertTree() { Expression = it.TwoItem2.ThreeItem2.First().TwoItem2Id } } }) .AddSubList(it => it.TwoItem3) .ExecuteReturnPrimaryKey(); SubNoIdentity(db); SubIdentity(db); Console.WriteLine("#### Insertable End ####"); }
/// <summary> /// 测试select new /// </summary> /// <param name="p"></param> /// <param name="db"></param> private void SelectNew(Student2 p, SqlSugarClient db) { //测试用例 var queryable = db.Queryable<Student>().Where(c => c.id < 10) .Select<V_Student>(c => new V_Student { id = c.id, name = c.name, AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET" }); var list = queryable.ToList(); //多表操作将Student转换成V_Student var queryable2 = db.Queryable<Student>() .JoinTable<School>((s1, s2) => s1.sch_id == s2.id) .Where<School>((s1, s2) => s2.id < 10) .Select<School, V_Student>((s1, s2) => new V_Student() { id = s1.id, name = s1.name, AreaName = "默认地区", SchoolName = s2.name, SubjectName = "NET" });//select new 目前只支持这种写法 var list2 = queryable2.ToList(); //select字符串 转换成V_Student var list3 = db.Queryable<Student>() .JoinTable<School>((s1, s2) => s1.sch_id == s2.id) .Where(s1 => s1.id <= 3) .Select<V_Student>("s1.*,s2.name SchoolName") .ToList(); //新容器转换函数的支持 只支持ObjToXXX和Convert.ToXXX var f1 = db.Queryable<InsertTest>().Select<Student>(it => new Student() { name = it.d1.ObjToString(), id = it.int1.ObjToInt() // 支持ObjToXXX 所有函数 }).ToList(); var f2 = db.Queryable<InsertTest>().Select<Student>(it => new Student() { name = Convert.ToString(it.d1),//支持Convet.ToXX所有函数 id = it.int1.ObjToInt(), sex = Convert.ToString(it.d1), }).ToList(); var f3 = db.Queryable<InsertTest>() .JoinTable<InsertTest>((i1, i2) => i1.id == i2.id) .Select<InsertTest, Student>((i1, i2) => new Student() { name = Convert.ToString(i1.d1), //多表查询例子 id = i1.int1.ObjToInt(), sex = Convert.ToString(i2.d1), }).ToList(); //Select 外部参数用法 var f4 = db.Queryable<InsertTest>().Where("1=1", new { id = 100 }).Select<Student>(it => new Student() { id = "@id".ObjToInt(), //取的是 100 的值 name = "张三",//内部参数可以直接写 sex = it.txt, sch_id = it.id }).ToList(); var f6 = db.Queryable<InsertTest>() .JoinTable<InsertTest>((i1, i2) => i1.id == i2.id) .Where("1=1", new { id = 100, name = "张三", isOk = true }) //外部传参给@id .Select<InsertTest, Student>((i1, i2) => new Student() { name = "@name".ObjToString(), //多表查询例子 id = "@id".ObjToInt(), sex = i2.txt, sch_id = 1, isOk = "@isOk".ObjToBool() }).ToList(); try { //测试用例 db.Queryable<Student>().Where(c => c.id < 10) .Select<V_Student>(c => new V_Student { id = c.id, name = _p.name, AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET" }).ToList(); } catch (Exception ex) { Console.WriteLine(ex.Message); } try { //测试用例 db.Queryable<Student>().Where(c => c.id < 10) .Select<V_Student>(c => new V_Student { id = c.id, name = p.name, AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET" }).ToList(); } catch (Exception ex) { Console.WriteLine(ex.Message); } try { //测试用例 db.Queryable<Student>().Where(c => c.id < 10) .Select<V_Student>(c => new V_Student { id = c.id, name = Getp().name, AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET" }).ToList(); } catch (Exception ex) { Console.WriteLine(ex.Message); } try { //测试用例 db.Queryable<Student>().Where(c => c.id < 10) .Select<V_Student>(c => new V_Student { id = c.id, name = Getp2(), AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET" }).ToList(); } catch (Exception ex) { Console.WriteLine(ex.Message); } try { //测试用例 db.Queryable<InsertTest>() .JoinTable<InsertTest>((i1, i2) => i1.id == i2.id) .Select<InsertTest, Student>((i1, i2) => new Student() { name = Getp2(), //多表查询例子 id = i1.int1.ObjToInt(), sex = Convert.ToString(i2.d1), }).ToList(); } catch (Exception ex) { Console.WriteLine(ex.Message); } try { //测试用例 db.Queryable<InsertTest>() .JoinTable<InsertTest>((i1, i2) => i1.id == i2.id) .Select<InsertTest, Student>((i1, i2) => new Student() { name = Getp().name, //多表查询例子 id = i1.int1.ObjToInt(), sex = Convert.ToString(i2.d1), }).ToList(); } catch (Exception ex) { Console.WriteLine(ex.Message); } try { //测试用例 db.Queryable<InsertTest>() .JoinTable<InsertTest>((i1, i2) => i1.id == i2.id) .Select<InsertTest, Student>((i1, i2) => new Student() { name = Getp().name, //多表查询例子 id = i1.int1.ObjToInt(), sex = Convert.ToString(i2.d1), }).ToList(); } catch (Exception ex) { Console.WriteLine(ex.Message); } try { //测试用例 db.Queryable<InsertTest>() .JoinTable<InsertTest>((i1, i2) => i1.id == i2.id) .Select<InsertTest, Student>((i1, i2) => new Student() { name = Getp().name, //多表查询例子 id = i1.int1.ObjToInt() + 1, sex = Convert.ToString(i2.d1), }).ToList(); } catch (Exception ex) { Console.WriteLine(ex.Message); } try { //测试用例 db.Queryable<InsertTest>() .JoinTable<InsertTest>((i1, i2) => i1.id == i2.id) .Select<InsertTest, Student>((i1, i2) => new Student() { name = p.name, //多表查询例子 sex = Convert.ToString(i2.d1), }).ToList(); } catch (Exception ex) { Console.WriteLine(ex.Message); } }
private static void DistributedTransactionExample() { Console.WriteLine(""); Console.WriteLine("#### Distributed TransactionExample Start ####"); SqlSugarClient db = new SqlSugarClient(new List <ConnectionConfig>() { new ConnectionConfig() { ConfigId = "1", DbType = DbType.PostgreSQL, ConnectionString = Config.ConnectionString, InitKeyType = InitKeyType.Attribute, IsAutoCloseConnection = true }, new ConnectionConfig() { ConfigId = "2", DbType = DbType.PostgreSQL, ConnectionString = Config.ConnectionString2, InitKeyType = InitKeyType.Attribute, IsAutoCloseConnection = true } }); var db1 = db.Ado.Connection.Database; //use db1 db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Order), typeof(OrderItem));// db.Insertable(new Order() { Name = "order1", CreateTime = DateTime.Now }).ExecuteCommand(); Console.WriteLine(db.CurrentConnectionConfig.DbType + ":" + db.Queryable <Order>().Count()); //use db2 db.ChangeDatabase("2"); var db2 = db.Ado.Connection.Database; db.DbMaintenance.CreateDatabase();//Create Database2 db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Order), typeof(OrderItem)); db.Insertable(new Order() { Name = "order1", CreateTime = DateTime.Now }).ExecuteCommand(); Console.WriteLine(db.CurrentConnectionConfig.DbType + ":" + db.Queryable <Order>().Count()); if (db2 == db1) { return; } // Example 1 Console.WriteLine("Example 1"); try { db.BeginTran(); db.ChangeDatabase("1");//use db1 db.Deleteable <Order>().ExecuteCommand(); Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType); Console.WriteLine(db.Queryable <Order>().Count()); db.ChangeDatabase("2");//use db2 db.Deleteable <Order>().ExecuteCommand(); Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType); Console.WriteLine(db.Queryable <Order>().Count()); throw new Exception(); db.CommitTran(); } catch { db.RollbackTran(); Console.WriteLine("---Roll back"); db.ChangeDatabase("1");//use db1 Console.WriteLine(db.CurrentConnectionConfig.DbType); Console.WriteLine(db.Queryable <Order>().Count()); db.ChangeDatabase("2");//use db2 Console.WriteLine(db.CurrentConnectionConfig.DbType); Console.WriteLine(db.Queryable <Order>().Count()); } // Example 2 Console.WriteLine("Example 2"); var result = db.UseTran(() => { db.ChangeDatabase("1");//use db1 db.Deleteable <Order>().ExecuteCommand(); Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType); Console.WriteLine(db.Queryable <Order>().Count()); db.ChangeDatabase("2");//use db2 db.Deleteable <Order>().ExecuteCommand(); Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType); Console.WriteLine(db.Queryable <Order>().Count()); throw new Exception(""); }); if (result.IsSuccess == false) { Console.WriteLine("---Roll back"); db.ChangeDatabase("1");//use db1 Console.WriteLine(db.CurrentConnectionConfig.DbType); Console.WriteLine(db.Queryable <Order>().Count()); db.ChangeDatabase("2");//use db2 Console.WriteLine(db.CurrentConnectionConfig.DbType); Console.WriteLine(db.Queryable <Order>().Count()); } // Example 3 Console.WriteLine("Example 3"); var result2 = db.UseTranAsync(async() => { db.ChangeDatabase("1");//use db1 await db.Deleteable <Order>().ExecuteCommandAsync(); Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType); Console.WriteLine(db.Queryable <Order>().Count()); db.ChangeDatabase("2");//use db2 await db.Deleteable <Order>().ExecuteCommandAsync(); Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType); Console.WriteLine(db.Queryable <Order>().Count()); throw new Exception(""); }); result2.Wait(); if (result2.Result.IsSuccess == false) { Console.WriteLine("---Roll back"); db.ChangeDatabase("1");//use sqlserver Console.WriteLine(db.CurrentConnectionConfig.DbType); Console.WriteLine(db.Queryable <Order>().Count()); db.ChangeDatabase("2");//use mysql Console.WriteLine(db.CurrentConnectionConfig.DbType); Console.WriteLine(db.Queryable <Order>().Count()); } Console.WriteLine("#### Distributed TransactionExample End ####"); }
/// <summary> /// 拉姆达测试 /// </summary> /// <param name="p"></param> /// <param name="db"></param> private void Exp(Student2 p, SqlSugarClient db) { //解析拉姆达各种情况组合 //基本 var t1 = db.Queryable<Student>().Where(it => it.id == 1).ToList(); var t2 = db.Queryable<Student>().Where(it => it.id == p.id).ToList(); var t3 = db.Queryable<Student>().Where(it => it.id == _p.id).ToList(); var t4 = db.Queryable<Student>().Where(it => it.id == Getp().id).ToList(); var t5 = db.Queryable<Student>().Where(it => it.id == MyCost.p.id).ToList(); //BOOL var t11 = db.Queryable<Student2>("Student").Where(it => it.isOk).ToList(); var t21 = db.Queryable<Student2>("Student").Where(it => it.isOk == MyCost.p.isOk).ToList(); var t31 = db.Queryable<Student2>("Student").Where(it => !it.isOk).ToList(); var t41 = db.Queryable<Student2>("Student").Where(it => it.isOk == true).ToList(); var t51 = db.Queryable<Student2>("Student").Where(it => it.isOk == false).ToList(); var t61 = db.Queryable<Student2>("Student").Where(it => it.isOk == _p.isOk).ToList(); var t71 = db.Queryable<Student2>("Student").Where(it => !it.isOk && !p.isOk == it.isOk).ToList(); var t91 = db.Queryable<Student2>("Student").Where(it => _p.isOk == false).ToList(); var t81 = db.Queryable<Student>().Where(it => it.isOk == false).ToList(); var t111 = db.Queryable<Student2>("Student").Where(it => it.isOk && false).ToList(); //length var c1 = db.Queryable<Student>().Where(c => c.name.Length > 4).ToList(); var c2 = db.Queryable<Student>().Where(c => c.name.Length > _p.name.Length).ToList(); var c3 = db.Queryable<Student>().Where(c => c.name.Length > "aa".Length).ToList(); var c4 = db.Queryable<Student>().Where(c => c.name.Length > Getp().id).ToList(); //Equals var a1 = db.Queryable<Student>().Where(c => c.name.Equals(null)).ToList(); var x = new InsertTest() { }; var x1 = db.Queryable<Student>().Where(c => c.name.Equals(x.int1)).ToList(); var a2 = db.Queryable<Student>().Where(c => c.name.Equals(p.name)).ToList(); var a4 = db.Queryable<Student>().Where(c => c.name.Equals(Getp().name)).ToList(); //Contains var s = db.Queryable<Student>().Where(c => c.name.Contains(null)).ToList(); var s0 = new InsertTest() { }; var s1 = db.Queryable<Student>().Where(c => c.name.Contains(x.v1)).ToList(); var s3 = db.Queryable<Student>().Where(c => c.name.Contains(p.name)).ToList(); var s4 = db.Queryable<Student>().Where(c => c.name.Contains(Getp().name)).ToList(); var s5 = db.Queryable<Student>().Where(c => c.name.StartsWith(Getp().name)).ToList(); var s6 = db.Queryable<Student>().Where(c => c.name.EndsWith(Getp().name)).ToList(); //异常处理测试,防止程序中出现未知错误 try { var e6 = db.Queryable<Student>().Where(c => Getp().name.StartsWith(c.name)).ToList(); } catch (Exception ex) { Console.WriteLine(ex.Message); } try { var e6 = db.Queryable<Student>().Where(c => Getp().name.Equals(c.name)).ToList(); } catch (Exception ex) { Console.WriteLine(ex.Message); } try { var e6 = db.Queryable<Student>().Where(c => c.name.First() != null).ToList(); } catch (Exception ex) { Console.WriteLine(ex.Message); } //组合测试 var z = db.Queryable<Student>().Where(c => (c.name.Equals(Getp().name) || c.name == p.name) && true && c.id > 1).ToList(); var z23 = db.Queryable<Student>().Where(c => !string.IsNullOrEmpty(c.name) || (c.id == 1 || c.name.Contains(p.name))).ToList(); var z2 = db.Queryable<Student>().Where(c => !string.IsNullOrEmpty(c.name) || !string.IsNullOrEmpty(c.name)).ToList(); }
/// <summary> /// 拉姆达测试 /// </summary> /// <param name="p"></param> /// <param name="db"></param> private void Exp(Student2 p, SqlSugarClient db) { //解析拉姆达各种情况组合 //基本 var t1 = db.Queryable <Student>().Where(it => it.id == 1).ToList(); var t2 = db.Queryable <Student>().Where(it => it.id == p.id).ToList(); var t3 = db.Queryable <Student>().Where(it => it.id == _p.id).ToList(); var t4 = db.Queryable <Student>().Where(it => it.id == Getp().id).ToList(); var t5 = db.Queryable <Student>().Where(it => it.id == MyCost.p.id).ToList(); //BOOL var t11 = db.Queryable <Student2>("Student").Where(it => it.isOk).ToList(); var t21 = db.Queryable <Student2>("Student").Where(it => it.isOk == MyCost.p.isOk).ToList(); var t31 = db.Queryable <Student2>("Student").Where(it => !it.isOk).ToList(); var t41 = db.Queryable <Student2>("Student").Where(it => it.isOk == true).ToList(); var t51 = db.Queryable <Student2>("Student").Where(it => it.isOk == false).ToList(); var t61 = db.Queryable <Student2>("Student").Where(it => it.isOk == _p.isOk).ToList(); var t71 = db.Queryable <Student2>("Student").Where(it => !it.isOk && !p.isOk == it.isOk).ToList(); var t91 = db.Queryable <Student2>("Student").Where(it => _p.isOk == false).ToList(); var t81 = db.Queryable <Student>().Where(it => it.isOk == false).ToList(); var t111 = db.Queryable <Student2>("Student").Where(it => it.isOk && false).ToList(); //length var c1 = db.Queryable <Student>().Where(c => c.name.Length > 4).ToList(); var c2 = db.Queryable <Student>().Where(c => c.name.Length > _p.name.Length).ToList(); var c3 = db.Queryable <Student>().Where(c => c.name.Length > "aa".Length).ToList(); var c4 = db.Queryable <Student>().Where(c => c.name.Length > Getp().id).ToList(); //Equals var a1 = db.Queryable <Student>().Where(c => c.name.Equals(null)).ToList(); var x = new InsertTest() { }; var x1 = db.Queryable <Student>().Where(c => c.name.Equals(x.int1)).ToList(); var a2 = db.Queryable <Student>().Where(c => c.name.Equals(p.name)).ToList(); var a4 = db.Queryable <Student>().Where(c => c.name.Equals(Getp().name)).ToList(); //Contains var s = db.Queryable <Student>().Where(c => c.name.Contains(null)).ToList(); var s0 = new InsertTest() { }; var s1 = db.Queryable <Student>().Where(c => c.name.Contains(x.v1)).ToList(); var s3 = db.Queryable <Student>().Where(c => c.name.Contains(p.name)).ToList(); var s4 = db.Queryable <Student>().Where(c => c.name.Contains(Getp().name)).ToList(); var s5 = db.Queryable <Student>().Where(c => c.name.StartsWith(Getp().name)).ToList(); var s6 = db.Queryable <Student>().Where(c => c.name.EndsWith(Getp().name)).ToList(); //异常处理测试,防止程序中出现未知错误 try { var e6 = db.Queryable <Student>().Where(c => Getp().name.StartsWith(c.name)).ToList(); } catch (Exception ex) { Console.WriteLine(ex.Message); } try { var e6 = db.Queryable <Student>().Where(c => Getp().name.Equals(c.name)).ToList(); } catch (Exception ex) { Console.WriteLine(ex.Message); } try { var e6 = db.Queryable <Student>().Where(c => c.name.First() != null).ToList(); } catch (Exception ex) { Console.WriteLine(ex.Message); } //组合测试 var z = db.Queryable <Student>().Where(c => (c.name.Equals(Getp().name) || c.name == p.name) && true && c.id > 1).ToList(); var z23 = db.Queryable <Student>().Where(c => !string.IsNullOrEmpty(c.name) || (c.id == 1 || c.name.Contains(p.name))).ToList(); var z2 = db.Queryable <Student>().Where(c => !string.IsNullOrEmpty(c.name) || !string.IsNullOrEmpty(c.name)).ToList(); }
/// <summary> /// 查询测试 /// </summary> /// <param name="p"></param> /// <param name="d"></param> private void Select(Student2 p, SqlSugarClient db) { //查询第一条 var select1= db.Queryable<Student>().Single(it => it.id == p.id); var select2 = db.Queryable<Student>().Single(it => it.id == _p.id); //查询分页 id3-id5 var select3 = db.Queryable<Student>().OrderBy(it=>it.id).Skip(2).Take(3).ToList(); //查询分页 id4-id6 var select4 = db.Queryable<Student>().OrderBy(it => it.id).ToPageList(2,3).ToList(); //2表join var join1 = db.Queryable<Student>().JoinTable<School>((st, sc) => st.sch_id == sc.id) .Select<School,V_Student>((st, sc) => new V_Student() {SchoolName=sc.name,id=st.id, name=st.name}).ToList(); //3表join var join2 = db.Queryable<Student>() .JoinTable<School>((st, sc) => st.sch_id == sc.id) .JoinTable<School,Area>((st,sc,a)=>sc.AreaId==a.id) .Select<School,Area, V_Student>((st, sc,a) => new V_Student() { SchoolName = sc.name, id = st.id, name = st.name,AreaName=a.name }).ToList(); }