I wish if there is a plan to generate complex queries using a Sql Server like Query Editor GUI.

It will really make our lives easy. I work for a company where any minor modification to views or sps requires a long list of justifications, not to mention that typed list requires tables to be realated and the ERP system I'm getting the data from is not.

Is it hard to do an extension like that?.

dpanet wrote:

I wish if there is a plan to generate complex queries using a Sql Server like Query Editor GUI.

It will really make our lives easy. I work for a company where any minor modification to views or sps requires a long list of justifications, not to mention that typed list requires tables to be realated and the ERP system I'm getting the data from is not.

Is it hard to do an extension like that?.

That would be awesome

So you could generate design prefetch paths and relation predicate buckets, maybe just into a window so you could copy-paste them into your own code...

dpanet wrote:

I wish if there is a plan to generate complex queries using a Sql Server like Query Editor GUI.

It will really make our lives easy. I work for a company where any minor modification to views or sps requires a long list of justifications, not to mention that typed list requires tables to be realated and the ERP system I'm getting the data from is not.

Is it hard to do an extension like that?.

You can create relations between entities in the designer, so you can create typedlists without having database relations.

The tool you're proposing, how should that tool work: you type in a piece of code and it should run and return results? If so, why not typing the code into your own code as well?

SQL Server's Query Editor's way of letting you drag and drop tables, set up their joins, and set the filters seems like it would be more intuitive for non-veteran llblgen users. We have GUI designers who know the data they want but need lots of help setting up the prefechpaths and relationpredicatebuckets...a visual tool would really come in handy I think.

In addition, being able to design the query and hit a "Run" button to see the results would be really useful.

mikeg22 wrote:

SQL Server's Query Editor's way of letting you drag and drop tables, set up their joins, and set the filters seems like it would be more intuitive for non-veteran llblgen users. We have GUI designers who know the data they want but need lots of help setting up the prefechpaths and relationpredicatebuckets...a visual tool would really come in handy I think.

Could you elaborate a bit on: the UI designer should be able to write code but has a hard time, though finds it easy to write the other code which uses the filters ?

In addition, being able to design the query and hit a "Run" button to see the results would be really useful.

To test things out you mean?

Your point about non-programmers who have to setup filters is interesting, though I then wonder why are these people have the need to use code anyway? I think I miss something here, so if you could explain a bit more about the context it would be great

Otis wrote:

mikeg22 wrote:

SQL Server's Query Editor's way of letting you drag and drop tables, set up their joins, and set the filters seems like it would be more intuitive for non-veteran llblgen users. We have GUI designers who know the data they want but need lots of help setting up the prefechpaths and relationpredicatebuckets...a visual tool would really come in handy I think.

Could you elaborate a bit on: the UI designer should be able to write code but has a hard time, though finds it easy to write the other code which uses the filters ?

In addition, being able to design the query and hit a "Run" button to see the results would be really useful.

To test things out you mean?

Your point about non-programmers who have to setup filters is interesting, though I then wonder why are these people have the need to use code anyway? I think I miss something here, so if you could explain a bit more about the context it would be great

Well, at least in my part of our company, pretty much every developer can create complex queries using SQL Server's Query editor (the one where you can right-click, add table, go down to the grid, set up filters on columns, etc...). These same developers often work on forms or reports where they know exactly what data they need, but are a bit lost in the syntax of LLBLGen prefetchpaths and relationpredicatebuckets. As far as I can tell, setting these up is just understanding the join and where clause translation, so the process of using the Query Editor is very close to the process of setting up the EntityRelations and Predicates. Setting up prefetchpaths is a bit different, but its effectively setting up seperate queries connected by an EntityRelation...right?

Come to think of it, if you could set up a particular prefetchpath that defines a graph using a GUI tool, you could probably strongly type it like EntityRelations and single entity PrefetchPaths...so you could do something like:

Dim daa as new DataAccessAdapter()
Dim employee as new EmployeeEntity("CHOPS")
daa.FetchEntity(employee, EmployeeEntity.GraphTypeManagementChain())

This would be very nice for our UI developers (although myself, I would prefer being able to set these things up outside of the LLBLGen Designer).

And yes, I mean the "Run" button could be used to test the setup to make sure the correct data is coming back instead of having to rely on poking through the entities once they come back in the real code.

I would like to see something like this, mainly for use in Visual Studio.

