none
Generating LINQ query based on the form field values RRS feed

  • Question

  • I would like to generate a LINQ statement in a situation where user can enter values to form fields (firstName, lastName and email) and the LINQ would generate the following t-sql on demand (where only firstName and email fields are filled in):

    SELECT FirstName, LastName, Email FROM myTable WHERE FirstName = txtFirstName.Text AND Email = txtEmail.Text


    Currently my code overwrites the previous WHERE clause so that if txtFirstName is filled, it gets appended to the WHERE part, but whenever there is also txtEmail filled, only email is contained in the WHERE clause. Also I'm not quite sure where to put the AND-operator in this case, since it's needed in the case where multiple form fields are filled in:


     private IQueryable<myTable> GetStuff(string firstName, string lastName, string email)
            {
                IQueryable<myTable> results = from res in this.myTable
                                                  select res;
                                  
                if (!string.IsNullOrEmpty(firstName))
                {

                    // AND should be here?

                    res = myTable.Where(o => o.FirstName== firstName);
                }
                if (!string.IsNullOrEmpty(lastName))
                {

                    // AND should be here?
                    res = myTable.Where(o => o.LastName.Contains(lastName));
                }
                if (!string.IsNullOrEmpty(email))
                {

                    // AND should be here?
                    res = myTable.Where(o => o.Email.Contains(email));
                }
                return results;

    }
    Tuesday, August 26, 2008 6:56 AM

Answers

  • Fairly simple to do - use the results rather than the table directly. Each query returns an IQueryable so can be used in sequence, and are ANDed together:

     

    Code Snippet

    private IQueryable<myTable> GetStuff(string firstName, string lastName, string email)
            {
                IQueryable<myTable> results = from res in this.myTable
                                                  select res;
                                  
                if (!string.IsNullOrEmpty(firstName))
                {

                    // add firstname filter

                    results = from res in results where res.FirstName == firstName select res;

                }
                if (!string.IsNullOrEmpty(lastName))
                {

                    // add lastname filter

                    results = from res in results where res.LastName.Contains(lastName) select res;
                }
                if (!string.IsNullOrEmpty(email))
                {

                    // add Email filter
                    results = from res in results where res.Email.Contains(email) select res;
                }
                return results;

    }

     

     

     

    Tuesday, August 26, 2008 9:08 AM
    Answerer

All replies

  • Fairly simple to do - use the results rather than the table directly. Each query returns an IQueryable so can be used in sequence, and are ANDed together:

     

    Code Snippet

    private IQueryable<myTable> GetStuff(string firstName, string lastName, string email)
            {
                IQueryable<myTable> results = from res in this.myTable
                                                  select res;
                                  
                if (!string.IsNullOrEmpty(firstName))
                {

                    // add firstname filter

                    results = from res in results where res.FirstName == firstName select res;

                }
                if (!string.IsNullOrEmpty(lastName))
                {

                    // add lastname filter

                    results = from res in results where res.LastName.Contains(lastName) select res;
                }
                if (!string.IsNullOrEmpty(email))
                {

                    // add Email filter
                    results = from res in results where res.Email.Contains(email) select res;
                }
                return results;

    }

     

     

     

    Tuesday, August 26, 2008 9:08 AM
    Answerer
  • Thanks for this tip! I never realized that AND gets appended automatically this way. Anyway, this seems to be working which is great!

    Timo
    Tuesday, August 26, 2008 12:49 PM