I have a Database Schema like this.

I am working on gettingDetailByPostID API which needs to return 1 Object as follows.

    Guid PostId;
    Guid UserId;
    String? Username;
    String? MediaPath;
    int Likes; // count the number of likes of the post
    String? Caption;
    bool IsLiked;
    IEnumerable<Comment>? Comments; // List of comments in the post

I am having difficulty in joining the tables together to get more data from the User and Comment tables. How can I do this with ef core 6.

CodePudding user response：

firsr have a DbSet property for in your db context inherited class which may look like this public DbSet<Post> Posts { get; set; }.

then inject the dbcontext to your required service as constructor injection.

the you can do var post = yourDbContenxt.Posts.FirstOrDefaultAsync(p=>p.PostId==yourPostId);

CodePudding user response：

The best way to do this would be in a single transaction. I believe you already have configured EF with navigation and you should have an entities like this

    public class Post
    {
        public int post_id { get; set; }
        public int user_id { get; set; }
        public string caption { get; set; }
        public string? media_path { get; set; }
        virtual public List<Like> Likes { get; set; }

    }
    public class Like
    {
        public int post_id { get; set; }
        public int user_id { get; set; }
        virtual public Post Post { get; set; }
        virtual public UserProfile UserProfile { get; set; }
    }
    public class UserProfile
    {
        public int post_id { get; set; }
        public int user_id { get; set; }
        public string username { get; set; }
        virtual public Post Post { get; set; }
        virtual public List<Like> Likes { get; set; }
    }

If not, you should have a look on the microsoft documentation or on this great tutorial site

If it is configured with navigation (but without lazy loading to avoid infinite loop, and to have an ecological use of your server ressources :) )

Then you can build your object within the same context instanciation such as this short exemple

 public class DetailedPost
    {
        Guid PostId;
        Guid UserId;
        String? Username;
        String? MediaPath;
        int Likes; // count the number of likes of the post
        String? Caption;
        bool IsLiked;
        IEnumerable<Comment>? Comments; // List of comments in the post
        
        public DetailedPost GettingDetailByPostID(int id)
        {
            DbContext context = new DbContext();

            UserProfile userprofile = context.UserProfile
                .Include(user => user.Posts.Where(p => p.post_id == id)) // Select post by id
                .ThenInclude(post => post.Comments) // Include the Comment Db relative to post selected
                .Include(user => user.Likes); // Include like db relative to userProfile db

// Return object construction
            return new DetailedPost()
            {
                PostId = userprofile.post_id,
                UserId = userprofile.user_id,
                Username = userprofile.username,
                Caption = userprofile.Posts.FirstOrDefault().Caption,
                Likes = userprofiles.Likes.Count(),

            };

        }
    }

I hope it helps !