linq querying entity framework how to join tables? navigation properties?

Question

Hi there I am stuck on a problem and hope someone can explain the answer to me!

So in my database I have 3 tables that connect like this:

Customer CustomerAddress Address -CustomerID -CustomerID -AddressID -FirstName -AddressID -Street -LastName -City

So I create my entity model and the middle table(CustomerAddress) is removed and replaced by navigation properties?

So what I'm trying to do is join the tables using a LINQ query in C#. in SQl the query would look something like this:

Select * From dbo.customer as c left join dbo.customeraddress as ca on c.customerID = ca.customerID left Join dbo.Address as a on a.addressID = ca.addressID

I realise thats not the case here since there is no customeraddress table. Do I use the navigation properties columns to make a join? In my model diagram I notice there is a navigation property called Addresses in the Customer class/table that seems to map to the Address class/table property called Customers.

So I've tried this:

var customerQuery = (from customer in db.Customers join address in db.Addresses on customer.Addresses equals address.Customers into add from rt2 in add.DefaultIfEmpty() select new { //.. });

But that is obviously incorrect, as I am unsure what to do with the navigation properties to join them. I would really appreciate if anyone could explain to me how I go about joining with this model!

Why not make em one table anyway? Though just to be clear you want to join em so you could select from the joined tables? — Niklas
– Niklas, Commented Jun 23, 2016 at 12:09

Ivan Stoev · Accepted Answer · 2016-06-23 12:40:48Z

When you have navigation properties, you just use them in your queries like what you would do if they were objects - there is no need to use joins, EF will generate them for you.

For instance, your query would be like this:

var customerQuery = (from customer in db.Customers from address in customer.Addresses.DefaultIfEmpty() select new { //.. });

This will generate left outer join for you. If you want inner join, remove DefaultIfEmpty().

What if the relationship between customer and address is 1:1? The DefaultIfEmpty method is not available then.
I think I figured it out. It would look like db.Customers.Where(c=>c.Address).DefaultIfEmpty();

Collectives™ on Stack Overflow

linq querying entity framework how to join tables? navigation properties?

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related