先看看我的班级结构。
public class CustomerBase
{
    public int CustomerID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Address1 { get; set; }
    public string Address2 { get; set; }
    public string Phone { get; set; }
    public string Fax { get; set; }
}
public class Customer : CustomerBase
{
    public virtual List<Addresses> Addresses { get; set; }
}
public class Addresses
{
    [Key]
    public int AddressID { get; set; }
    public string Address1 { get; set; }
    public string Address2 { get; set; }
    public bool IsDefault { get; set; }
    public virtual List<Contacts> Contacts { get; set; }
    public int CustomerID { get; set; }
    public virtual Customer Customer { get; set; }
}
public class Contacts
{
    [Key]
    public int ContactID { get; set; }
    public string Phone { get; set; }
    public string Fax { get; set; }
    public bool IsDefault { get; set; }
    public int AddressID { get; set; }
    public virtual Addresses Customer { get; set; } 
}
public class TestDBContext : DbContext
{
    public TestDBContext()
        : base("name=TestDBContext")
    {
    }
    public DbSet<Customer> Customer { get; set; }
    public DbSet<Addresses> Addresses { get; set; }
    public DbSet<Contacts> Contacts { get; set; }
}现在,我试图填充我的客户群,但得到了错误。
    var bsCustomer1 = (from c in db.Customer
                       where (c.CustomerID == 2)
                       select new
                       {
                           CustomerID = c.CustomerID,
                           FirstName = c.FirstName,
                           LastName = c.LastName,
                           Addresses = (from ad in c.Addresses
                                        where (ad.IsDefault == true)
                                        from cts in ad.Contacts
                                        where (cts != null && cts.IsDefault == true)
                                        select ad).ToList(),
                       }).ToList()
            .Select(x => new CustomerBase
            {
                CustomerID = x.CustomerID,
                FirstName = x.FirstName,
                LastName = x.LastName,
                Address1 = x.Addresses.Select(a => a.Address1).SingleOrDefault(),
                Address2 = x.Addresses.Select(a => a.Address2).SingleOrDefault(),
                Phone = x.Addresses.Select(c => c.Contacts.Select(cd => cd.Phone).SingleOrDefault()),
                Fax = x.Addresses.Select(c => c.Contacts.Select(cd => cd.Fax).SingleOrDefault())
            }).ToList();根据我的情况,单个客户可能有多个地址,但应该有一个默认地址,我正在提取。一个地址可能有多个联系人的细节,但应该有一个默认的一个,我正在拉。
address1、address2、Phone和Fax都属于基本客户类别。我想从基于的地址和联系人表中提取单个数据,是真的,并填充我的客户。我在linq方面不是很好。所以不能组成查询。请帮我作曲。谢谢
发布于 2016-09-07 15:38:07
尝试下面的代码,猜测它可能适合您的要求。
 var bsCustomer1 = db.Customer.Where(p => p.CustomerID == 2)
        .Select(x => new CustomerBase
        {
            CustomerID = x.CustomerID,
            FirstName = x.FirstName,
            LastName = x.LastName,
            Address1 = x.Addresses.First(a => a.IsDefault).Address1,
            Address2 = x.Addresses.First(a => a.IsDefault).Address2,
            Phone = x.Addresses.First(a => a.IsDefault).Contacts.First(c => c.IsDefault).Phone),
            Fax = x.Addresses.First(a => a.IsDefault).Contacts.First(c => c.IsDefault).Fax)
        }).ToList();发布于 2016-09-07 16:00:58
