I have three tables below. How can I find out list of business for a given user with the entity context of UserNotifications? Is it possible?

In my DTO u can see this property, public string SenderUserPrimaryBusinessName { get; set; }, I am looking for how to fill this value which is residing in the BusinessInfo Table so the lookup is as follows, Notification --> User --> UserBusinessInfo --> BuinsessInfo (Name property is here)

I think the only option is load all users using UserNotifications context as it has a relationship with the User table and then iterate from the UserBusiness.

It needs a lot of database round trips, looking for a single query to load all this info in a single shot. Thanks for your help.

User
    id

UserBusiness
    user_id (multiple records for the same user_id)

Notifications
    user_id

More info with the class details and query I am trying under the context of notifications class

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

CodePudding user response：

Probably in the following way:

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

CodePudding user response：

You could improve navigation in your code first model.

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

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

then you could do

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

in your query. (or possibly SingleOrDefault if a single primary is guaranteed)