Best way for complex query when using a service

Apr 23, 2013 at 10:06 PM
Markus,
I am used to being able to specify a query (either with EF or by building an SQL query string) and using that for complex queries (those involving and/or/not logic, etc.)

In the CODE FW, are there any guidelines on the way to pass such queries?

Examples:
Specify that I want anything except records with the given value
Grouping:
field1 is x AND field2 is y
field1 is x OR (field2 is y and field3 is z)

In the FW, we define the fields we might use in the request we pass to the service. I know I can come up with a way to add support for these options, but was hoping that someone had already come up with some ways that worked well.
Coordinator
Apr 26, 2013 at 5:30 PM
I am not entirely sure I follow the exact question, but are you considering to pass in a query expression as part of your service call somehow? Like passing a string that has the where clause? It is not recommended to that in a service based app. Of course you could do that if you wanted and then pass it on to your data back end. At that point, you'd be building a scenario where the caller of the service has to understand what is going on behind the service and you would never be able to just change the things behind the service without the caller breaking. So that breaks all kinds of SOA principles and would really remove many of the benefits you get out of the SOA setup.

The "clean" way to do something like that if you really need kind of a "query engine" approach would be to abstract the whole scenario out and have service contracts that can express that same intent. Basically build up some kind of expression tree. So you could have a class like this:
[DataContract]
public class Expression
{
    public ExpressionCollection SubExpressions { get; set; }
}

[DataContract]
public class IndividualExpression
{
    public object Left { get; set; }
    public Operator Operator { get; set; }
    public object Right { get; set; }
}
You could then use this to express some of your examples like this:
// example 1:
var expression = new Expression();
expression.SubExpressions.Add(new IndividualExpression{ Left = "field1", Operator = Operators.Equals, Right = "x" });
expression.SubExpressions.Add(Operators.And, new IndividualExpression{ Left = "field2", Operator = Operators.Equals, Right = "y" });

// example 2:
var expression = new Expression();

var subExpression = new Expression();
subExpression.SubExpressions.Add(new IndividualExpression{ Left = "field2", Operator = Operators.Equals, Right = "y" });
subExpression.SubExpressions.Add(Operators.And, new IndividualExpression{ Left = "field3", Operator = Operators.Equals, Right = "z" });

expression.SubExpressions.Add(new IndividualExpression{ Left = "field1", Operator = Operators.Equals, Right = "x" });
expression.SubExpressions.Add(Operators.Or, subExpression);
And so on, and so forth. This way, you can build a logical definition of what you want to do in a way that is agnostic to your implementation. (Note: Expression trees like this is what all query systems create behind the scenes). And then, in your service, you can pick up on this logical definition of what you are trying to achieve and then implement a way that runs this against your actual back end. For instance, you can use this information to create a T-SQL query string. Or, you can use LINQ expressions to actually create a LINQ query equivalent that you can then use to query anything LINQ can query (like EF).

Markus
Apr 29, 2013 at 7:14 PM
Markus,

The main issue was how to get the information to the backend search. If I just pass values (as is the case with the search examples I have seen), this will work if all the values being sent are cumulative (fid1 AND fid2 AND fid3, etc.)

I am trying to figure out the best way to model the request object so that I can pass additional filter criteria (OR, NOT, >, <, etc.) modifiers along with the actual data entered by the user to use in the search.

If I have 3 fid properties in the search request object, I would need a way to define the request object so I can specify how the Backend should handle the various submitted values. I was thinking that, if I preceded the value with a special character, then I could use that. But then I would have to pass all values as strings and convert on both sides. And I would still have issues with grouping the search criteria ((fid1 AND fid2) OR fid3).

I could build the expressions on the client as you indicated, but would probably break some rules because the client would have to have more intimate knowledge of the Backend than I think it should.

What does EPS do when they are using a service and the frontend allows the user to specify data that may have more than just AND type searches?

Thanks
Coordinator
Apr 29, 2013 at 7:48 PM
I think my example above handles all that though, doesn't it? I agree however that it needs more knowledge about the back end than it should have. You could probably eliminate that by using an enum of available fields rather than allowing to specify a field as text. Something like this:
new IndividualExpression{ Left = Fields.Field1, Operator = Operators.Equals, Right = "x" };
The grouping (parenthesis) are handled by hierarchies, which is what they really indicate.

But yeah, this is quite the pain. If you really need a true query mechanism where you allow people to create their own queries in a "pick your field and comparison operator and combine with other things" kind of fashion (as we always used to do in VFP) then I don't think there is much else you can do.

As for what we are doing: We try to think in different patterns if we can. We are trying to do more guided searches as you would see in many apps today. So instead of building a query builder in a relational fashion, we would just have several search fields. For instance, we may have a field for item name, another for item description, another for max price and another for min price. We then look for which ones are set. If the user specifies all 3, we would have a query that is "where (item = x or description = y) and (price <= maxPrice and price >= minPrice)". So the ands and ors become somewhat obvious in the context, but we don't let the user specify that.

Another option that is real popular is to have a single search field (like Google, basically) and try to intelligently interpret what people might want to search for. I think this is a great option, but it is also harder to get right than people expect.

But if you really have to do the whole query engine thing, then I am not sure there is a good way other than defining an expression tree.

Markus
Apr 29, 2013 at 11:47 PM
Markus,

Hmmm, the enum idea is actually pretty cool and it should allow for enough separation between the FE and BE. Since the FE has to know enough to be able to display the fids, etc., the enum should solve the problem about how the data is actually stored, etc. Sure there is still some info that needs to be known (like data type), but that should be a trivial detail.

Most of our searches fit the type of searches that are most common (company name, phone number, and/or invoice number). But I will definitely need to do the complex searches and wanted to be sure I didn't adopt an approach that would cause problems later.

Thanks.
Coordinator
Apr 30, 2013 at 10:19 PM
Yeah, data type could be a concern a little bit. In our older framework, we had data access components that could abstract away a lot of that, but we haven't brought that forward yet since people use things like EF and nHibernate. But I personally actually don't mind using ADO for a lot of things, so we will bring that over. Can't hurt to have it, and the rest of the people can ignore it, right? :-)

Markus
May 1, 2013 at 3:57 AM
Markus,
I was thinking of using an XML construct and the enum approach to specify the fields. The BE will know what to convert the values to and the FE doesn't care much - since it will either be a simple string, number, or date. And XML will make it easy to specify groups, etc.

I only need this for the complex queries - which are the exception, not the rule. And that should allow me to build a generic parser as well.

Thanks again,

Fletcher
Coordinator
May 1, 2013 at 7:47 AM
You can use XML directly, but then be aware that it breaks the concepts of SOA and thus remove the benefits of SOA. In particular, the whole thing with defining a contract with strong structure. And especially since you can express the same thing you express in XML as objects, which will be much easier to use in memory than constantly parsing XML, I am not sure you'd get much of a benefit and quite a few downsides.

I am not saying you can't use XML. Just be aware of the consequences.


Markus
May 1, 2013 at 4:21 PM
Markus,

See, I got so hung up on the whole XML thing that I missed the obvious.... I don't need it (XML).

Thanks,

Fletcher