C# QueryBuilder.Table方法代码示例

 public void GetSqlCommandGroupByAfterJoinIsTranslatedToSqlGroupBy()
 {
     var builder = new QueryBuilder();
     var roleCounts = from u in builder.Table<User>()
                      join l in builder.Table<UserRoleLink>() on u.Id equals l.UserId
                      group l by u.Login into ug
                      select new { Login = ug.Key, NumOfRoles = ug.Count(x => x.RoleId) };
     var command = roleCounts.ToSql();
     Assert.That(
         command.Command,
         Is.EqualTo(
             "SELECT [u].[Login] AS [Login], COUNT( ([l].[RoleId])) AS [NumOfRoles] " +
             "FROM [User] [u]  " +
                 "JOIN [UserRoleLink] [l] ON [u].[Id] = [l].[UserId] " +
             "GROUP BY [u].[Login]"));
 }

 public void GetSqlCommandExceptIsTranslatedToSqlExcept()
 {
     var builder = new QueryBuilder();
     var command = builder.Table<User>()
         .Except(builder.Table<User>().Where(u => u.Login == "kasia"))
         .ToSql();
     Assert.That(
         command.Command,
         Is.EqualTo("(SELECT * FROM [User] " +
                     "EXCEPT " +
                     "SELECT * FROM [User] [so1_u] WHERE ([so1_u].[Login]) = (@so1_p0))"));
 }

        public static List<RangePriceGroup> List(Range Range)
        {
            List<RangePriceGroup> result = new List<RangePriceGroup> ();

            QueryBuilder qb = new QueryBuilder (QueryBuilderType.Select);
            qb.Table (DatabaseTableName);
            qb.Columns ("id");
            qb.AddWhere ("rangeid", "=", Range.Id);

            Query query = Runtime.DBConnection.Query (qb.QueryString);
            if (query.Success)
            {
                while (query.NextRow ())
                {
                    try
                    {
                        result.Add (Load (query.GetGuid (qb.ColumnPos ("id"))));
                    }
                    catch
                    {}
                }
            }

            query.Dispose ();
            query = null;
            qb = null;

            return result;
        }

 public void GetSqlCommandUnionCanBeFiltered()
 {
     var builder = new QueryBuilder();
     var command = builder.Table<User>().Where(u => u.Login == "jacenty")
         .Union(builder.Table<User>().Where(u => u.Login == "kasia"))
         .Where(u => u.LoginAttempts == 0)
         .ToSql();
     Assert.That(
         command.Command,
         Is.EqualTo("SELECT * FROM " +
                         "(SELECT * FROM [User] [sq0_so0_u] WHERE ([sq0_so0_u].[Login]) = (@sq0_so0_p0) " +
                         "UNION " +
                         "SELECT * FROM [User] [sq0_so1_u] WHERE ([sq0_so1_u].[Login]) = (@sq0_so1_p0)) [u] " +
                     "WHERE ([u].[LoginAttempts]) = (@p2)"));
 }

 public void GetSqlCommandUsesNameResolverForTableAndColumnNames1()
 {
     var builder = new QueryBuilder(new PrefixBasedNameResolver());
     var roleCounts = from u in builder.Table<User>()
                      join l in builder.Table<UserRoleLink>() on u.Id equals l.UserId
                      group l by u.Login into ug
                      select new { Login = ug.Key, NumOfRoles = ug.Count(x => x.RoleId) };
     var command = roleCounts.ToSql();
     Assert.That(
         command.Command,
         Is.EqualTo(
             "SELECT [u].[col_Login] AS [Login], COUNT( ([l].[col_RoleId])) AS [NumOfRoles] " +
             "FROM [TBL_User] [u]  " +
                 "JOIN [TBL_UserRoleLink] [l] ON [u].[col_Id] = [l].[col_UserId] " +
             "GROUP BY [u].[col_Login]"));
 }

 public void GetSqlCommandTranslatesLeftJoinClauseAfterSelectToNestedSelect()
 {
     var builder = new QueryBuilder();
     var command = builder.Table<User>()
         .Select(u => new { UserId = u.Id, UserName = u.Login })
         .LeftJoin(builder.Table<UserRoleLink>(), u => u.UserId, l => l.UserId, (u, l) => new { u, l })
         .LeftJoin(builder.Table<Role>(), ul => ul.l.RoleId, r => r.Id, (ul, r) => new { ul.u.UserName, RoleName = r.Name })
         .ToSql();
     Assert.That(
         command.Command,
         Is.EqualTo(
             "SELECT [u].[UserName] AS [UserName], [r].[Name] AS [RoleName] " +
             "FROM (SELECT [sq0_u].[Id] AS [UserId], [sq0_u].[Login] AS [UserName] FROM [User] [sq0_u]) [u] " +
                 "LEFT JOIN [UserRoleLink] [l] ON [u].[UserId] = [l].[UserId] " +
                 "LEFT JOIN [Role] [r] ON [l].[RoleId] = [r].[Id]"));
 }

 public void GetSqlCommandTranslatesWhereClauseAfterSelectToNestedSelect()
 {
     var builder = new QueryBuilder();
     var command = builder.Table<User>()
         .Select(u => new { UserName = u.Login, Email = u.Email })
         .Where(u => u.UserName == "jacenty")
         .ToSql();
     Assert.That(
         command.Command,
         Is.EqualTo(
             "SELECT * FROM (SELECT [sq0_u].[Login] AS [UserName], [sq0_u].[Email] AS [Email] FROM [User] [sq0_u]) [u] " +
             "WHERE ([u].[UserName]) = (@p0)"));
 }

 public void GetSqlCommandJoinToItselfIsTranslatedCorrectly()
 {
     var builder = new QueryBuilder();
     var parentAndChildren = from parent in builder.Table<Role>()
                             join child in builder.Table<Role>() on parent.Id equals child.ParentId
                             where child.Name != "Admin"
                             select new { ParentName = parent.Name, ChildName = child.Name };
     var command = parentAndChildren.ToSql();
     Assert.That(
         command.Command,
         Is.EqualTo(
             "SELECT [parent].[Name] AS [ParentName], [child].[Name] AS [ChildName] " +
             "FROM [Role] [parent]  " +
             "JOIN [Role] [child] ON [parent].[Id] = [child].[ParentId] " +
             "WHERE ([child].[Name]) <> (@p0)"));
 }

 public void GetSqlCommandJoinToItselfWithoutSelectClauseIsCalculatedCorrectly()
 {
     var builder = new QueryBuilder();
     var parentAndChildren = from parent in builder.Table<Role>()
                             join child in builder.Table<Role>() on parent.Id equals child.ParentId
                             select new { ParentName = parent.Name, ChildName = child.Name };
     var command = parentAndChildren.ToSql();
     Assert.That(
         command.Command,
         Is.EqualTo(
             "SELECT [parent].[Name] AS [ParentName], [child].[Name] AS [ChildName] " +
             "FROM [Role] [parent]  " +
             "JOIN [Role] [child] ON [parent].[Id] = [child].[ParentId]"));
 }

 public void GetSqlCommandJoinIsTranslatedToSqlJoin()
 {
     var builder = new QueryBuilder();
     var roles = from user in builder.Table<User>()
                 join link in builder.Table<UserRoleLink>() on user.Id equals link.UserId
                 join role in builder.Table<Role>() on link.RoleId equals role.Id
                 select new RoleInfo { Login = user.Login, RoleName = role.Name};
     var command = roles.ToSql();
     Assert.That(
         command.Command,
         Is.EqualTo(
             "SELECT [user].[Login] AS [Login], [role].[Name] AS [RoleName] " +
             "FROM [User] [user]  " +
                 "JOIN [UserRoleLink] [link] ON [user].[Id] = [link].[UserId]  " +
                 "JOIN [Role] [role] ON [link].[RoleId] = [role].[Id]"));
 }

 public void GetSqlCommandJoinsWithSubqueriesAreAllowed()
 {
     var builder = new QueryBuilder();
     var result = from u in builder.Table<User>()
                  join l in builder.Table<UserRoleLink>().Where(l => l.RoleId != 1) on u.Id equals l.UserId
                  select u.Login;
     var command = result.ToSql();
     Assert.That(
         command.Command,
         Is.EqualTo(
         "SELECT [u].[Login] " +
         "FROM [User] [u]  " +
         "JOIN (SELECT * FROM [UserRoleLink] [sq1_l] WHERE ([sq1_l].[RoleId]) <> (@sq1_p0)) [l] ON [u].[Id] = [l].[UserId]"));
 }

 public void GetSqlCommandGroupByWithSumIsTranslatedToSqlGroupBy()
 {
     var builder = new QueryBuilder();
     var loginAttempts = from r in builder.Table<Role>()
                         join l in builder.Table<UserRoleLink>() on r.Id equals l.RoleId
                         join u in builder.Table<User>() on l.UserId equals u.Id
                         group u by r.Name into rg
                         select new { Role = rg.Key, LoginAttempts = rg.Sum(x => x.LoginAttempts) };
     var command = loginAttempts.ToSql();
     Assert.That(
         command.Command,
         Is.EqualTo(
             "SELECT [r].[Name] AS [Role], SUM( ([u].[LoginAttempts])) AS [LoginAttempts] " +
             "FROM [Role] [r]  " +
                 "JOIN [UserRoleLink] [l] ON [r].[Id] = [l].[RoleId]  " +
                 "JOIN [User] [u] ON [l].[UserId] = [u].[Id] " +
             "GROUP BY [r].[Name]"));
 }

 public void GetSqlCommandGroupByWithOnePropertyResultIsTranslatedToSqlGroupBy()
 {
     var builder = new QueryBuilder();
     var loginCounts = from u in builder.Table<User>()
                       group u.Login by new { u.FirstName, u.LastName } into ug
                       select new { User = ug.Key.FirstName + " " + ug.Key.LastName, NumOfLogins = ug.Count() };
     var command = loginCounts.ToSql();
     Assert.That(
         command.Command,
         Is.EqualTo(
             "SELECT (([u].[FirstName]) + (@p0)) + ([u].[LastName]) AS [User], COUNT(*) AS [NumOfLogins] " +
             "FROM [User] [u] " +
             "GROUP BY [u].[FirstName], [u].[LastName]"));
 }

 public void GetSqlCommandGroupByWithCompoundResultIsTranslatedToSqlGroupBy()
 {
     var builder = new QueryBuilder();
     var loginCounts = from u in builder.Table<User>()
                       group new { u.FirstName, u.LastName } by u.Login into ug
                       select new { Login = ug.Key, NumOfNames = ug.Count(x => x.FirstName + " " + x.LastName) };
     var command = loginCounts.ToSql();
     Assert.That(
         command.Command,
         Is.EqualTo(
             "SELECT [u].[Login] AS [Login], COUNT( ((([u].[FirstName]) + (@p0)) + ([u].[LastName]))) AS [NumOfNames] " +
             "FROM [User] [u] " +
             "GROUP BY [u].[Login]"));
 }

 public void GetSqlCommandTranslatesAndWithBoolPropertyToIntComparison()
 {
     var builder = new QueryBuilder();
     var command = builder.Table<User>().Where(u => u.Disabled && u.LoginAttempts == 0).ToSql();
     Assert.That(
         command.Command,
         Is.EqualTo("SELECT * FROM [User] [u] WHERE ([u].[Disabled] = 1) AND (([u].[LoginAttempts]) = (@p0))"));
 }