public void TestDistinctCount()
{
var fsql = g.sqlite;
var sql = fsql.Select <ts_up_dywhere01>().ToSql(a => SqlExt.DistinctCount(a.status));
fsql.Select <ts_up_dywhere01>().Aggregate(a => SqlExt.DistinctCount(a.Key.status), out var count);
Assert.Equal(@"SELECT count(distinct a.""status"") as1
FROM ""ts_up_dywhere01"" a", sql);
}
public async Task <List <BaseTimeModel> > GetServiceHeatMap(BasicFilter filter, List <string> Time)
{
var format = GetDateFormat(filter);
var expression = GetServiceExpression(filter);
string[] span = { "0-200", "200-400", "400-600", "600-800", "800-1000", "1000-1200", "1200-1400", "1400-1600", "1600+" };
var list = await freeSql.Select <RequestInfo>().Where(expression)
.GroupBy(x => new
{
KeyField = SqlExt.Case()
.When(0 < x.Milliseconds && x.Milliseconds <= 200, "0-200")
.When(200 < x.Milliseconds && x.Milliseconds <= 400, "200-400")
.When(400 < x.Milliseconds && x.Milliseconds <= 600, "400-600")
.When(600 < x.Milliseconds && x.Milliseconds <= 800, "600-800")
.When(800 < x.Milliseconds && x.Milliseconds <= 1000, "800-1000")
.When(1000 < x.Milliseconds && x.Milliseconds <= 1200, "1000-1200")
.When(1200 < x.Milliseconds && x.Milliseconds <= 1400, "1200-1400")
.When(1400 < x.Milliseconds && x.Milliseconds <= 1600, "1400-1600")
.Else("1600+").End(),
TimeField = x.CreateTime.ToString(format)
}).ToListAsync(x => new BaseTimeModel
{
KeyField = x.Key.KeyField,
TimeField = x.Key.TimeField,
ValueField = x.Count()
});
var model = new List <BaseTimeModel>();
foreach (var t in Time)
{
foreach (var s in span)
{
var c = list.Where(x => x.TimeField == t && x.KeyField == s).FirstOrDefault();
model.Add(new BaseTimeModel
{
TimeField = t,
KeyField = s,
ValueField = c == null ? 0 : c.ValueField
});
}
}
return(model);
}
public void Test03()
{
var sqlxx = g.pgsql.InsertOrUpdate <userinfo>().SetSource(new userinfo {
userid = 10
}).UpdateColumns(a => new { a.birthday, a.CardNo }).ToSql();
var aff1 = g.sqlite.GetRepository <Edi, long>().Delete(10086);
var aff2 = g.sqlite.Delete <Edi>(10086).ExecuteAffrows();
Assert.Equal(aff1, aff2);
var testStringFormat = g.sqlite.Select <Edi>().First(a => new {
str = $"x{a.Id}_{DateTime.Now.ToString("yyyyMM")}z",
str2 = string.Format("{0}x{0}_{1}z", a.Id, DateTime.Now.ToString("yyyyMM"))
});
var sql123 = g.sqlserver.Select <Edi>()
.WithSql(
g.sqlserver.Select <Edi>().ToSql(a => new { a.Id }, FieldAliasOptions.AsProperty) +
" UNION ALL " +
g.sqlserver.Select <Edi>().ToSql(a => new { a.Id }, FieldAliasOptions.AsProperty))
.Page(1, 10).ToSql("Id");
var sqlextMax1 = g.mysql.Select <EdiItem>()
.GroupBy(a => a.Id)
.ToSql(a => new
{
Id = a.Key, EdiId = SqlExt.Max(a.Key).Over().ToValue()
});
var sqlextGroupConcat = g.mysql.Select <Edi, EdiItem>()
.InnerJoin((a, b) => b.Id == a.Id)
.ToSql((a, b) => new
{
Id = a.Id,
EdiId = b.Id,
case1 = SqlExt.Case()
.When(a.Id == 1, 10)
.When(a.Id == 2, 11)
.When(a.Id == 3, 12)
.When(a.Id == 4, 13)
.When(a.Id == 5, SqlExt.Case().When(b.Id == 1, 10000).Else(999).End())
.End(),
groupct1 = SqlExt.GroupConcat(a.Id).Distinct().OrderBy(b.EdiId).Separator("_").ToValue()
});
var sqlextGroupConcatToList = g.mysql.Select <Edi, EdiItem>()
.InnerJoin((a, b) => b.Id == a.Id)
.ToList((a, b) => new
{
Id = a.Id,
EdiId = b.Id,
case1 = SqlExt.Case()
.When(a.Id == 1, 10)
.When(a.Id == 2, 11)
.When(a.Id == 3, 12)
.When(a.Id == 4, 13)
.When(a.Id == 5, SqlExt.Case().When(b.Id == 1, 10000).Else(999).End())
.End(),
groupct1 = SqlExt.GroupConcat(a.Id).Distinct().OrderBy(b.EdiId).Separator("_").ToValue()
});
var sqlextCase = g.sqlserver.Select <Edi, EdiItem>()
.InnerJoin((a, b) => b.Id == a.Id)
.ToSql((a, b) => new
{
Id = a.Id,
EdiId = b.Id,
case1 = SqlExt.Case()
.When(a.Id == 1, 10)
.When(a.Id == 2, 11)
.When(a.Id == 3, 12)
.When(a.Id == 4, 13)
.When(a.Id == 5, SqlExt.Case().When(b.Id == 1, 10000).Else(999).End())
.End(),
over1 = SqlExt.Rank().Over().OrderBy(a.Id).OrderByDescending(b.EdiId).ToValue(),
});
var sqlextCaseToList = g.sqlserver.Select <Edi, EdiItem>()
.InnerJoin((a, b) => b.Id == a.Id)
.ToList((a, b) => new
{
Id = a.Id,
EdiId = b.Id,
case1 = SqlExt.Case()
.When(a.Id == 1, 10)
.When(a.Id == 2, 11)
.When(a.Id == 3, 12)
.When(a.Id == 4, 13)
.When(a.Id == 5, SqlExt.Case().When(b.Id == 1, 10000).Else(999).End())
.End(),
over1 = SqlExt.Rank().Over().OrderBy(a.Id).OrderByDescending(b.EdiId).ToValue(),
});
var sqlextOver = g.sqlserver.Select <Edi, EdiItem>()
.InnerJoin((a, b) => b.Id == a.Id)
.ToSql((a, b) => new
{
Id = a.Id,
EdiId = b.Id,
over1 = SqlExt.Rank().Over().OrderBy(a.Id).OrderByDescending(b.EdiId).ToValue()
});
var sqlextOverToList = g.sqlserver.Select <Edi, EdiItem>()
.InnerJoin((a, b) => b.Id == a.Id)
.ToList((a, b) => new
{
Id = a.Id,
EdiId = b.Id,
over1 = SqlExt.Rank().Over().OrderBy(a.Id).OrderByDescending(b.EdiId).ToValue()
});
var tttrule = 8;
var tttid = new long[] { 18, 19, 4017 };
g.sqlserver.Update <Author123>().Set(it => it.SongId == (short)(it.SongId & ~tttrule)).Where(it => (it.SongId & tttrule) == tttrule && !tttid.Contains(it.Id)).ExecuteAffrows();
g.sqlite.Delete <Song123>().Where("1=1").ExecuteAffrows();
g.sqlite.Delete <Author123>().Where("1=1").ExecuteAffrows();
g.sqlite.Insert(new Song123(1)).ExecuteAffrows();
g.sqlite.Insert(new Author123(11, 1)).ExecuteAffrows();
var song = g.sqlite.Select <Song123>()
.From <Author123>((a, b) => a.InnerJoin(a1 => a1.Id == b.SongId))
.First((a, b) => a); // throw error
Console.WriteLine(song == null);
g.sqlite.Select <Edi>().ToList();
var itemId2 = 2;
var itemId = 1;
var edi = g.sqlite.Select <Edi>()
.Where(a => a.Id == itemId2 && g.sqlite.Select <EdiItem>().Where(b => b.Id == itemId).Any())
.First(a => a); //#231
var lksdjkg1 = g.sqlite.Select <Edi>()
.AsQueryable().Where(a => a.Id > 0).Where(a => a.Id == 1).ToList();
var lksdjkg11 = g.sqlite.Select <Edi>()
.AsQueryable().Where(a => a.Id > 0).Where(a => a.Id == 1).Any();
var lksdjkg2 = g.sqlite.Select <Edi>()
.AsQueryable().Where(a => a.Id > 0).First();
var lksdjkg3 = g.sqlite.Select <Edi>()
.AsQueryable().Where(a => a.Id > 0).FirstOrDefault();
var sql222efe = g.sqlite.Select <Edi, EdiItem>()
.InnerJoin((a, b) => b.Id == g.sqlite.Select <EdiItem>().As("c").Where(c => c.EdiId == a.Id).OrderBy(c => c.Id).ToOne(c => c.Id))
.ToSql((a, b) => new
{
Id = a.Id,
EdiId = b.Id
});
var subSyetemId = "xxx";
var list = g.sqlite.Select <Menu, SubSystem>()
.LeftJoin((a, b) => a.SubNameID == b.Id)
.WhereIf(!string.IsNullOrEmpty(subSyetemId), (a, s) => a.SubNameID == subSyetemId)
.ToList((a, s) => new Menu
{
ID = a.ID,
SystemName = s.Name,
SubNameID = s.Id,
CreateTime = a.CreateTime,
Description = a.Description,
EnName = a.EnName,
Name = a.Name,
OperationIds = a.OperationIds,
Parent = a.Parent,
ParentID = a.ParentID,
Url = a.Url,
UserID = a.UserID
});
var context = new TestDbContext(g.sqlite);
var sql = context.Songs
.Where(a =>
context.Authors
//.Select //加上这句就不报错，不加上报 variable 'a' of type 'Song' referenced from scope '', but it is not defined
.Where(b => b.SongId == a.Id)
.Any())
.ToSql(a => a.Name);
sql = context.Songs
.Where(a =>
context.Authors
.Select //加上这句就不报错，不加上报 variable 'a' of type 'Song' referenced from scope '', but it is not defined
.Where(b => b.SongId == a.Id)
.Any())
.ToSql(a => a.Name);
//using (var conn = new SqlConnection("Data Source=.;Integrated Security=True;Initial Catalog=webchat-abc;Pooling=true;Max Pool Size=13"))
//{
// conn.Open();
// conn.Close();
//}
//using (var fsql = new FreeSql.FreeSqlBuilder()
// .UseConnectionString(FreeSql.DataType.SqlServer, "Data Source=.;Integrated Security=True;Initial Catalog=webchat-abc;Pooling=true;Max Pool Size=13")
// .UseAutoSyncStructure(true)
// //.UseGenerateCommandParameterWithLambda(true)
// .UseMonitorCommand(
// cmd => Trace.WriteLine("\r\n线程" + Thread.CurrentThread.ManagedThreadId + ": " + cmd.CommandText) //监听SQL命令对象，在执行前
// //, (cmd, traceLog) => Console.WriteLine(traceLog)
// )
// .UseLazyLoading(true)
// .Build())
//{
// fsql.Select<ut3_t1>().ToList();
//}
//var testByte = new TestByte { pic = File.ReadAllBytes(@"C:\Users\28810\Desktop\71500003-0ad69400-289e-11ea-85cb-36a54f52ebc0.png") };
//var sql = g.sqlserver.Insert(testByte).NoneParameter().ToSql();
//g.sqlserver.Insert(testByte).NoneParameter().ExecuteAffrows();
//var getTestByte = g.sqlserver.Select<TestByte>(testByte).First();
//File.WriteAllBytes(@"C:\Users\28810\Desktop\71500003-0ad69400-289e-11ea-85cb-36a54f52ebc0_write.png", getTestByte.pic);
var ib = new IdleBus <IFreeSql>(TimeSpan.FromMinutes(10));
ib.Notice += (_, e2) => Trace.WriteLine($"[{DateTime.Now.ToString("HH:mm:ss")}] 线程{Thread.CurrentThread.ManagedThreadId}：{e2.Log}");
ib.Register("db1", () => new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.MySql, "Data Source=127.0.0.1;Port=3306;User ID=root;Password=root;Initial Catalog=cccddd;Charset=utf8;SslMode=none;Max pool size=3")
.UseAutoSyncStructure(true)
.UseGenerateCommandParameterWithLambda(true)
.UseMonitorCommand(cmd => Trace.WriteLine("\r\n线程" + Thread.CurrentThread.ManagedThreadId + ": " + cmd.CommandText))
.UseLazyLoading(true)
.Build());
ib.Register("db2", () => new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.Oracle, "user id=user1;password=123456;data source=//127.0.0.1:1521/XE;Pooling=true;Max Pool Size=3")
.UseAutoSyncStructure(true)
.UseGenerateCommandParameterWithLambda(true)
.UseLazyLoading(true)
.UseNameConvert(FreeSql.Internal.NameConvertType.ToUpper)
.UseMonitorCommand(cmd => Trace.WriteLine("\r\n线程" + Thread.CurrentThread.ManagedThreadId + ": " + cmd.CommandText))
.Build());
ib.Register("db3", () => new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.Sqlite, @"Data Source=|DataDirectory|\document.db;Attachs=xxxtb.db;Pooling=true;Max Pool Size=3")
.UseAutoSyncStructure(true)
.UseGenerateCommandParameterWithLambda(true)
.UseLazyLoading(true)
.UseMonitorCommand(cmd => Trace.WriteLine("\r\n线程" + Thread.CurrentThread.ManagedThreadId + ": " + cmd.CommandText))
.Build());
//...注入很多个
var fsql = ib.Get("db1"); //使用的时候用 Get 方法，不要存其引用关系
var sqlparamId = 100;
fsql.Select <ut3_t1>().Limit(10).Where(a => a.id == sqlparamId).ToList();
fsql = ib.Get("db2");
fsql.Select <ut3_t1>().Limit(10).Where(a => a.id == sqlparamId).ToList();
fsql = ib.Get("db3");
fsql.Select <ut3_t1>().Limit(10).Where(a => a.id == sqlparamId).ToList();
fsql = g.sqlserver;
fsql.Insert <OrderMain>(new OrderMain {
OrderNo = "1001", OrderTime = new DateTime(2019, 12, 01)
}).ExecuteAffrows();
fsql.Insert <OrderDetail>(new OrderDetail {
OrderNo = "1001", ItemNo = "I001", Qty = 1
}).ExecuteAffrows();
fsql.Insert <OrderDetail>(new OrderDetail {
OrderNo = "1001", ItemNo = "I002", Qty = 1
}).ExecuteAffrows();
fsql.Insert <OrderDetail>(new OrderDetail {
OrderNo = "1001", ItemNo = "I003", Qty = 1
}).ExecuteAffrows();
fsql.Insert <OrderMain>(new OrderMain {
OrderNo = "1002", OrderTime = new DateTime(2019, 12, 02)
}).ExecuteAffrows();
fsql.Insert <OrderDetail>(new OrderDetail {
OrderNo = "1002", ItemNo = "I011", Qty = 1
}).ExecuteAffrows();
fsql.Insert <OrderDetail>(new OrderDetail {
OrderNo = "1002", ItemNo = "I012", Qty = 1
}).ExecuteAffrows();
fsql.Insert <OrderDetail>(new OrderDetail {
OrderNo = "1002", ItemNo = "I013", Qty = 1
}).ExecuteAffrows();
fsql.Ado.Query <object>("select * from orderdetail left join ordermain on orderdetail.orderno=ordermain.orderno where ordermain.orderno='1001'");
g.oracle.Delete <SendInfo>().Where("1=1").ExecuteAffrows();
g.oracle.Insert(new[]
{
new SendInfo {
Code = "001", Binary = Encoding.UTF8.GetBytes("我是中国人")
},
new SendInfo {
Code = "002", Binary = Encoding.UTF8.GetBytes("我是地球人")
},
new SendInfo {
Code = "003", Binary = Encoding.UTF8.GetBytes("我是.net")
},
new SendInfo {
Code = "004", Binary = Encoding.UTF8.GetBytes("我是freesql")
},
new SendInfo {
Code = "005", Binary = Encoding.UTF8.GetBytes("我是freesql233")
},
})
.NoneParameter()
.BatchOptions(3, 200)
.BatchProgress(a => Trace.WriteLine($"{a.Current}/{a.Total}"))
.ExecuteAffrows();
var slslsl = g.oracle.Select <SendInfo>().ToList();
var slsls1Ids = slslsl.Select(a => a.ID).ToArray();
var slslss2 = g.oracle.Select <SendInfo>().Where(a => slsls1Ids.Contains(a.ID)).ToList();
var mt_codeId = Guid.Parse("2f48c5ca-7257-43c8-9ee2-0e16fa990253");
Assert.Equal(1, g.oracle.Insert(new SendInfo {
ID = mt_codeId, Code = "mt_code", Binary = Encoding.UTF8.GetBytes("我是mt_code")
})
.ExecuteAffrows());
var mt_code = g.oracle.Select <SendInfo>().Where(a => a.ID == mt_codeId).First();
Assert.NotNull(mt_code);
Assert.Equal(mt_codeId, mt_code.ID);
Assert.Equal("mt_code", mt_code.Code);
mt_code = g.oracle.Select <SendInfo>().Where(a => a.ID == Guid.Parse("2f48c5ca725743c89ee20e16fa990253".ToUpper())).First();
Assert.NotNull(mt_code);
Assert.Equal(mt_codeId, mt_code.ID);
Assert.Equal("mt_code", mt_code.Code);
mt_codeId = Guid.Parse("2f48c5ca-7257-43c8-9ee2-0e16fa990251");
Assert.Equal(1, g.oracle.Insert(new SendInfo {
ID = mt_codeId, Code = "mt_code2", Binary = Encoding.UTF8.GetBytes("我是mt_code2")
})
.NoneParameter()
.ExecuteAffrows());
mt_code = g.oracle.Select <SendInfo>().Where(a => a.ID == mt_codeId).First();
Assert.NotNull(mt_code);
Assert.Equal(mt_codeId, mt_code.ID);
Assert.Equal("mt_code2", mt_code.Code);
mt_code = g.oracle.Select <SendInfo>().Where(a => a.ID == Guid.Parse("2f48c5ca725743c89ee20e16fa990251".ToUpper())).First();
Assert.NotNull(mt_code);
Assert.Equal(mt_codeId, mt_code.ID);
Assert.Equal("mt_code2", mt_code.Code);
var id = g.oracle.Insert(new TestORC12()).ExecuteIdentity();
}
public void Test03()
{
var tshop01sql = g.sqlite.Select <tshop01>().Include(a => a.cate).ToSql();
var testisnullsql1 = g.sqlite.Select <t102>().Where(a => SqlExt.IsNull(a.isxx, false).Equals(true)).ToSql();
var testisnullsql2 = g.sqlite.Select <t102>().Where(a => SqlExt.IsNull(a.isxx, false).Equals(false)).ToSql();
var guid1 = Guid.NewGuid();
var guid2 = Guid.NewGuid();
var guid3 = Guid.NewGuid();
var tqsql = g.sqlite.Select <tq01, t102, t102>()
.WithSql(
g.sqlite.Select <tq01>().As("sub1").Where(a => a.id == guid1).ToSql(),
g.sqlite.Select <t102>().As("sub2").Where(a => a.id == guid2).ToSql(),
g.sqlite.Select <t102>().As("sub3").Where(a => a.id == guid3).ToSql()
)
.LeftJoin((a, b, c) => a.id == b.id)
.LeftJoin((a, b, c) => b.id == c.id)
.ToSql();
var updateSql = g.sqlite.Update <object>()
.AsType(typeof(testInsertNullable))
.SetDto(new { str1 = "xxx" })
.WhereDynamic(1)
.ToSql();
var sqlextMax112 = g.sqlserver.Select <EdiItem>()
.GroupBy(a => a.Id)
.ToSql(a => new
{
Id = a.Key,
EdiId1 = SqlExt.Max(a.Key).Over().PartitionBy(new { a.Value.EdiId, a.Value.Id }).OrderByDescending(new { a.Value.EdiId, a.Value.Id }).ToValue(),
EdiId2 = SqlExt.Max(a.Key).Over().PartitionBy(a.Value.EdiId).OrderByDescending(a.Value.Id).ToValue(),
EdiId3 = SqlExt.Sum(a.Key).ToValue(),
EdiId4 = a.Sum(a.Key)
});
Assert.Throws <ArgumentException>(() => g.sqlite.Update <testUpdateNonePk>().SetSource(new testUpdateNonePk()).ExecuteAffrows());
g.sqlite.Insert(new testInsertNullable()).NoneParameter().ExecuteAffrows();
g.sqlite.Select <testInsertNullable>().Select(a => a.Id).ToList();
var ddlsql = g.sqlite.CodeFirst.GetComparisonDDLStatements(typeof(testInsertNullable), "tb123123");
Assert.Equal(@"CREATE TABLE IF NOT EXISTS ""main"".""tb123123"" (
""Id"" INTEGER PRIMARY KEY AUTOINCREMENT,
""str1"" NVARCHAR(255) NOT NULL,
""int1"" INTEGER NOT NULL,
""int2"" INTEGER ,
""price"" DECIMAL(10,5)
)
;
", ddlsql);
var select16Sql1 = g.sqlite.Select <userinfo, userinfo, userinfo, userinfo, userinfo, userinfo, userinfo, userinfo, userinfo, userinfo, userinfo, userinfo, userinfo, userinfo, userinfo, userinfo>()
.InnerJoin((a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p) => b.userid == a.userid)
.InnerJoin((a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p) => c.userid == b.userid)
.InnerJoin((a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p) => d.userid == c.userid)
.InnerJoin((a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p) => e.userid == d.userid)
.InnerJoin((a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p) => f.userid == e.userid)
.InnerJoin((a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p) => g.userid == f.userid)
.InnerJoin((a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p) => h.userid == g.userid)
.InnerJoin((a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p) => i.userid == h.userid)
.InnerJoin((a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p) => j.userid == i.userid)
.InnerJoin((a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p) => k.userid == j.userid)
.InnerJoin((a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p) => l.userid == k.userid)
.InnerJoin((a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p) => m.userid == l.userid)
.InnerJoin((a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p) => n.userid == m.userid)
.InnerJoin((a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p) => o.userid == n.userid)
.InnerJoin((a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p) => p.userid == o.userid)
.ToSql((a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p) => p);
Assert.Equal(@"SELECT p.""userid"" as1, p.""badgenumber"" as2, p.""ssn"" as3, p.""IDCardNo"" as4, p.""name"" as5, p.""title"" as6, p.""birthday"" as7, p.""hiredday"" as8, p.""hetongdate"" as9, p.""street"" as10, p.""zip"" as11, p.""ophone"" as12, p.""pager"" as13, p.""fphone"" as14, p.""CardNo"" as15, p.""email"" as16, p.""idcardvalidtime"" as17, p.""homeaddress"" as18, p.""minzu"" as19, p.""leavedate"" as20, p.""loginpass"" as21, p.""picurl"" as22, p.""managerid"" as23
FROM ""userinfo"" a
INNER JOIN ""userinfo"" b ON b.""userid"" = a.""userid""
INNER JOIN ""userinfo"" c ON c.""userid"" = b.""userid""
INNER JOIN ""userinfo"" d ON d.""userid"" = c.""userid""
INNER JOIN ""userinfo"" e ON e.""userid"" = d.""userid""
INNER JOIN ""userinfo"" f ON f.""userid"" = e.""userid""
INNER JOIN ""userinfo"" g ON g.""userid"" = f.""userid""
INNER JOIN ""userinfo"" h ON h.""userid"" = g.""userid""
INNER JOIN ""userinfo"" i ON i.""userid"" = h.""userid""
INNER JOIN ""userinfo"" j ON j.""userid"" = i.""userid""
INNER JOIN ""userinfo"" k ON k.""userid"" = j.""userid""
INNER JOIN ""userinfo"" l ON l.""userid"" = k.""userid""
INNER JOIN ""userinfo"" m ON m.""userid"" = l.""userid""
INNER JOIN ""userinfo"" n ON n.""userid"" = m.""userid""
INNER JOIN ""userinfo"" o ON o.""userid"" = n.""userid""
INNER JOIN ""userinfo"" p ON p.""userid"" = o.""userid""", select16Sql1);
var select16Sql2 = g.sqlite.Select <userinfo>()
.From <userinfo, userinfo, userinfo, userinfo, userinfo, userinfo, userinfo, userinfo, userinfo, userinfo, userinfo, userinfo, userinfo, userinfo, userinfo>(
(s, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p) => s
.InnerJoin(a => b.userid == a.userid)
.InnerJoin(a => c.userid == b.userid)
.InnerJoin(a => d.userid == c.userid)
.InnerJoin(a => e.userid == d.userid)
.InnerJoin(a => f.userid == e.userid)
.InnerJoin(a => g.userid == f.userid)
.InnerJoin(a => h.userid == g.userid)
.InnerJoin(a => i.userid == h.userid)
.InnerJoin(a => j.userid == i.userid)
.InnerJoin(a => k.userid == j.userid)
.InnerJoin(a => l.userid == k.userid)
.InnerJoin(a => m.userid == l.userid)
.InnerJoin(a => n.userid == m.userid)
.InnerJoin(a => o.userid == n.userid)
.InnerJoin(a => p.userid == o.userid)
)
.ToSql((a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p) => p);
Assert.Equal(@"SELECT p.""userid"" as1, p.""badgenumber"" as2, p.""ssn"" as3, p.""IDCardNo"" as4, p.""name"" as5, p.""title"" as6, p.""birthday"" as7, p.""hiredday"" as8, p.""hetongdate"" as9, p.""street"" as10, p.""zip"" as11, p.""ophone"" as12, p.""pager"" as13, p.""fphone"" as14, p.""CardNo"" as15, p.""email"" as16, p.""idcardvalidtime"" as17, p.""homeaddress"" as18, p.""minzu"" as19, p.""leavedate"" as20, p.""loginpass"" as21, p.""picurl"" as22, p.""managerid"" as23
FROM ""userinfo"" a
INNER JOIN ""userinfo"" b ON b.""userid"" = a.""userid""
INNER JOIN ""userinfo"" c ON c.""userid"" = b.""userid""
INNER JOIN ""userinfo"" d ON d.""userid"" = c.""userid""
INNER JOIN ""userinfo"" e ON e.""userid"" = d.""userid""
INNER JOIN ""userinfo"" f ON f.""userid"" = e.""userid""
INNER JOIN ""userinfo"" g ON g.""userid"" = f.""userid""
INNER JOIN ""userinfo"" h ON h.""userid"" = g.""userid""
INNER JOIN ""userinfo"" i ON i.""userid"" = h.""userid""
INNER JOIN ""userinfo"" j ON j.""userid"" = i.""userid""
INNER JOIN ""userinfo"" k ON k.""userid"" = j.""userid""
INNER JOIN ""userinfo"" l ON l.""userid"" = k.""userid""
INNER JOIN ""userinfo"" m ON m.""userid"" = l.""userid""
INNER JOIN ""userinfo"" n ON n.""userid"" = m.""userid""
INNER JOIN ""userinfo"" o ON o.""userid"" = n.""userid""
INNER JOIN ""userinfo"" p ON p.""userid"" = o.""userid""", select16Sql2);
var sqlxx = g.pgsql.InsertOrUpdate <userinfo>().SetSource(new userinfo {
userid = 10
}).UpdateColumns(a => new { a.birthday, a.CardNo }).ToSql();
var aff1 = g.sqlite.GetRepository <Edi, long>().Delete(10086);
var aff2 = g.sqlite.Delete <Edi>(10086).ExecuteAffrows();
Assert.Equal(aff1, aff2);
g.sqlserver.Delete <Edi>().Where("1=1").ExecuteAffrows();
g.sqlserver.Delete <EdiItem>().Where("1=1").ExecuteAffrows();
g.sqlserver.Insert(new[] { new Edi {
Id = 1
}, new Edi {
Id = 2
}, new Edi {
Id = 3
}, new Edi {
Id = 4
}, new Edi {
Id = 5
} }).ExecuteAffrows();
g.sqlserver.Insert(new[] {
new EdiItem {
Id = 1, EdiId = 1
}, new EdiItem {
Id = 2, EdiId = 1
}, new EdiItem {
Id = 3, EdiId = 1
},
new EdiItem {
Id = 4, EdiId = 2
}, new EdiItem {
Id = 5, EdiId = 2
},
new EdiItem {
Id = 6, EdiId = 3
}, new EdiItem {
Id = 7, EdiId = 3
},
new EdiItem {
Id = 8, EdiId = 4
}, new EdiItem {
Id = 9, EdiId = 4
},
new EdiItem {
Id = 10, EdiId = 5
}, new EdiItem {
Id = 11, EdiId = 5
},
}).ExecuteAffrows();
var testStringFormat = g.sqlite.Select <Edi>().First(a => new {
str = $"x{a.Id}_{DateTime.Now.ToString("yyyyMM")}z",
str2 = string.Format("{0}x{0}_{1}z", a.Id, DateTime.Now.ToString("yyyyMM"))
});
var sql123 = g.sqlserver.Select <Edi>()
.WithSql(
g.sqlserver.Select <Edi>().ToSql(a => new { a.Id }, FieldAliasOptions.AsProperty) +
" UNION ALL " +
g.sqlserver.Select <Edi>().ToSql(a => new { a.Id }, FieldAliasOptions.AsProperty))
.Page(1, 10).ToSql("Id");
var sqlextMax1 = g.sqlserver.Select <EdiItem>()
.GroupBy(a => a.Id)
.ToSql(a => new
{
Id = a.Key,
EdiId1 = SqlExt.Max(a.Key).Over().PartitionBy(new { a.Value.EdiId, a.Value.Id }).OrderByDescending(new { a.Value.EdiId, a.Value.Id }).ToValue(),
EdiId2 = SqlExt.Max(a.Key).Over().PartitionBy(a.Value.EdiId).OrderByDescending(a.Value.Id).ToValue(),
EdiId3 = SqlExt.Sum(a.Key).ToValue(),
EdiId4 = a.Sum(a.Key)
});
var sqlextIsNull = g.sqlserver.Select <EdiItem>()
.ToSql(a => new
{
nvl = SqlExt.IsNull(a.EdiId, 0)
});
var sqlextGroupConcat = g.mysql.Select <Edi, EdiItem>()
.InnerJoin((a, b) => b.Id == a.Id)
.ToSql((a, b) => new
{
Id = a.Id,
EdiId = b.Id,
case1 = SqlExt.Case()
.When(a.Id == 1, 10)
.When(a.Id == 2, 11)
.When(a.Id == 3, 12)
.When(a.Id == 4, 13)
.When(a.Id == 5, SqlExt.Case().When(b.Id == 1, 10000).Else(999).End())
.End(),
groupct1 = SqlExt.GroupConcat(a.Id).Distinct().OrderBy(b.EdiId).Separator("_").ToValue(),
testb1 = b == null ? 1 : 0,
testb2 = b != null ? 1 : 0,
});
var sqlextGroupConcatToList = g.mysql.Select <Edi, EdiItem>()
.InnerJoin((a, b) => b.Id == a.Id)
.ToList((a, b) => new
{
Id = a.Id,
EdiId = b.Id,
case1 = SqlExt.Case()
.When(a.Id == 1, 10)
.When(a.Id == 2, 11)
.When(a.Id == 3, 12)
.When(a.Id == 4, 13)
.When(a.Id == 5, SqlExt.Case().When(b.Id == 1, 10000).Else(999).End())
.End(),
groupct1 = SqlExt.GroupConcat(a.Id).Distinct().OrderBy(b.EdiId).Separator("_").ToValue(),
testb1 = b == null ? 1 : 0,
testb2 = b != null ? 1 : 0,
});
var sqlextCase = g.sqlserver.Select <Edi, EdiItem>()
.InnerJoin((a, b) => b.Id == a.Id)
.ToSql((a, b) => new
{
Id = a.Id,
EdiId = b.Id,
case1 = SqlExt.Case()
.When(a.Id == 1, 10)
.When(a.Id == 2, 11)
.When(a.Id == 3, 12)
.When(a.Id == 4, 13)
.When(a.Id == 5, SqlExt.Case().When(b.Id == 1, 10000).Else(999).End())
.End(),
over1 = SqlExt.Rank().Over().OrderBy(a.Id).OrderByDescending(b.EdiId).ToValue(),
});
var sqlextCaseToList = g.sqlserver.Select <Edi, EdiItem>()
.InnerJoin((a, b) => b.Id == a.Id)
.ToList((a, b) => new
{
Id = a.Id,
EdiId = b.Id,
case1 = SqlExt.Case()
.When(a.Id == 1, 10)
.When(a.Id == 2, 11)
.When(a.Id == 3, 12)
.When(a.Id == 4, 13)
.When(a.Id == 5, SqlExt.Case().When(b.Id == 1, 10000).Else(999).End())
.End(),
over1 = SqlExt.Rank().Over().OrderBy(a.Id).OrderByDescending(b.EdiId).ToValue(),
});
var sqlextCaseGroupBy1 = g.sqlserver.Select <Edi, EdiItem>()
.InnerJoin((a, b) => b.Id == a.Id)
.GroupBy((a, b) => new { aid = a.Id, bid = b.Id })
.ToDictionary(a => new
{
sum = a.Sum(a.Value.Item2.EdiId),
testb1 = a.Value.Item2 == null ? 1 : 0,
testb2 = a.Value.Item2 != null ? 1 : 0,
});
var sqlextCaseGroupBy2 = g.sqlserver.Select <Edi, EdiItem>()
.InnerJoin((a, b) => b.Id == a.Id)
.GroupBy((a, b) => new { aid = a.Id, bid = b.Id })
.ToList(a => new
{
a.Key, sum = a.Sum(a.Value.Item2.EdiId),
testb1 = a.Value.Item2 == null ? 1 : 0,
testb2 = a.Value.Item2 != null ? 1 : 0,
});
var sqlextOver = g.sqlserver.Select <Edi, EdiItem>()
.InnerJoin((a, b) => b.Id == a.Id)
.ToSql((a, b) => new
{
Id = a.Id,
EdiId = b.Id,
over1 = SqlExt.Rank().Over().OrderBy(a.Id).OrderByDescending(b.EdiId).ToValue()
});
var sqlextOverToList = g.sqlserver.Select <Edi, EdiItem>()
.InnerJoin((a, b) => b.Id == a.Id)
.ToList((a, b) => new
{
Id = a.Id,
EdiId = b.Id,
over1 = SqlExt.Rank().Over().OrderBy(a.Id).OrderByDescending(b.EdiId).ToValue()
});
var tttrule = 8;
var tttid = new long[] { 18, 19, 4017 };
g.sqlserver.Update <Author123>().Set(it => it.SongId == (short)(it.SongId & ~tttrule)).Where(it => (it.SongId & tttrule) == tttrule && !tttid.Contains(it.Id)).ExecuteAffrows();
g.sqlite.Delete <Song123>().Where("1=1").ExecuteAffrows();
g.sqlite.Delete <Author123>().Where("1=1").ExecuteAffrows();
g.sqlite.Insert(new Song123(1)).ExecuteAffrows();
g.sqlite.Insert(new Author123(11, 1)).ExecuteAffrows();
var song = g.sqlite.Select <Song123>()
.From <Author123>((a, b) => a.InnerJoin(a1 => a1.Id == b.SongId))
.First((a, b) => a); // throw error
Console.WriteLine(song == null);
g.sqlite.Select <Edi>().ToList();
var itemId2 = 2;
var itemId = 1;
var edi = g.sqlite.Select <Edi>()
.Where(a => a.Id == itemId2 && g.sqlite.Select <EdiItem>().Where(b => b.Id == itemId).Any())
.First(a => a); //#231
var lksdjkg1 = g.sqlite.Select <Edi>()
.AsQueryable().Where(a => a.Id > 0).Where(a => a.Id == 1).ToList();
var lksdjkg11 = g.sqlite.Select <Edi>()
.AsQueryable().Where(a => a.Id > 0).Where(a => a.Id == 1).Any();
var lksdjkg2 = g.sqlite.Select <Edi>()
.AsQueryable().Where(a => a.Id > 0).First();
var lksdjkg3 = g.sqlite.Select <Edi>()
.AsQueryable().Where(a => a.Id > 0).FirstOrDefault();
var sql222efe = g.sqlite.Select <Edi, EdiItem>()
.InnerJoin((a, b) => b.Id == g.sqlite.Select <EdiItem>().As("c").Where(c => c.EdiId == a.Id).OrderBy(c => c.Id).ToOne(c => c.Id))
.ToSql((a, b) => new
{
Id = a.Id,
EdiId = b.Id
});
var subSyetemId = "xxx";
var list = g.sqlite.Select <Menu, SubSystem>()
.LeftJoin((a, b) => a.SubNameID == b.Id)
.WhereIf(!string.IsNullOrEmpty(subSyetemId), (a, s) => a.SubNameID == subSyetemId)
.ToList((a, s) => new Menu
{
ID = a.ID,
SystemName = s.Name,
SubNameID = s.Id,
CreateTime = a.CreateTime,
Description = a.Description,
EnName = a.EnName,
Name = a.Name,
OperationIds = a.OperationIds,
Parent = a.Parent,
ParentID = a.ParentID,
Url = a.Url,
UserID = a.UserID
});
var context = new TestDbContext(g.sqlite);
var sql = context.Songs
.Where(a =>
context.Authors
//.Select //加上这句就不报错，不加上报 variable 'a' of type 'Song' referenced from scope '', but it is not defined
.Where(b => b.SongId == a.Id)
.Any())
.ToSql(a => a.Name);
sql = context.Songs
.Where(a =>
context.Authors
.Select //加上这句就不报错，不加上报 variable 'a' of type 'Song' referenced from scope '', but it is not defined
.Where(b => b.SongId == a.Id)
.Any())
.ToSql(a => a.Name);
//using (var conn = new SqlConnection("Data Source=.;Integrated Security=True;Initial Catalog=webchat-abc;Pooling=true;Max Pool Size=13"))
//{
// conn.Open();
// conn.Close();
//}
//using (var fsql = new FreeSql.FreeSqlBuilder()
// .UseConnectionString(FreeSql.DataType.SqlServer, "Data Source=.;Integrated Security=True;Initial Catalog=webchat-abc;Pooling=true;Max Pool Size=13")
// .UseAutoSyncStructure(true)
// //.UseGenerateCommandParameterWithLambda(true)
// .UseMonitorCommand(
// cmd => Trace.WriteLine("\r\n线程" + Thread.CurrentThread.ManagedThreadId + ": " + cmd.CommandText) //监听SQL命令对象，在执行前
// //, (cmd, traceLog) => Console.WriteLine(traceLog)
// )
// .UseLazyLoading(true)
// .Build())
//{
// fsql.Select<ut3_t1>().ToList();
//}
//var testByte = new TestByte { pic = File.ReadAllBytes(@"C:\Users\28810\Desktop\71500003-0ad69400-289e-11ea-85cb-36a54f52ebc0.png") };
//var sql = g.sqlserver.Insert(testByte).NoneParameter().ToSql();
//g.sqlserver.Insert(testByte).NoneParameter().ExecuteAffrows();
//var getTestByte = g.sqlserver.Select<TestByte>(testByte).First();
//File.WriteAllBytes(@"C:\Users\28810\Desktop\71500003-0ad69400-289e-11ea-85cb-36a54f52ebc0_write.png", getTestByte.pic);
var ib = new IdleBus <IFreeSql>(TimeSpan.FromMinutes(10));
ib.Notice += (_, e2) => Trace.WriteLine($"[{DateTime.Now.ToString("HH:mm:ss")}] 线程{Thread.CurrentThread.ManagedThreadId}：{e2.Log}");
ib.Register("db1", () => new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.MySql, "Data Source=127.0.0.1;Port=3306;User ID=root;Password=root;Initial Catalog=cccddd;Charset=utf8;SslMode=none;Max pool size=3")
.UseAutoSyncStructure(true)
.UseGenerateCommandParameterWithLambda(true)
.UseMonitorCommand(cmd => Trace.WriteLine("\r\n线程" + Thread.CurrentThread.ManagedThreadId + ": " + cmd.CommandText))
.UseLazyLoading(true)
.Build());
ib.Register("db2", () => new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.Oracle, "user id=user1;password=123456;data source=//127.0.0.1:1521/XE;Pooling=true;Max Pool Size=3")
.UseAutoSyncStructure(true)
.UseGenerateCommandParameterWithLambda(true)
.UseLazyLoading(true)
.UseNameConvert(FreeSql.Internal.NameConvertType.ToUpper)
.UseMonitorCommand(cmd => Trace.WriteLine("\r\n线程" + Thread.CurrentThread.ManagedThreadId + ": " + cmd.CommandText))
.Build());
ib.Register("db3", () => new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.Sqlite, @"Data Source=|DataDirectory|\document.db;Attachs=xxxtb.db;Pooling=true;Max Pool Size=3")
.UseAutoSyncStructure(true)
.UseGenerateCommandParameterWithLambda(true)
.UseLazyLoading(true)
.UseMonitorCommand(cmd => Trace.WriteLine("\r\n线程" + Thread.CurrentThread.ManagedThreadId + ": " + cmd.CommandText))
.Build());
//...注入很多个
var fsql = ib.Get("db1"); //使用的时候用 Get 方法，不要存其引用关系
var sqlparamId = 100;
fsql.Select <ut3_t1>().Limit(10).Where(a => a.id == sqlparamId).ToList();
fsql = ib.Get("db2");
fsql.Select <ut3_t1>().Limit(10).Where(a => a.id == sqlparamId).ToList();
fsql = ib.Get("db3");
fsql.Select <ut3_t1>().Limit(10).Where(a => a.id == sqlparamId).ToList();
fsql = g.sqlserver;
fsql.Insert <OrderMain>(new OrderMain {
OrderNo = "1001", OrderTime = new DateTime(2019, 12, 01)
}).ExecuteAffrows();
fsql.Insert <OrderDetail>(new OrderDetail {
OrderNo = "1001", ItemNo = "I001", Qty = 1
}).ExecuteAffrows();
fsql.Insert <OrderDetail>(new OrderDetail {
OrderNo = "1001", ItemNo = "I002", Qty = 1
}).ExecuteAffrows();
fsql.Insert <OrderDetail>(new OrderDetail {
OrderNo = "1001", ItemNo = "I003", Qty = 1
}).ExecuteAffrows();
fsql.Insert <OrderMain>(new OrderMain {
OrderNo = "1002", OrderTime = new DateTime(2019, 12, 02)
}).ExecuteAffrows();
fsql.Insert <OrderDetail>(new OrderDetail {
OrderNo = "1002", ItemNo = "I011", Qty = 1
}).ExecuteAffrows();
fsql.Insert <OrderDetail>(new OrderDetail {
OrderNo = "1002", ItemNo = "I012", Qty = 1
}).ExecuteAffrows();
fsql.Insert <OrderDetail>(new OrderDetail {
OrderNo = "1002", ItemNo = "I013", Qty = 1
}).ExecuteAffrows();
fsql.Ado.Query <object>("select * from orderdetail left join ordermain on orderdetail.orderno=ordermain.orderno where ordermain.orderno='1001'");
g.oracle.Delete <SendInfo>().Where("1=1").ExecuteAffrows();
g.oracle.Insert(new[]
{
new SendInfo {
Code = "001", Binary = Encoding.UTF8.GetBytes("我是中国人")
},
new SendInfo {
Code = "002", Binary = Encoding.UTF8.GetBytes("我是地球人")
},
new SendInfo {
Code = "003", Binary = Encoding.UTF8.GetBytes("我是.net")
},
new SendInfo {
Code = "004", Binary = Encoding.UTF8.GetBytes("我是freesql")
},
new SendInfo {
Code = "005", Binary = Encoding.UTF8.GetBytes("我是freesql233")
},
})
.NoneParameter()
.BatchOptions(3, 200)
.BatchProgress(a => Trace.WriteLine($"{a.Current}/{a.Total}"))
.ExecuteAffrows();
var slslsl = g.oracle.Select <SendInfo>().ToList();
var slsls1Ids = slslsl.Select(a => a.ID).ToArray();
var slslss2 = g.oracle.Select <SendInfo>().Where(a => slsls1Ids.Contains(a.ID)).ToList();
var mt_codeId = Guid.Parse("2f48c5ca-7257-43c8-9ee2-0e16fa990253");
Assert.Equal(1, g.oracle.Insert(new SendInfo {
ID = mt_codeId, Code = "mt_code", Binary = Encoding.UTF8.GetBytes("我是mt_code")
})
.ExecuteAffrows());
var mt_code = g.oracle.Select <SendInfo>().Where(a => a.ID == mt_codeId).First();
Assert.NotNull(mt_code);
Assert.Equal(mt_codeId, mt_code.ID);
Assert.Equal("mt_code", mt_code.Code);
mt_code = g.oracle.Select <SendInfo>().Where(a => a.ID == Guid.Parse("2f48c5ca725743c89ee20e16fa990253".ToUpper())).First();
Assert.NotNull(mt_code);
Assert.Equal(mt_codeId, mt_code.ID);
Assert.Equal("mt_code", mt_code.Code);
mt_codeId = Guid.Parse("2f48c5ca-7257-43c8-9ee2-0e16fa990251");
Assert.Equal(1, g.oracle.Insert(new SendInfo {
ID = mt_codeId, Code = "mt_code2", Binary = Encoding.UTF8.GetBytes("我是mt_code2")
})
.NoneParameter()
.ExecuteAffrows());
mt_code = g.oracle.Select <SendInfo>().Where(a => a.ID == mt_codeId).First();
Assert.NotNull(mt_code);
Assert.Equal(mt_codeId, mt_code.ID);
Assert.Equal("mt_code2", mt_code.Code);
mt_code = g.oracle.Select <SendInfo>().Where(a => a.ID == Guid.Parse("2f48c5ca725743c89ee20e16fa990251".ToUpper())).First();
Assert.NotNull(mt_code);
Assert.Equal(mt_codeId, mt_code.ID);
Assert.Equal("mt_code2", mt_code.Code);
var id = g.oracle.Insert(new TestORC12()).ExecuteIdentity();
}
public void TwoTablePartitionBy()
{
var fsql = g.sqlserver;
fsql.Delete <TwoTablePartitionBy_User>().Where("1=1").ExecuteAffrows();
fsql.Delete <TwoTablePartitionBy_UserExt>().Where("1=1").ExecuteAffrows();
fsql.Insert(new[] {
new TwoTablePartitionBy_User {
Id = 1, Nickname = "name01"
},
new TwoTablePartitionBy_User {
Id = 2, Nickname = "name01"
},
new TwoTablePartitionBy_User {
Id = 3, Nickname = "name01"
},
new TwoTablePartitionBy_User {
Id = 4, Nickname = "name02"
},
new TwoTablePartitionBy_User {
Id = 5, Nickname = "name03"
},
new TwoTablePartitionBy_User {
Id = 6, Nickname = "name03"
},
}).ExecuteAffrows();
fsql.Insert(new[] {
new TwoTablePartitionBy_UserExt {
UserId = 1, Remark = "remark01"
},
new TwoTablePartitionBy_UserExt {
UserId = 2, Remark = "remark02"
},
new TwoTablePartitionBy_UserExt {
UserId = 3, Remark = "remark03"
},
new TwoTablePartitionBy_UserExt {
UserId = 4, Remark = "remark04"
},
new TwoTablePartitionBy_UserExt {
UserId = 5, Remark = "remark05"
},
new TwoTablePartitionBy_UserExt {
UserId = 6, Remark = "remark06"
},
}).ExecuteAffrows();
var sql01 = fsql.Select <TwoTablePartitionBy_User, TwoTablePartitionBy_UserExt>()
.InnerJoin((a, b) => a.Id == b.UserId)
.WithTempQuery((a, b) => new
{
user = a,
userext = b,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.ToSql();
var assertSql01 = @"SELECT *
FROM (
SELECT a.[Id], a.[Nickname], b.[UserId], b.[Remark], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
FROM [TwoTablePartitionBy_User] a
INNER JOIN [TwoTablePartitionBy_UserExt] b ON a.[Id] = b.[UserId] ) a
WHERE (a.[rownum] = 1)";
Assert.Equal(assertSql01, sql01);
var sel01 = fsql.Select <TwoTablePartitionBy_User, TwoTablePartitionBy_UserExt>()
.InnerJoin((a, b) => a.Id == b.UserId)
.WithTempQuery((a, b) => new
{
user = a,
userext = b,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1);
Assert.Equal(assertSql01, sel01.ToSql());
var list01 = sel01.ToList();
Assert.Equal(3, list01.Count);
Assert.Equal(list01[0].rownum, 1);
Assert.Equal(list01[0].user.Id, 1);
Assert.Equal(list01[0].user.Nickname, "name01");
Assert.Equal(list01[0].userext.Remark, "remark01");
Assert.Equal(list01[1].rownum, 1);
Assert.Equal(list01[1].user.Id, 4);
Assert.Equal(list01[1].user.Nickname, "name02");
Assert.Equal(list01[1].userext.Remark, "remark04");
Assert.Equal(list01[2].rownum, 1);
Assert.Equal(list01[2].user.Id, 5);
Assert.Equal(list01[2].user.Nickname, "name03");
Assert.Equal(list01[2].userext.Remark, "remark05");
var sql02 = fsql.Select <TwoTablePartitionBy_User, TwoTablePartitionBy_UserExt>()
.InnerJoin((a, b) => a.Id == b.UserId)
.WithTempQuery((a, b) => new
{
user = a,
userext = b,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.ToSql(a => a.user);
var assertSql02 = @"SELECT a.[Id] as1, a.[Nickname] as2
FROM (
SELECT a.[Id], a.[Nickname], b.[UserId], b.[Remark], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
FROM [TwoTablePartitionBy_User] a
INNER JOIN [TwoTablePartitionBy_UserExt] b ON a.[Id] = b.[UserId] ) a
WHERE (a.[rownum] = 1)";
Assert.Equal(assertSql02, sql02);
var sel02 = fsql.Select <TwoTablePartitionBy_User, TwoTablePartitionBy_UserExt>()
.InnerJoin((a, b) => a.Id == b.UserId)
.WithTempQuery((a, b) => new
{
user = a,
userext = b,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1);
Assert.Equal(assertSql02, sel02.ToSql(a => a.user));
var list02 = sel02.ToList(a => a.user);
Assert.Equal(3, list02.Count);
Assert.Equal(list02[0].Id, 1);
Assert.Equal(list02[0].Nickname, "name01");
Assert.Equal(list02[1].Id, 4);
Assert.Equal(list02[1].Nickname, "name02");
Assert.Equal(list02[2].Id, 5);
Assert.Equal(list02[2].Nickname, "name03");
var sql022 = fsql.Select <TwoTablePartitionBy_User, TwoTablePartitionBy_UserExt>()
.InnerJoin((a, b) => a.Id == b.UserId)
.WithTempQuery((a, b) => new
{
user = a,
userext = b,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.ToSql(a => a.userext);
var assertSql022 = @"SELECT a.[UserId] as1, a.[Remark] as2
FROM (
SELECT a.[Id], a.[Nickname], b.[UserId], b.[Remark], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
FROM [TwoTablePartitionBy_User] a
INNER JOIN [TwoTablePartitionBy_UserExt] b ON a.[Id] = b.[UserId] ) a
WHERE (a.[rownum] = 1)";
Assert.Equal(assertSql022, sql022);
var sel022 = fsql.Select <TwoTablePartitionBy_User, TwoTablePartitionBy_UserExt>()
.InnerJoin((a, b) => a.Id == b.UserId)
.WithTempQuery((a, b) => new
{
user = a,
userext = b,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1);
Assert.Equal(assertSql022, sel022.ToSql(a => a.userext));
var list022 = sel022.ToList(a => a.userext);
Assert.Equal(3, list022.Count);
Assert.Equal(list022[0].UserId, 1);
Assert.Equal(list022[0].Remark, "remark01");
Assert.Equal(list022[1].UserId, 4);
Assert.Equal(list022[1].Remark, "remark04");
Assert.Equal(list022[2].UserId, 5);
Assert.Equal(list022[2].Remark, "remark05");
var sql03 = fsql.Select <TwoTablePartitionBy_User, TwoTablePartitionBy_UserExt>()
.InnerJoin((a, b) => a.Id == b.UserId)
.WithTempQuery((a, b) => new
{
user = a,
userext = b,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.ToSql(a => new
{
a.user.Id,
a.rownum
});
var assertSql03 = @"SELECT a.[Id] as1, a.[rownum] as2
FROM (
SELECT a.[Id], a.[Nickname], b.[UserId], b.[Remark], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
FROM [TwoTablePartitionBy_User] a
INNER JOIN [TwoTablePartitionBy_UserExt] b ON a.[Id] = b.[UserId] ) a
WHERE (a.[rownum] = 1)";
Assert.Equal(assertSql03, sql03);
var sel03 = fsql.Select <TwoTablePartitionBy_User, TwoTablePartitionBy_UserExt>()
.InnerJoin((a, b) => a.Id == b.UserId)
.WithTempQuery((a, b) => new
{
user = a,
userext = b,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1);
Assert.Equal(assertSql03, sel03.ToSql(a => new
{
a.user.Id,
a.rownum
}));
var list03 = sel03.ToList(a => new
{
a.user.Id,
a.rownum
});
Assert.Equal(3, list03.Count);
Assert.Equal(list03[0].rownum, 1);
Assert.Equal(list03[0].Id, 1);
Assert.Equal(list03[1].rownum, 1);
Assert.Equal(list03[1].Id, 4);
Assert.Equal(list03[2].rownum, 1);
Assert.Equal(list03[2].Id, 5);
var sql04 = fsql.Select <TwoTablePartitionBy_User, TwoTablePartitionBy_UserExt>()
.InnerJoin((a, b) => a.Id == b.UserId)
.WithTempQuery((a, b) => new
{
a.Id,
a.Nickname,
b.Remark,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.ToSql(a => new TwoTablePartitionBy_UserDto());
var assertSql04 = @"SELECT a.[Id] as1, a.[rownum] as2, a.[Remark] as3
FROM (
SELECT a.[Id], a.[Nickname], b.[Remark], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
FROM [TwoTablePartitionBy_User] a
INNER JOIN [TwoTablePartitionBy_UserExt] b ON a.[Id] = b.[UserId] ) a
WHERE (a.[rownum] = 1)";
Assert.Equal(assertSql04, sql04);
var sel04 = fsql.Select <TwoTablePartitionBy_User, TwoTablePartitionBy_UserExt>()
.InnerJoin((a, b) => a.Id == b.UserId)
.WithTempQuery((a, b) => new
{
a.Id,
a.Nickname,
b.Remark,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1);
Assert.Equal(assertSql04, sel04.ToSql(a => new TwoTablePartitionBy_UserDto()));
var list04 = sel04.ToList <TwoTablePartitionBy_UserDto>();
Assert.Equal(3, list04.Count);
Assert.Equal(list04[0].rownum, 1);
Assert.Equal(list04[0].Id, 1);
Assert.Equal(list04[0].remark, "remark01");
Assert.Equal(list04[1].rownum, 1);
Assert.Equal(list04[1].Id, 4);
Assert.Equal(list04[1].remark, "remark04");
Assert.Equal(list04[2].rownum, 1);
Assert.Equal(list04[2].Id, 5);
Assert.Equal(list04[2].remark, "remark05");
var sql05 = fsql.Select <TwoTablePartitionBy_User>()
.WithTempQuery(a => new
{
user = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.WithTempQuery(a => a.user)
.Where(a => a.Nickname == "name03")
.ToSql(a => new TwoTablePartitionBy_UserDto());
var assertSql05 = @"SELECT a.[Id] as1
FROM (
SELECT a.[Id], a.[Nickname]
FROM (
SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
FROM [TwoTablePartitionBy_User] a ) a
WHERE (a.[rownum] = 1) ) a
WHERE (a.[Nickname] = N'name03')";
Assert.Equal(sql05, assertSql05);
var list05 = fsql.Select <TwoTablePartitionBy_User>()
.WithTempQuery(a => new
{
user = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.WithTempQuery(a => a.user)
.Where(a => a.Nickname == "name03")
.ToList <TwoTablePartitionBy_UserDto>();
Assert.Equal(list05.Count, 1);
Assert.Equal(5, list05[0].Id);
Assert.Equal(0, list05[0].rownum);
Assert.Null(list05[0].remark);
var sql06 = fsql.Select <TwoTablePartitionBy_User>()
.WithTempQuery(a => new
{
user = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.WithTempQuery(a => a.user)
.From <TwoTablePartitionBy_UserExt>()
.InnerJoin((a, b) => a.Id == b.UserId)
.Where((a, b) => a.Nickname == "name03" || a.Nickname == "name02")
.ToSql((a, b) => new TwoTablePartitionBy_UserDto());
var assertSql06 = @"SELECT a.[Id] as1, b.[Remark] as2
FROM (
SELECT a.[Id], a.[Nickname]
FROM (
SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
FROM [TwoTablePartitionBy_User] a ) a
WHERE (a.[rownum] = 1) ) a
INNER JOIN [TwoTablePartitionBy_UserExt] b ON a.[Id] = b.[UserId]
WHERE ((a.[Nickname] = N'name03' OR a.[Nickname] = N'name02'))";
Assert.Equal(sql06, assertSql06);
var list06 = fsql.Select <TwoTablePartitionBy_User>()
.WithTempQuery(a => new
{
user = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.WithTempQuery(a => a.user)
.From <TwoTablePartitionBy_UserExt>()
.InnerJoin((a, b) => a.Id == b.UserId)
.Where((a, b) => a.Nickname == "name03" || a.Nickname == "name02")
.ToList <TwoTablePartitionBy_UserDto>();
Assert.Equal(list06.Count, 2);
Assert.Equal(list06[0].rownum, 0);
Assert.Equal(list06[0].Id, 4);
Assert.Equal(list06[0].remark, "remark04");
Assert.Equal(list06[1].rownum, 0);
Assert.Equal(list06[1].Id, 5);
Assert.Equal(list06[1].remark, "remark05");
var sql061 = fsql.Select <TwoTablePartitionBy_User>()
.WithTempQuery(a => new
{
user = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.WithTempQuery(a => a.user)
.From <TwoTablePartitionBy_UserExt>()
.AsTable((type, old) => type == typeof(TwoTablePartitionBy_UserExt) ? old.Replace("TwoTablePartitionBy_", "") : old)
.InnerJoin((a, b) => a.Id == b.UserId)
.Where((a, b) => a.Nickname == "name03" || a.Nickname == "name02")
.ToSql((a, b) => new TwoTablePartitionBy_UserDto());
var assertSql061 = @"SELECT a.[Id] as1, b.[Remark] as2
FROM (
SELECT a.[Id], a.[Nickname]
FROM (
SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
FROM [TwoTablePartitionBy_User] a ) a
WHERE (a.[rownum] = 1) ) a
INNER JOIN [UserExt] b ON a.[Id] = b.[UserId]
WHERE ((a.[Nickname] = N'name03' OR a.[Nickname] = N'name02'))";
Assert.Equal(sql061, assertSql061);
var sql07 = fsql.Select <TwoTablePartitionBy_User>()
.WithTempQuery(a => new
{
user = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.WithTempQuery(a => a.user)
.FromQuery(fsql.Select <TwoTablePartitionBy_UserExt>())
.InnerJoin((a, b) => a.Id == b.UserId)
.Where((a, b) => a.Nickname == "name03" || a.Nickname == "name02")
.ToSql((a, b) => new TwoTablePartitionBy_UserDto());
var assertSql07 = @"SELECT a.[Id] as1, b.[Remark] as2
FROM (
SELECT a.[Id], a.[Nickname]
FROM (
SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
FROM [TwoTablePartitionBy_User] a ) a
WHERE (a.[rownum] = 1) ) a
INNER JOIN (
SELECT a.[UserId], a.[Remark]
FROM [TwoTablePartitionBy_UserExt] a) b ON a.[Id] = b.[UserId]
WHERE ((a.[Nickname] = N'name03' OR a.[Nickname] = N'name02'))";
Assert.Equal(sql07, assertSql07);
var list07 = fsql.Select <TwoTablePartitionBy_User>()
.WithTempQuery(a => new
{
user = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.WithTempQuery(a => a.user)
.FromQuery(fsql.Select <TwoTablePartitionBy_UserExt>())
.InnerJoin((a, b) => a.Id == b.UserId)
.Where((a, b) => a.Nickname == "name03" || a.Nickname == "name02")
.ToList <TwoTablePartitionBy_UserDto>();
Assert.Equal(list07.Count, 2);
Assert.Equal(list07[0].rownum, 0);
Assert.Equal(list07[0].Id, 4);
Assert.Equal(list07[0].remark, "remark04");
Assert.Equal(list07[1].rownum, 0);
Assert.Equal(list07[1].Id, 5);
Assert.Equal(list07[1].remark, "remark05");
var sql08 = fsql.Select <TwoTablePartitionBy_User>()
.WithTempQuery(a => new
{
user = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.FromQuery(fsql.Select <TwoTablePartitionBy_UserExt>().Where(b => b.UserId > 0))
.InnerJoin((a, b) => a.user.Id == b.UserId)
.Where((a, b) => a.user.Nickname == "name03" || a.user.Nickname == "name02")
.ToSql((a, b) => new TwoTablePartitionBy_UserDto());
var assertSql08 = @"SELECT a.[rownum] as1, b.[Remark] as2
FROM (
SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
FROM [TwoTablePartitionBy_User] a ) a
INNER JOIN (
SELECT a.[UserId], a.[Remark]
FROM [TwoTablePartitionBy_UserExt] a
WHERE (a.[UserId] > 0)) b ON a.[Id] = b.[UserId]
WHERE (a.[rownum] = 1) AND ((a.[Nickname] = N'name03' OR a.[Nickname] = N'name02'))";
Assert.Equal(sql08, assertSql08);
var list08 = fsql.Select <TwoTablePartitionBy_User>()
.WithTempQuery(a => new
{
user = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.FromQuery(fsql.Select <TwoTablePartitionBy_UserExt>().Where(b => b.UserId > 0))
.InnerJoin((a, b) => a.user.Id == b.UserId)
.Where((a, b) => a.user.Nickname == "name03" || a.user.Nickname == "name02")
.ToList <TwoTablePartitionBy_UserDto>();
Assert.Equal(list08.Count, 2);
Assert.Equal(list08[0].rownum, 1);
Assert.Equal(list08[0].Id, 0);
Assert.Equal(list08[0].remark, "remark04");
Assert.Equal(list08[1].rownum, 1);
Assert.Equal(list08[1].Id, 0);
Assert.Equal(list08[1].remark, "remark05");
var sql09 = fsql.Select <TwoTablePartitionBy_User>()
.WithTempQuery(a => new
{
user = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.FromQuery(fsql.Select <TwoTablePartitionBy_UserExt>().Where(b => b.UserId > 0).WithTempQuery(b => new { b.UserId, b.Remark }))
.InnerJoin((a, b) => a.user.Id == b.UserId)
.Where((a, b) => a.user.Nickname == "name03" || a.user.Nickname == "name02")
.ToSql((a, b) => new TwoTablePartitionBy_UserDto());
var assertSql09 = @"SELECT a.[rownum] as1, b.[Remark] as2
FROM (
SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
FROM [TwoTablePartitionBy_User] a ) a
INNER JOIN (
SELECT a.[UserId], a.[Remark]
FROM [TwoTablePartitionBy_UserExt] a
WHERE (a.[UserId] > 0) ) b ON a.[Id] = b.[UserId]
WHERE (a.[rownum] = 1) AND ((a.[Nickname] = N'name03' OR a.[Nickname] = N'name02'))";
Assert.Equal(sql09, assertSql09);
var list09 = fsql.Select <TwoTablePartitionBy_User>()
.WithTempQuery(a => new
{
user = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.FromQuery(fsql.Select <TwoTablePartitionBy_UserExt>().Where(b => b.UserId > 0).WithTempQuery(b => new { b.UserId, b.Remark }))
.InnerJoin((a, b) => a.user.Id == b.UserId)
.Where((a, b) => a.user.Nickname == "name03" || a.user.Nickname == "name02")
.ToList <TwoTablePartitionBy_UserDto>();
Assert.Equal(list09.Count, 2);
Assert.Equal(list09[0].rownum, 1);
Assert.Equal(list09[0].Id, 0);
Assert.Equal(list09[0].remark, "remark04");
Assert.Equal(list09[1].rownum, 1);
Assert.Equal(list09[1].Id, 0);
Assert.Equal(list09[1].remark, "remark05");
var sql091 = fsql.Select <TwoTablePartitionBy_User>()
.WithTempQuery(a => new
{
user = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.FromQuery(fsql.Select <TwoTablePartitionBy_UserExt>().Where(b => b.UserId > 0).GroupBy(b => new { b.UserId, b.Remark }).WithTempQuery(b => b.Key))
.InnerJoin((a, b) => a.user.Id == b.UserId)
.Where((a, b) => a.user.Nickname == "name03" || a.user.Nickname == "name02")
.ToSql((a, b) => new TwoTablePartitionBy_UserDto());
var assertSql091 = @"SELECT a.[rownum] as1, b.[Remark] as2
FROM (
SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
FROM [TwoTablePartitionBy_User] a ) a
INNER JOIN (
SELECT a.[UserId], a.[Remark]
FROM [TwoTablePartitionBy_UserExt] a
WHERE (a.[UserId] > 0)
GROUP BY a.[UserId], a.[Remark] ) b ON a.[Id] = b.[UserId]
WHERE (a.[rownum] = 1) AND ((a.[Nickname] = N'name03' OR a.[Nickname] = N'name02'))";
Assert.Equal(sql091, assertSql091);
var list091 = fsql.Select <TwoTablePartitionBy_User>()
.WithTempQuery(a => new
{
user = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.FromQuery(fsql.Select <TwoTablePartitionBy_UserExt>().Where(b => b.UserId > 0).GroupBy(b => new { b.UserId, b.Remark }).WithTempQuery(b => b.Key))
.InnerJoin((a, b) => a.user.Id == b.UserId)
.Where((a, b) => a.user.Nickname == "name03" || a.user.Nickname == "name02")
.ToList <TwoTablePartitionBy_UserDto>();
Assert.Equal(list091.Count, 2);
Assert.Equal(list091[0].rownum, 1);
Assert.Equal(list091[0].Id, 0);
Assert.Equal(list091[0].remark, "remark04");
Assert.Equal(list091[1].rownum, 1);
Assert.Equal(list091[1].Id, 0);
Assert.Equal(list091[1].remark, "remark05");
var sql10 = fsql.Select <TwoTablePartitionBy_User>()
.WithTempQuery(a => new
{
user = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.FromQuery(fsql.Select <TwoTablePartitionBy_UserExt>().Where(b => b.UserId > 0).GroupBy(b => new { b.UserId, b.Remark }).WithTempQuery(b => new { b.Key, rownum = b.Sum(b.Value.UserId) }))
.InnerJoin((a, b) => a.user.Id == b.Key.UserId)
.Where((a, b) => a.user.Nickname == "name03" || a.user.Nickname == "name02")
.ToSql((a, b) => new TwoTablePartitionBy_UserDto());
var assertSql10 = @"SELECT a.[rownum] as1
FROM (
SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
FROM [TwoTablePartitionBy_User] a ) a
INNER JOIN (
SELECT a.[UserId], a.[Remark], sum(a.[UserId]) [rownum]
FROM [TwoTablePartitionBy_UserExt] a
WHERE (a.[UserId] > 0)
GROUP BY a.[UserId], a.[Remark] ) b ON a.[Id] = b.[UserId]
WHERE (a.[rownum] = 1) AND ((a.[Nickname] = N'name03' OR a.[Nickname] = N'name02'))";
Assert.Equal(sql10, assertSql10);
var list10 = fsql.Select <TwoTablePartitionBy_User>()
.WithTempQuery(a => new
{
user = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.FromQuery(fsql.Select <TwoTablePartitionBy_UserExt>().Where(b => b.UserId > 0).GroupBy(b => new { b.UserId, b.Remark }).WithTempQuery(b => new { b.Key, rownum = b.Sum(b.Value.UserId) }))
.InnerJoin((a, b) => a.user.Id == b.Key.UserId)
.Where((a, b) => a.user.Nickname == "name03" || a.user.Nickname == "name02")
.ToList <TwoTablePartitionBy_UserDto>();
Assert.Equal(list10.Count, 2);
Assert.Equal(list10[0].rownum, 1);
Assert.Equal(list10[0].Id, 0);
Assert.Null(list10[0].remark);
Assert.Equal(list10[1].rownum, 1);
Assert.Equal(list10[1].Id, 0);
Assert.Null(list10[1].remark);
var sql11 = fsql.Select <TwoTablePartitionBy_User>()
.WithTempQuery(a => new
{
user = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.FromQuery(fsql.Select <TwoTablePartitionBy_UserExt>().Where(b => b.UserId > 0).GroupBy(b => b.UserId).WithTempQuery(b => new { uid = b.Key, rownum = b.Sum(b.Value.UserId) }))
.InnerJoin((a, b) => a.user.Id == b.uid)
.Where((a, b) => a.user.Nickname == "name03" || a.user.Nickname == "name02")
.ToSql((a, b) => new TwoTablePartitionBy_UserDto());
var assertSql11 = @"SELECT a.[rownum] as1
FROM (
SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
FROM [TwoTablePartitionBy_User] a ) a
INNER JOIN (
SELECT a.[UserId] [uid], sum(a.[UserId]) [rownum]
FROM [TwoTablePartitionBy_UserExt] a
WHERE (a.[UserId] > 0)
GROUP BY a.[UserId] ) b ON a.[Id] = b.[uid]
WHERE (a.[rownum] = 1) AND ((a.[Nickname] = N'name03' OR a.[Nickname] = N'name02'))";
Assert.Equal(sql11, assertSql11);
var list11 = fsql.Select <TwoTablePartitionBy_User>()
.WithTempQuery(a => new
{
user = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.FromQuery(fsql.Select <TwoTablePartitionBy_UserExt>().Where(b => b.UserId > 0).GroupBy(b => b.UserId).WithTempQuery(b => new { uid = b.Key, rownum = b.Sum(b.Value.UserId) }))
.InnerJoin((a, b) => a.user.Id == b.uid)
.Where((a, b) => a.user.Nickname == "name03" || a.user.Nickname == "name02")
.ToList <TwoTablePartitionBy_UserDto>();
Assert.Equal(list11.Count, 2);
Assert.Equal(list11[0].rownum, 1);
Assert.Equal(list11[0].Id, 0);
Assert.Null(list11[0].remark);
Assert.Equal(list11[1].rownum, 1);
Assert.Equal(list11[1].Id, 0);
Assert.Null(list11[1].remark);
var sql12 = fsql.Select <TwoTablePartitionBy_User>()
.FromQuery(fsql.Select <TwoTablePartitionBy_UserExt>()
.Where(b => b.UserId > 0))
.LeftJoin((a, b) => a.Id == b.UserId)
.Where((a, b) => a.Id > 0 && b.UserId > 0)
.GroupBy((a, b) => new { a.Nickname })
.ToSql(g => new
{
g.Key,
sum1 = g.Sum(g.Value.Item1.Id),
sum2 = g.Sum(g.Value.Item2.UserId),
});
var assertSql12 = @"SELECT a.[Nickname], sum(a.[Id]) as1, sum(b.[UserId]) as2
FROM [TwoTablePartitionBy_User] a
LEFT JOIN (
SELECT a.[UserId], a.[Remark]
FROM [TwoTablePartitionBy_UserExt] a
WHERE (a.[UserId] > 0)) b ON a.[Id] = b.[UserId]
WHERE (a.[Id] > 0 AND b.[UserId] > 0)
GROUP BY a.[Nickname]";
Assert.Equal(sql12, assertSql12);
var list12 = fsql.Select <TwoTablePartitionBy_User>()
.FromQuery(fsql.Select <TwoTablePartitionBy_UserExt>()
.Where(b => b.UserId > 0))
.LeftJoin((a, b) => a.Id == b.UserId)
.Where((a, b) => a.Id > 0 && b.UserId > 0)
.GroupBy((a, b) => new { a.Nickname })
.ToList(g => new
{
g.Key,
sum1 = g.Sum(g.Value.Item1.Id),
sum2 = g.Sum(g.Value.Item2.UserId),
});
Assert.Equal(list12.Count, 3);
Assert.Equal("name01", list12[0].Key.Nickname);
Assert.Equal(6, list12[0].sum1);
Assert.Equal(6, list12[0].sum2);
Assert.Equal("name02", list12[1].Key.Nickname);
Assert.Equal(4, list12[1].sum1);
Assert.Equal(4, list12[1].sum2);
Assert.Equal("name03", list12[2].Key.Nickname);
Assert.Equal(11, list12[2].sum1);
Assert.Equal(11, list12[2].sum2);
var sql13 = fsql.Select <TwoTablePartitionBy_User>().AsTable((_, old) => old.Replace("TwoTablePartitionBy_", ""))
.FromQuery(fsql.Select <TwoTablePartitionBy_UserExt>().AsTable((_, old) => old.Replace("TwoTablePartitionBy_", ""))
.Where(b => b.UserId > 0))
.LeftJoin((a, b) => a.Id == b.UserId)
.Where((a, b) => a.Id > 0 && b.UserId > 0)
.GroupBy((a, b) => new { a.Nickname })
.ToSql(g => new
{
g.Key,
sum1 = g.Sum(g.Value.Item1.Id),
sum2 = g.Sum(g.Value.Item2.UserId),
});
var assertSql13 = @"SELECT a.[Nickname], sum(a.[Id]) as1, sum(b.[UserId]) as2
FROM [User] a
LEFT JOIN (
SELECT a.[UserId], a.[Remark]
FROM [UserExt] a
WHERE (a.[UserId] > 0)) b ON a.[Id] = b.[UserId]
WHERE (a.[Id] > 0 AND b.[UserId] > 0)
GROUP BY a.[Nickname]";
Assert.Equal(sql13, assertSql13);
var sql14 = fsql.Select <TwoTablePartitionBy_User>()
.Where(a => a.Id > 0)
.WithTempQuery(a => new
{
item = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.FromQuery(fsql.Select <TwoTablePartitionBy_UserExt>().Where(b => b.UserId > 0))
.InnerJoin((a, b) => a.item.Id == b.UserId)
.ToSql((a, b) => new
{
user = a.item,
rownum = a.rownum,
userext = b
});
var assertSql14 = @"SELECT a.[Id] as1, a.[Nickname] as2, a.[rownum] as3, b.[UserId] as4, b.[Remark] as5
FROM (
SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
FROM [TwoTablePartitionBy_User] a
WHERE (a.[Id] > 0) ) a
INNER JOIN (
SELECT a.[UserId], a.[Remark]
FROM [TwoTablePartitionBy_UserExt] a
WHERE (a.[UserId] > 0)) b ON a.[Id] = b.[UserId]
WHERE (a.[rownum] = 1)";
Assert.Equal(sql14, assertSql14);
var list14 = fsql.Select <TwoTablePartitionBy_User>()
.Where(a => a.Id > 0)
.WithTempQuery(a => new
{
item = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.FromQuery(fsql.Select <TwoTablePartitionBy_UserExt>().Where(b => b.UserId > 0))
.InnerJoin((a, b) => a.item.Id == b.UserId)
.ToList((a, b) => new
{
user = a.item,
rownum = a.rownum,
userext = b
});
Assert.Equal(list14.Count, 3);
Assert.Equal(list14[0].rownum, 1);
Assert.Equal(list14[0].user.Id, 1);
Assert.Equal(list14[0].user.Nickname, "name01");
Assert.Equal(list14[0].userext.UserId, 1);
Assert.Equal(list14[0].userext.Remark, "remark01");
Assert.Equal(list14[1].rownum, 1);
Assert.Equal(list14[1].user.Id, 4);
Assert.Equal(list14[1].user.Nickname, "name02");
Assert.Equal(list14[1].userext.UserId, 4);
Assert.Equal(list14[1].userext.Remark, "remark04");
Assert.Equal(list14[2].rownum, 1);
Assert.Equal(list14[2].user.Id, 5);
Assert.Equal(list14[2].user.Nickname, "name03");
Assert.Equal(list14[2].userext.UserId, 5);
Assert.Equal(list14[2].userext.Remark, "remark05");
var sql15 = fsql.Select <TwoTablePartitionBy_User>()
.WithTempQuery(a => new
{
user = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.FromQuery(fsql.Select <TwoTablePartitionBy_UserExt>().Where(b => b.UserId > 0)
.GroupBy(b => new { b.UserId, b.Remark })
.WithTempQuery(b => new { b.Key, sum1 = b.Sum(b.Value.UserId) }))
.InnerJoin((a, b) => a.user.Id == b.Key.UserId)
.Where((a, b) => a.user.Nickname == "name02" || a.user.Nickname == "name03")
.ToSql((a, b) => new
{
user = a.user,
rownum = a.rownum,
groupby = b
}, FieldAliasOptions.AsProperty);
var assertSql15 = @"SELECT a.[Id], a.[Nickname], a.[rownum], b.[UserId], b.[Remark], b.[sum1]
FROM (
SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
FROM [TwoTablePartitionBy_User] a ) a
INNER JOIN (
SELECT a.[UserId], a.[Remark], sum(a.[UserId]) [sum1]
FROM [TwoTablePartitionBy_UserExt] a
WHERE (a.[UserId] > 0)
GROUP BY a.[UserId], a.[Remark] ) b ON a.[Id] = b.[UserId]
WHERE (a.[rownum] = 1) AND ((a.[Nickname] = N'name02' OR a.[Nickname] = N'name03'))";
Assert.Equal(sql15, assertSql15);
var list15 = fsql.Select <TwoTablePartitionBy_User>()
.WithTempQuery(a => new
{
user = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.FromQuery(fsql.Select <TwoTablePartitionBy_UserExt>().Where(b => b.UserId > 0)
.GroupBy(b => new { b.UserId, b.Remark })
.WithTempQuery(b => new { b.Key, sum1 = b.Sum(b.Value.UserId) }))
.InnerJoin((a, b) => a.user.Id == b.Key.UserId)
.Where((a, b) => a.user.Nickname == "name02" || a.user.Nickname == "name03")
.ToList((a, b) => new
{
user = a.user,
rownum = a.rownum,
groupby = b
});
Assert.Equal(list15.Count, 2);
Assert.Equal("remark04", list15[0].groupby.Key.Remark);
Assert.Equal(4, list15[0].groupby.Key.UserId);
Assert.Equal(4, list15[0].groupby.sum1);
Assert.Equal(1, list15[0].rownum);
Assert.Equal(4, list15[0].user.Id);
Assert.Equal("name02", list15[0].user.Nickname);
Assert.Equal("remark05", list15[1].groupby.Key.Remark);
Assert.Equal(5, list15[1].groupby.Key.UserId);
Assert.Equal(5, list15[1].groupby.sum1);
Assert.Equal(1, list15[1].rownum);
Assert.Equal(5, list15[1].user.Id);
Assert.Equal("name03", list15[1].user.Nickname);
}
public void SingleTablePartitionBy()
{
var fsql = g.sqlserver;
fsql.Delete <SingleTablePartitionBy_User>().Where("1=1").ExecuteAffrows();
fsql.Insert(new[] {
new SingleTablePartitionBy_User {
Id = 1, Nickname = "name01"
},
new SingleTablePartitionBy_User {
Id = 2, Nickname = "name01"
},
new SingleTablePartitionBy_User {
Id = 3, Nickname = "name01"
},
new SingleTablePartitionBy_User {
Id = 4, Nickname = "name02"
},
new SingleTablePartitionBy_User {
Id = 5, Nickname = "name03"
},
new SingleTablePartitionBy_User {
Id = 6, Nickname = "name03"
},
}).ExecuteAffrows();
var sql01 = fsql.Select <SingleTablePartitionBy_User>()
.WithTempQuery(a => new
{
item = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.ToSql();
var assertSql01 = @"SELECT *
FROM (
SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
FROM [SingleTablePartitionBy_User] a ) a
WHERE (a.[rownum] = 1)";
Assert.Equal(assertSql01, sql01);
var sel01 = fsql.Select <SingleTablePartitionBy_User>()
.WithTempQuery(a => new
{
item = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1);
Assert.Equal(assertSql01, sel01.ToSql());
var list01 = sel01.ToList();
Assert.Equal(3, list01.Count);
Assert.Equal(list01[0].rownum, 1);
Assert.Equal(list01[0].item.Id, 1);
Assert.Equal(list01[0].item.Nickname, "name01");
Assert.Equal(list01[1].rownum, 1);
Assert.Equal(list01[1].item.Id, 4);
Assert.Equal(list01[1].item.Nickname, "name02");
Assert.Equal(list01[2].rownum, 1);
Assert.Equal(list01[2].item.Id, 5);
Assert.Equal(list01[2].item.Nickname, "name03");
var sql02 = fsql.Select <SingleTablePartitionBy_User>()
.WithTempQuery(a => new
{
item = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.ToSql(a => a.item);
var assertSql02 = @"SELECT a.[Id] as1, a.[Nickname] as2
FROM (
SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
FROM [SingleTablePartitionBy_User] a ) a
WHERE (a.[rownum] = 1)";
Assert.Equal(assertSql02, sql02);
var sel02 = fsql.Select <SingleTablePartitionBy_User>()
.WithTempQuery(a => new
{
item = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1);
Assert.Equal(assertSql02, sel02.ToSql(a => a.item));
var list02 = sel02.ToList(a => a.item);
Assert.Equal(3, list02.Count);
Assert.Equal(list02[0].Id, 1);
Assert.Equal(list02[0].Nickname, "name01");
Assert.Equal(list02[1].Id, 4);
Assert.Equal(list02[1].Nickname, "name02");
Assert.Equal(list02[2].Id, 5);
Assert.Equal(list02[2].Nickname, "name03");
var sql03 = fsql.Select <SingleTablePartitionBy_User>()
.WithTempQuery(a => new
{
item = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.ToSql(a => new
{
a.item.Id,
a.rownum
});
var assertSql03 = @"SELECT a.[Id] as1, a.[rownum] as2
FROM (
SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
FROM [SingleTablePartitionBy_User] a ) a
WHERE (a.[rownum] = 1)";
Assert.Equal(assertSql03, sql03);
var sel03 = fsql.Select <SingleTablePartitionBy_User>()
.WithTempQuery(a => new
{
item = a,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1);
Assert.Equal(assertSql03, sel03.ToSql(a => new
{
a.item.Id,
a.rownum
}));
var list03 = sel03.ToList(a => new
{
a.item.Id,
a.rownum
});
Assert.Equal(3, list03.Count);
Assert.Equal(list03[0].rownum, 1);
Assert.Equal(list03[0].Id, 1);
Assert.Equal(list03[1].rownum, 1);
Assert.Equal(list03[1].Id, 4);
Assert.Equal(list03[2].rownum, 1);
Assert.Equal(list03[2].Id, 5);
var sql04 = fsql.Select <SingleTablePartitionBy_User>()
.WithTempQuery(a => new
{
a.Id,
a.Nickname,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1)
.ToSql(a => new SingleTablePartitionBy_UserDto());
var assertSql04 = @"SELECT a.[Id] as1, a.[rownum] as2
FROM (
SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
FROM [SingleTablePartitionBy_User] a ) a
WHERE (a.[rownum] = 1)";
Assert.Equal(assertSql04, sql04);
var sel04 = fsql.Select <SingleTablePartitionBy_User>()
.WithTempQuery(a => new
{
a.Id,
a.Nickname,
rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
})
.Where(a => a.rownum == 1);
Assert.Equal(assertSql04, sel04.ToSql(a => new SingleTablePartitionBy_UserDto()));
var list04 = sel04.ToList <SingleTablePartitionBy_UserDto>();
Assert.Equal(3, list04.Count);
Assert.Equal(list04[0].rownum, 1);
Assert.Equal(list04[0].Id, 1);
Assert.Equal(list04[1].rownum, 1);
Assert.Equal(list04[1].Id, 4);
Assert.Equal(list04[2].rownum, 1);
Assert.Equal(list04[2].Id, 5);
var sql05 = fsql.Select <TwoTablePartitionBy_User>()
.Where(a => a.Id > 0)
.WithTempQuery(a => new
{
a.Id,
a.Nickname
})
.GroupBy(a => new { a.Nickname })
.WithTempQuery(a => new
{
a.Key,
sum1 = a.Sum(a.Value.Id),
cou1 = a.Count()
})
.ToSql();
var assertSql05 = @"SELECT *
FROM (
SELECT a.[Nickname], sum(a.[Id]) [sum1], count(1) [cou1]
FROM (
SELECT a.[Id], a.[Nickname]
FROM [TwoTablePartitionBy_User] a
WHERE (a.[Id] > 0) ) a
GROUP BY a.[Nickname] ) a";
Assert.Equal(assertSql05, sql05);
var list05 = fsql.Select <TwoTablePartitionBy_User>()
.Where(a => a.Id > 0)
.WithTempQuery(a => new
{
a.Id,
a.Nickname
})
.GroupBy(a => new { a.Nickname })
.WithTempQuery(a => new
{
a.Key,
sum1 = a.Sum(a.Value.Id),
cou1 = a.Count()
})
.ToList();
Assert.Equal(3, list05.Count);
Assert.Equal("name01", list05[0].Key.Nickname);
Assert.Equal(6, list05[0].sum1);
Assert.Equal(3, list05[0].cou1);
Assert.Equal("name02", list05[1].Key.Nickname);
Assert.Equal(4, list05[1].sum1);
Assert.Equal(1, list05[1].cou1);
Assert.Equal("name03", list05[2].Key.Nickname);
Assert.Equal(11, list05[2].sum1);
Assert.Equal(2, list05[2].cou1);
}
