implementing effective searching in business code(maybe with linq?)
-
hi, 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 = valueand 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 -
hi, 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 = valueand 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 Gideongiddy_guitarist wrote:
If I were to use linq how again would I write a function to build a query on the fly?
That's what Lamda queries are for. Take a look at them, because they really do fit the bill with regards to your requirements.
Deja View - the feeling that you've seen this post before.
-
giddy_guitarist wrote:
If I were to use linq how again would I write a function to build a query on the fly?
That's what Lamda queries are for. Take a look at them, because they really do fit the bill with regards to your requirements.
Deja View - the feeling that you've seen this post before.
ah! You mean take a lambda expression in my function parameter? I would have never thought of that? Also, are you saying loading all objects in memory and then searching them would be more efficient than searching a data base? Because I cant use Linq to SQL, I'm using an Access database. But I'm curious, searching data like this is a common thing in a lot of apps, how do they implement it? Thanks so much Gideon
-
hi, 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 = valueand 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 GideonDiamond Binding's business objects expose a variety of methods for selecting sets of objects. For the simple searching we expose what you described T[] T.FindByColumn(T.Columns.Foo, value). In most apps that I've implemented then its by far the most common useage. If you were implementing your own data layer by hand, then providing FindByColumn, FindAll and for the rare occasions that you need something more complex, select by stored proc. Select by stored proc could work like T[] T.FindUsing(procName, params[]). The next level of complexity works using NHibernate Expressions - this is a fairly simple API that covers your boolean logic scenarios. This is implemented by a FindBy that takes an Expression object. The "generic" way that this expression is constructed is by using factory methods. so expression = Expression.Or( expr1, expr2 ). Expression.Between(column, low, high), etc. This is do-able in house if you wanted to go for the learning experience - a simple implementation would be to just emit an SQL condition straight from this tree. We also provide Hibernate Query Language support - HQL could be seen as an "object-orientated" version of SQL, referring to the model, not the DB. So you can do conditions like "order.product.department.name = 'foo'". Linq is on a similar level of complexity to this. So theres a fair few options. My recommendation (apart from buying our product :P) would be to start with a bunch of Expression style objects for single column search (= != gt lt) and then expose a FindByExpression and perhaps some thin wrappers such as FindByColumn(...) { r FindByExpression(Expression.Eq(...)) }. Then you can build up your "library" of expressions and go from there. Definitely don't load everything and query in memory. Imagine if you wanted only one out of the million audit records ;)
Mark Churchill Director, Dunn & Churchill Pty Ltd Free Download: Diamond Binding: The simple, powerful, reliable, and effective data layer toolkit for Visual Studio.
Alpha release: Entanglar: Transparant multiplayer framework for .Net games. -
ah! You mean take a lambda expression in my function parameter? I would have never thought of that? Also, are you saying loading all objects in memory and then searching them would be more efficient than searching a data base? Because I cant use Linq to SQL, I'm using an Access database. But I'm curious, searching data like this is a common thing in a lot of apps, how do they implement it? Thanks so much Gideon
Do you have to use Access? Could you not replace it with SQL Server CE, which would give you the easy deployment capabilities you get with Access databases, and the ability to use L2S.
Deja View - the feeling that you've seen this post before.
-
Do you have to use Access? Could you not replace it with SQL Server CE, which would give you the easy deployment capabilities you get with Access databases, and the ability to use L2S.
Deja View - the feeling that you've seen this post before.
Yes! I already did this whole app(Hotel Management Studio) with sql server 2k5 express. Turned into a 15,000 line mess, mostly because I didn't design it well(or didn't design it all!). I'm doing the whole thing again and this time building use cases and going through everything right(I think) Part of my failure was because Access was the perfect solution and when i did it with sqlservr it raised the complexity and especially the networking stuff got crazy, I have to use TCP/IP to open the data base over LAN, with access I can just open the file over a UNC path. Sweet and simple. Just like the app should be, I dont think its such a big app anyway. If you are wondering what kind of computer programming, I'm one that turned 18 last week :^) , I dont know hardly anything when it comes to good design but I know quite a bit about technology and API(I'm an MCTS :WinDev and I've even done Win32 API) It would be very helpful if you could comment on Mark's Solution? Thanks so much. Gideon
-
Diamond Binding's business objects expose a variety of methods for selecting sets of objects. For the simple searching we expose what you described T[] T.FindByColumn(T.Columns.Foo, value). In most apps that I've implemented then its by far the most common useage. If you were implementing your own data layer by hand, then providing FindByColumn, FindAll and for the rare occasions that you need something more complex, select by stored proc. Select by stored proc could work like T[] T.FindUsing(procName, params[]). The next level of complexity works using NHibernate Expressions - this is a fairly simple API that covers your boolean logic scenarios. This is implemented by a FindBy that takes an Expression object. The "generic" way that this expression is constructed is by using factory methods. so expression = Expression.Or( expr1, expr2 ). Expression.Between(column, low, high), etc. This is do-able in house if you wanted to go for the learning experience - a simple implementation would be to just emit an SQL condition straight from this tree. We also provide Hibernate Query Language support - HQL could be seen as an "object-orientated" version of SQL, referring to the model, not the DB. So you can do conditions like "order.product.department.name = 'foo'". Linq is on a similar level of complexity to this. So theres a fair few options. My recommendation (apart from buying our product :P) would be to start with a bunch of Expression style objects for single column search (= != gt lt) and then expose a FindByExpression and perhaps some thin wrappers such as FindByColumn(...) { r FindByExpression(Expression.Eq(...)) }. Then you can build up your "library" of expressions and go from there. Definitely don't load everything and query in memory. Imagine if you wanted only one out of the million audit records ;)
Mark Churchill Director, Dunn & Churchill Pty Ltd Free Download: Diamond Binding: The simple, powerful, reliable, and effective data layer toolkit for Visual Studio.
Alpha release: Entanglar: Transparant multiplayer framework for .Net games.hi mark, Thanks for your reply, I like your solution, could you please elaborate on the Expression objects Is it something like:
class Expression{
//represents an expression
}Person[] p = Person.FindByExpression(Expression.And("Age>20","Country='Australia'"));
Person[] FindByExpression(Expression exp)
{
_personDataAccess.GetByQuery("SELECT * FROM Persons WHERE " + exp.ToString());
//exp.ToString would return something like (Age>20 AND Country='Australia")
}Could you correct me if I'm wrong please. Thanks so much Gideon
-
hi mark, Thanks for your reply, I like your solution, could you please elaborate on the Expression objects Is it something like:
class Expression{
//represents an expression
}Person[] p = Person.FindByExpression(Expression.And("Age>20","Country='Australia'"));
Person[] FindByExpression(Expression exp)
{
_personDataAccess.GetByQuery("SELECT * FROM Persons WHERE " + exp.ToString());
//exp.ToString would return something like (Age>20 AND Country='Australia")
}Could you correct me if I'm wrong please. Thanks so much Gideon
Roughly correct. A simple implementation would have say: Expression { virtual ToString() } ColumnEqExpression : Expression { column, value } ColumnGtExpression : Expression { column, value } OrExpression : Expression { Expression left, right } So you could construct up (and some nicer static methods could make this more readable - see our API) myExpr = new OrExpression( left = new ColumnEqExpression(name, joe), right = new ColumnGtExpression(age, 20) ); So the ColumnMatchExpression.ToString might* return "name = 'joe'" OrExpression.ToString might* return left.ToString() + " OR " + right.ToString() * The expression structure shouldn't actaully return out SQL with ToString. It should be treated as a model - so have an SQLQueryDielectThingo to iterate over it and emit SQL (so you could write an AccessQueryDielect, etc). Also then you would be able to use parameterised queries where appropriate.
Mark Churchill Director, Dunn & Churchill Pty Ltd Free Download: Diamond Binding: The simple, powerful, reliable, and effective data layer toolkit for Visual Studio.
Alpha release: Entanglar: Transparant multiplayer framework for .Net games. -
Roughly correct. A simple implementation would have say: Expression { virtual ToString() } ColumnEqExpression : Expression { column, value } ColumnGtExpression : Expression { column, value } OrExpression : Expression { Expression left, right } So you could construct up (and some nicer static methods could make this more readable - see our API) myExpr = new OrExpression( left = new ColumnEqExpression(name, joe), right = new ColumnGtExpression(age, 20) ); So the ColumnMatchExpression.ToString might* return "name = 'joe'" OrExpression.ToString might* return left.ToString() + " OR " + right.ToString() * The expression structure shouldn't actaully return out SQL with ToString. It should be treated as a model - so have an SQLQueryDielectThingo to iterate over it and emit SQL (so you could write an AccessQueryDielect, etc). Also then you would be able to use parameterised queries where appropriate.
Mark Churchill Director, Dunn & Churchill Pty Ltd Free Download: Diamond Binding: The simple, powerful, reliable, and effective data layer toolkit for Visual Studio.
Alpha release: Entanglar: Transparant multiplayer framework for .Net games.Thanks so much Mark, it all looks good on paper so far!