implementing effective searching in the business[maybe with linq?]

[giddy]

hi,

Yes its a design question again. =)

If I have something like:
class Person
{
//functions:
static Person[] GetAllPersons();
static Person[] Search(string field,string value);
}

In the second function I would do something like build an sql query
like this:

SELECT ........... WHERE field = value

and send it through another data class to retrieve the right Persons.

eg:
Person[] teens = Person.Search("Age",18);

Firstly the biggest problem searching with AND, >,<, OR, Between etc.?
Which terribly limits the search.I could make more functions for that,
but its turns into a little bit of a mess. Is there a better/more
generic way to build a query on the fly?

Would I benefit by using Linq instead/would it be more efficient to
load all persons in memory and then search them with linq? If I were
to use linq how again would I write a function to build a query on the
fly?

Thanks so much

Gideon

 

[Göran Andersson]

hi,

Yes its a design question again. =)

If I have something like:
class Person
{
//functions:
static Person[] GetAllPersons();
static Person[] Search(string field,string value);
}

In the second function I would do something like build an sql query
like this:

SELECT ........... WHERE field = value

and send it through another data class to retrieve the right Persons.

eg:
Person[] teens = Person.Search("Age",18);

Firstly the biggest problem searching with AND, >,<, OR, Between etc.?
Which terribly limits the search.I could make more functions for that,
but its turns into a little bit of a mess. Is there a better/more
generic way to build a query on the fly?

Would I benefit by using Linq instead/would it be more efficient to
load all persons in memory and then search them with linq? If I were
to use linq how again would I write a function to build a query on the
fly?

Thanks so much

Gideon

You can make a query or stored procedure that takes various parameters,
and write the conditions so that it can use null values in the parameters.

Example:

create procedure Person_Search
@LastName varchar(200)
@AgeFrom int,
@AgeTo int
as
select FirstName, LastName, Age
from Person
where LastName = isnull(@LastName, LastName)
and Age between isnull(@AgeFrom, 0) and isnull(@AgeTo, 1000)

Now you just send null values for the parameters that you don't want to use.

 

[William Stacey]

If using EF, you can use esql string predicates like so and get sql
operators such as like, and, or, etc.

private void button6_Click(object sender, EventArgs e)
{
var u = Search("it.Tag like '%illiam' and it.IsLocal=true");
ObjectDumper.Write(u);

var u2 = Search("it.UserID=1");
ObjectDumper.Write(u2);
}

private IEnumerable<Test.Users> Search(string predicate)
{
using (Test.TestDB db = new Test.TestDB())
{
var q = db.Users.Where(predicate).Select(u => u);
return q.ToList();
}
}

If using L2S, you can download dynamic library:
http://weblogs.asp.net/scottgu/arch...t-1-using-the-linq-dynamic-query-library.aspx

--William

hi,

Yes its a design question again. =)

If I have something like:
class Person
{
//functions:
static Person[] GetAllPersons();
static Person[] Search(string field,string value);
}

In the second function I would do something like build an sql query
like this:

SELECT ........... WHERE field = value

and send it through another data class to retrieve the right Persons.

eg:
Person[] teens = Person.Search("Age",18);

Firstly the biggest problem searching with AND, >,<, OR, Between etc.?
Which terribly limits the search.I could make more functions for that,
but its turns into a little bit of a mess. Is there a better/more
generic way to build a query on the fly?

Would I benefit by using Linq instead/would it be more efficient to
load all persons in memory and then search them with linq? If I were
to use linq how again would I write a function to build a query on the
fly?

Thanks so much

Gideon