locked
How to join two tables with LINQ inner join in a class file? RRS feed

  • Question

  • User-1001836498 posted

    Hello,

    I need a solution on how to INNER JOIN two tables with LINQ in a class file which I can utilize from an ObjectDataSource control. I want to do development in object oriented fashion. Currently I only know how to use LINQ to pull data from single tables such as in the example below:

    Imports System
    Imports System.Collections.Generic
    Imports System.Linq
    Imports System.Data.Linq

    Partial Public Class Class1

        Public Shared Function GetCategories() As IEnumerable(Of Category)
            Dim dc As New DataClassesDataContext()
            Dim query = From m In dc.Categories Select m
            Return query
        End Function

        Public Shared Function GetProducts() As IEnumerable(Of Product)
            Dim dc As New DataClassesDataContext()
            Dim query = From m In dc.Products Select m
            Return query
        End Function

    End Class

    And in an aspx file I can utilize these results with two ObjectDataSource controls such as in the below examples:

    <asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
            SelectMethod="GetCategories" TypeName="Class1"></asp:ObjectDataSource>

    <asp:ObjectDataSource ID="ObjectDataSource2" runat="server"
            SelectMethod="GetProducts" TypeName="Class1"></asp:ObjectDataSource>

    I need to INNER JOIN the two tables with LINQ in a class file so that I can utilize the retrieved results with one ObjectDataSource control. I have a vague apprehension that it can somehow be done with 'anonymous' or 'transient, fleeting type'. I read in a book that I need to create a transient class that represents the columns that I require. But nowhere in the materials that are accessible to me can I find exactly how this is to be done.

    Your help is greatly appreciated.

    Saturday, November 29, 2008 6:29 PM

Answers

  • User1055287656 posted

    Actually my code doesn't deal with display anything at all, so not sure where you got "your code plainly displays information on the screen??" from.  My example shows how to join a table and return the results as a new class that has DepartmentName and EmployeeName that can then be passed to the display later.  You asked how to do a join and i offered an example with further reading.  In fact if i have a class named EmployeeInformation I could have done this:

    var query =
      from department in departments
      join employee in employees
          
    on department.ID equals employee.DepartmentID
        
      into employeeGroups
      from employee in employeeGroups.DefaultIfEmpty()
      select new EmployeeInformation{ DepartmentName = department.Name, EmployeeName = employee.Name };


     Perhaps you should do more readin and learining at the learnin center http://www.asp.net/learn/linq-videos/ and you should also check the mvc store front starter kit http://www.asp.net/learn/mvc-videos/ which shows examples of what i am talking about.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, November 30, 2008 10:59 AM
  • User-1001836498 posted

    Dear exuviae,

    Thank you for your help! Your help and references are very much appreciated! I have managed to find a solution to my requirement and have posted the source code at the location specified below:
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=4187769&SiteID=1

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 2, 2008 9:04 AM

All replies

  • User1055287656 posted

    you can use the join keyword to get what you want.  I don;t know the relations of your tables so i can't give you the exact query, but here is an example of a join:

     var query =
      from department in departments
      join employee in employees
          
    on department.ID equals employee.DepartmentID
        
      into employeeGroups
      from employee in employeeGroups.DefaultIfEmpty()
      select new { DepartmentName = department.Name, EmployeeName = employee.Name };

    more examples can be found here:

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

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

    Saturday, November 29, 2008 10:05 PM
  • User-1001836498 posted

    What is important for me is in this situation is not the LINQ query itself but the way how to represent the selected columns of my choice from multiple tables in an ObjectDataSource control.

    The examples that you have provided do not deal with representing data in ObjectDataSource control but rather plainly writing the data on the screen.

    Sunday, November 30, 2008 2:55 AM
  • User1055287656 posted

    Actually my code doesn't deal with display anything at all, so not sure where you got "your code plainly displays information on the screen??" from.  My example shows how to join a table and return the results as a new class that has DepartmentName and EmployeeName that can then be passed to the display later.  You asked how to do a join and i offered an example with further reading.  In fact if i have a class named EmployeeInformation I could have done this:

    var query =
      from department in departments
      join employee in employees
          
    on department.ID equals employee.DepartmentID
        
      into employeeGroups
      from employee in employeeGroups.DefaultIfEmpty()
      select new EmployeeInformation{ DepartmentName = department.Name, EmployeeName = employee.Name };


     Perhaps you should do more readin and learining at the learnin center http://www.asp.net/learn/linq-videos/ and you should also check the mvc store front starter kit http://www.asp.net/learn/mvc-videos/ which shows examples of what i am talking about.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, November 30, 2008 10:59 AM
  • User-1001836498 posted

    Dear exuviae,

    Thank you for your help! Your help and references are very much appreciated! I have managed to find a solution to my requirement and have posted the source code at the location specified below:
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=4187769&SiteID=1

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 2, 2008 9:04 AM
  • User-157715258 posted

    Hi chris..

    The code below looks fantastic,

    you can use the join keyword to get what you want.  I don;t know the relations of your tables so i can't give you the exact query, but here is an example of a join:

     var query =
      from department in departments
      join employee in employees
          
    on department.ID equals employee.DepartmentID
        
      into employeeGroups
      from employee in employeeGroups.DefaultIfEmpty()
      select new { DepartmentName = department.Name, EmployeeName = employee.Name };

    my question is its ok for taking department name & employeename from two tables,my need is if i want to access all the colummns from both the table using this LINQ wat should i do my friend?

    Waiting for ur reply!


    Thanks in Adv

    Ashok



    Wednesday, January 12, 2011 2:14 AM