How to display data from multiple tables in a single gridview or gridpanel using linq2sql?

Question

I have two sql tables Department and Lecturer with:

Department: DepartmentID, Name
Lecturer: LecturerID, Name, DepartmentID

I want to show the data in a table with columns:

Lecturer ID, Name and Department Name

How can I achieve this using Linq to Sql( with or without lambda expression)? I really appreciate your help. Thank you.

Answer 1

Thanks for your answer singhm0077. I am little confused with join, and unfortunately its not working as I thought. Luckily, I found another solution.

Since, there is no column DepartmentName in the table Lecturer, I had to create a partial class of the Lecturer table because I was going to bind the data from Lecturer table and, I defined a property DepartmentName which added a column in the database table.

public partial class Lecturer
    {
        public string DepartmentName
        {
            get;
            set;
        }
    }

The design part is:

<ext:GridPanel ID="grid1" runat="server" Width="600px" Header="false">
            <Store>
                <ext:Store ID="store1" runat="server">
                    <Model>
                        <ext:Model ID="model1" runat="server">
                            <Fields>
                                <ext:ModelField Name="LecturerID" Type="Int" />
                                <ext:ModelField Name="Name" Type="String" />
                                <ext:ModelField Name="DepartmentName" Type="String" />
                            </Fields>
                        </ext:Model>
                    </Model>
                </ext:Store>
            </Store>
            <ColumnModel>
                <Columns>
                    <ext:Column ID="colLecturerID" runat="server" Flex="1" DataIndex="LecturerID" Text="Lecturer ID"></ext:Column>
                    <ext:Column ID="colName" runat="server" Flex="1" DataIndex="Name" Text="Name"></ext:Column>
                    <ext:Column ID="colDepartmentName" runat="server" Flex="1" DataIndex="DepartmentName" Text="Department Name"></ext:Column>
                </Columns>
            </ColumnModel>
        </ext:GridPanel>

And using linq2sql, the code behind part is:

protected void Page_Load(object sender, EventArgs e)
        {
            if (!X.IsAjaxRequest)
            {
                this.store1.DataSource = GetDataToBind();
                this.store1.DataBind();
            }
        }

        private List<Lecturer> GetDataToBind()
        {
            DataBaseDataContext db = new DataBaseDataContext();

            List<Lecturer> lstLecturers = db.Lecturers.OrderBy(x => x.LecturerID).ToList();
            foreach (Lecturer lecturer in lstLecturers)
            {
                lecturer.DepartmentName = lecturer.Department.Name;
            }

            return lstLecturers;
        }

Finally, if the database are as follows:

The output appears like this:

Still, I don't know if its the best way to do it or not. However, its working all the time.

Answer 2

you need to use join beetween Department table and Lecturer table

DataClassesDataContext tdc = new DataClassesDataContext();

var res = (from p in tdc.Lecturers

               join br in tdc.Departments on p.DepartmentID equals br.DepartmentID

               select new
               {
                   p.DepartmentID,
                   p.Name,
                   lectID = p.DepartmentID,
                   depname = br.Name
               }
                   ).ToList();

you query will be like this

http://www.dotnetperls.com/join