Hi all! I'm use LLBLGen 4.2 and SQLServer 2012. Self-Servicing

I have "User" table with "id" and "username" column and "CampaignsLead" table with "id" and another column. In UI asp.Net 4.0 page : <%@ Register Assembly="SD.LLBLGen.Pro.ORMSupportClasses.Web" Namespace="SD.LLBLGen.Pro.ORMSupportClasses" TagPrefix="llblgenpro" %> using LLBLGenProDataSource as datasource.

I want to query get all row in "CampaignsLead" with "id" greater "id" of "User" have "username" equal "kites"

I have try DynamicRelation in https://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/gencode_derivedtabledynamicrelation.htm but it not work.

Code :

Private Sub LLBLGenProDataSource1_PerformSelect(sender As Object, e As PerformSelectEventArgs) Handles LLBLGenProDataSource1.PerformSelect

            Dim filterEx As New PredicateExpression()
            filterEx.Add(New FieldCompareValuePredicate(UserFields.UserName, ComparisonOperator.Equal, "kites"))

            Dim dtFields As New ResultsetFields(1)
            dtFields.DefineField(UserFields.Id, 0)
            Dim dtDefinition As New DerivedTableDefinition(dtFields, "c", filterEx)

            Dim relation As New DynamicRelation(dtDefinition, JoinHint.Inner,
                                IRIS.EntityType.CampaignsLeadEntity, "o",
                               (New EntityField(UserFields.Id.ToString(), "c", GetType(String)) =
                                CampaignsLeadFields.Id.SetObjectAlias("o")))

            Dim relations As New RelationCollection()
            relations.Add(relation)
            relations.SelectListAlias = "o"

            e.ContainedCollection.GetMulti(envelopfilter, Me.txtMax.Value, Nothing, relations)

Error : An exception was caught during the execution of a retrieval query: Invalid column name 'SD.LLBLGen.Pro.ORMSupportClasses.EntityField'. The multi-part identifier "CRM.dbo.CampaignsLeads.ProjectId" could not be bound. The multi-part identifier "CRM.dbo.CampaignsLeads.CampaignId" could not be bound. The multi-part identifier "CRM.dbo.CampaignsLeads.CampaignId" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

The error is because you are using UserFields.Id.ToString(), while you should use UserFieldIndex.Id.ToString(), in the CTor of the EntityField.

Having said that, I don't think you need to use a DerivedTable. Just a custom relation, using thee following CTor and a filter in the GetMulti() should do the Job. And don't use aliases.

public EntityRelation(
    IEntityField2 primaryKeyField,
    IEntityField2 foreignKeyField,
    RelationType typeOfRelation
)

Hi Walla Thank for you help. It wonderful. And i try to using another way as you said : Custom Relation. But it may not correct. And no have error. Result is empty. "CampaignsLead" and "User" tables no have any relation on any column. Like different world. Can we add relation on them ?

            Dim filterEx As New PredicateExpression()
            filterEx.Add(New FieldCompareValuePredicate(UserFields.UserName, ComparisonOperator.Equal, "kites"))

            Dim relation As IEntityRelation = New EntityRelation(SD.LLBLGen.Pro.ORMSupportClasses.RelationType.OneToMany, "CampaignsLead", True)
            relation.AddEntityFieldPair(CampaignsLeadFields.Id, UserFields.Id)
            relation.InheritanceInfoPkSideEntity = InheritanceInfoProviderSingleton.GetInstance().GetInheritanceInfo("CampaignsLeadEntity", True)
            relation.InheritanceInfoFkSideEntity = InheritanceInfoProviderSingleton.GetInstance().GetInheritanceInfo("UserEntity", False)

            filter.Add(New FieldCompareExpressionPredicate(CampaignsLeadFields.Id, ComparisonOperator.LesserThan,
                New Expression(UserFields.Id)))
            envelopfilter.AddWithAnd(filterEx)

            Dim relations As New RelationCollection()
            relations.Add(relation)

            e.ContainedCollection.GetMulti(envelopfilter, Me.txtMax.Value, Nothing, relations)

You don't need to use that. What you should use is DynamicRelation, but you used the wrong overload. See the code snippet below:

Dim filter = UserFields.UserName.Equal("kites")
Dim relations = new RelationCollection()
relations.Add(new DynamicRelation(CampaignsLeadFields.Id, JoinHint.Inner, IRIS.EntityType.UserEntity, string.Empty, string.Empty, CampaignsLeadFields.Id.Equal(UserFields.Id)))
e.ContainedCollection.GetMulti(filter, me.txtMax.value, nothing, relations)

Add to the top of the code file:

Imports SD.LLBLGen.Pro.QuerySpec

This will give you access to the extension method '.Equal()'. Makes writing predicates easier

I first create the filter for the User.UserName, store it in filter. I then create the relation. Relations passed to GetMulti are used to join additional elements to the one fetched by the collection. e.ContainedCollection is a collection of CampaignLeadEntity instances, so we have to join UserEntity to it. I use the CampaignsLeadFields.Id field constructor to create a field object representing that field. I then pass it to the GetMulti call to fetch the rows.

I didn't know what 'envelopFilter' was, so I didn't use it. If it's an additional filter you want to use, you have to add 'filter' to that predicate expression and then pass envelopFilter to GetMulti() instead.