I would like an easy way to construct queries in VS and see the resulting sql output.

The only way to do it now is to build the queries and then run the code.

The construction of the queries is not as intuitive as using llbgendatasource.

Sorry if I've picked up the wrong end of this thread

mikeg22 wrote:

Well, at least in my part of our company, pretty much every developer can create complex queries using SQL Server's Query editor (the one where you can right-click, add table, go down to the grid, set up filters on columns, etc...). These same developers often work on forms or reports where they know exactly what data they need, but are a bit lost in the syntax of LLBLGen prefetchpaths and relationpredicatebuckets. As far as I can tell, setting these up is just understanding the join and where clause translation, so the process of using the Query Editor is very close to the process of setting up the EntityRelations and Predicates. Setting up prefetchpaths is a bit different, but its effectively setting up seperate queries connected by an EntityRelation...right?

I must say that query editor of SSMS is pretty bad, you still have to formulate the filter yourself by hand in raw sql. Though indeed, setting up the relations and the fields to return, which aggregates to use etc, could help. Though, that's already in the typedlist editor (for read-only data). Do these people use the typedlist editor?

For entity fetches, it's indeed different. Though starting with the entity you want to fetch, walking towards the entity you want to filter on, with intellisense it should work out much faster than a visual tool could do.

That's also what I have against these tools: it takes a truckload of time to create the visual editor, and it will always be mediocre, plus the person operating it never learns that what s/he wants to do is tic-tac-toe in code once you grasp it, but if a visual tool walks you through it in much less productive steps, will they pick it up, that all they need to do is start with the entity to fetch and end with the entity to filter when it comes to relations? (for example)

