如何将查询的SQL结果映射到嵌套模型

Question

我有一个SQL查询如下:

SELECT d.domain_id, d.name, sd.subdomain_id, sd.name, sd.[description] 
FROM [myDB].SubDomains sd INNER JOIN [myDB].Domains d 
ON d.domain_id = sd.domain_id ORDER BY d.[name], sd.[name]

返回如下结果:

domain_id | name | subdomain_id |name | description
1             A       11          AB      AB description
1             A       12          AC      AC description
2             B       21          BA      BA description

我有c#模型如下:

// This is Domain Mapping as we can have list of domains
public class HigherLevel
{
public List<DomainModel> Domains { get; set; }
}
public class DomainModel
{
public int Id { get; set; }
public string Name { get; set; }
public List<SubDomainModel> SubDomains { get; set; }
}
public class SubDomainModel
{
public int Id { get; set; }
public string Name { get; set; }
public string Description { get; set; }
}

我想保持模型相同的API我需要响应为:

HigherLevel: {
domains: [{
id:, 
name:,
subdomains: [{ id: ,name: , description:  }]
},
{   id:, 
name:,
subdomains: [{ id: ,name: , description:  },
{ id: ,name: , description:  }]
},
id:, 
name:,
subdomains: [{ id: ,name: , description:  }]
},
{   id:, 
name:,
subdomains: [{ id: ,name: , description:  },
{ id: ,name: , description:  }]
}]
}

在Higher Level上，可以有多个域，其中可以有多个子域。我想知道我如何才能返回SQL结果映射到模型，以便它可以把结果放入域的嵌套列表->子域。

Answer 1

根据您的对象模型，您想要的(json)结构将是:

HigherLevel: {
domains: [{
id:, 
name:,
subdomains: [{ id: ,name: , description:  }]
},
{   id:, 
name:,
subdomains: [{ id: ,name: , description:  },
{ id: ,name: , description:  }]
},
id:, 
name:,
subdomains: [{ id: ,name: , description:  }]
},
{   id:, 
name:,
subdomains: [{ id: ,name: , description:  },
{ id: ,name: , description:  }]
}]
}

接下来，获得相同结果集的Linq将是:

var data = (from d in db.Domains
join sd in db.SubDomains on d.DomainId equals sd.DomainId
orderby d.Name, sd.Name
select new 
{ 
Id = d.DomainId, 
Name=d.name,
SubdomainID =  sd.SubdomainId, 
SubDomainName = sd.Name,
Description = sd.Description
}).ToList();

现在，我找不到一种简单的方法来将数据移动到你想要的结构中，所以让我们在循环中完成。

var domainId = -1;
var higherLevels = new HigherLevel();
higherLevels.Domains = new List<Domain>(); // should be handled in ctor
Domain domain = null;
foreach(var row in data)
{
if (row.DomainId != domainId)
{
domainId = row.DomainId;
domain = new Domain();
domain.SubDomains = new List<SubDomain>(); // again, in ctor
domain.Id = row.Id;
domain.Name = row.Name;
higherLevels.Domains.Add(domain);
}
var sd = new SubDomain();
sd.Id = row.SubDomainId;
sd.Name = row.SubDomainName;
sd.Description = row.Description;
domain.SubDomains.Add(sd);
}
return higherLevels;