OK I looked through the forum for the answer to this but couldn't find one. I have a table called Person which has a PK field called PersonID, a nullable GUID field called UserID, and a nullable integer field called ParentID. The table holds parents and their children.

• All records, both parent and children, have a PersonID value
• Parent records have a UserID value, but no ParentID value
• Child records have no UserID value, but have a ParentID value which contains the PersonID of the record they are a child of

I'm trying to do a simple query to return a list of children records for a specific parent. All I know at the time of the query is the parent's UserID (not the ParentID). I can't figure out how to do this with LLBLGen. If I was going to write the SQL directly, I'd do something like: Select (FirstName + ' ' + LastName) "Name", Gender, BirthDate, PersonID FROM Person WHERE ParentID = (SELECT PersonID FROM Person WHERE UserID = @UserID). I set up a relationship between PersonID and ParentID in the database, not sure if that was necessary or not, but still can't figure it out.

I'm using version 2.0, adapter method, SQL 2005.

Any help is appreciated. Thanks

where parentid=(select...) is equal to a FieldCompareSetPredicate.

EntityCollection<Person> children = new EntityCollection<Person>(new PersonEntityFactory());
RelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(new FieldCompareSetPredicate(
        // field to compare: where >>ParentID<< = (
        PersonFields.ParentID, null,
        // field to compare with: = ( SELECT >>P1.ParentID<< FROM Person P1 WHERE ...)
        PersonFields.ParentID.SetObjectAlias("P1"), null,
        // operator to use: =
        SetOperator.Equal,          
        // filter to use in subquery: Person.UserID = P1.UserID
        (PersonFields.UserID==PersonFields.UserID.SetObjectAlias("P1"),   
        // specify single select, no alias for field to compare and no sorter.
        string.Empty, 1, null));    
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(children, filter);
}

I added comments which explain the fragments. I hope this works OK, I haven't tested it.

relations with self are often a source of confusion: where to place the alias...

Another way could be:

SELECT Children.PersonID 
FROM Person Children
    INNER JOIN Person Parents
        ON Children.ParentID = Parents.PersonID
WHERE Parents.UserID = @UserID

and LLBL:

// set parameter
int userID = 2;

// collection of children
EntityCollection<PersonEntity> children = new EntityCollection<PersonEntity>(new PersonEntityFactory());

// filter
IRelationPredicateBucket filter = new RelationPredicateBucket();

// filterRealtion: "...INNER JOIN Person Parents ON Children.ParentID = Parents.PersonID ..."
filter.Relations.Add(PersonEntity.Relations.PersonEntityUsingPersonIdParentId, "Parents");

// filter: "... WHERE Parents.UserID = @UserID ..."
filter.PredicateExpression.Add(PersonFields.UserId.SetObjectAlias("Parents") == userID);

// retrieve data
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(children, filter);
}

I hope this was helpful

Otis, I tried the code you posted and I get the following error message at the filter.PredicateExpression.Add line:

Unable to cast object of type 'System.String' to type 'SD.LLBLGen.Pro.ORMSupportClasses.IRelationCollection'.

Here is the full code I am using (I think your FieldCompareSetPredicate example was missing the parameter for "negate", so I added that in as False).

Public Function GetMyFamilyListByUser(ByVal pUserID As String)
            Dim UserID As Guid = New Guid(pUserID)

            Dim fields As New ResultsetFields(3)
            fields.DefineField(PersonFields.FirstName, 0, "Name")
            fields.DefineField(PersonFields.Gender, 1, "Gender")
            fields.DefineField(PersonFields.BirthDate, 2, "BirthDate")

            Dim ExpRight As Expression = New Expression(" ", ExOp.Add, PersonFields.LastName)
            Dim ExpFull As Expression = New Expression(PersonFields.FirstName, ExOp.Add, ExpRight)
            fields(0).ExpressionToApply = ExpFull

_** BTW, if there's a better way to return (FirstName + ' ' + LastName) as one field, let me know. **_

            Dim filter As New RelationPredicateBucket()
            filter.PredicateExpression.Add(New FieldCompareSetPredicate(PersonFields.ParentId, Nothing, PersonFields.ParentId.SetObjectAlias("P1"), Nothing, SetOperator.Equal, PersonFields.UserId = pUserID, String.Empty, False, 0, Nothing))

            Dim children As New EntityCollection(New PersonEntityFactory)

            Dim ResultTable As New DataTable

            Using adapter As DataAccessAdapter = New DataAccessAdapter(connStr)
                adapter.FetchTypedList(fields, ResultTable, filter, 0, Nothing, True, Nothing, 0, 10)
                Return ResultTable
            End Using

