How can I do something like this in SelfServicing VS2005 llblgen code?

Return all managers that have at least 2 employees:

Select * from Managers where ( select count(*) from Employees where Managers.ManagerID=Employees.ManagerID) > 2

I think your query can be re-written as follows:

Select * from Managers
where ManagerID IN
( 
select ManagerID 
from Employees 
Group By ManagerID
Having Count(*) >2
)

And then you can use FieldCompareSetPredicate to construct it.

Uh.....that does not really help me.

I read all the docs I cannot find out how to do this. The example in the docs on the Having uses a TypedList, and in general does not help.

Also, as far as the examples in the help file go, what would be nice is to show the SQL and then show the code. As it now, it is just code - so not 'great'.

I did this:

filter.Add( new FieldCompareSetPredicate( ManagerFields.ManagerID, EmployeeFields.ManagerID, SetOperator.In, <filter> );

So, that is part of it, now I have no idea how to construct the <filter>

Please can you help? And I am guessing I cannot do it the other way, which is why you re-wrote the query?

Ok. I finally figured that one out!

I ran in the debugger, looked at the query generated, rinse, repeat...

Here is the answer for anyone else looking. Produce the following SQL:

Select * from Managers
where 
( select count(EmployeeID) 
from Employees 
where Managers.ManagerID=Employees.ManagerID) > 2

which is then rewritten (cause no one knows how to do that one!)

Select * from Managers
where ManagerID IN
( 
select ManagerID 
from Employees 
Group By ManagerID
Having Count(EmployeeID) >2
)

PredicateExpression whereClause = new PredicateExpression();

//create the 'count(EmployeeID)'
EntityField employeeEmployeeIDField = EmployeeFields.EmployeeID;
employeeEmployeeIDField.AggregateFunctionToApply = AggregateFunction.Count;

//create the 'count(EmployeeID)>2' 
IPredicateExpression subQueryFilter = new PredicateExpression(employeeEmployeeIDField > 2);

//this puts in the 'group by' clause and the 'having' clause
IGroupByCollection groupBy = new GroupByCollection();
groupBy.Add(EmployeeFields.ManagerID);
groupBy.HavingClause = subQueryFilter;

//this creates the entire 'in' clause 
whereClause.Add(new FieldCompareSetPredicate(ManagerFields.ManagerID, EmployeeFields.ManagerID, SetOperator.In, null, null, "", 0, null, false, groupBy));


yourEntityCollectoin.GetMulti(whereClause);

Man, that is a lot of code for that simple query - huh?

LLBLGen Pro is a great product. It makes some things that used to be lots of work absolutely easy - and things that used to be easy (the above) take hours. Thankfully, it saves more time than it costs (that and I am still learning).

Otis - if you are reading, what would be GREAT is a section in the documentation that had like 20 different queries writting in SQL - everything from simple SQL to complex and then the LLBLGen code to produce that query. That could have saved me tons of time.

Glad it worked with you.

LLBLGen Pro is a great product. It makes some things that used to be lots of work absolutely easy - and things that used to be easy (the above) take hours. Thankfully, it saves more time than it costs (that and I am still learning).

Once you pass that learning curve of building those kind of queries with LLBLGen Pro, it won't take that long from you, now the next time you are going to have a Group By with Having predicate, I bet you will do it in a couple of minutes. At least by Copy & Paste (as I do most of the time )

I once struggled with building those complex queries and as You did I spent more time on them, but now I find them much easier to build.

WayneBrantley wrote:

Otis - if you are reading, what would be GREAT is a section in the documentation that had like 20 different queries writting in SQL - everything from simple SQL to complex and then the LLBLGen code to produce that query. That could have saved me tons of time.

That's a really great idea! I often find myself reading back through the docs to find something a little more obscure that I don't often perform, then cutting and pasting a bit of code.

A complete round-up of samples would be great. Or, perhaps something along the lines of the 'walkthrough' examples that are provided with vs.net. (Search 'walkthrough' in help to see one.)

THere are filter examples in the how-do-I section. Though even with 50 queries in place, it still will be a problem in some situations if you're locked into a query mindset.

A good start is often to let go of the SQL mindset and to think in sets of entities, and to define filters to define those sets. That frees you from 'how do I convert this SQL to llblgen statements?', because that's a struggle. If you take a step back and start with 'what does this sql actually do?' you could easier convert that answer to llblgen pro statements than the raw sql.

So I guess that is a 'no'

My thought is this.

EVERY user that buys your product thinks in terms of SQL - maybe after 6 months with your product they will start to think more like your design. But, until there the learning curve is pretty big on this one. I posted messages and spent about 2 hours over 2 days on this one query.

I understand your 'theory', but it sure would help out your users.

I don't think it's a no really. I had the same problems the first month I used LLBLGen and that was before the How Do I section.

The issue is that there are examples in the How Do I section and now matter how many examples are included people will have ways that they write sql that don't fit in there and want more. This forum was helpful, but the problem for me was that I didn't want to seem like an idiot going how do I do this (select * from tableA where ColumnA > ColumnB) or something else that seems so trivial. Maybe a beginner forum or something along those lines may be helpful.

Keep firing away all of the questions you have though, since they are probably helping many others that haven't even thought to ask them yet.

WayneBrantley wrote:

EVERY user that buys your product thinks in terms of SQL - maybe after 6 months with your product they will start to think more like your design. But, until there the learning curve is pretty big on this one. I posted messages and spent about 2 hours over 2 days on this one query.

The learning curve is always big for people who stick with SQL as their primary way of doing things with the db. I can assure you, not everyone thinks in terms of sql, a lot don't and want to keep it that way. So please take a step back, relax and read up which mechanisms there are for filtering, sorting etc. and why they're there. Don't ask the question: "how do I convert this sql to llblgen?" but ask the question: "how do I get these entities with a filter on x, y and z ?". Because that's what you're really want to do, and with that last question, it's easy to answer with the mechanisms in place.

I understand your 'theory', but it sure would help out your users.

In the northwind example on our website I illustrated some common things people will run into. Easy to grasp, easy to follow stuff. It didn't help much. Simply put: some people are locked in a given mindset and it's a bit hard for them to take that step back and start with what they want to do (i.e.: what kind of data do you want to retrieve and under which conditions) instead of trying to convert a RESULT of that question in a different format (SQL) into another format which represents an answer to the same question (llblgen pro code).

I fully understand why people are locked in these mindsets, we all are in some way or another. It's simply very hard to write docs which appeal to everyone and make everyone understand it without a problem.

Seems like we are almost on the same page.

You agree it is a big learning curve from people that are used to doing SQL. The only difference is I think (and swallace above) that a list of SQL commands converted to LLBLGen would help us relate how the SQL way of thinking maps onto your product. During that process hopefully we will begin to learn how to use your software to generate the results we want.

Since we don't have said list I have a new question!

Say you have Managers -> Department -> Employees.

I want the following information:

select Manager.Name, Manager.Phone, Department.Name, Employee.* from Manager (join...blah) where Manager.DivisionId=12 and EmployeeHireDate between @x and @y

So, I actually tried to build this end result 'thinking about what I wanted' instead of the sql I used above to describe to you what I wanted. I got it to work, except I could not get the Manager.Name and Manager.Phone items prefetched.

EmployeeCollection empColl = new EmployeeCollection();
PredicateExpression filter = new PredicateExpression();
RelationCollection relations = new RelationCollection();

relations.Add(EmployeeEntity.Relations.DepartementEntityUsingDepartmentId);
relations.Add(DepartmentEntity.Relations.ManagerEntityUsingManagerId);

filter.Add(new FieldBetweenPredicate(EmployeeFields.HireDate, FromDate, ToDate));
filter.Add(ManagerFields.DivisionID == 12);

PrefetchPath prefetch = new PrefetchPath((int)EntityType.EmployeeEntity);
prefetch.Add(EmployeeEntity.PrefetchPathDeparement);
//also tried some subpath stuff here..

empColl.GetMulti(filter, 0, null, relations, prefetch);

When I watched it in the debugger, it only showed one SQL statement being executed and it did not return any information about the Manager entity.

So, how can I get that ManagerEntity inside the EmployeeEntity prefetched in the above scenario?

(BTW, I ended up just making a TypedList with the columns from the tables I needed. I was thinking the above should have worked though).

Thanks for looking.

I think you have missed to prefetchPath the ManagerEntity

PrefetchPath prefetch = new PrefetchPath((int)EntityType.EmployeeEntity);
prefetch.Add(EmployeeEntity.PrefetchPathDeparement).SubPath.Add(DeparementEntity.PrefetchPathManager);

If the above didn't work, please post the generated SQL query(ies)

Thanks

Otis:

I think you are under-estimating the value of the examples in your documentation. The existing examples are really helpful. However, I agree with Wayne's suggestion that even more examples would be helpful. I also think that most people need the translation from SQL to LLBLGen. Maybe there should also be a "common speech" translation.

I'm thinking that perhaps you need a specific forum or help document that uses Northwind as a basis and people could ask/contribute examples of how to get back specific data using LLBLGen.

If this were a help document, I'd suggest the following structure.

A. Common speech. Example:

I want all customers in France sorted by Company.

B. SQL Example:

Select * from Customers where country = 'France' Order By CompanyName

C. LLBLGen Example:

            Dim da As New DataAccessAdapter
            Dim customers As EntityCollection
            Dim bucket As New RelationPredicateBucket()
            Dim sortOrder As New SortExpression 

            customers = New EntityCollection(New CustomerEntityFactory)
            bucket.PredicateExpression.Add(CustomerFields.Country = "France")
            sortOrder = (CustomerFields.CompanyName Or SortOperator.Ascending)

            da.FetchEntityCollection(customers, bucket, 0, sortOrder)

Speaking for myself, the thing I find most lacking at the moment are examples that deal with aggregates and sub-queries.

I'm hoping that 2.0 will open up lots of new possibilities to deal with those things.

Joel Reinford Data Management Solutions LLC

jovball wrote:

Otis:

I think you are under-estimating the value of the examples in your documentation. The existing examples are really helpful. However, I agree with Wayne's suggestion that even more examples would be helpful. I also think that most people need the translation from SQL to LLBLGen. Maybe there should also be a "common speech" translation.

But samples in which areas? Most questions about 'please convert this SQL for me' are around the more high-end sql constructs, and it's likely samples won't cover these.

THough I know that more examples might be helpful, even if there will still be room for more, as it's impossible to answer every question up front. Also a tool which allows you to test a predicate/relation set up front (which spits out sql in a separate area) might be helpful. But they're all covering up the real cause: some people fail to grasp the concepts behind the querying system for whatever reason. I think it's also key for us to invest even more time to get these concepts across, because I think/believe that if the concept is understood, you don't need examples for all kinds of applications of those concepts, just a few. However, to get these concepts understood is a holy grail, and after 2.5 years we're still not making it understandable for everyone.

I'm thinking that perhaps you need a specific forum or help document that uses Northwind as a basis and people could ask/contribute examples of how to get back specific data using LLBLGen.

If this were a help document, I'd suggest the following structure.

A. Common speech. Example:

I want all customers in France sorted by Company.

B. SQL Example:

Select * from Customers where country = 'France' Order By CompanyName

C. LLBLGen Example:

            Dim da As New DataAccessAdapter
            Dim customers As EntityCollection
            Dim bucket As New RelationPredicateBucket()
            Dim sortOrder As New SortExpression 

            customers = New EntityCollection(New CustomerEntityFactory)
            bucket.PredicateExpression.Add(CustomerFields.Country = "France")
            sortOrder = (CustomerFields.CompanyName Or SortOperator.Ascending)

            da.FetchEntityCollection(customers, bucket, 0, sortOrder)

Good suggestion, I'll think about it. You want all SQL produced by all examples or just a couple in a how-do-I section?

Speaking for myself, the thing I find most lacking at the moment are examples that deal with aggregates and sub-queries.

Could you elaborate on that a bit more? If you want, you can start a new thread. Aggregates and subqueries are possible, so it shouldn't be a problem to get these build with todays code.

I'm hoping that 2.0 will open up lots of new possibilities to deal with those things.

Which improvements are you expecting?

Otis:

See my previous post:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=5626&HighLight=1

For most data access, LLBLGen is wonderful. The place where I would most like to use it is reporting because reporting often calls for flexibility in criteria. That is exactly where I'm getting stuck on results that involve aggregates.

I need to do these things and get a resultset. 1) Get aggregate data based on criteria 2) Join the aggregate data with additional data 3) The joined data might need criteria also but I could probably live without this one.

