Linq - Query from two different tabels
Posted on 2011-10-06
Im making a new website in ASP.NET MVC 3 (C#), using MS SQL 2008 as database.
In my database I have two tabels, named Users and Messages.
The Users tabel have these columns:
- Id (PK)
- Username (varchar)
- Age (varchar)
- City (varchar)
The Messages tabel have these columns:
- Id (PK)
- SentTo (int - contains the Id of a user from Users)
- SentBy (int - contains the Id of a user from Users)
- Content (text - contains the content of the message)
- SentDate (datetime - time of sent)
If a user gets 20 messages from another user, I would like the inbox to only show the user one time instead of 20 times. When the user then select the specified sender I redirect the user to another page that shows all messages from the specified user.
In the overview I want to display the Username of the sender, the Content of the last message and the SentDate. (Like facebook).
I have tried different things, but im new to this, so I only got to this code as shown below, where I still needs to make a join in the Users tabel to get the Username, and I still needs to get the Messages.SentDate and Messages.Content.
var Messages = (from r in db.Messages
where r.SentTo == Basic.SessionUserID()
Is it possible to do what I want in just one query? So it will return Users.Username, Messages.Content and Messages.SentDate.
I hope there is some Experts out there who can help me :-)