For v2.0 I'm looking for a more easier way to define filters. As I'm definitely not the first on this planet to come up with a great query construction mechanism, I looked around to see how others solved this problem.

There are 3 major options: 1) string based: Query q = new Query("Customer where CustomerID > {0}", 10); 2) operator overloading constructions 3) objects like the current predicate mechanism

Now, I'm not fond of string based query mechanisms. THe major drawback is that they're not typesafe. Say you change a field in the DB or your rename a field in an entity. You re-compile your code after re-generating it and everything compiles. But it can break at runtime. Not something you want.

Operator overloading is great, though VB.NET 1.x doesn't support it hence I ignored it for the current version. So I went for option 3.

Now, VB.NET gets operator overloading in .NET 2.0, so I thought, lets look at it again. What I definitely will keep are my predicate objects: for compatibility and also because the whole framework is build on top of them and they work fairly straightforward internally (no string mangling voodoo, everything is easy to understand).

The first thing I had to solve was: How to specify a field? Now, Ive been working on LLBLGen Pro for 2.5 years now and I never had this idea before, but suddenly it hit me: why not add a static property to each entity class for each field ?

like: EntityField2 field = CustomerEntity._CustomerID;

No more: EntityField2 field = EntityFieldCreator.Create(EntityFieldIndex.CustomerID);

For testing I added 2 operator overloads to the current code's EntityField2 objects: the operator == and the operator !=. I defined them as follows:

public static IPredicate operator==(EntityField2 field, object value)
{
    if(value==null)
    {
        return new FieldCompareNullPredicate(field, null);
    }
    else
    {
        return new FieldCompareValuePredicate(field, null, ComparisonOperator.Equal, value);
    }
}

public static IPredicate operator!=(EntityField2 field, object value)
{
// didn't implement a null compare predicate here, but could be done)
    return new FieldCompareValuePredicate(field, null, ComparisonOperator.NotEqual, value);
}

and then, after I added for testing the _CustomerID property to my CustomerEntity class, I could do:

[Test]
public void OperatorOverloadingTest()
{
    IPredicate filter = (CustomerEntity._CustomerIdField == 10);
    IPredicate filter2 = (CustomerEntity._CustomerIdField == null);
//...
}

filter will become a FieldCompareValuePredicate. filter2 will become a FieldCompareNullPredicate.

This is typesave, and reads ok. I can also add '&' and '|' operators to predicates and for the predicate expression object. .

Of course this still has problems, for now it looks pretty OK.

Any pro/con arguments? Please leave them below, I'm very eager to hear what you all have to say about this

To see it with predicates:

PredicateExpression filter3 =   (
                                    (CustomerEntity._CustomerIdField == 10) & 
                                    (CustomerEntity._CustomerIdField == 12) & 
                                    (CustomerEntity._CustomerIdField == 13)
                                ) | 
                                (
                                    (CustomerEntity._CustomerIdField == null) &
                                    (CustomerEntity._CustomerIdField == null) &
                                    (CustomerEntity._CustomerIdField == null)
                                );

It will resolve in 2 predicate expressions being or-ed. So the () evaluate automatically

I think I like it a lot. And I really understand now why you don't accept a where clause as a string.

What about losing the underscore in favor of some other prefix?

Now why didn't i think of that?

Top Idea!

The number of people on here that must have come up with all sorts of ways for generating predicate filters, i'm surprised nobody has suggested this before.

JimFoye wrote:

I think I like it a lot. And I really understand now why you don't accept a where clause as a string.

What about losing the underscore in favor of some other prefix?

I haven't decided yet which prefix to use, the "_" is not a good candidate I think (for FxCop lovers ), though it has to be something which is definitely never causing a collision with possible existing fields, AND has to be descriptive (and not a lot to type ).

netclectic wrote:

Now why didn't i think of that? Top Idea!

The number of people on here that must have come up with all sorts of ways for generating predicate filters, i'm surprised nobody has suggested this before.

The operators have to be defined on EntityField(2) classes, which are in the runtime lib, so creating these operators is pretty hard. Also the predicate operators have to be defined in the runtime lib.