Maybe this can be done currently, maybe not.

My solution at the moment is to use a stored procedure. However, that means I lose any strongly typed attributes. I see in many other posts that you do not think it's possible (or that it's "fragile") to add strong typing for stored procedures to LLBLGen.

Joel Reinford Data Management Solutions LLC

jovball wrote:

Otis:

See my previous post:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=5626&HighLight=1

For most data access, LLBLGen is wonderful. The place where I would most like to use it is reporting because reporting often calls for flexibility in criteria. That is exactly where I'm getting stuck on results that involve aggregates.

I need to do these things and get a resultset. 1) Get aggregate data based on criteria 2) Join the aggregate data with additional data 3) The joined data might need criteria also but I could probably live without this one.

Maybe this can be done currently, maybe not.

My solution at the moment is to use a stored procedure. However, that means I lose any strongly typed attributes. I see in many other posts that you do not think it's possible (or that it's "fragile") to add strong typing for stored procedures to LLBLGen.

That's correct, however v2 will have some sort of way to produce a resultset from a proc and project it on classes or whatever structure using our new projection system. This could be a way for you to get the data out the db in strongly typed objects, though it requires you to write a couple of lines of code to make the projection (from a row of data in a resultset to a set of fields in the target object).

(proc can also be other ways to formulate a query, like resultsetfields + a set of filters, relations etc.)

