none
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
    
            inner join t2
    
             on t1.foreign_key = t2.primary_key
    
    
    
    take a look at my attempt in LINQ 

    var query = from c in Customers
    join o in Orders
           
    on c.CustomerID equals o.CustomerID
           
    select new {customer_name = c.CompanyName,                
                  order_date = o.OrderDate,
                  order_Id = o.OrderID};

    love dot net
    Wednesday, December 30, 2009 6:22 PM

Answers

  • So, if I understand you...you want to (for example) get every customer that has placed an order?

    (i.e., you have 20 customers, but only 8 have placed orders, so if you inner join ORDERS and CUSTOMERS on customerId, it will return those 8 people?)

    If I'm right, I think you should be able to do something similar to this:

    var customersWithOrdersQuery =
                            from o in myEnts.Orders.Include("Customer")
                            orderby o.Customer.FullName
                            group o by o.Customer.Id into g //group the people by customerId
                            select new { customerId = g.Key, theCustomer = g.FirstOrDefault().Customer };
    
                    foreach (var g in customersWithOrdersQuery)
                    {
                        Response.Write(g.theCustomer.FullName);
                    }
    Am I on the right track, at least?  Can you give a specific example of the data you have, and what you wish to retrieve?
    My tech blog has EF, C#, and ASP.NET resources
    Wednesday, December 30, 2009 9:09 PM
  • Allen,

    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.


      var q = from c in db.Customers
                        join o in db.Orders 
                        on c equals o.Customers
                        select new
                                   {
                                       custid = c.CustomerID,
                                       cname = c.CompanyName,
                                       address = c.Address,
                                       orderid = o.OrderID,
                                       freight = o.Freight,                                   
                                       orderdate = o.OrderDate
    
                                   };



    love dot net
    Thursday, December 31, 2009 7:38 PM

All replies

  • Danny, if you're trying to select an entity like in your SQL statement, (Orders, for example), the syntax would be something like this:

     
    IQueryable<Order> ordersQuery =
           from c in myEnts.Customers
           from o in c.Orders
           where c.Id == 1
           select o;

    That may not be 100%, but it's along the right lines, I believe


    Also, depending on what you're after, the following should work:

    IQueryable<Order> someOrders = myEnts.Orders.Where(o => o.Customer.Id == 1);
    
    ||
    
    Order anOrder = myEnts.Orders.Where(o => o.Customer.Id == 1).FirstOrDefault();


    Mind you, those may not actually work, I'm just going off the top, here.


    My tech blog has EF, C#, and ASP.NET resources
    • Edited by allen d mfc Wednesday, December 30, 2009 7:41 PM added stuff
    Wednesday, December 30, 2009 6:53 PM
  • Allen,

    Allow me to refer you to this site. http://msdn.microsoft.com/en-us/library/bb918093.aspx

    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!


    love dot net

    Wednesday, December 30, 2009 7:50 PM
  • 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):

    http://allenringgold.com/?p=115

    Hopefully I can help you out shortly.


    My tech blog has EF, C#, and ASP.NET resources
    Wednesday, December 30, 2009 8:21 PM
  • So, if I understand you...you want to (for example) get every customer that has placed an order?

    (i.e., you have 20 customers, but only 8 have placed orders, so if you inner join ORDERS and CUSTOMERS on customerId, it will return those 8 people?)

    If I'm right, I think you should be able to do something similar to this:

    var customersWithOrdersQuery =
                            from o in myEnts.Orders.Include("Customer")
                            orderby o.Customer.FullName
                            group o by o.Customer.Id into g //group the people by customerId
                            select new { customerId = g.Key, theCustomer = g.FirstOrDefault().Customer };
    
                    foreach (var g in customersWithOrdersQuery)
                    {
                        Response.Write(g.theCustomer.FullName);
                    }
    Am I on the right track, at least?  Can you give a specific example of the data you have, and what you wish to retrieve?
    My tech blog has EF, C#, and ASP.NET resources
    Wednesday, December 30, 2009 9:09 PM
  • Allen, 

    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. 



    love dot net
    Thursday, December 31, 2009 2:36 PM
  • 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.

    -Allen
    My tech blog has EF, C#, and ASP.NET resources
    Thursday, December 31, 2009 3:10 PM
  • Allen,

    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.


      var q = from c in db.Customers
                        join o in db.Orders 
                        on c equals o.Customers
                        select new
                                   {
                                       custid = c.CustomerID,
                                       cname = c.CompanyName,
                                       address = c.Address,
                                       orderid = o.OrderID,
                                       freight = o.Freight,                                   
                                       orderdate = o.OrderDate
    
                                   };



    love dot net
    Thursday, December 31, 2009 7:38 PM