I considered it before but it doesn't work in VB.NET 1.x (though it will in 2.0 finally), and before creating another way to formulate filters, it should be pretty solid. I now think I've found a pretty solid way to do it .

Also, things like Aliasses on fields for example:

CustomerEntity._CustomerID.SetObjectAlias("C")

and if SetObjectAlias returns an EntityField(2) object, you can concatenate these calls into one statement. Which look more logical than specifying an alias 'somewhere'.

Too bad you can't define NEW operators in .NET, so relation JoinHint relation JoinHint relation etc. isn't possible, but what is possible is a very good start.

I must say, the static EntityField object producing properties occured to me yesterday afternoon, before I didn't see that either.. funny how people seem to miss the most simple things sometimes..

Would it make sense to create a nested type inside the entity?

For example:

CustomerEntity.ColumnNames.FirstName

That way there would be no prefix issues. It would be a bit more verbose. Another option would be to allow developers to specify their own prefix.

All in all, I think what youre proposing is a very simple solution that will work well no matter how its implemented.

Definitely avoid the strings. The argument I always hear from people, particularly LAN administrators, against using a tool like LLBLGen is SQL injection. The string idea opens that possibility up to lazy programmers.

swallace wrote:

Definitely avoid the strings. The argument I always hear from people, particularly LAN administrators, against using a tool like LLBLGen is SQL injection. The string idea opens that possibility up to lazy programmers.

No, that's not the case. Well, if you parse the string of course and not mangle it. Some O/R mappers simply 'transform' the string into SQL (let's not mention names here ) which, with inproper code, could lead to injection attacks, perhaps.

Others actually parse the string into elements and then use those elements to produce the SQL query again, but not re-use the elements.

Devildog74 wrote:

Would it make sense to create a nested type inside the entity?

For example:

CustomerEntity.ColumnNames.FirstName

That way there would be no prefix issues. It would be a bit more verbose. Another option would be to allow developers to specify their own prefix.

Thought of that as well, the nested type, but I think it's too verbose. This will be introduced to cut verbosity so I think introducing verbosity with a solution to verbosity isn't the way to go .

I like the option idea though, thanks!

All in all, I think what youre proposing is a very simple solution that will work well no matter how its implemented.

After seeing it in action, I also think it's a huge step forwards without compromising to a string-based query mechanism.

Regarding the prefix issue, I like devildog's idea of a nested type within the entity. It may be a littlebit verbose, but sowhat, it makes it extremely clear.

I also like the direction your going with the operator overload constructs. It's more sql like.

I'm a noob here, so take it with a grain of salt *)

Eric

erichar11 wrote:

Regarding the prefix issue, I like devildog's idea of a nested type within the entity. It may be a littlebit verbose, but sowhat, it makes it extremely clear.

True, I'll think about it.

I, too, vote for the more verbose option of grouping them under a "CoumnNames" property.

Otis wrote:

Devildog74 wrote:

Would it make sense to create a nested type inside the entity?

For example:

CustomerEntity.ColumnNames.FirstName

That way there would be no prefix issues. It would be a bit more verbose. Another option would be to allow developers to specify their own prefix.

Thought of that as well, the nested type, but I think it's too verbose.

The templates we use here have "evolved" to something similar, and I feel they are very effect (Intellisense removes any concern about the nested types being verbose).

We’ve basically merged the helper, factory, and validation classes into the Entity classes. So there is only the “Entity” Classes and “Manager” Classes.

Sample Fetch:

public static User Login(string emailAddress, string password)
{
                bucket = CreateBucket(User.Factory.Fields.EmailAddress,emailAddress);
    bucket = AddFilter(bucket,User.Factory.Fields.Password,password);

    return Fetch(bucket);
}

Sample collection:

public static EntityCollection SelectByJob(int jobId)
{   
    EntityCollection _ec = new EntityCollection(new JobLineHeader.Factory());
            
    bucket = CreateBucket(JobLineHeader.Factory.Fields.JobId,jobId);
    bucket = AddFilter(bucket, JobLineHeader.Factory.Fields.IsDeleted,false);
    sort = SortASC(JobLineHeader.Factory.Fields.LineNumber);

    return FetchCollection(_ec, bucket, sort);
}

Emmet wrote:

Otis wrote:

Devildog74 wrote:

Would it make sense to create a nested type inside the entity?

For example:

CustomerEntity.ColumnNames.FirstName

That way there would be no prefix issues. It would be a bit more verbose. Another option would be to allow developers to specify their own prefix.

Thought of that as well, the nested type, but I think it's too verbose.

The templates we use here have "evolved" to something similar, and I feel they are very effect (Intellisense removes any concern about the nested types being verbose).

We’ve basically merged the helper, factory, and validation classes into the Entity classes. So there is only the “Entity” Classes and “Manager” Classes.

Interesting!

Sample Fetch:

public static User Login(string emailAddress, string password)
{
                bucket = CreateBucket(User.Factory.Fields.EmailAddress,emailAddress);
    bucket = AddFilter(bucket,User.Factory.Fields.Password,password);

    return Fetch(bucket);
}

Personally I find this too verbose, no offence. The main thing is that with the more complex filters, like fieldcomparesetpredicates, the elements used should be compact as possible, to keep the amount of text as low as possible. With longer names it gets problematic.

I'll come up with something and you can shoot holes in it. The feedback given here is good, gave me some interesting viewpoints I didn't think of. Thanks for that!

My first thought about this was "why start creating ANOTHER way to define filters when the predicate system works, and people are used to it". But then I changed my mind quickly.

When I think back, one of the biggest problems I had selling LLBLGen to my colleages (with varying .NET experience) was that they were comfortable with SQL, but did not take to the predicate concept easily, they found it difficult, especially the lesser .NET experienced ones.

What you are proposing looks much simpler and cleaner. Less scary to newbies to .NET and to LLBLgen - all good

I have to admit I use the "_" prefix myself so i like it, but I perhaps shouldn't admit to that!!!

The only thing I would say is that if the new method for filtering does not offer the same level of flexibility, then you should perhaps be careful. For us in this forum, we'd be happy to use operator overloading to define the filter, and then throw in some predicate code to add more advanced stuff to the filter expression. But for less profficient developers, they might still have to learn the predicate syntax, which is what put a few developers off where I am..

MattWoberts wrote:

My first thought about this was "why start creating ANOTHER way to define filters when the predicate system works, and people are used to it". But then I changed my mind quickly.

When I think back, one of the biggest problems I had selling LLBLGen to my colleages (with varying .NET experience) was that they were comfortable with SQL, but did not take to the predicate concept easily, they found it difficult, especially the lesser .NET experienced ones.

What you are proposing looks much simpler and cleaner. Less scary to newbies to .NET and to LLBLgen - all good

Well, what most people understand after 5 minutes is 'what's a predicate'. The thing that's odd is that the order in which you want to define them isn't the order in which you write them in the code for example, or better: the code looks different as if it's in a different order.

A string based language would be best to keep close to SQL, though the loss of compile time checking was too big of a drawback.

I have to admit I use the "_" prefix myself so i like it, but I perhaps shouldn't admit to that!!!

haha

The reason I added the '' is that it assures there's no field conflicting with it now or in the future. Having an object as a property, like FF or FieldsFactory, is also good, it just adds more typing, and I want to bring down the typing, as it makes the query code more compact. But I've to check what's best. Having the '' in front of the fields, makes them appear together in the intellisense list, and at the top if I'm not mistaken, which is also not what you want perhaps.

The only thing I would say is that if the new method for filtering does not offer the same level of flexibility, then you should perhaps be careful. For us in this forum, we'd be happy to use operator overloading to define the filter, and then throw in some predicate code to add more advanced stuff to the filter expression. But for less profficient developers, they might still have to learn the predicate syntax, which is what put a few developers off where I am..

It does offer the same level of flexibility, it's just another way of writing it down, that's all .

So instead of saying:

(CustomerEntity._CustomerIdField == 10) & (CustomerEntity._CustomerIdField == 12)

I can also say:

(CustomerEntity._CustomerIdField == 10) & PredicateFactory.CompareValue(....)

or

(CustomerEntity._CustomerIdField == 10) & new FieldCompareValuePredicate(....)

That already makes it worthwhile I think as the order of the operands and the operator is the same as you would write it down in SQL. Completely different from the way it is done now: first define the predicate, then add it to the collection with AddWithAnd/Or ... which switches the order of operand operator operand completely.