Though perhaps there's a sweetspot somewhere, we've to look into that. But make no mistake: a visual editor is slower than a texteditor in this case and limiting: it won't be able to offer all possible query possibilities. You also see that in the SSMS query editor: If you need to alias a table, you can only do that in the sql output window. It's not as intuitive as you might think (I find it horrible, but that's me, I think ). So if I was asked to write a visual query editor, I'd never come up with this horrible dialog.

We dropped expression designing in the designer for this reason as well: it took simply too much time to produce an expression visually, you could better type it into code, which was much easier as well.

Come to think of it, if you could set up a particular prefetchpath that defines a graph using a GUI tool, you could probably strongly type it like EntityRelations and single entity PrefetchPaths...so you could do something like:

Dim daa as new DataAccessAdapter()
Dim employee as new EmployeeEntity("CHOPS")
daa.FetchEntity(employee, EmployeeEntity.GraphTypeManagementChain())

This would be very nice for our UI developers (although myself, I would prefer being able to set these things up outside of the LLBLGen Designer).

We are considering this for v2.1, actually. More in the sense of: you define prefetch paths in the designer (which is just a tree you check related entities in to prefetch) and these paths are considered when you fetch the root entity. Example: order and order details. So if you define a prefetch path in the designer for order: Order - Order details, it will fetch order details always when you fetch an order, unless you switch it off.

One big drawback of having things setup in the designer is that if 2 or more team members want to add a prefetch path to the project, they need to have the designer open, which could give concurrency issues with the project file (we will release a merger tool but it's always an action to be done afterwards).

And yes, I mean the "Run" button could be used to test the setup to make sure the correct data is coming back instead of having to rely on poking through the entities once they come back in the real code.

That will only work if the editor is able to fully design expressions as well and what not. I haven't seen a visual SQL editor which can do all that without having to fall back on text, and I therefore have no illusion I have to do that as well. Setting an aggregate function on a field, that's not a problem. Though creating a scalarquery expression for a field in the selectlist, create an expression which involves a couple of fields, functions etc... that's a different ballgame, and often used in reporting for example.

Though what could be done, and which probably would help your graphical oriented programmers already, is a tool in which they could write C# or VB.NET and type in the code they THINK they need, (eventually helped with small wizards) which is then runnable on the live db. Is that what you're saying?

May be the requirement is like what www.genom-e.com offers.

"Tool for testing business layers and mappings: OQL queries can be executed and object models of business layers traversed without writing user interfaces using the OQL Query Analyzer"

regards

Khuzema

Otis wrote:

We are considering this for v2.1, actually. More in the sense of: you define prefetch paths in the designer (which is just a tree you check related entities in to prefetch) and these paths are considered when you fetch the root entity. Example: order and order details. So if you define a prefetch path in the designer for order: Order - Order details, it will fetch order details always when you fetch an order, unless you switch it off.

One big drawback of having things setup in the designer is that if 2 or more team members want to add a prefetch path to the project, they need to have the designer open, which could give concurrency issues with the project file (we will release a merger tool but it's always an action to be done afterwards).

I could see this as somewhat limiting, as right now you have complete control over the graph that gets fetched. Preselecting a graph for each entity would make this a lot more like NHibernate, where you "configure" your entities to be fetched in a certain way, and if you want to fetch different graphs for the same entity, you need to create a separate entity based on the same table in the database. (That's all based on conversations I've had with NHibernate users--I have very little experience with it myself).

Maybe you could have the option to set up multiple prefetch path graphs per entity in the designer, give each one a name, and have each one generated as an enum off the entity itself (OrderEntity.PrefetchEnum.Graph1), which you could select and pass into an overload of the fetch instead of an actual prefetch path? Or the actual prefetch paths would be generated, so that no overloads would have to be added? Maybe this is along the lines of what you had in mind--it sounds like you mean one preconfigured path per entity, which I would see as limiting.

Also, how would filtering and sorting on prefetch nodes other than the root node work?

Thanks,

Phil

Khuzema wrote:

May be the requirement is like what www.genom-e.com offers.

"Tool for testing business layers and mappings: OQL queries can be executed and object models of business layers traversed without writing user interfaces using the OQL Query Analyzer"

Yes something like that, you type in a C#/VB snippet, eventually with some 'prefetch path creators' but that's it.

It can be helpful as it's otherwise for example necessary to build your large app, start a testserver, wait for a few minutes then try... rinse repeat.

though then again, a small unittest which gives you the necessary information will look the same as the snippet you might type in. Genom-e needs this tool also to test mappings. We don't have to, as the mappings are already correct.

psandler wrote:

Otis wrote:

We are considering this for v2.1, actually. More in the sense of: you define prefetch paths in the designer (which is just a tree you check related entities in to prefetch) and these paths are considered when you fetch the root entity. Example: order and order details. So if you define a prefetch path in the designer for order: Order - Order details, it will fetch order details always when you fetch an order, unless you switch it off.

One big drawback of having things setup in the designer is that if 2 or more team members want to add a prefetch path to the project, they need to have the designer open, which could give concurrency issues with the project file (we will release a merger tool but it's always an action to be done afterwards).

I could see this as somewhat limiting, as right now you have complete control over the graph that gets fetched. Preselecting a graph for each entity would make this a lot more like NHibernate, where you "configure" your entities to be fetched in a certain way, and if you want to fetch different graphs for the same entity, you need to create a separate entity based on the same table in the database. (That's all based on conversations I've had with NHibernate users--I have very little experience with it myself).

It's indeed very limiting. It's also not set in stone we'll add this. . First we had the idea of creating entities which were build as combinations of other entities. However, when we were designing it and thought about use-cases, we couldn't come up with a single one.

Though the necessity of 'an orderline is meaningless without an order' has still value, though I think the limitation of 'always have to work with this requirement', is too harsh, so writing that very small piece of code to get the order entity IF you want to is actually a thing you want.

Maybe you could have the option to set up multiple prefetch path graphs per entity in the designer, give each one a name, and have each one generated as an enum off the entity itself (OrderEntity.PrefetchEnum.Graph1), which you could select and pass into an overload of the fetch instead of an actual prefetch path? Or the actual prefetch paths would be generated, so that no overloads would have to be added? Maybe this is along the lines of what you had in mind--it sounds like you mean one preconfigured path per entity, which I would see as limiting.

I agree, it was / is an idea which needs more thought but I indeed think that in the end it's great demo-ware but absolutely useless to the user as the designer stuff is too limiting and writing a couple of lines of code is much more flexible.

Also, how would filtering and sorting on prefetch nodes other than the root node work? Thanks, Phil

That's one of the issues with it indeed.

Though the idea isn't bad, though it's misplaced in the designer. For example, having prefetch paths easily designed in a query test tool, it would be better.

I think that the a query test tool would be very useful for both single entity and collection fetches. For collection fetches, setting up the relation predicate bucket (for example to create a report) can be very tricky. Maybe something like a visual tool that shows the approximate SQL equivalent of the bucket...You know how people are very often asking how to turn some complex query into LLBLGen language in these forums? Half the time it is because the query doesn't need to be as complex as they think, but the other half is because the SQL is easier for them to understand than the combination of relations and predicates. So like I said, maybe something that can visually show the relationship...

As for the predefined prefetch paths, I think it is too limiting to just have one prefetch path defined. Sometimes you need one path, and sometimes you need another path. For an employee, a payroll department may need salary history and an HR department may need a benefits history. These are two branches of the employee that are needed in entirely different situations.

Otis wrote:

psandler wrote:

Maybe you could have the option to set up multiple prefetch path graphs per entity in the designer, give each one a name, and have each one generated as an enum off the entity itself (OrderEntity.PrefetchEnum.Graph1), which you could select and pass into an overload of the fetch instead of an actual prefetch path? Or the actual prefetch paths would be generated, so that no overloads would have to be added? Maybe this is along the lines of what you had in mind--it sounds like you mean one preconfigured path per entity, which I would see as limiting.

I agree, it was / is an idea which needs more thought but I indeed think that in the end it's great demo-ware but absolutely useless to the user as the designer stuff is too limiting and writing a couple of lines of code is much more flexible. [...] Though the idea isn't bad, though it's misplaced in the designer. For example, having prefetch paths easily designed in a query test tool, it would be better.

Some of the new users of LLBLGen here do like the idea of configuring things in the designer, and writing less code. One great example of this is typed lists. My personal preference is to use dynamic lists for just about everything, as I find this to be a much more flexible approach, but some of our newer users vastly prefer generated typed lists.

So the more I think about it, the more I think having generated prefetch paths would actually be a nice benefit, provided that you could have more than one per entity.

Otis wrote:

psandler wrote:

Also, how would filtering and sorting on prefetch nodes other than the root node work? Thanks, Phil

That's one of the issues with it indeed.

I'm not saying this would be easy (or possible for that matter), but users could visually set up prefetch paths, and apply sorting and filtering at the level they wanted right in the designer. Then they could create a prefetch path object in code, which would have a constructor that took all their filtering and sorting requirements as a parameter.

So:

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
   OrderEntity order = new OrderEntity(orderId);
   IPrefetchPath2 prePath = order.CreatePhilsGraph(myProductFilter, myOrderLinesSorter);
   adapter.FetchEntity(order, prePath);
}

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
   OrderEntity order = new OrderEntity(orderId);
   IPrefetchPath2 prePath = order.CreateBobsGraph();
   adapter.FetchEntity(order, prePath);
}

(Or maybe the prefetch path just remains part of the entity itself and you wouldn't have to pass it as a parameter? But then you wouldn't be able to add to it).

Way too complicated?

Again, I'm pretty happy with the way things are currently, but I could see the value in something like this.

Phil

mikeg22 wrote:

I think that the a query test tool would be very useful for both single entity and collection fetches. For collection fetches, setting up the relation predicate bucket (for example to create a report) can be very tricky. Maybe something like a visual tool that shows the approximate SQL equivalent of the bucket...You know how people are very often asking how to turn some complex query into LLBLGen language in these forums? Half the time it is because the query doesn't need to be as complex as they think, but the other half is because the SQL is easier for them to understand than the combination of relations and predicates. So like I said, maybe something that can visually show the relationship...

Often these sql queries are that complex to convert because they're formulated pretty complex as well but I do agree that relations shown visually is often a trigger for some people to understand them better.

As for the predefined prefetch paths, I think it is too limiting to just have one prefetch path defined. Sometimes you need one path, and sometimes you need another path. For an employee, a payroll department may need salary history and an HR department may need a benefits history. These are two branches of the employee that are needed in entirely different situations.

Of course, you would be able to define multiple paths, store them under a name, and for example make 1 mandatory (if you want to).

@psandler: I agree with you, dyn. lists are easier, but indeed others simply prefer the typedlist editor . The thing with sorting/filtering inside the designer is the way to specify filters and sorts. Sorts aren't that hard, but the filters can be pretty complicated. To specify these properly, you need a way to do that which is both expressive enough and also easy enough to learn and read.

I haven't found a way to do that yet. One thing we're looking at, also in a future perspective, is DSLs. The downside of DSLs is that you have to learn a new language (which can be totally visual), however the upside is that they're often way more expressive and thus simpler to use and the result is more maintainable.

I initiated the thread and then forgot about it, I should check my posts more often.

First of all, I was clear when I said that the ERP system I'm working on does not define FK relationships between tables which is an essential requirement in order to make Typed Lists. Which leave us to hardcode the joins between the tables not to mention the filters.

As I said I can declare views and mapped it to Typed Views, but creating it in the backend is a big hassle for me in the place where I work on.

I like the idea of the typed list; I want to take it step further by freely join table tables or entities in that matter that you wish, Plus You can from the designer do the complex queries that you wish using the Query Editor,

Trust me guys, making such extension will put your product in a whole another level than the rest.

This the added advantage I think developers will gain by this tool

1- Hide the complexity of mapping the concepts of sql joins to LLblGen from developers

2- You can write correct complex LLBLGEN queries on the fly, as the tool will automatically generate the llblgen queries behind the scene based on the user column selection, joins and filters selection. Especially of we're talking about grouping columns and multiple joins.

3- Users can also use the designer as a way to generate their queries they wish and paste the output in their code since they know that the generated queries will work without the need to compile and run their code to check for correctness and results.

That is the least that I can imagine, from your replies I feel like you don't want to go down that road. If it is from cost stand point I can understand, but if it is from a value and worthiness standpoint, I may disagree with you.

dpanet wrote:

I initiated the thread and then forgot about it, I should check my posts more often.

First of all, I was clear when I said that the ERP system I'm working on does not define FK relationships between tables which is an essential requirement in order to make Typed Lists. Which leave us to hardcode the joins between the tables not to mention the filters.

And I was clear that you can define these in the designer. Which makes no difference for the designer to create typedlists or for the generated code.

As I said I can declare views and mapped it to Typed Views, but creating it in the backend is a big hassle for me in the place where I work on.

I like the idea of the typed list; I want to take it step further by freely join table tables or entities in that matter that you wish, Plus You can from the designer do the complex queries that you wish using the Query Editor,

Well, you can, today. Go into the designer, and right click 'Relations' below an entity. Then add a relation of your choice to whatever entity. Then you can build typed lists.

Trust me guys, making such extension will put your product in a whole another level than the rest.

Could you please look at the in-designer creation of relations we have in place since v1.0.2004.1 I believe ?

This the added advantage I think developers will gain by this tool

1- Hide the complexity of mapping the concepts of sql joins to LLblGen from developers

Relations are always needed in some queries. You can't run away from that. We can hide it a bit with some sugar but they're still there. The reason why we put them in the open is that from the beginning people have to use the relation objects in the generated code so they're not confronted with them all of a sudden when they're in a situation where they need to use them.

2- You can write correct complex LLBLGEN queries on the fly, as the tool will automatically generate the llblgen queries behind the scene based on the user column selection, joins and filters selection. Especially of we're talking about grouping columns and multiple joins.

I'm a bit confused here. You can do that today with hardly any code using the relation objects and you can define these relations, if they're not defined in the db, in the designer, so what extras do you need besides these? (so I get insight in what to add)

3- Users can also use the designer as a way to generate their queries they wish and paste the output in their code since they know that the generated queries will work without the need to compile and run their code to check for correctness and results.

Though with intellisense this works too. If I want to load all customers who have purchased a given product, I follow simple steps you always follow:

// first define the collection we're going to fetch data into:
EntityCollection<CustomerEntity> customers = new EntityCollection<CustomerEntity>();
// then we're going to formulate the filter on the total # of entities so we only get the 
// entities we need. 
RelationPredicateBucket filter = new RelationPredicateBucket();
// as we need to filter on a different entity than we're fetching, we need to specify 
// relations. We ALWAYS start with the entity we're fetching and then walk towards the
// entity we're filtering on. Use intellisense to get the right relation.
// At every line, add the relation from the entity you ended the previous one.
// so we start with the entity we're fetching, customer
filter.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID);
// the previous relation added ended with 'order' so we start with order for our
// next relation to add:
filter.Relations.Add(OrderEntity.Relations.OrderDetailsEntityUsingOrderID);
// we start with OrderDetails
filter.Relations.Add(OrderDetailsEntity.Relations.ProductEntityUsingProductID);

// we've now arrived at the product. Specify the filter for the product.
filter.PredicateExpression.Add(ProductFields.ProductID==_someID);

// we're done. We're now set to fetch the customers.
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(customers, filter);
}

