/// <summary>
/// 查询多条记录，包含授权状态的
/// </summary>
/// <param name="key">角色的Guid</param>
/// <param name="menuKey">菜单的Guid</param>
/// <returns></returns>
public Task <ApiResult <Page <SysBtnFunDto> > > GetPagesAsync(string key, string menuKey)
{
var res = new ApiResult <Page <SysBtnFunDto> >();
try
{
using (Db)
{
var query = Db.Queryable <SysBtnFun>()
.Where(m => m.MenuGuid.Contains(menuKey))
.Select(it => new SysBtnFunDto()
{
Guid = SqlFunc.GetSelfAndAutoFill(it.Guid),
Status = SqlFunc.Subqueryable <SysPermissions>().Where(g => g.BtnFunGuid == it.Guid && g.Types == 3 && g.MenuGuid == menuKey && g.RoleGuid == key).Any()
})
.ToPage(1, 100);
res.success = true;
res.message = "获取成功！";
res.data = query;
}
}
catch (Exception ex)
{
res.message = ApiEnum.Error.GetEnumText() + ex.Message;
res.statusCode = (int)ApiEnum.Error;
}
return(Task.Run(() => res));
}
private void Multiple2()
{
Expression <Func <Student, School, object> > exp = (it, school) => new ViewModelStudent3()
{
SchoolName = school.Name, Id = SqlFunc.GetSelfAndAutoFill(it.Id)
};
OracleExpressionContext expContext = new OracleExpressionContext();
expContext.IsSingle = false;
expContext.Resolve(exp, ResolveExpressType.SelectMultiple);
var selectorValue = expContext.Result.GetString();
var pars = expContext.Parameters;
base.Check(
selectorValue,
pars,
@" ""SCHOOL"".""NAME"" AS ""SCHOOLNAME"" ,it.*",
new List <SugarParameter>()
{
},
"Select.Multiple Error");
}
public void Q2()
{
using (var db = GetInstance())
{
var t1 = db.Queryable <Student>().ToSql();
base.Check("SELECT `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent`", null, t1.Key, null, "single t1 Error");
var t2 = db.Queryable <Student>().With(SqlWith.NoLock).ToSql();
base.Check("SELECT `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` ", null, t2.Key, null, "single t2 Error");
var t3 = db.Queryable <Student>().OrderBy(it => it.Id).ToSql();
base.Check("SELECT `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` ORDER BY `ID` ASC", null, t3.Key, null, "single t3 Error");
var t4 = db.Queryable <Student>().OrderBy(it => it.Id).Take(3).ToSql();
base.Check(@"SELECT `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` ORDER BY `ID` ASC LIMIT 0,3", null, t4.Key, null, "single t4 Error");
var t5 = db.Queryable <Student>().OrderBy(it => it.Id).Skip(3).ToSql();
base.Check(@"SELECT `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` LIMIT 3,9223372036854775807", null, t5.Key, null, "single t5 Error");
int pageIndex = 2;
int pageSize = 10;
var t6 = db.Queryable <Student>().OrderBy(it => it.Id, OrderByType.Desc).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToSql();
base.Check(@"SELECT `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` ORDER BY `ID` DESC LIMIT 10,10", null, t6.Key, null, "single t6 Error");
int studentCount = db.Ado.GetInt("select count(1) from Student");
var countIsSuccess = db.Queryable <Student>().Count() == studentCount;
if (!countIsSuccess)
{
throw new Exception(" single countIsSuccess Error");
}
var t7 = db.Queryable <Student>().OrderBy(it => it.Id, OrderByType.Desc).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToPageList(pageIndex, pageSize, ref studentCount);
countIsSuccess = studentCount == db.Queryable <Student>().OrderBy(it => it.Id, OrderByType.Desc).Skip((pageIndex - 1) * pageSize).Take(pageSize * pageIndex).Count();
if (!countIsSuccess)
{
throw new Exception("single t7 Error");
}
int studentMin = db.Ado.GetInt("select min(id) from Student");
var minIsSuccess = db.Queryable <Student>().Min(it => it.Id) == studentMin;
if (!minIsSuccess)
{
throw new Exception("single minIsSuccess Error");
}
int studentMax = db.Ado.GetInt("select max(id) from Student");
var maxIsSuccess = db.Queryable <Student>().Max(it => it.Id) == studentMax;
if (!maxIsSuccess)
{
throw new Exception("single maxIsSuccess Error");
}
int studentAvg = db.Ado.GetInt("select avg(id) from Student");
var avgIsSuccess = db.Queryable <Student>().Avg(it => it.Id) == studentAvg;
if (!maxIsSuccess)
{
throw new Exception(" single avgIsSuccess Error");
}
int studentSum = db.Ado.GetInt("select sum(id) from Student");
var sumIsSuccess = db.Queryable <Student>().Sum(it => it.Id) == studentSum;
if (!sumIsSuccess)
{
throw new Exception("single sumIsSuccess Error");
}
var t8 = db.Queryable <Student>()
.Where(it => it.Id == 1)
.WhereIF(true, it => SqlFunc.Contains(it.Name, "a"))
.OrderBy(it => it.Id, OrderByType.Desc).Skip((pageIndex - 1) * pageSize).Take(pageSize).With(SqlWith.NoLock).ToSql();
base.Check(@"SELECT `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` WHERE ( `ID` = @Id0 ) AND (`Name` like concat('%',@MethodConst1,'%')) ORDER BY `ID` DESC LIMIT 10,10", new List <SugarParameter>()
{
new SugarParameter("@Id0", 1), new SugarParameter("@MethodConst1", "a")
}, t8.Key, t8.Value, "single t8 Error");
var t9 = db.Queryable <Student>()
.In(1)
.Select(it => new { it.Id, it.Name, x = it.Id }).ToSql();
base.Check("SELECT `ID` AS `Id` , `Name` AS `Name` , `ID` AS `x` FROM `STudent` WHERE `ID` IN (@InPara0) ", new List <SugarParameter>()
{
new SugarParameter("@InPara0", 1)
}, t9.Key, t9.Value, "single t9 error");
var t10 = db.Queryable <Student>().Select(it => new StudentEnum()
{
Id = SqlFunc.GetSelfAndAutoFill(it.Id)
}).ToSql();
base.Check("SELECT * FROM `STudent` ", null, t10.Key, t10.Value, "single t10 error");
}
}
public void Q2()
{
using (var db = GetInstance())
{
var t1 = db.Queryable <Student>().ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent]", null, t1.Key, null, "single t1 Error");
var t2 = db.Queryable <Student>().With(SqlWith.NoLock).ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent] WITH(NOLOCK)", null, t2.Key, null, "single t2 Error");
var t3 = db.Queryable <Student>().OrderBy(it => it.Id).ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent] ORDER BY [ID] ASC", null, t3.Key, null, "single t3 Error");
var t4 = db.Queryable <Student>().OrderBy(it => it.Id).Take(3).ToSql();
base.Check(@"SELECT * FROM (SELECT [ID],[SchoolId],[Name],[CreateTime],ROW_NUMBER() OVER(ORDER BY [ID] ASC) AS RowIndex FROM [STudent] ) T WHERE RowIndex BETWEEN 1 AND 3", null, t4.Key, null, "single t4 Error");
var t5 = db.Queryable <Student>().OrderBy(it => it.Id).Skip(3).ToSql();
base.Check(@"SELECT * FROM (SELECT [ID],[SchoolId],[Name],[CreateTime],ROW_NUMBER() OVER(ORDER BY [ID] ASC) AS RowIndex FROM [STudent] ) T WHERE RowIndex BETWEEN 4 AND 9223372036854775807", null, t5.Key, null, "single t5 Error");
int pageIndex = 2;
int pageSize = 10;
var t6 = db.Queryable <Student>().OrderBy(it => it.Id, OrderByType.Desc).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToSql();
base.Check(@"SELECT * FROM (SELECT [ID],[SchoolId],[Name],[CreateTime],ROW_NUMBER() OVER(ORDER BY [ID] DESC) AS RowIndex FROM [STudent] ) T WHERE RowIndex BETWEEN 11 AND 20", null, t6.Key, null, "single t6 Error");
int studentCount = db.Ado.GetInt("select count(1) from Student");
var countIsSuccess = db.Queryable <Student>().Count() == studentCount;
if (!countIsSuccess)
{
throw new Exception(" single countIsSuccess Error");
}
var t7 = db.Queryable <Student>().OrderBy(it => it.Id, OrderByType.Desc).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToPageList(pageIndex, pageSize, ref studentCount);
countIsSuccess = studentCount == db.Queryable <Student>().OrderBy(it => it.Id, OrderByType.Desc).Skip((pageIndex - 1) * pageSize).Take(pageSize * pageIndex).Count();
if (!countIsSuccess)
{
throw new Exception("single t7 Error");
}
int studentMin = db.Ado.GetInt("select min(id) from Student");
var minIsSuccess = db.Queryable <Student>().Min(it => it.Id) == studentMin;
if (!minIsSuccess)
{
throw new Exception("single minIsSuccess Error");
}
int studentMax = db.Ado.GetInt("select max(id) from Student");
var maxIsSuccess = db.Queryable <Student>().Max(it => it.Id) == studentMax;
if (!maxIsSuccess)
{
throw new Exception("single maxIsSuccess Error");
}
int studentAvg = db.Ado.GetInt("select avg(id) from Student");
var avgIsSuccess = db.Queryable <Student>().Avg(it => it.Id) == studentAvg;
if (!maxIsSuccess)
{
throw new Exception(" single avgIsSuccess Error");
}
int studentSum = db.Ado.GetInt("select sum(id) from Student");
var sumIsSuccess = db.Queryable <Student>().Sum(it => it.Id) == studentSum;
if (!sumIsSuccess)
{
throw new Exception("single sumIsSuccess Error");
}
var t8 = db.Queryable <Student>()
.Where(it => it.Id == 1)
.WhereIF(true, it => SqlFunc.Contains(it.Name, "a"))
.OrderBy(it => it.Id, OrderByType.Desc).Skip((pageIndex - 1) * pageSize).Take(pageSize).With(SqlWith.NoLock).ToSql();
base.Check(@"SELECT * FROM (SELECT [ID],[SchoolId],[Name],[CreateTime],ROW_NUMBER() OVER(ORDER BY [ID] DESC) AS RowIndex FROM [STudent] WITH(NOLOCK) WHERE ( [ID] = @Id0 ) AND ([Name] like '%'+@MethodConst1+'%') ) T WHERE RowIndex BETWEEN 11 AND 20", new List <SugarParameter>()
{
new SugarParameter("@Id0", 1), new SugarParameter("@MethodConst1", "a")
}, t8.Key, t8.Value, "single t8 Error");
var t9 = db.Queryable <Student>()
.In(1)
.Select(it => new { it.Id, it.Name, x = it.Id }).ToSql();
base.Check("SELECT [ID] AS [Id] , [Name] AS [Name] , [ID] AS [x] FROM [STudent] WHERE [Id] IN (@InPara0) ", new List <SugarParameter>()
{
new SugarParameter("@InPara0", 1)
}, t9.Key, t9.Value, "single t9 error");
var t10 = db.Queryable <Student>().Select(it => new StudentEnum()
{
Id = SqlFunc.GetSelfAndAutoFill(it.Id)
}).ToSql();
base.Check("SELECT * FROM [STudent] ", null, t10.Key, t10.Value, "single t10 error");
var t11 = db.Queryable <Student>().GroupBy("id").OrderBy("id").Select("id").ToSql();
base.Check("SELECT id FROM [STudent] GROUP BY id ORDER BY id ", null, t11.Key, t11.Value, "single t11 error");
var t12 = db.Queryable <Student>().Where(it => it.Id != null).ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent] WHERE ( [ID] IS NOT NULL )", null, t12.Key, t12.Value, "single t12 error");
}
}
public void Q2()
{
using (var db = GetInstance())
{
var t1 = db.Queryable <Student>().ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent]", null, t1.Key, null, "single t1 Error");
var t2 = db.Queryable <Student>().With(SqlWith.NoLock).ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent] WITH(NOLOCK)", null, t2.Key, null, "single t2 Error");
var t3 = db.Queryable <Student>().OrderBy(it => it.Id).ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent] ORDER BY [ID] ASC", null, t3.Key, null, "single t3 Error");
var t4 = db.Queryable <Student>().OrderBy(it => it.Id).Take(3).ToSql();
base.Check(@"SELECT * FROM (SELECT [ID],[SchoolId],[Name],[CreateTime],ROW_NUMBER() OVER(ORDER BY [ID] ASC) AS RowIndex FROM [STudent] ) T WHERE RowIndex BETWEEN 1 AND 3", null, t4.Key, null, "single t4 Error");
var t5 = db.Queryable <Student>().OrderBy(it => it.Id).Skip(3).ToSql();
base.Check(@"SELECT * FROM (SELECT [ID],[SchoolId],[Name],[CreateTime],ROW_NUMBER() OVER(ORDER BY [ID] ASC) AS RowIndex FROM [STudent] ) T WHERE RowIndex BETWEEN 4 AND 9223372036854775807", null, t5.Key, null, "single t5 Error");
int pageIndex = 2;
int pageSize = 10;
var t6 = db.Queryable <Student>().OrderBy(it => it.Id, OrderByType.Desc).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToSql();
base.Check(@"SELECT * FROM (SELECT [ID],[SchoolId],[Name],[CreateTime],ROW_NUMBER() OVER(ORDER BY [ID] DESC) AS RowIndex FROM [STudent] ) T WHERE RowIndex BETWEEN 11 AND 20", null, t6.Key, null, "single t6 Error");
int studentCount = db.Ado.GetInt("select count(1) from Student");
var countIsSuccess = db.Queryable <Student>().Count() == studentCount;
if (!countIsSuccess)
{
throw new Exception(" single countIsSuccess Error");
}
var t7 = db.Queryable <Student>().OrderBy(it => it.Id, OrderByType.Desc).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToPageList(pageIndex, pageSize, ref studentCount);
countIsSuccess = studentCount == db.Queryable <Student>().OrderBy(it => it.Id, OrderByType.Desc).Skip((pageIndex - 1) * pageSize).Take(pageSize * pageIndex).Count();
if (!countIsSuccess)
{
throw new Exception("single t7 Error");
}
int studentMin = db.Ado.GetInt("select min(id) from Student");
var minIsSuccess = db.Queryable <Student>().Min(it => it.Id) == studentMin;
if (!minIsSuccess)
{
throw new Exception("single minIsSuccess Error");
}
int studentMax = db.Ado.GetInt("select max(id) from Student");
var maxIsSuccess = db.Queryable <Student>().Max(it => it.Id) == studentMax;
if (!maxIsSuccess)
{
throw new Exception("single maxIsSuccess Error");
}
int studentAvg = db.Ado.GetInt("select avg(id) from Student");
var avgIsSuccess = db.Queryable <Student>().Avg(it => it.Id) == studentAvg;
if (!maxIsSuccess)
{
throw new Exception(" single avgIsSuccess Error");
}
int studentSum = db.Ado.GetInt("select sum(id) from Student");
var sumIsSuccess = db.Queryable <Student>().Sum(it => it.Id) == studentSum;
if (!sumIsSuccess)
{
throw new Exception("single sumIsSuccess Error");
}
var t8 = db.Queryable <Student>()
.Where(it => it.Id == 1)
.WhereIF(true, it => SqlFunc.Contains(it.Name, "a"))
.OrderBy(it => it.Id, OrderByType.Desc).Skip((pageIndex - 1) * pageSize).Take(pageSize).With(SqlWith.NoLock).ToSql();
base.Check(@"SELECT * FROM (SELECT [ID],[SchoolId],[Name],[CreateTime],ROW_NUMBER() OVER(ORDER BY [ID] DESC) AS RowIndex FROM [STudent] WITH(NOLOCK) WHERE ( [ID] = @Id0 ) AND ([Name] like '%'+@MethodConst1+'%') ) T WHERE RowIndex BETWEEN 11 AND 20", new List <SugarParameter>()
{
new SugarParameter("@Id0", 1), new SugarParameter("@MethodConst1", "a")
}, t8.Key, t8.Value, "single t8 Error");
var t9 = db.Queryable <Student>()
.In(1)
.Select(it => new { it.Id, it.Name, x = it.Id }).ToSql();
base.Check("SELECT [ID] AS [Id] , [Name] AS [Name] , [ID] AS [x] FROM [STudent] WHERE [Id] IN (@InPara0) ", new List <SugarParameter>()
{
new SugarParameter("@InPara0", 1)
}, t9.Key, t9.Value, "single t9 error");
var t10 = db.Queryable <Student>().Select(it => new StudentEnum()
{
Id = SqlFunc.GetSelfAndAutoFill(it.Id)
}).ToSql();
base.Check("SELECT * FROM [STudent] ", null, t10.Key, t10.Value, "single t10 error");
var t11 = db.Queryable <Student>().GroupBy("id").OrderBy("id").Select("id").ToSql();
base.Check("SELECT id FROM [STudent] GROUP BY id ORDER BY id ", null, t11.Key, t11.Value, "single t11 error");
var t12 = db.Queryable <Student>().Where(it => it.Id != null).ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent] WHERE ( [ID] IS NOT NULL )", null, t12.Key, t12.Value, "single t12 error");
var id = 1;
var t13 = db.Queryable <Student>().Where(it => SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id && s.Id == id).Max(s => s.Id) == 1).ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent] it WHERE ((SELECT MAX([Id]) FROM [School] WHERE (( [Id] = [it].[ID] ) AND ( [Id] = @Id0 ))) = @Const1 )",
new List <SugarParameter>()
{
new SugarParameter("@Id0", 1),
new SugarParameter("@Const1", 1)
}, t13.Key, t13.Value, "single t13 error ");
var t14 = db.Queryable <Student>()
.Where(it => it.Name == "a" && SqlFunc.HasValue(it.Name)).ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent] WHERE (( [Name] = @Name0 ) AND ( [Name]<>'' AND [Name] IS NOT NULL ))",
new List <SugarParameter>()
{
new SugarParameter("@Name0", "a")
}, t14.Key, t14.Value, "single t14 error ");
var t15 = db.Queryable <CapitalEntity>()
.Select(x => new
{
TGYArea = SqlFunc.AggregateSum(SqlFunc.IIF(x.FlatProp == "1", x.Areas, 0))
}).ToSql();
base.Check("SELECT SUM(( CASE WHEN ( [FlatProp] = @FlatProp0 ) THEN [Areas] ELSE @MethodConst1 END )) AS [TGYArea] FROM [RENT_CAPITAL] ", new List <SugarParameter>()
{
new SugarParameter("@FlatProp0", "1"),
new SugarParameter("@MethodConst1", 0)
}, t15.Key, t15.Value, "single t15 error");
}
}
public void Init()
{
var x2 = DB.Queryable <School>().Where(x => x.Id == SqlFunc.Subqueryable <School>().Where(y => y.Id == SqlFunc.Subqueryable <Student>().Where(yy => y.Id == x.Id).Select(yy => yy.Id)).Select(y => y.Id)).ToSql();
if (!x2.Key.Contains("STudent"))
{
// throw new Exception("bug2 error");
}
var UserNameOrName = "111";
var OrganizationUnitId = 0;
var RoleId = 0;
var sql = DB.Queryable <User>().//一对多的子查询
WhereIF(!string.IsNullOrWhiteSpace(UserNameOrName), t1 => t1.Name.Contains(UserNameOrName)).
Where(t1 =>
SqlFunc.Subqueryable <UserOrganizationUnit>().
Where(t2 => t2.UserId == t1.Id).
WhereIF(OrganizationUnitId > 0, t2 => t2.OrganizationUnitId == OrganizationUnitId).Any())
// Where(t1 => SqlFunc.Subqueryable<UserRole>().
//Where(t3 => t3.UserId == t1.Id).
//WhereIF(RoleId > 0, t3 => t3.RoleId == RoleId).Any())
.Select(t1 => new User {
Id = SqlFunc.GetSelfAndAutoFill(t1.Id)
}).ToSql();
var model = DB.Queryable <ClientsModel, VipAccountsModel, AccountsModel, tLogonHistoryModel, VipBenefitsModel, LevelSettingModel, JewelsModel>((a, b, c, d, e, f, g) => new object[] {
JoinType.Left, a.ClientID == b.ClientID,
JoinType.Left, a.ClientID == c.ClientID && c.TournamentID == 0,
JoinType.Left, a.ClientID == d.ClientID,
JoinType.Left, (e.MinVipCredit <= b.VipCredit && e.MaxVipCredit >= b.VipCredit) && (e.MinConsumeAmount <= b.AccumulatedConsumeAmount && e.MaxConsumeAmount >= b.AccumulatedConsumeAmount),
JoinType.Left, (c.ExperiencePoints >= f.MinExperiencePoints && c.ExperiencePoints < f.MaxExperiencePoints) || (c.ExperiencePoints > f.MaxExperiencePoints && f.UserLevel == 30),
JoinType.Left, g.ClientID == a.ClientID
})
.WhereIF(true, (a, b, c, d, e, f, g) => a.ClientID == 1)
.WhereIF(!string.IsNullOrEmpty("a"), (a, b, c, d, e, f, g) => a.NickName == "a")
.Select((a, b, c, d, e, f, g) => new
{
GoldAmount = SqlFunc.Subqueryable <ExposureModel>().Where(s => s.TournamentID == 0 && s.ClientID == a.ClientID).Sum(s => SqlFunc.IsNull(SqlFunc.AggregateSum(s.Exposure), 0)),
ClientID = a.ClientID,
NickName = a.NickName,
UserChannel = a.UserChannel,
CountryCode = d.CountryCode,
Platform = a.Platform,
Email = a.Email,
PhoneNumber = a.PhoneNumber,
RegisteredTime = a.RegisteredTime,
DiamondAmount = SqlFunc.IsNull(g.JewelCount, 0),
AccumulatedRechargeAmount = SqlFunc.IsNull(b.AccumulatedRechargeAmount, 0),
VipLevel = SqlFunc.IsNull(e.VipLevel, 0),
UserLevel = SqlFunc.IsNull(f.UserLevel, 0)
})
.With(SqlWith.NoLock)
.ToSql();
var _sql = DB.Insertable(new UserInfo
{
BrandId = -1,
UserLevel = 1
}).IgnoreColumns(m => new { m.BlockingTime, m.CreditUpdatetime }).ToSql();
var _sql2 = DB.Insertable(new UserInfo
{
BrandId = -1,
UserLevel = 1
}).IgnoreColumns(m => new { m.UserId }).ToSql();
var _sql3 = DB.Updateable(new UserInfo
{
BrandId = -1,
UserLevel = 1
}).IgnoreColumns(m => new { m.CreditUpdatetime, m.UserId }).ToSql();
DB.CodeFirst.InitTables(typeof(DataTest));
DB.Insertable(new DataTest()).ExecuteCommand();
// 初始化实体表
DB.CodeFirst.SetStringDefaultLength(255).InitTables(typeof(TestA));
var testa = new TestA();
testa.Col1 = "2333333";
testa.Col3 = "444";
DB.Saveable(testa).ExecuteCommand();
Guid newCarTypePictureId = Guid.Empty;
Guid carTypePictureId = Guid.Empty;
DB.CodeFirst.InitTables(typeof(Picture), typeof(JobPlan));
DB.Updateable <Picture>()
.UpdateColumns(p => p.Value == SqlFunc.Subqueryable <Picture>()
.Where(pp => pp.ID == newCarTypePictureId)
.Select(pp => pp.Value))
.Where(p => p.ID == carTypePictureId)
.ExecuteCommand();
DB.Updateable <Picture>()
.UpdateColumns(p => p.Value == SqlFunc.Subqueryable <Picture>()
.Select(pp => pp.Value))
.Where(p => p.ID == carTypePictureId).ExecuteCommand();
var list = new List <JobPlan>()
{
new JobPlan()
{
},
new JobPlan()
{
}
};
DB.Updateable(new JobPlan()
{
})
.WhereColumns(s => new { s.CmdNo })
.UpdateColumns(s => new
{
s.HeatNo,
s.CmdNo
}).ExecuteCommand();
DB.CodeFirst.InitTables(typeof(VMaterialInfo), typeof(TStock), typeof(TTempStock));
var GoodsList = DB.Queryable <VMaterialInfo, TStock>((vmg, ts) => new object[] {
JoinType.Left, vmg.FMICode == ts.FMICode
})
.Select((vmg, ts) => new
{
AbleQty = SqlFunc.ToInt32(ts.FQty - SqlFunc.Subqueryable <TTempStock>().Where(s => s.FMICode == vmg.FMICode && s.FK_Store == "")
.Select(s => SqlFunc.AggregateSum(s.FKCSL)))
}).ToList();
var GoodsList2 = DB.Queryable <VMaterialInfo, TStock>((vmg, ts) => new object[] {
JoinType.Left, vmg.FMICode == ts.FMICode
})
.Where((vmg, ts) => ts.FK_Store == "" && vmg.FMICode == vmg.FMICode)
.Select((vmg, ts) => new
{
PKID = vmg.PKID,
FMICode = vmg.FMICode,
FMIName = vmg.FMIName,
FGauge = vmg.FGauge,
FBIName = vmg.FBIName,
FK_FOrigin = vmg.FK_FOrigin,
FOEM = vmg.FOEM,
FSIName = vmg.FSIName,
FUIName = vmg.FUIName,
OutFQty = SqlFunc.ToInt32(ts.FQty)
,
InFQty = SqlFunc.Subqueryable <TStock>().Where(s => s.FMICode == ts.FMICode && s.FK_Store == "").Select(s => SqlFunc.ToInt32(SqlFunc.IsNull(s.FQty, 0)))
,
TempQty = SqlFunc.IsNull(SqlFunc.Subqueryable <TTempStock>().Where(s => s.FMICode == vmg.FMICode && s.FK_Store == "")
.GroupBy(s => new { s.FMICode, s.FK_Store })
.Select(s => SqlFunc.AggregateSum(SqlFunc.ToInt32(s.FKCSL))), 0)
,
AbleQty = ts.FQty - SqlFunc.Subqueryable <TTempStock>().Where(s => s.FMICode == vmg.FMICode && s.FK_Store == "")
.Select(s => SqlFunc.AggregateSum(s.FKCSL))
}).ToList();
DB.CodeFirst.InitTables <h5linkpassloginfo, logtype>();
DB.Updateable <h5linkpassloginfo>().UpdateColumns(it =>
new h5linkpassloginfo()
{
LogKeyId = SqlFunc.Subqueryable <logtype>().Where(s => s.LogKey == "openpage").Select(s => s.Id),
StrVal = "sdsdsdsd"
}).Where(it => it.Id == 1).ExecuteCommand();
}