Well anything that makes the filter definition quicker and easier is def. good in my books... It'll be good to see this in action

I think I'll be happy with either "prefixing" or using an extra type to get to the column name - I'll leave that to you

Hi Newbie on board, didn't realise this forum was here

I implemented a similar system a little while back (cos I can't stand the predicate syntax, or verbosity. Sorry).

It actually sits on top of the existing system, by wrapping up PredicateExpression, Predicate, SortClause, SortExpression and the field type. These wrapper classes then overload the operators to perform exactly what was demo'd above:

IPredicateExpression filter = (ServicePriceFields.FkCompany == person.FkEmplCompany) & (ServicePriceFields.PkServicePrice != 23);

I can also do this with my sort expressions:

Sort by Name field:

ISortExpression sort = PersonFields.Name;

Sort by Name field descending:

ISortExpression sort = PersonFields.Name | SortOperator.Descending;

Sort by Name field descending and surname field ascending:

ISortExpression sort = (PersonFields.Name | SortOperator.Descending) + PersonFields.Surname;

Out of interest how long until the new system is available? If it's going to take a while and there's enough interest, I'll make my source-code and util app available to the group.

Paul

louthy wrote:

Hi Newbie on board, didn't realise this forum was here

I implemented a similar system a little while back (cos I can't stand the predicate syntax, or verbosity. Sorry).

The predicate syntax is indeed a little verbose

It actually sits on top of the existing system, by wrapping up PredicateExpression, Predicate, SortClause, SortExpression and the field type. These wrapper classes then overload the operators to perform exactly what was demo'd above:

IPredicateExpression filter = (ServicePriceFields.FkCompany == person.FkEmplCompany) & (ServicePriceFields.PkServicePrice != 23);

I can also do this with my sort expressions:

Sort by Name field:

ISortExpression sort = PersonFields.Name;

Sort by Name field descending:

ISortExpression sort = PersonFields.Name | SortOperator.Descending;

Sort by Name field descending and surname field ascending:

ISortExpression sort = (PersonFields.Name | SortOperator.Descending) + PersonFields.Surname;

Out of interest how long until the new system is available? If it's going to take a while and there's enough interest, I'll make my source-code and util app available to the group.

Paul

Excellent work, Paul! The sort operator idea is great!

I hope to get everything done this month and release the beta, so it will take at most a month or so before everyone can use / try it out (except the VB.NET users of course, who have to wait till .NET 2.0)

I also like the idea of the fields class. It would avoid having the fields inside the entity classes, which could break existing apps due to name conflicts with field names of the entity itself.

Otis wrote:

The predicate syntax is indeed a little verbose

Normally I'm fine with verbose code, I think the main reason why I find it a little odd is because of use of the word 'predicate'. I think because I don't really use it often when talking about queries or anything db related, I think it causes some kind of mental block. I can't really explain why, but I think if you'd have called them filters instead it wouldn't be so bad. ie. FilterExpression, etc.

However using the built in expression evaluator of C#/VB.NET has to be the way forward. What are your plans for the more complex predicate types? I have used the % operator for 'FieldLikePredicate', and implemented all of the 'FieldCompareValuePredicate' variants. But left the rest, they can obviously be created using the old system, but I wondered whether you'd got plans to use operators for any of those?

Otis wrote:

I also like the idea of the fields class. It would avoid having the fields inside the entity classes, which could break existing apps due to name conflicts with field names of the entity itself.

Yeah it works well, I have a util app that goes through the ConstantsEnums.cs and picks out the fields, and the class they belong to. It then spits out a new cs file with all the wrapped fields, eg.

namespace ORHelper
{
    public class AccountFields
    {
        public static FieldWrapper PkAccount { get { return new FieldWrapper(AccountFieldIndex.PkAccount); } }
        public static FieldWrapper FkAccount { get { return new FieldWrapper(AccountFieldIndex.FkAccount); } }
        public static FieldWrapper FkAccountType { get { return new FieldWrapper(AccountFieldIndex.FkAccountType); } }
    }
    public class AppointmentDocumentFields
    {
        public static FieldWrapper PkAppointmentDocument { get { return new FieldWrapper(AppointmentDocumentFieldIndex.PkAppointmentDocument); } }
        public static FieldWrapper FkDocument { get { return new FieldWrapper(AppointmentDocumentFieldIndex.FkDocument); } }
        public static FieldWrapper FkAppointment { get { return new FieldWrapper(AppointmentDocumentFieldIndex.FkAppointment); } }
    }
    ...
    ..
    .
}

I tried it without the Fields suffix, but as you mentioned it causes conflicts where the class name and the field name are the same.

I notice there's a 'plugins' menu with LLBLGenPro, do you think it would make sense for me to implement my util app as a plugin? What would be involved?

louthy wrote:

Otis wrote:

The predicate syntax is indeed a little verbose

Normally I'm fine with verbose code, I think the main reason why I find it a little odd is because of use of the word 'predicate'. I think because I don't really use it often when talking about queries or anything db related, I think it causes some kind of mental block. I can't really explain why, but I think if you'd have called them filters instead it wouldn't be so bad. ie. FilterExpression, etc.

True, though to please the purists as well, I decided to go for predicate, as officially a boolean clause in a where clause is called 'predicate' I found out, so I thought 'predicate' would be a good name. But filter would also have been a very good name indeed

However using the built in expression evaluator of C#/VB.NET has to be the way forward. What are your plans for the more complex predicate types? I have used the % operator for 'FieldLikePredicate', and implemented all of the 'FieldCompareValuePredicate' variants. But left the rest, they can obviously be created using the old system, but I wondered whether you'd got plans to use operators for any of those?

% is a good Like candicate indeed. The sad thing is that you can't define NEW operators, so when the set of operators runs out (or aren't usable, e.g.: look odd too), it's back to normal. What I have planned is to add utility methods like entityfield.SetObjectAlias(name) which then returns the field again, so you can chain the calls together in one line or in one construct, instead of having to put the object into a variable, use the variable to do things etc.

My first goal is to have a natural way of constructing filters, as in: the order in which the predicates are mentioned is also the order in which you want to have them in the query, with operators in between them, if possible.

Otis wrote:

I also like the idea of the fields class. It would avoid having the fields inside the entity classes, which could break existing apps due to name conflicts with field names of the entity itself.

Yeah it works well, I have a util app that goes through the ConstantsEnums.cs and picks out the fields, and the class they belong to. It then spits out a new cs file with all the wrapped fields, eg.

namespace ORHelper
{
    public class AccountFields
    {
        public static FieldWrapper PkAccount { get { return new FieldWrapper(AccountFieldIndex.PkAccount); } }
        public static FieldWrapper FkAccount { get { return new FieldWrapper(AccountFieldIndex.FkAccount); } }
        public static FieldWrapper FkAccountType { get { return new FieldWrapper(AccountFieldIndex.FkAccountType); } }
    }
    public class AppointmentDocumentFields
    {
        public static FieldWrapper PkAppointmentDocument { get { return new FieldWrapper(AppointmentDocumentFieldIndex.PkAppointmentDocument); } }
        public static FieldWrapper FkDocument { get { return new FieldWrapper(AppointmentDocumentFieldIndex.FkDocument); } }
        public static FieldWrapper FkAppointment { get { return new FieldWrapper(AppointmentDocumentFieldIndex.FkAppointment); } }
    }
    ...
    ..
    .
}

I tried it without the Fields suffix, but as you mentioned it causes conflicts where the class name and the field name are the same.

Nice little class, easy to generate as well with a simple template

I notice there's a 'plugins' menu with LLBLGenPro, do you think it would make sense for me to implement my util app as a plugin? What would be involved?

No plugins are for designer activities. What you're looking for is a template which is then added to the template set to use and an extra task is then added to your generator config of choice, so the generator generates code for you which makes it possible to use operator overloading.

The best thing to do then is to move generic code into an assembly and 'project/entity specific code', like the snippet above, is kept in generated code, which then references your assembly with generic code

Otis wrote:

True, though to please the purists as well, I decided to go for predicate, as officially a boolean clause in a where clause is called 'predicate' I found out, so I thought 'predicate' would be a good name.

Yeah I understand, that's totally fine Just in my experience I have found that when coders try to be clever and find the ultimate word to describe a concept it often ends up with less readable code, due to the fact that the word isn't in regular use in that context. I saw another example of this last year when one of the programmers in my team used the word 'datum' as part of a class name. eg. TypeDatum.

Datum is the singular of 'data'. Personally I would prefer my coders to use TypeData rather than TypeDatum as I believe it would be easier for other coders to read and understand, and the chances of misinterpreting the role of the class is negligible. Just my two penneths worth

Otis wrote:

% is a good Like candicate indeed.

Yeah I was a little concerned about misusing the operators at first, but when written down in code it just looks right. I also use ReSharper which highlights the use of overloaded operators in an expression, so it's obvious that some extra processing is happening.

Otis wrote:

The sad thing is that you can't define NEW operators, so when the set of operators runs out (or aren't usable, e.g.: look odd too), it's back to normal. What I have planned is to add utility methods like entityfield.SetObjectAlias(name) which then returns the field again, so you can chain the calls together in one line or in one construct, instead of having to put the object into a variable, use the variable to do things etc.