Some said: "it would be easier if we could have done simply: filter.Add(CustomerEntity.Orders.OrderDetails.Product.Fields==_someID);" or something like that. True, it would be simpler in SOME occasions. However, not in ALL occasions, as it hides some important thing: what if there are 2 relations between two entities? (customer and address for example). Also, I couldn't get this to work: Because, what should the CustomerEntity.Orders.OrderDetails.Product property in small pseudocode return? It should return all the info to get from customer to product. However, how to do that, if you just call properties? You can say: "you can find out from the model", but that's not the case if you have multiple paths from one entity to another, which is not that uncommon in models. So it results in a lot of code, as we're using a compiletime checked approach, so we can't just specify "CustomerEntity.Orders.OrderDetails.Product" and let a parser break it into pieces and use the proper relations.

Though maybe I've overlooked something, I don't know. What I do know is that the relation objects aren't that hard to understand, as they form a single line of code. It's not the relation object that's difficult.

What people find difficult is that they don't know HOW to get from customer to product. In the code snippet above I walk from customer to product via order and orderdetails. Though it might look obvious for this particular case, it doesn't necessarily mean it's obvious for the a lot of cases.

So it requires knowledge how the model looks like: how to get from customer to products. So does that come down to a visual approach to emit code for setting up a relation collection based on the model? If so, it will need to use the designer core assemblies, which means you can also use the designer yourself.

