I originally posted here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7914

But that was not as well-formed a question because I had just started playing around with the LLBLGen generated code (we are using the Adapter paradigm, with the latest version of LLBLGen and using .NET 2.0, C#).

Basically, we are in need of giving our users the ability to create custom queries in an ASP.NET application. I looked at the thread that is available here:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=332

But I am not sure if this is completely relevant to what I am doing (especially since that was posted 2.5 years ago) because I need to not only give the user the ability to construct a potentially complex query, but also need to persist it so that it can be ran by selecting the filter from a dropdownlist of filters, as well as give them the ability to edit aspects of a given filter.

I see there is a ToQueryText method on the PredicateExpression but I am not yet sure if that is relevant either, since I am literally a complete newbie to LLBLGen and the generated API. I am not sure if I want to store my filters as a string of a partial WHERE clause or if I want to break it up into pieces (e.g. Foo == "Bar" being an example of what I mean by a piece). This might help us if small parts of a larger predicate were to 'break' because of changes to the names in the data set, so that we could identify those parts...I don't know. Also, I saw this other post about aliases, and not sure if that is relevant either (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7157).

Anyone doing anything like this or have recommendations? I am groping around in the dark a little bit here....

I like the approach that Otis suggests in the thread you mentioned:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=332

As far as storing the queries for later re-use, you may be able to serialize the objects involved as XML, though that seems messy and I'm not sure I should have said that out loud.

You could also stored the values the user selected in the UI and re-parse them into a query later when they want to re-run the query.

If you're using SQL Server 2005, you may want to check out SQL Reporting Services, which has a nice a nice Ad-Hoc Query engine now as well. It's not as flexible as writing something yourself using LLBLGen, but it may be "good enough" for your situation.

The predicate system is very flexible, it's object based so you can create a set of objects and that's your filter.

The system I wrote a few years back in thread 332, is based on the following idea: you define a system of enums and other elements, which you can easily persist somewhere and which is also easy to use to build the filter on screen. Then, you use these elements to build the predicates to form the query at runtime.

For example: you already have available to you an enum for all entities (EntityType) and for each entity an enum with all the fields. You can define an enum for filter operators like the ComparisonOperator and SetOperator. You can also use the ExOp operator enum to define expressions.

What's cumbersome is the way you want the user to express the filter on screen. With web, you have a severe disadvantage, because you can't dynamically on the client add controls to the form (well, not easily ) like I did in the example of thread 332.

I wrote more of these systems like in thread 332 in the past and most of the time, users found them too complicated. Most of them just wanted to select fields from a list, then pick the operator etc. like a wizard thing. A wizard approach is slower but can be easier to make with webpages, and with the enums available it's a walk in the park

the enums can then be used to create fields and entity instances if you want, because you have factories available to you for that in the generated code which accept enums

Frans,

Thanks for taking the time to respond. I think that your answer, combined with ideas that have come to me in the last 2 days, will yield a decent solution. The UI piece needs to (potentially) be useful in solutions that may not use LLBLGen as the DAL, so I am going to try to allow for that in my design - I think I will end up creating some sort of XML (or something else easily-parsed) representation of the built query to store it, and then have code that translates that to the LLBLGen predicate stuff (other apps not using LLBLGen would, of course, need to translate it to whatever it needed).

One of the things I am asking stakeholders about is just how complex the queries will need to be, because I agree that users tend to have a problem understanding complex predicates in general and more importantly, rarely need to build such. Perhaps I will be able to create a query builder simple enough for general use and yet provide functionality enough for the few 'power users' that may use the system.

As for making it dynamic, I am fortunate to have enough DHTML experience that implementation will not be a problem - the hard part is making an easy-to-understand (read:intuitive) interface for them!

Thanks again!

Chester wrote:

As far as storing the queries for later re-use, you may be able to serialize the objects involved as XML, though that seems messy and I'm not sure I should have said that out loud.

Yeah, you should be ashamed for having said that. Well, while I am not going to serialize to XML, I may end up using it to store what the user creates, just because it may prove to be decent enough to do what I want it to. I was actually thinking of using JSON, since the UI will be built with DHTML, but I don't know how acceptable that will be, since there are many people that don't feel JSON is an acceptable format (because they only think of JSON as something you use in a web interface).

Chester wrote:

If you're using SQL Server 2005, you may want to check out SQL Reporting Services, which has a nice a nice Ad-Hoc Query engine now as well. It's not as flexible as writing something yourself using LLBLGen, but it may be "good enough" for your situation.

Yeah, I know - but this is mainly for filtering grids (bound to potentially-large sets of data) and that would be more than they want to deal with, I believe.

Thanks for your response.

Interesting idea to persist the queries as JSON. If it's a web app, maybe it's not such a bad idea.

Okay, I am building this filtering tool for the end users and the only question I can see getting in my way right now is illustrated in the following example of code I have (contrived, obviously, for this question):

   string entityType = "AccountEntity";
   string fieldName = "AccountName";
   FieldCompareValuePredicate predicate = new FieldCompareValuePredicate();
   predicate.Field = GetField(entityType, fieldName);

Given the above code, what would the code for GetField(string, string) look like? I am using the adapter model of generated code and am having a bit of a hard time figuring out how I would translate the strings (which ultimately will come from an abstraction of the web UI tool) to the things needed by your predicate system.

I see a static property, AccountFields.AccountName which returns an instance of an EntityField2, and when I look at the implementation I see that there is a call to the method EntityFieldFactory.Create() which takes as a parameter another enum AccountFieldIndex (which I know I could get using my string value) but I am still not sure how I could use my string entityType to get the type and then start in on that other stuff. I think my brain is melting from all of this new stuff....

I don't need someone to actually write that method for me, I simply need pointers in the right direction as far as the LLBLGen API (remember to be easy on me, I am completely new to this tool!).

Thanks.

Update to my post above - I have written GetField() as follows, after finding various things in the generated code:

        private static IEntityField2 GetField(string entityType, string fieldName)
        {
            IFieldInfoProvider provider = FieldInfoProviderSingleton.GetInstance();
            IEntityField2 fieldToReturn = new EntityField2(provider.GetFieldInfo(entityType, fieldName));
            return fieldToReturn;
        }

I don't know if this is a good way to do this or not, but regardless, the problem is that my code is in a different layer and project in the app, and the FieldInfoProviderSingleton.GetInstance() call doesn't work because FieldInfoProviderSingleton is declared as internal. I don't know if that is a big deal, and I can move things, but I just don't know if I am going to screw something up on account of my being so new with this thing.

Also, I am not sure how to construct a FieldCompareValuePredicate with it anyway, since the predicate.Field wants an instance of IEntityField and not IEntityField2 . . . ARGH. I thought I was making good progress on this but I am getting a little bit frustrated now...Maybe I need to take a break.

Please set me straight, I think I am lost in the API.

[UPDATE: Oh, predicate.Field is read-only - am I missing something here? How, other than using the constructor (not that I am opposed), do you set the field?]

instantiate the FieldInfoProvider from PersistenceInfoProviderBase.

IEntityField is used in SelfServicing while IEntityField2 is used in the Adapter model. Look for another overload which accepts IEntityField2.

Also, if you need a field instance, you can use the field factory for the entity, or use reflection and create an instance of the field using the _entityname_Fields class. Then pass that instance to the FieldCompareValuePredicate constructor.

And is there anything wrong with changing the DALDBSpecific.PersistenceInfoProviderCore class from internal to public?

As well, should I use the PersistenceInfoProviderSingleton to get an instance, and can I change that from internal to public?

I need access to it in a different assembly from the one it is declared in.

Other than that, I got things working - I just need to know if I am doing anything that may cause problems. I doubt changing something from internal to public is going to cause any problems, but I thought I would ask regardless. I suppose I could create some wrapper to get around it, but that seems a bit contrived....

It's more of a design thing: things that shouldn't be used outside the code, should be internal. THat's the reasoning behind the internal declaration.

So I wonder why you need the public instance instead of using the factories for field creation. The thing is that creating fields using the factories is enough to get things working: the persistence info is added for you later on in adapter, so no worries there

Otis wrote:

It's more of a design thing: things that shouldn't be used outside the code, should be internal. THat's the reasoning behind the internal declaration.

So I wonder why you need the public instance instead of using the factories for field creation. The thing is that creating fields using the factories is enough to get things working: the persistence info is added for you later on in adapter, so no worries there

Well, I feel fairly lost in this API - I am in a unique position in that:

• I have never worked with LLBLGen before, and there is a lot to learn, and
• The first exposure I have to it is in building this query translation layer (going from our simple filterpredicate system to LLBLGen's more complex one (for the Adapter model). So, I am literally using Reflector and your documentation and whatever I can find looking through this forum to try to get this piece working. Anyway, I will post what I have written here so that you can correct my errant ways. All of the code has been stripped down to just the basics for this discussion and is not necessarily representative of what I am ultimately going to do.

This is our simple set of code for filter predicates - it is meant to be simplistic and not as rich as the LLBLGen predicate system:

    public enum FilterOperator
    {
        Equal = 0,
        LessThanOrEqual = 1,
        LessThan = 2,
        GreaterThanOrEqual = 3,
        GreaterThan = 4,
        NotEqual = 5,
        Like = 6,
        Between = 7
    }

    public class FilterPredicate
    {
        private string m_EntityType;
        private string m_FieldName;
        private FilterOperator m_FilterOperator;
        private string m_FirstValue;
        private string m_SecondValue;

        public string EntityType
        {
            get { return m_EntityType; }
            set { m_EntityType = value; }
        }

        public string FieldName
        {
            get { return m_FieldName; }
            set { m_FieldName = value; }
        }

        public FilterOperator FilterOperator
        {
            get { return m_FilterOperator; }
            set { m_FilterOperator = value; }
        }

        public string FirstValue
        {
            get { return m_FirstValue; }
            set { m_FirstValue = value; }
        }

        public string SecondValue
        {
            get { return m_SecondValue; }
            set { m_SecondValue = value; }
        }

        public FilterPredicate(string entityType, string fieldName, string fieldDataType, FilterOperator filterOperator, string value)
        {
            m_EntityType = entityType;
            m_FieldName = fieldName;
            m_FilterOperator = filterOperator;
            m_FirstValue = value;
        }

        public FilterPredicate(string entityType, string fieldName, string fieldDataType, FilterOperator filterOperator, string firstValue, string secondValue)
        {
            m_EntityType = entityType;
            m_FieldName = fieldName;
            m_FilterOperator = filterOperator;
            m_FirstValue = firstValue;
            m_SecondValue = secondValue;
        }
   }

This is the piece that translates our filter predicate stuff to the LLBLGen stuff - this is the code in question, as far as how I did it (by changing the visibility of PersistenceInfoProviderSingleton and FieldInfoProviderSingleton from internal to public, so I could use them in the assembly containing this code):

    public class FilterPredicateTranslator
    {
        public static IPredicate ConvertToPredicate()
        {
            // This is obviously just for the sake of example
            FilterPredicate myFilterObject = new FilterPredicate("AccountEntity", "AccountName", "string", FilterOperator.Equal, "Foobar");


            IPersistenceInfoProvider provider = Steton.Suite.DAL.DatabaseSpecific.PersistenceInfoProviderSingleton.GetInstance();
            IFieldPersistenceInfo persistenceInfo = provider.GetFieldPersistenceInfo(myFilterObject.EntityType, myFilterObject.FieldName);
            IEntityFieldCore fieldCore = GetField(myFilterObject.EntityType, myFilterObject.FieldName);
            IPredicate predicate = null;
            switch (myFilterObject.FilterOperator)
            {
                case FilterOperator.Between:
                    predicate = new FieldBetweenPredicate(fieldCore, persistenceInfo, myFilterObject.FirstValue, myFilterObject.SecondValue);
                    break;
                case FilterOperator.Like:
                    predicate = new FieldLikePredicate(fieldCore, persistenceInfo, myFilterObject.FirstValue);
                    break;
                case FilterOperator.Equal:
                case FilterOperator.NotEqual:
                case FilterOperator.GreaterThan:
                case FilterOperator.LessThan:
                case FilterOperator.GreaterThanOrEqual:
                case FilterOperator.LessThanOrEqual:
                    predicate = new FieldCompareValuePredicate(fieldCore, persistenceInfo, GetComparisonOperator(myFilterObject.FilterOperator), myFilterObject.FirstValue);
                    break;
            }
            return predicate;
        }

        private static IEntityField2 GetField(string entityType, string fieldName)
        {
            IFieldInfoProvider provider = FieldInfoProviderSingleton.GetInstance();
            IEntityField2 fieldToReturn = new EntityField2(provider.GetFieldInfo(entityType, fieldName));
            return fieldToReturn;
        }

        private static ComparisonOperator GetComparisonOperator(FilterOperator filterOperator)
        {
            return (ComparisonOperator)Enum.Parse(typeof(ComparisonOperator), filterOperator.ToString(), true);
        }
    }

Can I do this more easily without needing to change the visibility of those classes?

Thanks!

JasonBunting wrote:

Otis wrote:

It's more of a design thing: things that shouldn't be used outside the code, should be internal. THat's the reasoning behind the internal declaration.

So I wonder why you need the public instance instead of using the factories for field creation. The thing is that creating fields using the factories is enough to get things working: the persistence info is added for you later on in adapter, so no worries there

Well, I feel fairly lost in this API - I am in a unique position in that:

I can imagine you feel a bit lost, as the API is big, you're new to the API and your task isn't straight forward, so the challenges you're facing are a bit big.

• I have never worked with LLBLGen before, and there is a lot to learn, and
• The first exposure I have to it is in building this query translation layer (going from our simple filterpredicate system to LLBLGen's more complex one (for the Adapter model). So, I am literally using Reflector and your documentation and whatever I can find looking through this forum to try to get this piece working.

I think it's better to use the reference manual and the sourcecode. You don't need reflector as the runtime lib sourcecode is on your harddisk, in the llblgen pro installation folder

Anyway, I will post what I have written here so that you can correct my errant ways. All of the code has been stripped down to just the basics for this discussion and is not necessarily representative of what I am ultimately going to do.

This is our simple set of code for filter predicates - it is meant to be simplistic and not as rich as the LLBLGen predicate system:

    public class FilterPredicate
    {
        private string m_EntityType;
        private string m_FieldName;
        private FilterOperator m_FilterOperator;
        private string m_FirstValue;
        private string m_SecondValue;

Why not use int for the EntityType ? You can then cast it to the EntityType enum and use the value with the GeneralEntityFactory to produce an entity instance . The thing is: with an entity instance you can grab fields IEntity2 dummy = GeneralEntityFactory.Create((EntityType)_entityType); IEntityField2 field = dummy.Fields[_fieldName];

et voila, you've your field.

You can use EntityType's values to fill a combo box to select the entity to filter/select (dunno if you're doing this already)

This is the piece that translates our filter predicate stuff to the LLBLGen stuff - this is the code in question, as far as how I did it (by changing the visibility of PersistenceInfoProviderSingleton and FieldInfoProviderSingleton from internal to public, so I could use them in the assembly containing this code):

The persistence info doesn't need to be set. You can pass in null there. This is the disconnected nature of adapter: you can specify filters without knowing the target db, so you can fabricate predicateexpressions in layer 1, send them to layer 2 which selects the adapter to execute a method on passing the filters and the DataAccessAdapter class then takes care of inserting persistence info. That's not something you've to worry about