Ejemplo n.º 1
0
        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();
            }
        }
Ejemplo n.º 2
0
        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();
            }
        }
Ejemplo n.º 3
0
        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();
            }
        }
Ejemplo n.º 4
0
        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();
            }
        }