LinqDataSource and inner/outer joins

LinqDataSource and inner/outer joins

Postby 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
lasseschou
 
Posts: 13
Joined: Mon 23 Nov 2009 08:59

Postby 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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby lasseschou » Mon 18 Jan 2010 07:43

Ok, thanks.

But is there any way to curcumvent this feature, apart from setting isPrimaryKey to false?
lasseschou
 
Posts: 13
Joined: Mon 23 Nov 2009 08:59

Postby 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
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby 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
lasseschou
 
Posts: 13
Joined: Mon 23 Nov 2009 08:59

Postby 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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby lasseschou » Wed 17 Feb 2010 12:21

Hi Andrey,

is there a way to control the IsForeignKey through the Entity Developer?
lasseschou
 
Posts: 13
Joined: Mon 23 Nov 2009 08:59

Postby AndreyR » Wed 17 Feb 2010 16:01

The only way to change this attribute is to manually modify it in code.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby 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.
lasseschou
 
Posts: 13
Joined: Mon 23 Nov 2009 08:59

Postby 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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle