How to join two table in C# web API

Question

I am new at C# entity framework. I am trying to build an API, but stuck in retrieving data from relational table.
I have a pei_crops table in MS SQL database, where c_id is the primary key. I have another table called pei_pests, where p_id is the primary key. Another table is pei_cropspests where I have built relation for which pest attack which crop. Multiple pests can attack one crop and one pest can attack multiple crops. In this pei_cropspests table I have put p_id as primary and foreign key and c_id as primary and foreign key as well.

pei_crops table:

c_id	c_name	c_description
1	Corn	NULL

pei_pests table:

p_id	p_name	p_URL
1	pest1	NULL
2	pest2	NULL

pei_cropspests table:

p_id	c_id
1	1
2	1

Now In my API I want to show something like that

[
{
    "cId":1,
    "pests":[
             {
               "pId":1,
               "pName": pest1,
               "pURL": null
             },
             {
               "pId":2,
               "pName": pest2,
               "pURL": null
             }

           ]
}
]

My get request looks like this so far in C# web API project:

[Route("Getspecific/{cropId}")]
[HttpGet]
public async Task<IActionResult> GetSpecific(int cropId)
{
    var cropDetails = await _db.PeiCrops.Where(c=>c.CId == cropId).Include(i=>i.PeiCropspests).ToListAsync();
    return Ok(cropDetails);
}

This code returns me only the pID and URL of the pest that effects cID number 1. But I also want the pest name and URL along with their id.

Could someone please show me how to do it. Maybe there is some way to join two table and show the data? I just do not know how to do it in C#. Any help appreciated. Thank you.

Entities class: PeiCrop:

using System;
using System.Collections.Generic;

#nullable disable

namespace PEI_API.EF
{
    public partial class PeiCrop
    {
        public PeiCrop()
        {
            PeiCropimages = new HashSet<PeiCropimage>();
            PeiCropsdiseases = new HashSet<PeiCropsdisease>();
            PeiCropspests = new HashSet<PeiCropspest>();
        }

        public int CId { get; set; }
        public string CName { get; set; }
        public string CPhotoUrl { get; set; }
        public string CDescription { get; set; }

        public virtual ICollection<PeiCropimage> PeiCropimages { get; set; }
        public virtual ICollection<PeiCropsdisease> PeiCropsdiseases { get; set; }
        public virtual ICollection<PeiCropspest> PeiCropspests { get; set; }
    }
}

PeiPest:

using System;
using System.Collections.Generic;

#nullable disable

namespace PEI_API.EF
{
    public partial class PeiPest
    {
        public PeiPest()
        {
            PeiCropspests = new HashSet<PeiCropspest>();
            PeiPestimages = new HashSet<PeiPestimage>();
        }

        public int PId { get; set; }
        public string PName { get; set; }
        public string PPhotoUrl { get; set; }
        public string PDescription { get; set; }

        public virtual ICollection<PeiCropspest> PeiCropspests { get; set; }
        public virtual ICollection<PeiPestimage> PeiPestimages { get; set; }
    }
}

PeiCropspest:

using System.Collections.Generic;

#nullable disable

namespace PEI_API.EF
{
    public partial class PeiCropspest
    {
        public int PId { get; set; }
        public int CId { get; set; }

        public virtual PeiCrop CIdNavigation { get; set; }
        public virtual PeiPest PIdNavigation { get; set; }
    }
}

Answer 1

You're pretty close, but you're also not entirely using EF like you could, I mean you do not actually have to make the relationship table yourself but could refer directly to a list of the entity pei_pests from the entity pei_crop and let EF create the other.

//Example just getting one property from each, 
//but you can new a composite return type up if you wish, using select
var cropDetails = await _db.PeiCrops
                            .Where(c=>c.CId == cropId)
                            .Include(i=>i.PeiCropspests)
                            .ThenInclucde(t => t.Pests)
                            .Select(s => new { CropId = s.p_id, PestName = s.PeiCropsPests.Pest.p_name  })
                            .ToListAsync();

https://learn.microsoft.com/en-us/dotnet/api/system.linq.enumerable.select?view=net-5.0

Answer 2

First, you need to configure the relationships :

class MyContext : DbContext
{
    ...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<PeiCropspest>()
            .HasKey(cp => new { cp.PId, cp.CId });

        //Configure one PeiPest to many PeiCropspest
        modelBuilder.Entity<PeiCropspest>()
            // Specify PeiCropspest's navigation property to one PeiPest
            .HasOne(cp => cp.PIdNavigation)
            // Specify PeiPest's navigaton property to many PeiCropspest
            .WithMany(p => p.PeiCropspests)
            // Specify PeiCropspest's navigation property
            // to use this PeiCropspest's property as foreign key
            .HasForeignKey(cp => cp.PId);

        //Configure one PeiCrop to many PeiCropspest
        modelBuilder.Entity<PeiCropspest>()
            // Specify PeiCropspest's navigation shadow property to one PeiCrop
            .HasOne<PeiCrop>()
            // Specify PeiCrop's navigaton property to many PeiCropspest
            .WithMany(c => c.PeiCropspests)
            // Specify PeiCropspest's navigation shadow property
            // to use this PeiCropspest's property as foreign key
            .HasForeignKey(cp => cp.CId);
    }

    public DbSet<PeiCrop> PeiCrops { get; set; }
}

Then you can do a projection in the LINQ query :

public async Task<IActionResult> GetSpecific(int cropId)
{
    var cropDetails = await _db.PeiCrops
    .Where(c=>c.CId == cropId)
    .Select(c => new {
        cId = c.CId,
        pests = c.PeiCropspests.Select(p => new {
            pId = p.PIdNavigation.PId,
            pName = p.PIdNavigation.PName,
            pUrl = p.PIdNavigation.PPhotoUrl
        })
    })
    .ToListAsync();
    return Ok(cropDetails);
}

Do you know? From EF Core 5, it's possible to do many to many relationship without intermediary entity. This can simplify your entity model. cf. the documentation