ASP.NET Dynamic Linq Search

Question


var query = (from u in results
            select u).AsQueryable();


//Build where clause
if (!string.IsNullOrEmpty(userRequest.searchData))
{
    if (userRequest.searchBy == "LastName")
    {
        var likestr = userRequest.searchData.Trim();
        query = (from n in query where n.StartsWith(likestr) select n).AsQueryable();

    }
    if (userRequest.searchBy == "FirstName")
    {

    }
    if (userRequest.searchBy == "Email")
    {
        //var likestr = string.Format("%{0}%", userRequest.searchData.Trim());

    }
    if (userRequest.searchBy == "UserId")
    {
        query = query.Where(x => SqlMethods.Equals(x.UserId, Convert.ToInt32(userRequest.searchData)));
    }
}

First I query the DB and store in var query.

Then if there is search data I am trying to tack on the Where clause using 1 or 4 possible searches.

Help?

score 0 · Answer 1 · answered Dec 09 '10 at 15:15

0

I would use .Contains instead.

answered Dec 09 '10 at 15:15

Jan Johansen

1,999
6
30
43

He just wanted the names starting with the uservalue. – Christian Dec 09 '10 at 15:27

Christian · Answer 2 · 2010-12-09T16:00:10.100

Don't try to imitate SQL-Behaviour with Linq. You got a list and can query this list based on object methods.

Try this instead:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;

namespace Test1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable table = new DataTable();
            table.Columns.Add("ID", typeof(int));
            table.Columns.Add("FIRSTNAME", typeof(string));
            table.Columns.Add("LASTNAME", typeof(string));
            table.Columns.Add("EMAIL", typeof(string));

            // Here we add five DataRows.
            table.Rows.Add(1, "Chris", "Foo", "chris.foo@mail.com");
            table.Rows.Add(2, "Christoph", "Bar", "christoph.bar@mail.com");
            table.Rows.Add(3, "Michael", "FooBar", "michael.foobar@mail.com");
            table.Rows.Add(4, "Andreas", "BarFoo", "andreas.barfoo@mail.com");
            table.Rows.Add(5, "Carl", "Bar", "carl.bar@mail.com");

            Console.WriteLine("//Query ID");
            var query1 = (from dr in table.AsEnumerable() where dr.Field<int>("ID") == 1 select dr).FirstOrDefault();

            Console.WriteLine(query1.Field<int>("ID"));

            Console.WriteLine("//Query Firstname");
            var query2 = (from dr in table.AsEnumerable() where dr.Field<string>("FIRSTNAME").StartsWith("C") select dr).ToList<System.Data.DataRow>();

            foreach (var q in query2)
            {
                Console.WriteLine(q.Field<int>("ID"));
            }

            Console.ReadLine();
        }
    }
}

Output:

//Query ID
1
//Query Firstname
1
2
5

I edited my answer with a more complete example. You should get the line. Use always the standard methods, not any sql methods. So for email in your above example .contains, .equals where you compare objects, startswith and so on. And by the way, the result of a query is a queryable, so no need for a transformation. — Christian, Dec 09 '10 at 16:00
var sortColumnBy = userRequest.sortColumn; var orderedQuery = query.OrderBy(x => x.AttributeBag[ sortColumnBy ] + " " + userRequest.sortType) — Mark, Dec 09 '10 at 17:35

score 0 · Answer 3 · answered Dec 10 '10 at 00:25

Just add to the query as you need to. You can chain multiple 'where' clauses onto it and they will execute in turn.

var query = (from u in results select u);

if (!string.IsNullOrEmpty(userRequest.searchData))
{
    if (userRequest.searchBy == "LastName")
    {
        var likestr = userRequest.searchData.Trim();
        query = (from n in query where n.LastName.StartsWith(likestr) select n);
    }
    if (userRequest.searchBy == "UserId")
    {
        var userId = Convert.ToInt32(userRequest.searchData);
        query = (from n in query where n.UserId == userId select n);
    }

etc

score 0 · Answer 4 · answered Dec 10 '10 at 02:43

0

Why not give a shot with Expression Trees

answered Dec 10 '10 at 02:43

ASP.NET Dynamic Linq Search

4 Answers4