MVC C# Web 服务联接两个表并合并输出

Question

我正在研究iTunes/amazon/googlePlay联盟计划，并希望将AffiliateCode添加到Web服务中的ExternalLinks Table中的URL。这意味着网络服务首先从外部链接[表]中收集id_cd不同的可用链接，然后需要遍历每个链接的联盟计划[表]，以比较是否有匹配的联盟计划可用。

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

联盟计划[表]

会员名称和外部链接[表]名称始终相同。

这就是我设置它的方式，但我还没有让它工作，现在在最后几天被卡住了。

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

这将在邮局主管中产生以下结果，其中有 4 个空结果：

[
{},
{},
{},
{},
{
"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

您的查询正在返回一个匿名对象，并且您正在访问查询未返回的属性。您可以更改查询以返回以下属性，然后访问它们：

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

Answer 2

您有许多语法错误，这些错误甚至会阻止此代码进行编译，因此通常很难准确找出错误的地方。至少确保您的代码首先编译，然后如果您仍然没有得到所需的结果，我们可以为您提供帮助。

也就是说，看起来你正在使这比它需要的要困难得多。如果希望能够联接这两个表，则应在它们之间创建一个外键。如果特定链接属于特定的联盟计划，则实际创建该关系：

public class ExternalLink
{
    ...
    [ForeignKey("AffiliateProgram")]
    public int AffiliateProgramId { get; set; }
    public virtual AffiliateProgram AffiliateProgram { get; set; }
}

然后，您可以在查询时简单地包含联盟计划并调用它：

var externalLinks = db.ExternalLinks
    .Include(m => m.AffiliateProgram)
    .Where(m => m.id_cd == id_cd)
    .ToListAsync();

然后，当您迭代链接时：

var affiliateCode = link.AffiliateProgram.AffiliateCode;

超级简单。

Answer 3

好吧，它开始工作（实际上必须更改很多东西），感谢 S.Dav 解释如何连接其他数据库表并使用数据修改字符串。非常有用的一课。

    [Route("api/CDS/ExternalLink/{id_cd}")]
    public async Task<List<ExternalLink>> GetAllExternalLinks(int id_cd)
    {
        List<ExternalLink> ListAllExternalLinks = new List<ExternalLink>();
            foreach (var item in await db.ExternalLink
                .Where(EL => EL.id_cd == id_cd)
                .ToListAsync())
            {
            ExternalLink CDEL = new ExternalLink();
            CDEL.id_cd = item.id_cd;
            CDEL.Name = item.Name;
            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
                        };
                CDEL.URL = item.URL + query.First().Name + "?mt=1&at=" + query.First().AffilateCode + "&ct=" + id_cd;          
            }
            ListAllExternalLinks.Add(/CDEL);
        }
        return ListAllExternalLinks;
    }

在这种情况下，会产生一个完美的网络服务，会员代码连接到iTunes：-）很想有一个解决方案，实际上可以找到会员代码本身，但这现在有效：-）

[
 {
"id_cd": 13376,
"name": "Itunes",
"url": "https://itunes.apple.com/...../countingcrows?mt=1&at=1010lmNu&ct=13376"
 },
 {
"id_cd": 13376,
"name": "Amazon"
"url": "https://www.amazon.com...../countingcrows"
 },
 {
"id_cd": 13376,
"name": "GooglePlay"
 "url": "https://play.google.com/....countingcrows?id=AEIKS4IbfNk&hl=en"
 }
]