That is the least that I can imagine, from your replies I feel like you don't want to go down that road. If it is from cost stand point I can understand, but if it is from a value and worthiness standpoint, I may disagree with you.

I think we're misunderstanding eachother. I understand from your posts that you need relations to be setup which aren't there, and you say that's not possible today, however it is, in the designer.

If you didn't mean that, if you meant to setup the relationpredicatebucket object I misunderstood you, though it would be great if you could confirm if I understood you correectly or not, so we can proceed from there .

It's not that I don't WANT to add a query tool, I do want to add what's necessary, and to know what's necessary I have to consult you, our customers. However, my experience is that it's best to ask more questions, dig deeper on a subject to get the real problem on the table and solve THAT. Please don't see my questions as "oh, he's a stubborn person who simply doesn't want to add anything", because that's not the case. My questions should be seen as research what the people really need and more importantly: WHY and what's causing that why.

Because that cause is the problem to solve. If that problem is solvable by better documentation or a simple tutorial, we should go that approach. If it's solved by visual tools, we should add these. Though it's important to add the right solution, not 'a' solution no-one uses. . Otherwise we'll end up with things like the objecttestbed in vs.net, great in demos but cumbersome and too slow to use in practise.

Thanks for understanding, probably I wasn't clear when I posted my suggestion.

