Hello,

is it possible to do the following thing with LLBLGen Pro 2.0 and MSSQL 2005?

select c.* from Content c left outer join FieldValues f on f.ContentID=c.ID order by f.FieldValue;

I have ContentEntity for the Content table and FieldValueEntity for the FieldValue table. And also I have a relationship between f.ContentID and ContentID in the database. I want Content collection order/filter by some criteria applied to FieldValue.

Regards

I better answer this one correctly or the monster in your avatar will come and get me

You would need to add a relation object (from ContentEntity To FieldValueEntity) to the RelationPredicateBucket or RelationCollection (depends on whether you are using Adapetr or SelfServicing) of the FetchMethod.

Then you may use a sortClause on a field on the FieldValueEntity.

Heh the beast is sealed... for now

I can't get it. I'm adding the relation this way

bucket.Relations.Add(ContentEntity.Relations.FieldValuesEntityUsingContentId, JoinHint.Left);

and then execute the query it works like if it is a "where" clause. I need it to be left outer join.

What am I doing wrong?

P.S: Is it possible to see the query sent to database in plain text at client side?

Is it possible to see the query sent to database in plain text at client side?

Yeah sure, I was just gonna ask you to examine the generated query.

You should enable DQE tracing in the config file, please refer to LLBLGen Pro documentation "Using the generated code -> Troubleshooting and debugging"

Ok. The SQL is quite what is needed. But I'm confused why there are more queries that are executed? 1 query for every content that is returned. And the resulted collection is not what is needed. Bellow is the dump...

[404] Generated Sql query:
[404] Query: SELECT DISTINCT [PlasmaBoxCore].[dbo].[Content].[ID] AS [Id], [PlasmaBoxCore].[dbo].[Content].[ContentName], [PlasmaBoxCore].[dbo].[Content].[Path], [PlasmaBoxCore].[dbo].[Content].[Duration], [PlasmaBoxCore].[dbo].[Content].[Type], [PlasmaBoxCore].[dbo].[Content].[ContentType], [PlasmaBoxCore].[dbo].[Content].[Title], [PlasmaBoxCore].[dbo].[Content].[MediaType], [PlasmaBoxCore].[dbo].[Content].[HouseID] AS [HouseId] FROM ( [PlasmaBoxCore].[dbo].[Content] LEFT JOIN [PlasmaBoxCore].[dbo].[FieldValues] ON [PlasmaBoxCore].[dbo].[Content].[ID]=[PlasmaBoxCore].[dbo].[FieldValues].[ContentID])

[404]
[404] Generated Sql query:
[404] Query: SELECT [PlasmaBoxCore].[dbo].[FieldValues].[ID] AS [Id], [PlasmaBoxCore].[dbo].[FieldValues].[ContentID] AS [ContentId], [PlasmaBoxCore].[dbo].[FieldValues].[FieldDescriptionID] AS [FieldDescriptionId], [PlasmaBoxCore].[dbo].[FieldValues].[FieldValue] FROM [PlasmaBoxCore].[dbo].[FieldValues] WHERE ( ( [PlasmaBoxCore].[dbo].[FieldValues].[ContentID] = @ContentId1)) [404] Parameter: @ContentId1 : Int32. Length: 1. Precision: 10. Scale: 0. Direction: Input. Value: 1.

[404]
[404] Generated Sql query:
[404] Query: SELECT [PlasmaBoxCore].[dbo].[FieldValues].[ID] AS [Id], [PlasmaBoxCore].[dbo].[FieldValues].[ContentID] AS [ContentId], [PlasmaBoxCore].[dbo].[FieldValues].[FieldDescriptionID] AS [FieldDescriptionId], [PlasmaBoxCore].[dbo].[FieldValues].[FieldValue] FROM [PlasmaBoxCore].[dbo].[FieldValues] WHERE ( ( [PlasmaBoxCore].[dbo].[FieldValues].[ContentID] = @ContentId1)) [404] Parameter: @ContentId1 : Int32. Length: 1. Precision: 10. Scale: 0. Direction: Input. Value: 2. . . . and so on... . . .

And one other question: How to add another restriction to the join part

select c.* from Content c left outer join FieldValues f on f.ContentID=c.ID and f.FieldDescriptionID=1 order by f.FieldValue;

if I add a predicate it adds it to query with "where" clause

select c.* from Content c left outer join FieldValues f on f.ContentID=c.ID where f.FieldDescriptionID=1 order by f.FieldValue;

which causes wrong result set.

Regards

But I'm confused why there are more queries that are executed?

To determine if all the queries you see are excuted or not, examine the SQLProfiler or any similar tool.

And one other question: How to add another restriction to the join part

Add the filter to the CustomerFilter property of the EntityRelation

The sort problem is solved. The CustomerFilter was the magic property. Now a new question pops up how to do this?

select c.* from Content c left join FieldValues f on f.ContentID=c.ID and f.FieldDescriptionID=1 left join FieldValues g on g.ContentID=c.ID and g.FieldDescriptionID=2 order by f.FieldValue, g.FieldValue;

Sorry to bother you with so stupid qustions...

The additional queries was my mistake

Regards

Sorry to bother you with so stupid qustions...

Not at all. Your questions are always welcomed

Just as you did in your SQL query, you need to use aliases for the entity that's joined more than once.

Use the following overload off Relations.Add(), to specify an alias for the entity in hand.

public IEntityRelation Add(IEntityRelation relationToAdd, string aliasRelationEndEntity, JoinHint hint);

Then use the same alias again as the objectAlias in the sortClause overload.

public SortClause(IEntityField fieldToSort, SortOperator sortOperatorToUse, string objectAlias);

Thx for your help. Everything works fine now.