Instructing entity to use left outer join

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
Anchor
Posts: 31
Joined: Mon 08 Dec 2008 21:02
Location: Massachustts/USA

Instructing entity to use left outer join

Post by Anchor » Thu 26 Feb 2009 19:34

I have an entity that maps to two tables. These two tables have a one-to-one relationship and are connected by a primary key. If I, using Linq, do a SELECT for all of the rows in the entity, I get an "inner join" of those two tables. In other words, only the rows that are common by the primary key between the two tables are retrieved and handed back. If there are rows of data that is in one table and not the other, those rows are never retrieved.

I would like the entity to use a left outer join so I can always get all of the records from table A no matter what is in table B. Is this possible? Can I change the behavior of the entity?

Thanks,
Joe

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Fri 27 Feb 2009 09:40

There are two ways to use the left outer join - using Entity SQL, like in the following post:
http://social.msdn.microsoft.com/Forums ... f7179e3fc/
or using inverse navigation properties, like here:
http://social.msdn.microsoft.com/forums ... 301bf130f/

Anchor
Posts: 31
Joined: Mon 08 Dec 2008 21:02
Location: Massachustts/USA

Post by Anchor » Fri 27 Feb 2009 20:00

I'm sorry AndreyR, but I'm confused. Don't these posts discuss way to perform an outer join between different entities with an EDM?

If this is so, what I want to do is have one entity behave differently by having it perform an outer join on the two tables that make up this one entity rather than an inner join.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Mon 02 Mar 2009 08:47

Unfortunately, using inner join is a default behaviour of Entity Framework.
One way is using two entities, as I described in my previous post.
Another way is to use defining query (this will let you create one entity using outer join). Defining queries are described here:
http://msdn.microsoft.com/en-us/library/bb738450.aspx

Anchor
Posts: 31
Joined: Mon 08 Dec 2008 21:02
Location: Massachustts/USA

Post by Anchor » Mon 09 Mar 2009 18:57

Okay. I decide to stay with the one entity and modify the .edmx file so I could add a new entity that represents the joined data object. I added a new EntitySet and EntityType in the SSDL section of the file. Then I created the entity in the VS designer and mapped it to the new entity set.

Basically, I followed this guy's advice from his blog and it worked out well.

http://blogs.microsoft.co.il/blogs/gilf ... ement.aspx

Thanks for the help.

Post Reply