none
Filter query problem RRS feed

  • Question

  • I have problem with below codes. I use a query to select data from 2 tables in database and put it in IQueryable<TEmployee> variable. Now I want to filter data from that variable, but I can't get the field of SECOND table. i only can get TEmployee Fields at here. Please help...

    at Employee Class i have this below method

            public IQueryable<TEmployee> GetData()
            {
                Connection oConn = new Connection();
                Dba oDB = oConn.CreateDataContext();
                var query = from emp in oDB.TEmployee
                            join ur in oDB.TUserRole
                                on emp.UserRoleID equals ur.UserRoleID
                            where emp.Gender == 'M'
                            select new
                            {
                               emp.EmployeeID,
                               emp.EmployeeName,
                               emp.BirthDate,
                               emp.Birthplace,
                               emp.Address,
                               emp.ContactNumber,
                               emp.Gender,
                               emp.UserName,
                               ur.UserRole
                            };
                 return (IQueryable<TEmployee>)query;
            }

            public IQueryable<TEmployee> FilterData(IQueryable<TEmployee> queryTEmployee,
                                                    string strCriteria,
                                                    string strQuery)
            {
                IQueryable<TEmployee> query = null;
               
                switch (strCriteria)
                {
                    case "Employee Name" :
                        query = from n in queryTEmployee
                                    where System.Data.Linq.SqlClient.SqlMethods.Like(n.EmployeeName,
                                                                                     "%" + strQuery + "%")
                                    select n;
                        break;
                    case "User Name" :
                        query = from n in queryTEmployee
                                where System.Data.Linq.SqlClient.SqlMethods.Like(n.UserName,
                                                                                 "%" + strQuery + "%")
                                select n;
                        break;
                    case "User Role" :
                        query = from n in queryTEmployee
                                //I can't get n.UserRole at here
                                //i know the problem lies on IQueryable<TEmployee>
                                //but i need some work around maybe..

                                where System.Data.Linq.SqlClient.SqlMethods.Like(n.....,
                                                                                 "%" + strQuery + "%")
                                select n;
                        break;
                    default:
                        break;      
                } //end switch

                return query;
            }

    At the Form i fill the datagrid view with this.


            IQueryable<TEmployee> employee;

            private void frmMasterUser_Load(object sender, EventArgs e)
            {
                TEmployee oEmployee = new TEmployee();
                employee = oEmployee.GetData();

                dgvEmployee.DataSource = employee;

            }

            private void btnSearch_Click(object sender, EventArgs e)
            {
                //now I want to filter record from this 'employee' variable (IQueryable Type)
                TEmployee oEmployee = new TEmployee();
                dgvEmployee.DataSource = oEmployee.FilterData(employee, "User Role", "Administrator");

            }
    Thursday, July 30, 2009 4:23 AM

Answers

  • Your GetData method is invalid because you're casting an anonymous type to IQueryable<TEmployee>.  You should create a simple DTO class:

    public class EmployeeAndUserRole
    {
        public int EmployeeID;
        public string EmployeeName;
        public DateTime BirthDate;
        public string Birthplace;
        public string Address;
        public string ContactNumber;
        public char Gender;
        public string UserName;
        public string UserRole;
    
        public EmployeeAndUserRole(int employeeId,string employeeName,DateTime birthDate,string birthPlace,string address,
                                    string contactNumber, char gender, string userName, string userRole)
        {
            EmployeeID = employeeID;
            EmployeeName = employeeName;
            BirthDate = birthDate;
            Birthplace = birthplace;
            Address = address;
            ContactNumber = contactNumber;
            Gender = gender;
            UserName = userName;
            UserRole = userRole;
    
        }
    }
    

    Then change GetData to:

    public IQueryable<EmployeeAndUserRole> GetData()
    {
        Connection oConn = new Connection();
        Dba oDB = oConn.CreateDataContext();
        var query = from emp in oDB.TEmployee
                    join ur in oDB.TUserRole
                        on emp.UserRoleID equals ur.UserRoleID
                    where emp.Gender == 'M'
                    select new EmployeeAndUserRole(
                       emp.EmployeeID,
                       emp.EmployeeName,
                       emp.BirthDate,
                       emp.Birthplace,
                       emp.Address,
                       emp.ContactNumber,
                       emp.Gender,
                       emp.UserName,
                       ur.UserRole
                    );
         return query;
    }

    And change FilterData to take IQueryable<EmployeeAndUserRole>:


    public IQueryable<EmployeeAndUserRole> FilterData(IQueryable<EmployeeAndUserRole> employeeAndUserRoleQuery,
                                                string strCriteria,
                                                string strQuery)
    {
        IQueryable<EmployeeAndUserRole> query = null;
    
        switch (strCriteria)
        {
            case "Employee Name" :
                query = from n in employeeAndUserRoleQuery
    					where n.EmployeeName.Contains(strQuery)
    					select n;
                break;
    
            case "User Name" :
                query = from n in employeeAndUserRoleQuery
                        where n.UserName.Contains(strQuery)
                        select n;
                break;
    
            case "User Role" :
                query = from n in employeeAndUserRoleQuery
                        where n.UserRole.Contains(strQuery)
                        select n;
                break;
        }
    
    	return query;
    }
    I haven't tested the code, so there maybe a few minor bugs, but the principle is correct.  I would question why you're separating the process into two functions, but that's for you to decide.







    • Proposed as answer by Paul Louth Monday, August 3, 2009 3:28 PM
    • Edited by Paul Louth Tuesday, August 4, 2009 1:48 AM
    • Marked as answer by Yichun_Feng Thursday, August 6, 2009 1:22 AM
    Monday, August 3, 2009 2:13 PM

All replies

  • I have problem with below codes. I use a query to select data from 2 tables in database and put it in IQueryable<TEmployee> variable. Now I want to filter data from that variable, but I can't get the field of SECOND table. i only can get TEmployee Fields at here. Please help...

    at Employee Class i have this below method

            public IQueryable<TEmployee> GetData()
            {
                Connection oConn = new Connection();
                DbWiDiJAsA oDB = oConn.CreateDataContext();
                var query = from emp in oDB.TEmployee
                            join ur in oDB.TUserRole
                                on emp.UserRoleID equals ur.UserRoleID
                            where emp.Gender == 'M'
                            select new
                            {
                               emp.EmployeeID,
                               emp.EmployeeName,
                               emp.BirthDate,
                               emp.Birthplace,
                               emp.Address,
                               emp.ContactNumber,
                               emp.Gender,
                               emp.UserName,
                               ur.UserRole
                            };
                 return (IQueryable<TEmployee>)query;
            }

    Hmm. That shouldn't even compile. Try changing from "select new ..." to "select emp"...

    .
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    Thursday, July 30, 2009 4:28 AM
    Answerer
  • okay, that's not the problem now :)

    The problem is in
            public IQueryable<TEmployee> FilterData(IQueryable<TEmployee> queryTEmployee,
                                                    string strCriteria,
                                                    string strQuery)
            {
                IQueryable<TEmployee> query = null;
               
                switch (strCriteria)
                {
                        case "User Role" :
                        query = from n in queryTEmployee
                                //I can't get n.UserRole at here
                                //i know the problem lies on IQueryable<TEmployee>
                                //but i need some work around maybe..

                                where System.Data.Linq.SqlClient.SqlMethods.Like(n.....,
                                                                                 "%" + strQuery + "%")
                                select n;
                        break;
             default:
                        break;      
                } //end switch

                return query;
            }


    =====

      private void btnSearch_Click(object sender, EventArgs e)
            {
                //now I want to filter record from this 'employee' variable (IQueryable Type)
                TEmployee oEmployee = new TEmployee();
                dgvEmployee.DataSource = oEmployee.FilterData(employee, "User Role", "Administrator");

            }
    Thursday, July 30, 2009 4:34 AM
  • query = from n in queryTEmployee
    where n.UserRole.Contains(strQuery)
    select n;

    Thursday, July 30, 2009 5:46 AM
  • Your GetData method is invalid because you're casting an anonymous type to IQueryable<TEmployee>.  You should create a simple DTO class:

    public class EmployeeAndUserRole
    {
        public int EmployeeID;
        public string EmployeeName;
        public DateTime BirthDate;
        public string Birthplace;
        public string Address;
        public string ContactNumber;
        public char Gender;
        public string UserName;
        public string UserRole;
    
        public EmployeeAndUserRole(int employeeId,string employeeName,DateTime birthDate,string birthPlace,string address,
                                    string contactNumber, char gender, string userName, string userRole)
        {
            EmployeeID = employeeID;
            EmployeeName = employeeName;
            BirthDate = birthDate;
            Birthplace = birthplace;
            Address = address;
            ContactNumber = contactNumber;
            Gender = gender;
            UserName = userName;
            UserRole = userRole;
    
        }
    }
    

    Then change GetData to:

    public IQueryable<EmployeeAndUserRole> GetData()
    {
        Connection oConn = new Connection();
        Dba oDB = oConn.CreateDataContext();
        var query = from emp in oDB.TEmployee
                    join ur in oDB.TUserRole
                        on emp.UserRoleID equals ur.UserRoleID
                    where emp.Gender == 'M'
                    select new EmployeeAndUserRole(
                       emp.EmployeeID,
                       emp.EmployeeName,
                       emp.BirthDate,
                       emp.Birthplace,
                       emp.Address,
                       emp.ContactNumber,
                       emp.Gender,
                       emp.UserName,
                       ur.UserRole
                    );
         return query;
    }

    And change FilterData to take IQueryable<EmployeeAndUserRole>:


    public IQueryable<EmployeeAndUserRole> FilterData(IQueryable<EmployeeAndUserRole> employeeAndUserRoleQuery,
                                                string strCriteria,
                                                string strQuery)
    {
        IQueryable<EmployeeAndUserRole> query = null;
    
        switch (strCriteria)
        {
            case "Employee Name" :
                query = from n in employeeAndUserRoleQuery
    					where n.EmployeeName.Contains(strQuery)
    					select n;
                break;
    
            case "User Name" :
                query = from n in employeeAndUserRoleQuery
                        where n.UserName.Contains(strQuery)
                        select n;
                break;
    
            case "User Role" :
                query = from n in employeeAndUserRoleQuery
                        where n.UserRole.Contains(strQuery)
                        select n;
                break;
        }
    
    	return query;
    }
    I haven't tested the code, so there maybe a few minor bugs, but the principle is correct.  I would question why you're separating the process into two functions, but that's for you to decide.







    • Proposed as answer by Paul Louth Monday, August 3, 2009 3:28 PM
    • Edited by Paul Louth Tuesday, August 4, 2009 1:48 AM
    • Marked as answer by Yichun_Feng Thursday, August 6, 2009 1:22 AM
    Monday, August 3, 2009 2:13 PM
  • Hi Paul,

    Thanks for the reply, I'll try it immediately with creating a Middle Class.
    Anyway, what is DTO?

    For your query, "why you're separating the process into two functions?"
    Because my intention is want to get data from the database only once through GetData() function, and then when user want to filter/find record, I only filter it through the variable by result of GetData() function. So according to me it works as a dataview, so no need another round trip to database. Is it correct for my idea or intention?

    Thanks for the help.
    Friday, August 7, 2009 1:47 AM