LinqDataSource and inner/outer joins

lasseschou · Post by **lasseschou** » Fri 15 Jan 2010 14:35

Hi,

I'm using dotConnect for Oracle and LinqDataSources to display data in a grid. When checking the dbMonitor I can see that some calls result in INNER JOINS and some in LEFT OUTER JOINS. When for instance I'm using this select parameter in my LinqDataSource:

Code: Select all

Select="new(TransId, Port.Name AS Portal, CreatedDate, UserMobileInf.Msisdn, UserEmail)">

I get this call in dbMonitor:

Code: Select all

SELECT [....]
    FROM [...] t2
    LEFT OUTER JOIN USER_MOBILE_INF t3 ON t2.USER_ID = t3.USER_ID
    INNER JOIN PORTS t4 ON t2.PORTAL_ID = t4.PORTAL_ID
    WHERE [...]

As you can see there is one LEFT OUTER JOIN and one INNER JOIN. Now what determines which join is used? I'm assuming that it has something to do with the relationships in the Entity Developer model, but I can't seem to figure out what exactly.

I hope you can help!

Lasse

AndreyR · Post by **AndreyR** » Fri 15 Jan 2010 16:24

The left outer join is generated in the situation when the isForeignKey attribute is set to false for the navigation property.

lasseschou · Post by **lasseschou** » Mon 18 Jan 2010 07:43

Ok, thanks.

But is there any way to curcumvent this feature, apart from setting isPrimaryKey to false?

AndreyR · Post by **AndreyR** » Mon 18 Jan 2010 11:27

Your database structure in fact defines the join you should use. But if you want to use a weaker one
(left outer instead of inner), you can look at this StackOverflow sample:
http://stackoverflow.com/questions/7005 ... outer-join

lasseschou · Post by **lasseschou** » Mon 18 Jan 2010 12:45

My problem is that I cannot change the database structure, but I have a situation where a two tables with the primary keys ("user_id") should be joined with a left outer join.

In the StackOverflow example only LINQ is used, and not a LinqDataSource. Are you aware of any alternative solutions to forcing a left outer join when using the LinqDataSource?

Thanks,

Lasse

AndreyR · Post by **AndreyR** » Mon 18 Jan 2010 14:38

Change the association mappings in your .lqml file in order to set the isForeignKey property in an appropriate way.

lasseschou · Post by **lasseschou** » Wed 17 Feb 2010 12:21

Hi Andrey,

is there a way to control the IsForeignKey through the Entity Developer?

AndreyR · Post by **AndreyR** » Wed 17 Feb 2010 16:01

The only way to change this attribute is to manually modify it in code.

lasseschou · Post by **lasseschou** » Thu 18 Feb 2010 09:20

Thank you.

Is this an issue that you could address in future builds of Entity Developer? It just requires a new property "IsForeignKey" on the associations, and as far as I'm concerned it's a critical feature to be able to control.

AndreyR · Post by **AndreyR** » Fri 19 Feb 2010 14:40

We will investigate the possibility to add this functionality.
I will let you know about the results of our investigation.