U see the code you wrote , I know it is simple, I want to take it to another level, instead of you writing and typing the code to traverse the relations in your code and add predicate expressions to filters, I want the user to have the option to generate that from a GUI, copy the generated code , paste it and continue on.

If you took a minute and try the SQL Server Query Editor you probably would understand what I'm trying to suggest

So let us say I want to convert this code in to GUI

EntityCollection<CustomerEntity> customers = new EntityCollection<CustomerEntity>(); // then we're going to formulate the filter on the total # of entities so we only get the // entities we need. RelationPredicateBucket filter = new RelationPredicateBucket(); // as we need to filter on a different entity than we're fetching, we need to specify // relations. We ALWAYS start with the entity we're fetching and then walk towards the // entity we're filtering on. Use intellisense to get the right relation. // At every line, add the relation from the entity you ended the previous one. // so we start with the entity we're fetching, customer filter.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID); // the previous relation added ended with 'order' so we start with order for our // next relation to add: filter.Relations.Add(OrderEntity.Relations.OrderDetailsEntityUsingOrderID); // we start with OrderDetails filter.Relations.Add(OrderDetailsEntity.Relations.ProductEntityUsingProductID);

// we've now arrived at the product. Specify the filter for the product. filter.PredicateExpression.Add(ProductFields.ProductID==_someID);

From the GUI I would see the two Entities , Customers And Orders, From there I would add a line to join the two entities, or the program can Identify that, afterwards I would add any filters on the columns I tick on and the GUI will take care on the syntax on how to convert those filters to LLBlGen code.

Again , if you look into the SQL Server GUI , you would understand what I'm trying to say.

The GUI will be very helpful when I decide to do complex queries.

