Answered by:

Inner Join in LINQ to Entities

Question

How do you accomplish the following in Linq to Entities? I have noticed that Intellisense won't display the foreign key column in my child table. I realize that it is not available and instead provides one of two things, the parent table as a strongly-typed object OR a EntityReference<YourParentTableType>. Here is a T-SQL-generalized version of what I am trying to compose in a LINQuery in C#.

select *
from t1
innerjoin t2
on t1.foreign_key = t2.primary_key
take a look at my attempt in LINQ

I finally figured out the correct LINQ query for EF. I created a sample MVC1.0 application in VS08 and focused on the Customer and Orders entities. I composed the query in VS, set a breakpoint, inspected the q variable and behold, the join-ed records were plainly visible. That is what I wanted. BUT, now to display these records on the VIEW PAGE side is still unclear to me. Trying to figure it out right now. I will post on my blog(http://pcolanet.spaces.live.com) from here on out. I will vote you or whatever the rating system is on this forum in order to give you points. Thanks again for your help. I will be visiting your blog as well, for it looks very usefull and very informative all-around. Talk to you soon. Danny Rosales

Nothing fancy, but note the ON clause, how only one side of the comparison operator(equals) specifies the foreign entitiy simply as it's name, o.Customers and the parent entity is simply refered to by the variable c, it does not require you to say c.CustomerID, just c.

Perform an Inner Join by Using the Join Clause
An INNER JOIN combines data from two collections. Items for which the specified key values match are included. Any items from either collection that do not have a matching item in the other collection are excluded.
In Visual Basic, LINQ provides two options for performing an INNER JOIN: an implicit join and an explicit join.
An implicit join specifies the collections to be joined in a From clause and identifies the matching key fields in a Where clause. Visual Basic implicitly joins the two collections based on the specified key fields.
You can specify an explicit join by using the Join clause when you want to be specific about which key fields to use in the join. In this case, a Where clause can still be used to filter the query results.

While I am sure you already know the differences between and an implicit vs. explicit INNER JOIN in T-SQL, I just want to give some background so we are on the same page. It appear as if the syntax you offered above is an implicit join, whereby the join is specified in the WHERE clause. Note that my problem is the right side of the WHERE comparison operator. You offered a number 1. This is precisely where my problem lies. If you are familiar with LINQ Pad, I SUCCESSFULLY achieve the inner join I am desiring, whether it be implicit or explicit, that doesn't really matter. In LINQ Pad(without it's intellisense), it recognizes the foreign key of the child table, albeit with no intellisense. While in VS08, the intellisense does not offer the foreignkey to the parent table. It offers one of two things, the actual parent entity type OR a EntityReference<ParentType>. I guess I am supposed to you use one of those two objects to 'navigate' and get the corresponding parent record.

Thanks for you help. I really appreciate. I have been stuck on this for days!

Danny, Are you looking for the linq equivalent of a "where exists" (which would be done in an EntityDataSourcem for example)?

I'm at work right now, so I'll have to look at your question more thoroughly when I get done here. In the meantime, I know the first example (less the where) is good for many-to-many relationships. I'll get back to you in an hour or two, but see if this is roughly what you're trying to accomplish (but via LINQ):

Thanks so much for responding so quickly. I apologize for not getting back to you last evening, had to host some guests last night for a dinner and movie. I realize you may be somewhat unavailable during the day, I completely understand ;)

Your reply is most excellent. This query in fact brings back the related child records that I was looking for. There are two technologies I am trying to understand, ASP.NET MVC and EF. My goal is to DISPLAY a set of data onto a VIEW PAGE(that is ASP.NET MVC LINGO). So, if I try to pass the query variable over to the VIEW PAGE, it doesn't work because it is expecting an object of type, something in my entity model, like in my particular domain, Jobs or JobTypes or Analysts or Departments, in NWinds, Customer or Orders, etc. When I tried passing the anonymously typed variable in the return statement - return View(jobsJoinJobTypesQuery);, I got a big fat nasty error saying the following; The model item passed into the dictionary is of type 'System.Data.Objects.ObjectQuery 'goblygook' but this dictionary requires a model item of type System.Collections.Generic.IEnumerable'1[JobTracker.Models.Jobs]'.

There is a possibility of stuffing the jobsJoinJobTypesQuery variable into the ViewData[""] dictionary and ATTEMPT to access it on the VIEW PAGE side of things. Consequently, having a nested foreach(right there in the html markup) and (if in fact I can cast this anonymously typed variable to IEnumerable) and do a match routine to properly display the associated child record, based on the g.Key found in the select clause of the LINQ query, because that is presumably the pkey of the parent record. I have some snagit screenshots that would hopefully better illustrate the conundrum that I am facing. I think if I just resorted to good old ADO.NET(datasets and command objects), I wouldn't have this bloody mess. Again, thanks for your interest and help in this matter. I really appreciate it.

Hmmmm. Don't really know where to go from there, I don't have any experience doing what you're doing. I'm not even sure that grouping query above is the best starting point, but hopefully someone more knowledgable than myself can step in and steer you in the right direction.

Sorry I don't have anything definitive for you, but good luck! I'll keep an eye on this, as I'm interested to see how it's solved.

I finally figured out the correct LINQ query for EF. I created a sample MVC1.0 application in VS08 and focused on the Customer and Orders entities. I composed the query in VS, set a breakpoint, inspected the q variable and behold, the join-ed records were plainly visible. That is what I wanted. BUT, now to display these records on the VIEW PAGE side is still unclear to me. Trying to figure it out right now. I will post on my blog(http://pcolanet.spaces.live.com) from here on out. I will vote you or whatever the rating system is on this forum in order to give you points. Thanks again for your help. I will be visiting your blog as well, for it looks very usefull and very informative all-around. Talk to you soon. Danny Rosales

Nothing fancy, but note the ON clause, how only one side of the comparison operator(equals) specifies the foreign entitiy simply as it's name, o.Customers and the parent entity is simply refered to by the variable c, it does not require you to say c.CustomerID, just c.