public void TestJoin() { 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 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, 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 TestJoin() { 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 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, 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 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(); } }