Hi

I'm a new user of LLBLGen.

I have to agree with all the users asking for more examples. I am really struggling to learn the "correct" way of doing things, mainly with converting current sql statements, the more examples that you can provide, the easier the transition is.

dregs

Have you looked at the sql snippets in the filtering section in the manual and also the How do I section which describes various 'where' statements?

Otis, I do not want to beat a dead horse, but where is that whip?

Now that 2.0 is here does it impact in any way the ability to write code to produce the following query:

Select * from Managers
where 
( select count(EmployeeID) 
from Employees 
where Managers.ManagerID=Employees.ManagerID) > 2

Or must it still be re-written as this to write the code:

Select * from Managers
where ManagerID IN
( 
select ManagerID 
from Employees 
Group By ManagerID
Having Count(EmployeeID) >2
)

I think you should run the queries in query analyzer and study the execution plans. I would assume the second one is faster, but I don't know for sure

Does this answer mean - yes we still have to re-write?

Execution plan. MSSQL - The first one is faster!!

I've tried it, and the second one execution plan came faster.

I've used the Northwind database (SQL Server 2000) with the following 2 queries:

Select * from Orders
where 
( select count(*) 
from [Order Details] 
where Orders.OrderID=[Order Details].OrderID) > 2

Select * from Orders
where OrderID IN
( 
select OrderID 
from [Order Details] 
Group By OrderID
Having Count(*) >2
) 

Interesting. I did it on our database, not northwind and had a slightly more complex query where the query did several joins and such. (Same in both queries though).

The subquery did a (select sum(somefield) from table)<>0.

One other difference is the outer query and inner query were using the same tables. Would be something like

select * from [Order Details] od1
where (select sum(quantity*unitprice) from [Order Details] od2 where od2.OrderID=od1.OrderID)<>0

and

select * from [Order Details] od1
where OrderID in (select OrderID from [Order Details] od2 group by OrderID having sum(quantity*unitprice)<>0)

When I ran that, they both took 50% of the costs.

Anyway, the real question was, not which was fastest, but can this still not be done in code both ways?

You can do both in code

Can you please provide an example of how to do the one without the "in" clause. I cannot find any example on how to do this.