What I'm trying to convey is to move my experience on how to embed complex sql statements in my sql stored procedures. I rarely if never reside in writing sql statements by hand, when I use the gui, simple clicks, add filters , copy the generated code and paste it and i'm done.

I hope that we come to understanding to what I have in my mind, determining the effectiveness of this idea, I will leave that to you.

dpanet wrote:

Thanks for understanding, probably I wasn't clear when I posted my suggestion.

U see the code you wrote , I know it is simple, I want to take it to another level, instead of you writing and typing the code to traverse the relations in your code and add predicate expressions to filters, I want the user to have the option to generate that from a GUI, copy the generated code , paste it and continue on.

Ok.

Well, you can forget about the predicates, that won't happen I think. I tried to explain that in detail in the second half of my post. Please understand what I'm trying to say there, as it's a tough subject for me to explain in detail what exactly is problematic.

In the SSMS query designer, the predicate editor is just forcing you to type in the predicate, which is precisely what you don't want.

It's not hard to create a designer for compare value predicates. It's very hard to create an editor which allows you to define expressions, subqueries etc. with aliases. The sad thing is that precisely THOSE queries justify perhaps a visual tool. Not the comparevalue predicates.

If you took a minute and try the SQL Server Query Editor you probably would understand what I'm trying to suggest

Ok. Though I have to add that I find that query editor extremely horrible. But I get your point (which is also a point others were making here).

So it's not about creating relations in the designer.

So let us say I want to convert this code in to GUI

EntityCollection<CustomerEntity> customers = new EntityCollection<CustomerEntity>(); // then we're going to formulate the filter on the total # of entities so we only get the // entities we need. RelationPredicateBucket filter = new RelationPredicateBucket(); // as we need to filter on a different entity than we're fetching, we need to specify // relations. We ALWAYS start with the entity we're fetching and then walk towards the // entity we're filtering on. Use intellisense to get the right relation. // At every line, add the relation from the entity you ended the previous one. // so we start with the entity we're fetching, customer filter.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID); // the previous relation added ended with 'order' so we start with order for our // next relation to add: filter.Relations.Add(OrderEntity.Relations.OrderDetailsEntityUsingOrderID); // we start with OrderDetails filter.Relations.Add(OrderDetailsEntity.Relations.ProductEntityUsingProductID);

// we've now arrived at the product. Specify the filter for the product. filter.PredicateExpression.Add(ProductFields.ProductID==_someID);

From the GUI I would see the two Entities , Customers And Orders, From there I would add a line to join the two entities, or the program can Identify that, afterwards I would add any filters on the columns I tick on and the GUI will take care on the syntax on how to convert those filters to LLBlGen code.

Entities on a canvas which have relations should have these relations visualized in front of them, and you can select which one you want to use.

This is hard to do visually though (e.g. not with the visual lib we use now in the hierarchy view of entities).

Selecting columns to filter on is pretty straight forward, but what if you want to define a fieldcompareset predicate?

You see, a visual editor which is capable of doing straightforward compare value predicates isn't really helpful, as the problematic queries are still not solved by that editor.

Frankly I have no idea how to do that visually AND productive. I mean: no-one will use an editor if it takes 15 minutes to setup a filter which takes 20 seconds to write in code.

Again , if you look into the SQL Server GUI , you would understand what I'm trying to say. The GUI will be very helpful when I decide to do complex queries.

What I'm trying to convey is to move my experience on how to embed complex sql statements in my sql stored procedures. I rarely if never reside in writing sql statements by hand, when I use the gui, simple clicks, add filters , copy the generated code and paste it and i'm done.

I hope that we come to understanding to what I have in my mind, determining the effectiveness of this idea, I will leave that to you.

Well, I understand your point, though I have a bit of a problem: it looks great on paper but I have to actually come up with a visual editor and I have no clue how that visual editor will look like. If the SSMS editor is the ultimate, I pass. Don't get me wrong, it will likely help a couple of people to get up and running, but if it can't deal with EVERY query to formulate, it thus WILL make you go back to code eventually.

A 3-level subquery query with field expressions... is that even doable visually? I doubt it. You see, the best DSL for queries is still SQL. A visual tool can mimic SQL but if SQL is much more expressive, a visual tool isn't going to cut it.

I have tried twice to come up with an expression editor visually and failed, simply the best DSL for expressions is some textual expression language.

