c# - 实体框架核心,左连接
问题描述
我有这三张桌子。
UserCredentials
桌子:
ID Username Password
------------------------
1 User1 1
2 User2 2
3 User3 3
4 User4 4
5 User5 5
Connection
桌子; 这里User1
和是该列User2
的外键:UserCredentials.ID
ID User1 User2
------------------
1 1 2
3 1 3
5 1 4
7 1 5
9 2 5
11 3 4
UserDetails
桌子; 这是列ID
的外键UserCredentials.ID
ID FullName Avatar LastSeen
---------------------------------------------------
1 User1 FullName 2 2021-09-09 00:38:00.000
2 User2 FullName 1 2021-09-09 01:38:00.000
3 User3 FullName 4 2021-09-24 04:38:00.000
4 User4 FullName 5 2021-09-24 18:38:00.000
5 User5 FullName 7 2021-09-24 06:40:00.000
数据上下文类:
namespace ChatWeb_MVC.DataModel
{
public partial class ChatWebAppContext : DbContext
{
private readonly string _connectionString;
public ChatWebAppContext(string connectionString)
{
_connectionString = connectionString;
}
public ChatWebAppContext(DbContextOptions<ChatWebAppContext> options)
: base(options)
{
}
public virtual DbSet<Connection> Connections { get; set; }
public virtual DbSet<UserCredential> UserCredentials { get; set; }
public virtual DbSet<UserDetail> UserDetails { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
optionsBuilder.UseSqlServer(_connectionString);
}
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
//Model creation code...
}
}
public partial class Connection
{
public Connection()
{
Messages = new HashSet<Message>();
}
public int Id { get; set; }
public int User1 { get; set; }
public int User2 { get; set; }
public virtual UserCredential User1Navigation { get; set; }
public virtual ICollection<Message> Messages { get; set; }
}
public partial class UserCredential
{
public UserCredential()
{
Connections = new HashSet<Connection>();
}
public int Id { get; set; }
public string Username { get; set; }
public string Password { get; set; }
public virtual Message Message { get; set; }
public virtual UserDetail UserDetail { get; set; }
public virtual ICollection<Connection> Connections { get; set; }
}
public partial class UserDetail
{
public int Id { get; set; }
public string FullName { get; set; }
public int Avatar { get; set; }
public DateTime LastSeen { get; set; }
public virtual UserCredential IdNavigation { get; set; }
}
internal ChatModel GetChatHistory(int userID)
{
ChatModel chat = new ChatModel();
chat.UserName = this.UserCredentials.FirstOrDefault(m => m.Id == userID).Username;
chat.UserID = userID;
List<ChatWeb_MVC.Models.UserDetail> connections = new List<ChatWeb_MVC.Models.UserDetail>();
// Problem
// I currently have login userID, by using It I want to get his/her friend's details and want to store them in `List<UserDetail> Connections`.
// For example, if the current login user ID is 3, then I want his friend [User ID: 1 and 4] details stored in `List<UserDetail> Connections`.
// I've tried this shown below, but I'm not getting the result I want:
var result = from conn in this.Connections
join userDetail in this.UserDetails on conn.User1 equals userDetail.Id into UserDetails
from m in UserDetails.DefaultIfEmpty()
where conn.User1 == userID || conn.User2 == userID
select new ChatWeb_MVC.Models.UserDetail
{
Id = m.IdNavigation.Id,
ConnectionID = conn.Id,
Username = m.IdNavigation.Username,
FullName = m.FullName,
Avatar = GetAvatar(m.Avatar),
LastSeen = m.LastSeen,
};
chat.Connections = result.ToListAsync().Result;
return chat;
}
}
这里我使用的查询没有给出我想要的结果。
数据模型类:
namespace ChatWeb_MVC.Models
{
public class ChatModel
{
public List<UserDetail> Connections { get; set; }
public string UserName { get; set; }
public int UserID { get; set; }
}
public class UserDetail
{
public int Id { get; set; }
public int ConnectionID { get; set; }
public string Username { get; set; }
public string FullName { get; set; }
public string Avatar { get; set; }
public DateTime LastSeen { get; set; }
}
}
解决方案
我正在发布答案,但我想要一个查询而不是三个。如果有人有更好的解决方案,请发布。
var friends = this.Connections.Where(m => m.User1 == userID).Select(m => new { conID = m.Id, friendID = m.User2 }).ToListAsync().Result;
friends.AddRange(this.Connections.Where(m => m.User2 == userID).Select(m => new { conID = m.Id, friendID = m.User1 }).ToListAsync().Result);
var result = from friend in friends
join userDetail in this.UserDetails on friend.friendID equals userDetail.Id into UserDetails
from m in UserDetails.DefaultIfEmpty()
select new ChatWeb_MVC.Models.UserDetail
{
Id = m.Id,
ConnectionID = friend.conID,
FullName = m.FullName,
Avatar = GetAvatar(m.Avatar),
LastSeen = m.LastSeen,
};
推荐阅读
- windows - 如何为 Windows 编译 SQLite ICU 扩展?
- c# - 出现错误:System.Exception:无法获取设备接口详细信息。错误代码:使用 HID.Net 和 USB.Net 时出现 122
- oracle - Springboot 问题:必须存在至少一个 JPA 元模型
- android - Android Retrofit:在 Recyclerview 中显示 JSON 数据
- css - AngularCLI 7.1.3 How to change color of owl-carousel dots?
- javascript - Angular - 有没有办法检查事件绑定的唯一属性?
- r - Subset dataframe based on several conditions in R
- node.js - ExpressJS:如何在 POST 请求后呈现新网页
- graphql - Q: Intellisense when using context.prisma
- javascript - 空白输入中断计时器