EntityFrameworkCore關系并使用代碼優先方法中的查找獲取詳細資訊

我在下面有三個表。如何查找具有物體背景關系的給定用戶的業務串列UserNotifications？是否可以？

在我的 DTO 中你可以看到這個屬性， public string SenderUserPrimaryBusinessName { get; 放; }，我正在尋找如何填充駐留在 BusinessInfo 表中的這個值，因此查找如下，Notification --> User --> UserBusinessInfo --> BuinsessInfo (Name property is here)

我認為唯一的選擇是使用UserNotifications背景關系加載所有用戶，因為它與表有關系，User然后從UserBusiness.

它需要大量的資料庫往返，尋找一個查詢來一次性加載所有這些資訊。謝謝你的幫助。

User
    id

UserBusiness
    user_id (multiple records for the same user_id)

Notifications
    user_id

更多關于類詳細資訊和查詢的資訊我在通知類的背景關系中嘗試

public abstract class BaseEntity : BaseExEntity
{
    public BaseEntity()
    {
        Id = System.Guid.NewGuid();
    }
    [Key]
    public Guid Id { get; set; }
    public bool IsDeleted { get; set; } = false;
}

public class User : BaseEntity
{
    [MaxLength(50)]
    public string FullName { get; set; }      

}

public class UserBusinessInfo : BaseEntity
{ 
    public Guid UserId { get; set; }
    
    public Guid BusinessInfoId { get; set; }

    public User User { get; set; }

    public BusinessInfo BusinessInfo { get; set; }

    public bool IsPrimary { get; set; }

}

public class Notification : BaseEntity
{
    public Guid RecieverUserId { get; set; }

    public User RecieverUser { get; set; }

    public bool ViewStatus { get; set; }

    public NotificationType NotificationType { get; set; }
    public string EventType { get; set; }

    public Guid SenderUserId { get; set; }
    public User SenderUser { get; set; }

    [Column(TypeName = "jsonb")]
    public string NotificationData { get; set; }
}

 public class BusinessInfo : BaseEntity
    {
      
        [Required]     
        public string Name { get; set; }

       
    }

  public class NotificationExistingDto : BaseEntity
{        
    public bool ViewStatus { get; set; }
    public NotificationType NotificationType { get; set; }
    public string EventType { get; set; }
    public Guid SenderUserId { get; set; }
    public string SenderUserProfilePictureUrl { get; set; }
    public string SenderUserFullName { get; set; }
    public string SenderUserName { get; set; }
    public string SenderUserPrimaryBusinessName { get; set; }
    public Guid RecieverUserId { get; set; }
    public string RecieverUserProfilePictureUrl { get; set; }
    public string RecieverUserFullName { get; set; }
    public string RecieverUserName { get; set; }
    public string NotificationData { get; set; }
    public ProfileType SenderUserProfileType { get; set; }
}

IQueryable<Notification> notifications;
var pagedData = await notifications
        .Select( n => new NotificationExistingDto()
        {
            Id = n.Id,
            CreatedAt = n.CreatedAt,
            EventType = n.EventType,
            IsDeleted = n.IsDeleted,
            ModifiedAt = n.ModifiedAt,
            NotificationType = n.NotificationType,
            NotificationData = n.NotificationData,
            RecieverUserId = n.RecieverUserId,
            RecieverUserFullName = n.RecieverUser.FullName,
            RecieverUserName = n.RecieverUser.UserName,
            RecieverUserProfilePictureUrl = n.RecieverUser.ProfilePictureUrl,
            SenderUserFullName = n.SenderUser.FullName,
            SenderUserId = n.SenderUserId,
            SenderUserName = n.SenderUser.UserName,
            SenderUserProfilePictureUrl = n.SenderUser.ProfilePictureUrl,
            //SenderUserPrimaryBusinessName = n.SenderUserBusinessInfo.Where(b => b.IsPrimary && b.UserId == n.SenderUserId).FirstOrDefault().BusinessInfo.Name,
            ViewStatus = n.ViewStatus,
            SenderUserProfileType = n.SenderUser.ProfileType

        })
        .Skip((validFilter.PageNumber - 1) * validFilter.PageSize)
        .Take(validFilter.PageSize)
        .ToListAsync();

uj5u.com熱心網友回復：

大概有以下幾種方式：

new NotificationExistingDto()
{
   ...
   SenderUserPrimaryBusinessName = context.UserBusinessInfo
      .FirstOrDefault(b => n.SenderUser.Id == b.UserId && b.IsPrimary && b.UserId == n.SenderUserId).BusinessInfo.Name,
   ...            
}

uj5u.com熱心網友回復：

您可以改進代碼優先模型中的導航。

public class User : BaseEntity
{
    [MaxLength(50)]
    public string FullName { get; set; }      

    // new navigation collection
    public ICollection<UserBusinessInfo> Infos { get; set; }
}

那么你可以做

SenderUserPrimaryBusinessName = n.SenderUser.Infos.FirstOrDefault(b => b.IsPrimary).BusinessInfo.Name,

在您的查詢中。（或者SingleOrDefault如果保證有一個主節點）

標籤：实体框架 实体框架核心

上一篇：SQLGETDATE()將01/01/00112:00:00AM插入資料庫

下一篇：EntityFrameworkCore-擁有的孩子和另一個連鎖擁有的孩子之間的關系