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; }
    }
}

@urlreader I am confused and not sure how to integrate this with my current code and return it to API. — Sami
– Sami, Commented Nov 12, 2021 at 16:42
@Sami use this site to learn how to ask SQL, is very useful for now and future: meta.stackoverflow.com/questions/333952/… — Leandro Bardelli
– Leandro Bardelli, Commented Nov 12, 2021 at 16:47
@LeandroBardelli thank you for pointing out. I have fixed it — Sami
– Sami, Commented Nov 12, 2021 at 16:55

Leandro Bardelli · Accepted Answer · 2021-11-12 16:57:21Z

2

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

edited Nov 12, 2021 at 16:57

Leandro Bardelli

11.7k16 gold badges88 silver badges130 bronze badges

answered Nov 12, 2021 at 16:49

T. Nielsen

8855 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Leandro Bardelli Over a year ago

I retracted my downvote and upvote you because the example was wrong, not the data.

David Browne - Microsoft Over a year ago

Include is not needed or used when using a custom Select projection.

Sami Over a year ago

Hi @LeandroBardelli I have added the entity classes to my question. I tried to add the codes given by you to my code. I am getting some errors. because some of the tables are not accessible. Like on ThenInclude() t.Pests showing me error that ICollection<PeiCropsPest> does not contain a definition for 'Pests' . Maybe its because I have not shown you the entity classes and you tried to give me an idea. Maybe I need to change some codes

Leandro Bardelli Over a year ago

@Sami oh no, the answer was made for T. Nielsen

Sami Over a year ago

Thanks again. Haha. I mentioned the wrong person.

|

vernou · Accepted Answer · 2021-11-14 18:54:08Z

1

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

answered Nov 14, 2021 at 18:54

vernou

7,6925 gold badges40 silver badges74 bronze badges

3 Comments

Sami Over a year ago

Hey @vernou thank you so much. This one is working for me.

vernou Over a year ago

If this answer answered your question, can you accept it?

Sami Over a year ago

sorry, this is my second post. I did not know the answers can be accepted. Accepted your answer

Collectives™ on Stack Overflow

How to join two table in C# web API

2 Answers 2

6 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related