Greetigns to all,

I need to convert some SQL statements that are embedded into ASP.NET code into LLBLGEN, I need help on how to achieve this:

            Cn.Open();
            this.Cmd.CommandText = "SELECT * FROM Users WHERE Disabled=? ";
            
            if (Request.QueryString["Disabled"] == "1")
                Utils.DB.FillCommandParamaters(Cmd, true);
            else
                Utils.DB.FillCommandParamaters(Cmd, false);

            if(!IsAdministrator)
                Cmd.CommandText += " AND HidePresence=0";


            if (Request.QueryString["Admin"] != null)
                Cmd.CommandText += " AND UserID IN (SELECT UserID FROM Administrators)";

            if (username != null && username.Trim() != "")
            {
                username = username.Replace("'", ""); //injection protection
                this.Cmd.CommandText += string.Format(" AND (UserName LIKE '{0}%' OR Email LIKE '{0}%') ", username);
            }

            string order = Request.QueryString["order"];

            if (order == "regdate")
                this.Cmd.CommandText += " ORDER BY RegistrationDate"; 
            else if (order == "email")
                this.Cmd.CommandText += " ORDER BY Email";
            else if (order == "posts")
                this.Cmd.CommandText += " ORDER BY Count";
            else if (order == "logondate")
                this.Cmd.CommandText += " ORDER BY LastLogonDate";
            else
                this.Cmd.CommandText += " ORDER BY UserName";

            DataTable dt = new DataTable();
            DbDataReader dr = Cmd.ExecuteReader();
            dt.Load(dr);
            dr.Close();
            Cn.Close();

            
            this.rptUsersList.DataSource = pagedSrc;
            this.rptUsersList.DataBind();
        }

So far this is what I came up with:

        public enum SortByOptions
        {
            RegDate, Email, Posts, LogonDate, UserName
        }

        public static DataTable GetUsersComplex(int UserId, string UserName, bool IsDisabled, bool IsAdministrator, SortByOptions SortBy) 
        {
            
            RelationPredicateBucket bucket = new RelationPredicateBucket();
            DataTable dt = new DataTable();
            ResultsetFields Results = new ResultsetFields(9);

            try
            {
                Results.DefineField(UserFields.Userid, 0);
                Results.DefineField(UserFields.Username, 1);
                Results.DefineField(UserFields.Name, 2);
                Results.DefineField(UserFields.Email, 3);
                Results.DefineField(UserFields.Count, 4);
                Results.DefineField(UserFields.Registrationdate, 5);
                Results.DefineField(UserFields.Disabled, 6);
                Results.DefineField(UserFields.Lastlogondate, 7);
                Results.DefineField(UserFields.Hidepresence, 8);

                if (IsDisabled)
                {
                    bucket.PredicateExpression.Add(UserFields.Disabled == 1);
                }
                else
                {
                    bucket.PredicateExpression.Add(UserFields.Disabled == 0);
                }

                if (!IsAdministrator)
                    bucket.PredicateExpression.Add(UserFields.Hidepresence == 0);

                // TODO: In Code

                SortExpression OrderBy = new SortExpression();
                switch (SortBy)
                {
                    case SortByOptions.UserName:
                        OrderBy = new SortExpression(UserFields.Username | SortOperator.Descending);
                        break;
                    case SortByOptions.LogonDate:
                        OrderBy = new SortExpression(UserFields.Lastlogondate | SortOperator.Descending);
                        break;
                    case SortByOptions.Posts:
                        OrderBy = new SortExpression(UserFields.Count | SortOperator.Descending);
                        break;
                    case SortByOptions.Email: 
                        OrderBy = new SortExpression(UserFields.Email | SortOperator.Descending);
                        break;
                    case SortByOptions.RegDate: 
                        OrderBy = new SortExpression(UserFields.Registrationdate | SortOperator.Descending);
                        break;
                    default:
                        break;
                }

                using (IDataAccessAdapter adapter = DataAccessManager.CreateAdapter())
                {
                    adapter.FetchTypedList(Results, dt, bucket, 0, OrderBy, false);
                }
            }
            catch (Exception ex)
            {

            }
            return dt;
        }

I looks like you are almost there. To do the "IN" clause you can use the FieldCompareSetPredicate:

 if (Request.QueryString["Admin"] != null)
{
     bucket.PredicateExpression.Add(
          var f1 = new FieldCompareSetPredicate(
          UserFields.Userid, null, 
          AdministratorFields.Userid, null,
          SetOperator.In, null, false);
}

Also, as you are fetching just fields from User, you could use an EntityCollection instead of a DynamicList:

public static DataTable GetUsersComplex(int UserId, string UserName, bool IsDisabled, bool IsAdministrator, SortByOptions SortBy) 
        {
            
            RelationPredicateBucket bucket = new RelationPredicateBucket();
            EntityCollection<UserEntity> results = new EntityCollection<UserEntity>();
            
             try
            {
                
                if (IsDisabled)
                {
                    bucket.PredicateExpression.Add(UserFields.Disabled == 1);
                }
                else
                {
                    bucket.PredicateExpression.Add(UserFields.Disabled == 0);
                }

                if (!IsAdministrator)
                    bucket.PredicateExpression.Add(UserFields.Hidepresence == 0);

                // TODO: In Code

                SortExpression OrderBy = new SortExpression();
                switch (SortBy)
                {
                    case SortByOptions.UserName:
                        OrderBy = new SortExpression(UserFields.Username | SortOperator.Descending);
                        break;
                    case SortByOptions.LogonDate:
                        OrderBy = new SortExpression(UserFields.Lastlogondate | SortOperator.Descending);
                        break;
                    case SortByOptions.Posts:
                        OrderBy = new SortExpression(UserFields.Count | SortOperator.Descending);
                        break;
                    case SortByOptions.Email: 
                        OrderBy = new SortExpression(UserFields.Email | SortOperator.Descending);
                        break;
                    case SortByOptions.RegDate: 
                        OrderBy = new SortExpression(UserFields.Registrationdate | SortOperator.Descending);
                        break;
                    default:
                        break;
                }

                using (IDataAccessAdapter adapter = DataAccessManager.CreateAdapter())
                {
                    adapter.FetchEntityCollection(results, bucket, 0, OrderBy);
                }
            }
            catch (Exception ex)
            {

            }
            return results;
        }