Need help in writing query on joined tables

Question

0.00/5 (No votes)

See more:

Hello

I have two Tables "Users" and "Letter". The "Letter" table has 2 FK related to the PK of "User" table: one is for "SendTo"; the other for "SentFrom". So if a letter has been posted, I can fetch the names of both recipient and sender for the Letter from the "User" table by their "ID".

I have written the code to fetch data for the user who has logged in. He can see his letters and who has sent them. But the problem is I don't know how to show the sender name. I can show his ID. How can I retrieve the sender name instead of ID?

What I have tried:

The code for joining "User" and "Letter" Tables:

C#

public IEnumerable ShowLetter()
        {
            using (Proj161021Entities _Proj161021Entities = new Proj161021Entities())
            {
                return _Proj161021Entities.UserLetter
                    .Where(c=>c.SendTo==1)
                                  .Select(l => new
                                  {
                                      SenderName = l.SentFrom,
                                      Subject = l.Subject,
                                      Date = l.Date
                                  }).ToList();
            }
        }

I need to show sentFrom By Name instead of ID from "user" table.

Thanks in Advance

Posted 25-Oct-16 8:07am

Ali Majed HA

Updated 25-Oct-16 10:44am

gggustafson

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

manu_dhobale · Answer 1 · 2016-10-25T10:44:00

I don't have exact database entities, you can try using joins. like I did here

C#

class User
{
    public int userId { get; set; }
    public string name { get; set; }
}
class Letter
{
    public int letterId { get; set; }
    public int SendTo { get; set; }
    public int SentFrom { get; set; }
    public string subject { get; set; }
    public DateTime date { get; set; }

}
public static void Main(string[] args)
{
    List<User> UserList = new List<User>
{
    new User{userId=1, name="nobel"},
    new User{userId=2, name="kunal"},
    new User{userId=3, name="rijul"},
    new User{userId=4, name="rohan"},
    new User{userId=5, name="manoj"}
};

    List<Letter> UserLetters = new List<Letter>{
    new Letter{letterId=1,subject="sibject text1", SendTo=1,SentFrom=5,date=DateTime.Today},
    new Letter{letterId=2,subject="sibject text2", SendTo=5,SentFrom=5,date=DateTime.Today},
    new Letter{letterId=3,subject="sibject text3", SendTo=1,SentFrom=5,date=DateTime.Today},
    new Letter{letterId=4,subject="sibject text4", SendTo=3,SentFrom=5,date=DateTime.Today},
    new Letter{letterId=5,subject="sibject text5", SendTo=3,SentFrom=5,date=DateTime.Today},
    new Letter{letterId=6,subject="sibject text6", SendTo=4,SentFrom=5,date=DateTime.Today}
};

    var letters = (from ul in UserLetters
                         join ulFrom in UserList on ul.SentFrom equals ulFrom.userId
                         join ulTo in UserList on ul.SendTo equals ulTo.userId
                         select new
                         {
                             SenderName = ulFrom.name,
                             recieverName = ulTo.name,
                             Subject = ul.subject,
                             Date = ul.date
                         }).ToList();



}