public void TestJoin() { var connection = new Npgsql.NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=1024;Database=test;"); using (IDbContext db = new DbProxyContext(connection, DbContextType.Postgresql)) { db.Open(); var list0 = db.From <Student, Stuclass>() .Join((a, b) => a.Id == b.Sid) .OrderBy((a, b) => a.Id) .OrderByDescending((a, b) => b.Id) .Select((a, b) => new { a.Id, a.Name, Class = b.Name }).ToList(); var(list1, total1) = db.From <Student, Stuclass>() .Join((a, b) => a.Id == b.Sid) .Page(1, 2) .SelectMany((a, b) => new { a.Id, a.Name, Class = b.Name }); var(list2, total2) = db.From <Student, Stuclass>() .Join((a, b) => a.Id == b.Sid) .GroupBy((a, b) => b.Name) .Page(1, 2) .SelectMany((a, b) => new { Class = b.Name, Count = SqlFun.COUNT(1L), //StuNames = SqlFun.GROUP_CONCAT(a.Name) }); var list3 = db.From <Student, Stuclass, Stuid>() .Join((Student a, Stuclass b) => a.Id == b.Sid) .Join((Student a, Stuid b) => a.Id == b.Sid) .Select((a, b, c) => new { a.Id, b.Name, c.IdNum }).ToList(); } }
public void TestGroup() { var connection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;password=1024;database=test;"); using (IDbContext db = new DbProxyContext(connection, DbContextType.Mysql)) { db.Open(); var list = db.From <Student>() .GroupBy(a => a.Name) .Having(a => SqlFun.COUNT(1L) > 1) .OrderByDescending(a => SqlFun.COUNT(1)) .Select(s => new { s.Name, Names = SqlFun.GROUP_CONCAT(s.Name), Count = SqlFun.COUNT(1L), Balance = SqlFun.SUM(s.Balance) }).ToList(); } }
public void TestGroup() { var connection = new Npgsql.NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=1024;Database=test;"); using (IDbContext db = new DbProxyContext(connection, DbContextType.Postgresql)) { db.Open(); var list = db.From <Student>() .GroupBy(a => a.Name) .Having(a => SqlFun.COUNT(1L) > 1) .OrderByDescending(a => SqlFun.COUNT(1)) .Select(s => new { s.Name, //Names = SqlFun.GROUP_CONCAT(s.Name), Count = SqlFun.COUNT(1L), Balance = SqlFun.SUM(s.Balance) }).ToList(); } }
public void TestGroup() { var connection = new System.Data.SqlClient.SqlConnection(@"Data Source=DESKTOP-9IS2HA6\SQLEXPRESS;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=1024"); using (IDbContext db = new DbProxyContext(connection, DbContextType.SqlServer)) { db.Open(); var list = db.From <Student>() .GroupBy(a => a.Name) .Having(a => SqlFun.COUNT(1L) > 1) .OrderByDescending(a => SqlFun.COUNT(1)) .Select(s => new { s.Name, //Names = SqlFun.GROUP_CONCAT(s.Name), Count = SqlFun.COUNT(1L), Balance = SqlFun.SUM(s.Balance) }).ToList(); } }
public void TestGroup() { var sb = new System.Data.SQLite.SQLiteConnectionStringBuilder(); sb.DataSource = "D:\\sqlite.db3"; var connection = new System.Data.SQLite.SQLiteConnection(sb.ConnectionString); using (IDbContext db = new DbProxyContext(connection, DbContextType.Sqlite)) { db.Open(); var list = db.From <Student>() .GroupBy(a => a.Name) .Having(a => SqlFun.COUNT(1L) > 1) .OrderByDescending(a => SqlFun.COUNT(1)) .Select(s => new { s.Name, Names = SqlFun.GROUP_CONCAT(s.Name), Count = SqlFun.COUNT(1L), Balance = SqlFun.SUM(s.Balance) }).ToList(); } }
public void TestPage() { var connection = new Npgsql.NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=1024;Database=test;"); using (IDbContext db = new DbProxyContext(connection, DbContextType.Postgresql)) { db.Open(); //第一种方式 var list1 = db.From <Student>().Page(1, 2, out long total1).Select().ToList(); var list2 = db.From <Student>().Page(2, 2, out long total2).Select().ToList(); //第二种方式 var id = 24; var(list3, total3) = db.From <Student>().Where(a => a.Id > id).Page(1, 2).SelectMany(); var(list4, total4) = db.From <Student>().Where(a => a.Id > id).Page(2, 2).SelectMany(); //分组分页 var(glist1, gtotal1) = db.From <Student>().GroupBy(a => a.Name).Page(1, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) }); var(glist2, gtotal2) = db.From <Student>().GroupBy(a => a.Name).Page(2, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) }); } }
public void TestPage() { var connection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;password=1024;database=test;"); using (IDbContext db = new DbProxyContext(connection, DbContextType.Mysql)) { db.Open(); //第一种方式 var list1 = db.From <Student>().Page(1, 2, out long total1).Select().ToList(); var list2 = db.From <Student>().Page(2, 2, out long total2).Select().ToList(); //第二种方式 var id = 24; var(list3, total3) = db.From <Student>().Where(a => a.Id > id).Page(1, 2).SelectMany(); var(list4, total4) = db.From <Student>().Where(a => a.Id > id).Page(2, 2).SelectMany(); //分组分页 var(glist1, gtotal1) = db.From <Student>().GroupBy(a => a.Name).Page(1, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) }); var(glist2, gtotal2) = db.From <Student>().GroupBy(a => a.Name).Page(2, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) }); } }
public void TestPage() { var connection = new System.Data.SqlClient.SqlConnection(@"Data Source=DESKTOP-9IS2HA6\SQLEXPRESS;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=1024"); using (IDbContext db = new DbProxyContext(connection, DbContextType.SqlServer)) { db.Open(); //第一种方式 var list1 = db.From <Student>().Page(1, 2, out long total1).Select().ToList(); var list2 = db.From <Student>().Page(2, 2, out long total2).Select().ToList(); //第二种方式,只分页不计数的可以通过skip实现 var id = 24; var(list3, total3) = db.From <Student>().Where(a => a.Id > id).Page(1, 2).SelectMany(); var(list4, total4) = db.From <Student>().Where(a => a.Id > id).Page(2, 2).SelectMany(); //分组分页 var(glist1, gtotal1) = db.From <Student>().GroupBy(a => a.Name).Page(1, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) }); var(glist2, gtotal2) = db.From <Student>().GroupBy(a => a.Name).Page(2, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) }); } }
public void TestPage() { var sb = new System.Data.SQLite.SQLiteConnectionStringBuilder(); sb.DataSource = "D:\\sqlite.db3"; var connection = new System.Data.SQLite.SQLiteConnection(sb.ConnectionString); using (IDbContext db = new DbProxyContext(connection, DbContextType.Sqlite)) { db.Open(); //第一种方式 var list1 = db.From <Student>().Page(1, 2, out long total1).Select().ToList(); var list2 = db.From <Student>().Page(2, 2, out long total2).Select().ToList(); //第二种方式 var id = 24; var(list3, total3) = db.From <Student>().Where(a => a.Id > id).Page(1, 2).SelectMany(); var(list4, total4) = db.From <Student>().Where(a => a.Id > id).Page(2, 2).SelectMany(); //分组分页 var(glist1, gtotal1) = db.From <Student>().GroupBy(a => a.Name).Page(1, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) }); var(glist2, gtotal2) = db.From <Student>().GroupBy(a => a.Name).Page(2, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) }); } }