Fetch data From Two tables using Linq

asp.net, c#, entity-framework, linq

Solution

You need join

var result = (from a in Context.Articles
             join c in Context.Comments on a.ArticleId equals c.ArticleId
             select new{ Article = a, Comment = c});

Problem

all 1st of all I want to say I am totally new to Entity Framework but I want to learn it so My question is I have three tables. 1st for storing author details. Structure of the table is 2nd table for storing all the articles Structure of the table is and Table 3 for Storing comments Structure of the table is and as My client want I have no foreign key in Database but they have relations and I am handling them from code (Please don't ask why because I also don't know why my client don't want foreign Key) :) What I am doing Now I have an Admin Panel where there is a Grid View Where I am binding the Author Table and on Clicking of Any Row it will open another page Page source Code Example: ``` <div> <asp:ListView ID="articles" runat="server"> <ItemTemplate> Label1//Here Goes the Article Id Label2//Here Goes the Article Names <div> //Another Listview For Comments <asp:ListView ID="comments" runat="server"> <ItemTemplate> Here Goes All the Comments </ItemTemplate> </asp:ListView> </div> </ItemTemplate> </asp:ListView> ``` So What I am doing First I am binding the Article List and after binding I am doing a Foreach Loop Inside the article List to find all the article id and the Comment List view Note: As for the code You can see the comment list will be repeated for each article Once I find the Comment ID the I fetch the comments for that id and bind it to the Comment List. So here my problem is I am connecting to the DB for two times but I want to do this in a Single connection is there any way by which I can fetch all the rows of comment table for the article id while fetching the Articles. My Article Fetching code is ``` Context.Artcles.Select(x=>x.x.RefAuth==AuthId).ToList(); ``` I will get the AuthID from GridView Row Click. I know it is not a huge thing but I am totally new in this. Question:2 Thanks to Ehsan Sajjad for giving me the perfect solution So there is another Question. In my first Question I was getting data from two tables so The suggestion was to use Join in Linq but in my Second Question I have one table and the Table format is like this. If the type is 1 then Received else if the type is 2 then replied. Refmailid is when I will reply to a mail for ex if "mailid" is 1 then for that reply "refmailid" will be 1 by which I can fetch all the replies of one mail So what I want, just like articles and comments here also I am fetching all the received mails from a user the from Column is a Unique Userid so when I will fetch ``` Context.SupportRequests.Select(x=>x.from=="theuniqueuserid" && type==1).ToList(); ``` I will get all the received mails from that use and just like the previous div ``` <div> <asp:ListView ID="receivedmails" runat="server"> <ItemTemplate> Label1//Here Goes subject Label2//Here Goes the content <div> //Another Listview For Replies <asp:ListView ID="replies" runat="server"> <ItemTemplate>Here Goes All the replies will be more then one so it will be reapeated with //Subject //Content </ItemTemplate> </asp:ListView> </div> </ItemTemplate> </asp:ListView> </div> ``` So What I am doing? First I am binding the Received List and after binding I am doing a Foreach Loop Inside the Received List to find all the "mailid" and the replied List view Note: As for the code You can see the replied list will be repeated for each received mail Once I find the "mailid" then I will fetch the replies for that id and bind it to the reply List. Code for that ``` Context.SupportRequests.Select(x=>x.refmailid=="mailid").ToList(); ```

Original source