How to have left outer join with entity consisting of 2 tables?

Question

We are having two tables named T1 and T2 in our database. We want to map these two tables(T1,T2) to single entity(E1) on entity framework. The relationship between T1 and T2 is not One-to-one. The T2 table may or may not have records in T1. But the reverse is always true.

If we add these 2 tables in single entity then by default we get ‘Inner join’ (If we attach profiler we can check that) when the SQL query is fired on the DB.

 

 We want the query to form the Left Outer join so as to get the expected result. How can we achieve the same?

We have considered option to create view on T1 and T2 tables and map it to our entity. But we cannot use view to carry out DML operation (insert,update,delete).

Is there any other alternative available which can satisfy our above requirement other than using view ?

 

Answer 1

Hi Kinnar,

For your issue, I think it is better for you to use table per type inheritance.

First, mapping multiply tables into one entity is for the tables that share the common primary key. The inner join feature is based on that prerequisite.

Second, with table per type inheritance, you can also insert, update, and delete records cascadingly, as long as you have foreign key constraints on the table.

Here is a blog talking about table per type inheritance:

http://blogs.msdn.com/bags/archive/2009/03/06/entity-framework-modeling-table-per-type-inheritance.aspx

This MSDN document is about add, updating and delete objects:

http://msdn.microsoft.com/en-us/library/bb738695.aspx

 

Does this works for you? If you have any questions or concerns, please update the thread and we will have a further discussion.

 

 

Best Regards

Yichun Feng

Answer 2

Hi Yichun,

Thanks for your reply. Both the tables in our database having same primary key. But one of table has some record which others table may not have. So, its not 1-to-1 strict mapping. Its like 0..1 to 1 mapping. We want the query to be have left outer join between tables rather taking "inner join" implicitly.
Can we achieve this ?

Answer 3

Hi Kinnar,

When you create a FK between the two table, the generated association will be 1 to 0.1.
If it is that case, you can use the function to achieve it to do left outer join.

http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/e73e6d29-101e-45ba-8650-f5718057a3bb

 

Does this works for you? If you have any questions or concerns, please update the thread and we will have a further discussion.

 

 

Best Regards

Yichun Feng

Answer 4

Hi Yichun,
I have the same problem, but some thing more.
What happends if I want to order the query by CustomerAddress.PostCode. It says that is a list and can't order it.
Thanks

Answer 5

Hi Fiege,

Could you post a new thread for this question and give more details about it?
In my sample code, my two tables have 1:1 association. In DB, there is a Fk between Customer.Id and CustomerAddress.CustomerId.
If you want using 1:M assocation, CustomerAddress.PostCode is collection.

Best Regards
Yichun Feng

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.