Makes sense

Otis wrote:

Nice little class, easy to generate as well with a simple template

Is this within the realms of a mere mortal like myself? Is there any reference on how to create a template?

Forgot to say btw, great app, it has massively improved the quality of my code and the speed of my development. Nice one

louthy wrote:

Otis wrote:

% is a good Like candicate indeed.

Yeah I was a little concerned about misusing the operators at first, but when written down in code it just looks right. I also use ReSharper which highlights the use of overloaded operators in an expression, so it's obvious that some extra processing is happening.

Oh, that's a nice feature! I didn't know resharper did that (I don't use resharper, it gets in teh way sometimes and I don't want anything to get in my way when writing code).

'%' is about the only operator for Like, though I also like the way the sortoperator was concatenated to the field in your example. It could be used to glue some operator enum to the field as well. Will look into that

Otis wrote:

Nice little class, easy to generate as well with a simple template

Is this within the realms of a mere mortal like myself? Is there any reference on how to create a template?

Yes it's in the SDK, complete with our fancy template IDE template studio Please go to the Extras section in the customer area and download the SDK + template studio and you'll have your templates running in no time

Forgot to say btw, great app, it has massively improved the quality of my code and the speed of my development. Nice one

THanks!

louthy wrote:

Out of interest how long until the new system is available? If it's going to take a while and there's enough interest, I'll make my source-code and util app available to the group.

Paul

I'm definatly interested in seeing your way of doing things. I've never really considered operator overloading and have been writting the predicate statements by hand and just simplify my bl methods for easy of use. If I could making writting those easier too, that would be awesome

I'm not sure if you ever looked at a product (now defunct) that was moved to SourceForge called ORM.Net.

I didn't like some of the stuff they did. For example, there entities are just wrappers around datasets.

But for fetch path and predicates (they called it criteria) they used enumerations. So, there was a join path enumeration that you used to specify fields.

So you could do:

dm.QueryCriteria.And(JoinPath.Student.Columns.FirstName, "Tom", MatchType.Exact)

dm is their datamanger, similar to NHibernate session.

Or:

dm.QueryCriteria.And(JoinPath.Student.Enrollment.Schedule.Course.Columns.ClassName,"Algorithms 100",MatchType.Exact)

You could even string them together with Or / And, whatever:

dm.QueryCriteria.And(JoinPath.Student.Columns.FirstName, "Bill", MatchType.Exact).Or(JoinPath.Student.Columns.LastName, "Clinton", MatchType.Exact)

As you can see, there was an overload with a third parameter for MatchType.

The also had a single FetchPath enumeration that also drilled through all the relations to define your FetchPaths:

student = dm.GetStudent(FetchPath.Student.Enrollment.Schedule.All);

I really liked their method, it was easy to understand and figure out. At least to me. I also think the code read very closely to the way the SQL might read.

I think the basic Idea here is that there were to Enumerations, FetchPath and JoinPath that let you define the Entity and Column (field) very simply.

You can check out ORM.Net on source forge at: http://sourceforge.net/projects/orm-net

Comments?

BOb