当您说:“我想从地址和联系人表中提取单个数据时,基于isdefault的数据是真的,并填充我的客户”,这可能意味着两件事,而不知道您的实际意义:
好吧,关于EF的几件事:
要创建新的对象,您不必引用它上面或下面的对象。您只需要创建它,然后用它更新数据库。
因此,对于EF的一个例子,假设我有两个数据库表:
我有一个表tePerson,其中有字段: PersonId、FirstName、LastName、OrderId。这个表有值
1   Brett   X 1
2   Emily   X 2
4   Ryan    Y 1
10  Mark    Z 1 OrderId是表teOrder的外键,只有两个字段: OrderId和Description。
1   Shirt
2   Dress从T4生成的POCO对象是:
public partial class tePerson
{
    public int PersonId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public Nullable<int> OrderId { get; set; }
    public virtual teOrder teOrder { get; set; }
}需要注意的是,“虚拟teOrder”为我指出了另一个POCO:
public partial class teOrder
{
    public teOrder()
    {
        this.tePersons = new HashSet<tePerson>();
    }
    public int OrderId { get; set; }
    public string Description { get; set; }
    public virtual ICollection<tePerson> tePersons { get; set; }
}示例仅用于投影和从上下文中更新数据库,以及更新下面的数据库。需要记住的关键是,在使用EF执行“选择”时,只有执行像“ToList()”这样的方法才能实现这些对象。否则,它们是上下文db集,您无法将其链接起来。
public class OtherPerson
{
  public int PersonId { get; set; }
  public string PersonLongName { get; set; }
  public teOrder Order { get; set; }
}
static void Main(string[] args)
{
  using (var context = new TesterEntities())
  {
    //Say I just want to project a new object with a select starting from orders and then traversing up.  Not too hard
    var newObjects = context.teOrders.Where(order => order.OrderId == 1)
      //SelectMan will FLATTEN a list off of a parent or child in a one to many relationship
      .SelectMany(peopleInOrderOne => peopleInOrderOne.tePersons)
      .ToList()
      .Select(existingPerson => new OtherPerson
      {
        PersonId = existingPerson.PersonId,
        PersonLongName = $"{existingPerson.FirstName} {existingPerson.LastName}",
        Order = existingPerson.teOrder
      })
      .ToList();
    newObjects.ForEach(newPerson => Console.WriteLine($"{newPerson.PersonId} {newPerson.PersonLongName} {newPerson.Order.Description}"));
    // Just an action clause to repeat find items in my context, the important thing to note is that y extends teOrder which is another POCO inside my POCO
    Action<string, List<tePerson>> GetOrdersForPeople = (header, people) => 
    {
      Console.WriteLine(header);
      people.ForEach(person => Console.WriteLine($"{person.FirstName} {person.LastName} {person.teOrder.Description}"));
      Console.WriteLine();
    };
    //I want to look at a person and their orders.  I don't have to do multiple selects down, lazy loading by default gives me a child object off of EF
    GetOrdersForPeople("First Run", context.tePersons.ToList());
    //Say I want a new order for a set of persons in my list?
    var newOrder = new teOrder { Description = "Shoes" };
    context.teOrders.Add(newOrder);
    context.SaveChanges();
    //Now I want to add the new order
    context.tePersons.SingleOrDefault(person => person.PersonId == 1).teOrder = newOrder;
    context.SaveChanges();
    //I want to rexamine now
    GetOrdersForPeople("After changes", context.tePersons.ToList());
    //My newOrder is in memory and I can alter it like clay still and the database will know if I change the context
    newOrder.Description = "Athletic Shoes";
    context.SaveChanges();
    GetOrdersForPeople("After changes 2", context.tePersons.ToList());
    //Say I want to update a few people with new orders at the same time
    var peopleBesidesFirst = context.tePersons.Where(person => person.PersonId != 1).ToList();
    var firstPersonInList = context.tePersons.Where(person => person.PersonId == 1).ToList();
    var newOrders = new List<teOrder> {
      new teOrder { Description = "Hat", tePersons = peopleBesidesFirst },
      new teOrder { Description = "Tie", tePersons = firstPersonInList }
      };
    context.teOrders.AddRange(newOrders);
    context.SaveChanges();
    GetOrdersForPeople("After changes 3", context.tePersons.ToList());
  }
  Console.ReadLine();
}https://stackoverflow.com/questions/39373591
复制相似问题