So, I'm more thinking in a different direction. Forget the SSMS editor as I find that a horrible example how a visual tool could be implemented.

What I think is more helpful is an application where you can specify in code (C# or VB.NET) the code you want to try and it will execute it, AND you can use helpers to setup parts of the code. E.g. use a dialog to click together a prefetch path. use a dialog to select the relations to use.

For predicates, I have to say... I find it very very hard to come up with even a reasonable visual approach. I find the SSMS way not helpful, as it doesn't offer you to specify field expressions, and how to formulate subqueries? Because THOSE are the hard things to overcome for most people: subqueries, joins with extra clauses, expressions in predicates, aliased entities in subqueries etc.

Perhaps good to explain is that LLBLGen Pro originally was started in 2003 as a visual designer to design stored procedures which were then generated, however it turned out to be incredibly complex to get that designer in a useful manner, as SQL looks simple but can have a lot of small elements, details which also have to be expressed in a visual tool. After 4 months of work we dropped it and kept the work we spend on the main UI (3 weeks of work or so). I can tell you that these kind of things aren't as easy as they might seem. Though, I also understand that if we succeed in this, it's a true gain for some people, so we can't write it off beforehand, and I won't do that, despite the experiences we have from the past 3-4 years now.

So it's not that we haven't spend serious time on this already, we have and I almost gave up on the whole project. The thing is that the conventional query designer as seen in access and SSMS are 'ok' for simple work, but when it gets tough, you need to type things in by hand. And exactly THAT last bit is what a tool could help, as the other stuff is compared to the serious issues, pretty simple and straight forward after a developer has seen it once or twice. (If not, the whole software development experience is perhaps too complicated).

So we need a different direction, as otherwise the end result will be a visual tool which allows you to design a simple query and when things get tough it gives up. That might sound like I'm lame and can't find a solution, but in this case it IS very hard and complicated and undoable in my view, to create a query designer everyone understands and which allows you to formulate the query you want, ALWAYS. Not sometimes, but in all possible occasions.

The thing is, the best way to handle this is to create a DSL which matches the problem. (A DSL can be visually or textual). That DSL already exists, ANSI SQL. The only problem here is that most people don't fully know ANSI SQL, as every db has a different dialect.

So that can't be a proper solution IMHO. What can be done also is helpers for the code parts that the user needs help on, like setting up a prefetch path, or setting up relations.

Though as I said... for predicates, I find it extremely hard to even come up with a visual image how it might look like on screen, left alone code it, because of the extremely large scope of what possibly can be done.

I know, it's not something you want to hear. Though if I tell you to write such a tool and come up with something that doesn't suffice in a lot of occasions when you do need help, we both have wasted our time, so that's not helpful.

Frans:

I have a different approach to suggest. I haven't thought through this very long so it might be a completely stupid idea.

What was (sometimes still is) hard for me when I first started using LLBLGen was to translate the SQL I had in my head (or the relationships I "saw" in my mind) into the code I needed to write.

I wonder if the IDE could have a code editor window that could would return data like SMSS/QA and also display the generated SQL.

So for example, you could type into a code editor in the LLBLGen IDE:

Dim customer As New CustomerEntity("CHOPS") Dim sorter As ISortExpression = New SortExpression( _ New SortClause(OrderFields.OrderDate, SortOperator.Descending)) customer.SetCollectionParametersOrders(10, sorter) Dim orders As OrderCollection = customer.GetMultiOrders(False)

and see the results in a grid and also see the generated SQL. Something like that might also go a long way in easing people's minds about dynamic SQL.

Joel Reinford Data Management Solutions LLC

Thanks Joel for giving your thoughts about this . I was also leaning towards that direction and this is very doable, and as I said in my previous post, this tool could help creating the code you type in with helpers like creating a prefetch path snippet.

I think it's also a great teacher for the developer: as the developer sees what the outcome is by writing the actual code manually, s/he will pick up what to write more quickly I think, and it's less time consuming as well.

I found this thread as I was contemplating writing something similar to the post that started this thread. I'm glad I read through this. I think Joel's suggestion would be most helpful and therefore wanted to bubble this thread back. Any chance this last suggestion made its way into 2.5?

It's not in v2.5, sorry. We'll see what we can do in the coming months though.

I'd love to see this too. LLBLGEN is based on a solid abstraction but it's an abstraction none the less and its not always easy to get the sql right.

Pete