I'm creating a dynamic view to join two SQL Views which are mapped as entities.
I create two EntityFields, one for the key in the first view one for the key to match in the second view. I want all the rows from the first view and only the matches from the second view; a left join.

EntityField leftField = (EntityField)EntityFieldFactory.Create(leftViewName,leftFieldname);
EntityField rightField = (EntityField)EntityFieldFactory.Create(rightViewName,rightFieldname);

Then I create the relationship and add it to the relationship collection

EntityRelation r = new EntityRelation(leftField,rightField,RelationType.ManyToMany);
relations.Add(r,JoinHint.Left);

I then create a datatable using GetMultiAsDataTable. It's at this point I noticed in the generated SQL that I'm getting a RIGHT JOIN and not a LEFT JOIN.

It doesn't seem to matter what the RelationType is, but if I swap the leftField and rightField round I still get a Right Join but the views are swapped the right wayround so I get the data I want.

Is this what's supposed to happen 'cos it seems backwards to me? (I know ( B RightJoin A) is the same as ( A LeftJoin B) )

Thanks Mark

Hi Mark, Could you please post the approximate SQL you are expecting? Also the generated SQL that you get for the first try (leftField, RightField).

What LLBLGen version are you using ?

If I were writing the SQL it would be something like

SELECT StudentView.Surname, ContactView.postcode
FROM StudentView LEFT JOIN ContactView ON StudentView.StudentID = ContactView.PersonID

what I get is

Query: SELECT TOP 1000 [db].[dbo].[StudentView].[Surname] AS [StudentView.Surname], [db].[dbo].[ContactsView].[HomePostcode] AS [ContactsView.HomePostcode] FROM ( [db].[dbo].[StudentView]  RIGHT JOIN [db].[dbo].[ContactsView]  ON  [db].[dbo].[StudentView].[StudentID]=[db].[dbo].[ContactsView].[PersonID])

LLBGEN version 2.5

Why are you using ManyToMany instead of RelationType.OneToMany? As ManyToMany suggests there is an intermediate table.

Which LLBLGen Pro runtime library version are you using? (check the following thread to know how to get the RTL version number: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7725)

Sorry about the (holiday) delay.

The runtime version is 2.5.07.0907

No reason for the "ManyToMany". I have tried them all to see if there was a difference in the generated SQL. I'd rather not put anything in for relation; just give the linking fields.

More often than not the join will be an INNER one in which case the generated code is fine, but not always sometimes the user may want all of ViewA and matches from ViewB.

The only change which affected the code was to swap the PK/FK round to give

IEntityRelation r = new EntityRelation(fkField, pkField, RelationType.OneToMany);

Thanks, Mark

Hi Mark,

I think the confusion is "where pk and fk are". I know, this is obvious, however as the entities are views originally there's not information so the framework could guess the relation info. So, this could be solved if you add some useful info to your relation:

EntityRelation newRel = new EntityRelation(VSomeFields.ThePKField, VSomeOtherFields.TheFKField, RelationType.ManyToOne, true, "ThePKField");

The last two parameters indicate that the first field is the PKField and its name is "ThePKField".

Please try that and let us know if everything is ok

David,

Thanks for the tip. That certainly produces the SQL I was expecting, and I see what you mean about the framework not having enough information to infer the relationship.

Does the RelationType.??? make any difference in this case? What would you set it to if you don't know the relationship?

Thanks Mark

As far as I know, _RelationType _is useful to infer (better word than "guess" ) the _StartEntityIsPkSide _property of the relation:

docs wrote:

StartEntityIsPkSide Property: Set to true if the start entity of the relation is the PK side of the relation. This is set in the generated code. This property is true in 1:n relations and in 1:1 relations where the start entity is the PK side and the end entity is thus the FK side. Required for determining which alias belongs to which entity.

So I think _RelationType _property works in conjunction with _StartEntityIsPkSide _property. In your case, if I'm not mistaken, the way you built your relation is m:1, that's why you had to add this additional info, added to that there was not info to infer the relationship as you pointed.