LINQ 嵌套选择“新建”

Question

我有 2 个 db 表，例如家庭和汽车，并有一个名为家庭的类。它们通过外键相互连接。

家庭

身份证 |姓氏 |地址

汽车

身份证 |家庭编号 |名字

一个家庭可以拥有多辆汽车。

我想将其绑定到网格，我写了这个查询：

var data = from d in Master.Connection.Familys.Where(x => some conditions).OrderByDescending(d => d.ID)
                       select new Families
                       {
                           Id = d.ID,
                           Surname = d.Surname,
                           Address = d.Address,
                           Cars = ""
                       };
foreach (var item in data)
            {
                var cr = Master.Connection.Cars.Where(x => x.FamilyID == item.ID);
                if (!cr.Any()) continue;
                foreach (var crItem in cr)
                {
                    item.Cars += crItem.Name + ", ";
                }
            }
public class Families
{
    public int Id { get; set; }
    public string Surname { get; set; }
    public string Address { get; set; }
    public string Cars { get; set; }
}

由数据填充的网格。但是，"汽车"列为空。如何在第一个查询中执行此操作？或者，有没有一种简单的方法可以做到这一点？

Answer 1

试试这个：

var data = from d in Master.Connection.Familys.Where(x => some conditions).OrderByDescending(d => d.ID)
                   select new Families
                   {
                       Id = d.ID,
                       Surname = d.Surname,
                       Address = d.Address,
                       Cars = ""
                   };

data.ForEach(f => f.Cars = String.Join(",", Master.Connection.Cars.Where(x => x.FamilyID == item.ID).Select(cr => cr.Name).ToArray()));

Answer 2

您的 linq 正在返回IEnumerable<T>这是设计上的懒惰。换句话说，当执行第一个 linq 时，它不会立即提取数据。数据实际上是在第一次使用时获取的。作为副作用，item.Cars += crItem.Name + ", ";被覆盖。可能的解决方法是添加一个.ToList()以立即获取数据。

var data = Master.Connection.Familys
    .Where(x => some conditions)
    .OrderByDescending(d => d.ID)
    .Select(d => new Families
                   {
                       Id = d.ID,
                       Surname = d.Surname,
                       Address = d.Address,
                       Cars = ""
                   })
    .ToList();

更新您的代码正在var cr = Master.Conn ... item.ID);对数据库进行多次调用，这是完全不必要的。相反，您可以按如下方式使用GroupJoin：

var data = Connection.Families   //main table
                .Where(x => some conditions)
                .GroupJoin(Connection.Cars   //left outer join
                    , f => f.ID   //join condition
                    , c => c.FamilyID // join condition
                    , (f, c) => new {Family = f, CarNames = c.Select(o => o.Name)})
                    .OrderByDescending(d => d.Family.ID)
                    .ToList()
                    .Select(o => new Families
                    {
                        Id = o.Family.ID,
                        Surname = o.Family.SurName,
                        Address = o.Family.Address,
                        // Check possible null from left join
                        Cars = o.CarNames.Count() > 0 ? o.CarNames.Aggregate((a, b) => a + "," + b) : string.Empty
                    })                    
                .ToList();

PS：不要按照tbean的建议使用.Join() .Join()因为会产生INNER Join而不是LEFT OUTER Join。

Answer 3

另一种方式：

   private IQueryable<Family> GetFamilys()
    {
        return Master.Connection.Familys;
    }
    private IQueryable<Car> GetCars()
    {
         return Master.Connection.Cars;
    }
    public List<Families> FillFamilies()
    {
      var list = (from family in GetFamilys()
                   join car in GetCars() on family.ID equals car.familyID 
                   into groupJoin from subcar in groupJoin.DefaultIfEmpty()
                   select new Families
                  {
                   Id = family.ID,
                   Surname = family.Surname,
                   Address = family.Address,
                   Cars = subcar?.Name ?? String.Empty
                  }).ToList();
       return list;
     }