使用多个左连接在select new linq查询中设置DTO属性

Question

我在MVC 6 (beta8)控制器中有以下代码:

public IActionResult Get()
    {
        var districtsdetails = from districts in _ctx.District
            select new
            {
                Id = districts.Id,
                CountyFP = districts.County.FIPSCode,
                DirectorName = districts.DirectorName,
                Email = districts.Email,
                EstStudentPop = districts.EstStudentPop,
                Name = districts.Name,
                Phone = districts.Phone,
                Ranking = districts.Ranking,
                RANumber = districts.RANumber,
                SchoolCount = districts.SchoolCount,
                Coop = districts.Coop.Name,
                County = districts.County.Name,
                Distributors = (from district in _ctx.District
                               join districtdistributor in _ctx.DistrictDistributor on district.Id equals districtdistributor.DistrictId
                               into group1
                               from g1 in group1.DefaultIfEmpty()
                               join distributor in _ctx.Distributor on g1.DistributorId equals distributor.Id
                               into group2
                               from g2 in group2.DefaultIfEmpty()
                               where district.Id == districts.Id
                               select new { g2.Id, g2.Name })
            };
        if (districtsdetails == null)
            return HttpNotFound();

        return new JsonResult(districtsdetails);
    }

问题出在Distributors属性设置器中。

在我的上下文中有District、distritdistributor和Distributor实体(并且在我的db中有匹配的表)。District和Distributor之间存在多对多关系，而distritdistributor映射了两者之间的多对多关系。在我的distritdetailsdto中，我试图弥合distritdistributor差距，因此我可以只做distritdetailsdto。分销商…通过JsonResult()可以看到，所有这些都被序列化为Json。

In the Distributor =(…)我试图有效地重现这个SQL:

select (...)
from [District] D
left join [DistrictDistributor] DD on
    DD.DistrictId = D.Id
left join [Distributor] Db on
    Db.Id = DD.DistributorId
where id = 57

然而，在我的linq中，57是区。因为我要返回所有的区。请帮帮我，我快疯了!无论我沿着这些行尝试什么，都会产生一个:

HTTP错误502.3 -坏网关指定的CGI应用程序遇到错误，服务器终止进程。

Answer 1

我认为可以这样解决。

首先，您的查询-困难的方式。这里根本不需要左连接。如果您正在生成一个合并的结果集(SelectMany)，则需要它们，但由于情况并非如此，您可以使用以下命令并让EF发挥其魔力使其工作:

var query =
    from district in _ctx.District.AsNoTracking()
    select new
    {
        Id = district.Id,
        Name = district.Name,
        // the rest of the district related fields
        // ...
        Distributors =
        from dd in _cxt.DistrictDistributor
        where dd.DistrictId == district.Id
        join d in _ctx.Distributor on dd.DistributorId equals d.Id
        select new { d.Id, d.Name }
    };

第二种简单的方法。EF很酷的一件事就是用导航属性和适当配置的关系来描述你的模型。通过这种方式，您几乎可以忘记手动连接，而让EF做任何必要的事情来满足您的查询。在您的示例中，适当的模型将具有District.Distributors和Distributor.Districts导航属性，并且可以通过以下简单查询获得相同的结果:

var query =
    from district in _ctx.District.AsNoTracking()
    select new
    {
        Id = district.Id,
        Name = district.Name,
        // the rest of the district related fields
        // ...
        Distributors = district.Distributors.Select(d => new { d.Id, d.Name })
    };