在连接表上编写查询时需要帮助 [英] Need help in writing query on joined tables

问题描述

你好



我有两个表用户和信件。 Letter表有2个与User表的PK相关的FK：一个用于SendTo;另一个是SentFrom。因此，如果已发布信件，我可以通过ID从用户表中获取信件的收件人和发件人的姓名。



I已经编写了代码来为已登录的用户获取数据。他可以看到他的信件以及谁发送了这些信件。但问题是我不知道如何显示发件人姓名。我可以出示他的身份证明。如何检索发件人姓名而不是ID？



我尝试过：



加入用户和信函表的代码：

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:

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();
            }
        }

我需要显示sentFrom By Name而不是来自user表的ID。



先谢谢

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

Thanks in Advance

推荐答案

我没有确切的数据库实体，你可以尝试使用连接。就像我在这里做的那样

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

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();



}

这篇关于在连接表上编写查询时需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！