MVC C# web service join two tables and combine output

Question

I am working on iTunes/amazon/googlePlay affiliate program and want to add the AffiliateCode to the URL i have in my ExternalLinks Table within the web service. This means that the webservice first collects with the id_cd the different available links from the ExternalLinks[Table] and after is needs to loop over the AffiliateProgram[Table] for each of them to compare if there is a matching affiliate program available.

ExternalLink[Table]
public int id_external_link { get; set; }
public int id_cd { get; set; }
public string Name { get; set; }
public string URL { get; set; }

AffiliateProgram[Table]
public int ID { get; set; }
public string AffiliateName { get; set; }
public string AffiliateSite { get; set; }
public string AffiliateCode { get; set; }
public Nullable<int> CodePosition { get; set; }

The AffiliateProgram[Table]AffiliateName and the ExternalLinks[Table]Name are always the same.

This is how i was setting it up but i don't get it to work yet and got stuck now for the last days.

[Route("api/Cds/ExternalLinks/{id_cd}")]
    public async Task<List<ExternalLink>> GetAllExternalLinks(int id_cd)
    {
        List<ExternalLink> externallinks = await db.ExternalLink
            .Where(EL => EL.id_cd == id_cd)
            .ToListAsync();

        List<ExternalLink> ListAllExternalLinks = new List<ExternalLink>();
        for (int i = 0; i < 4; i++)
        {
           ExternalLink CDEL = new ExternalLink();

           if (CDEL.Name == "Itunes") {   

           var query = from EL in db.ExternalLink
                       join AP in db.AffiliateProgram on EL.Name equals AP.AffiliateName
                       select new
                       {
                        AffiliateName = AP.AffiliateName,
                        Name = EL.Name,
                        AffilateCode = AP.AffiliateCode,
                        URL = EL.URL
                       };

            URL = query.First().Name + "?mt=1&at=" + query.First().AffiliateCode + "&ct=" + id_cd;  

            }

            ListAllExternalLinks.Add(CDEL);

        }   
        ListAllExternalLinks.AddRange(externallinks);
        return ListAllExternalLinks;
    }

this results in the following in postmaster, with 4 empty results:

[
{},
{},
{},
{},
{
"id_external_link": 1459,
"id_cd": 13376,
"name": "Itunes",
"url": "https://itunes.apple.com/...countingcrows"
},
{
"id_external_link": 1460,
"id_cd": 13376,
"name": "Amazon",
"url": "https://www.amazon.com/....countingcrows"
},
{
"id_external_link": 1461,
"id_cd": 13376,
"name": "GooglePlay",
"url": "https://play.google.com/...countingcrows"
}
]

Answer 1

Your query is returning an anonymous object and you are accessing attributes not being returned by the query. You could change your query to return the following attributes and then access them:

        var query = from EL in db.ExternalLink
                    join AP in db.AffiliateProgram on EL.Name equals AP.AffiliateName
                    select new
                    {
                        AffiliateName = AP.AffiliateName
                        Name = EL.Name
                        AffiliateCode = AP.AffiliateCode
                    };