End Function

** BTW, if there's a better way to return (FirstName + ' ' + LastName) as one field, let me know. **

I would use a **string Property **at the **Custom Entity code section **of your PersonEntity.cs file (Database Generic Generated Project)... something like this:

public string FullName
{
    get
    {
        return (this.FisrtName + " " + this.LastName);
    }
}

This Property will be accessible in your Entity as if it would be a Field at your DB.

You can use relations at your filters, you can add Properties for custom fields and you can bind a EntityCollection in controls. So, there is a specific reason for use a adapter.FetchTypedList?

Thanks to both of you for the help. daelmo, I got your code working perfectly. I'm sure Otis' code works too, I'm probably doing something wrong since I'm pretty new to LLBL.

daelmo wrote:

I would use a **string Property **at the **Custom Entity code section **of your PersonEntity.cs file (Database Generic Generated Project)

Hmm, that's interesting. Every time I regenerated the classes, I would need to remember to go back in and add this again, right?

You can use relations at your filters, you can add Properties for custom fields and you can bind a EntityCollection in controls. So, there is a specific reason for use a adapter.FetchTypedList?

No reason, just a newbie . I'm trying to return the data as a DataReader, is there a better way to do that?

Hmm, that's interesting. Every time I regenerated the classes, I would need to remember to go back in and add this again, right?

No. LLBLGenPro Generator respects your Custom Entity code section. Cool! Ref: Help > Using the generated code > Adding your own code to the generated classes

No reason, just a newbie Regular Smiley. I'm trying to return the data as a DataReader, is there a better way to do that?

LLBLGenPro provides an extraordinary O/R Mapping so you could take advantage of the class structure it provides for you. Cool too! Ref: Help > Using the generated code > Adapter> Using the entity collection classes

I suggest you to review the starting points of the LLBLGenPro Help included in the software.

daelmo wrote:

I suggest you to review the starting points of the LLBLGenPro Help included in the software.

I've spent quite a bit of time doing that, unfortunately I haven't been able to figure out if there is a way to just return specific fields when using FetchEntityCollection instead of returning all the fields from that table. I know that using FetchDataReader or FetchTypedList allows you to specify only certain fields to be returned. Is it possible with FetchEntityCollection?

Not yet v2.1 will support that. In v2.0 you have to fetch a projection onto a collection.

Entities in LLBLGen Pro are **1:1 **representations of tables or views found in the catalog(s)/schema(s) of the project loaded. A Typed List is a **collection of entity fields from a set of entities **which have one or more relations with each other of the type. and of course, there is TypedView, which map in a 1:1 way on existing views in the catalog(s)/schema(s).

So, if you want to retrieve only a few fields from an Entity (an element of an EnttyCollection), and even fields from entities related, you could use a TypedList. You can do that by code (as your code reflect) or by LLBLGenPro Designer (recommended if the TypedList is inherent to the entire project and no only for a fragment of code).

Hope this was a little helpful... see u.

Otis wrote:

Not yet v2.1 will support that. In v2.0 you have to fetch a projection onto a collection.

So is that to say that to simply return a single field from a single entity, all that projector logic has to be used? I'm just trying to return the PK value from a table when I only know a different value in that same table (which is also unique). It's a scalar query: "select ID from person where userID = 12345". I'd like to return the result as an entity object. Seems like this should be a piece of cake in LLBL, for some reason I can't find the easy solution anywhere.

reprezent wrote:

Otis wrote:

Not yet v2.1 will support that. In v2.0 you have to fetch a projection onto a collection.

So is that to say that to simply return a single field from a single entity, all that projector logic has to be used? I'm just trying to return the PK value from a table when I only know a different value in that same table (which is also unique). It's a scalar query: "select ID from person where userID = 12345". I'd like to return the result as an entity object. Seems like this should be a piece of cake in LLBL, for some reason I can't find the easy solution anywhere.

If you want THAT, you can just use a scalar query of course. I was under the impression you wanted to fetch a couple of entities but not all fields of each entity.

LLBLGen Pro supports scalar queries. See DataAccessAdapter.GetScalar() in the reference manual and Using the generated code -> Field expressions and aggregates -> Aggregate functions in scalar queries for an example