我需要对客户进行加入。地址为主要地址,并填充PrimaryAddress1和PrimaryCity字段。客户映射已经与地址类具有很多关系,但是我不想获取所有地址(性能问题)。
请帮忙..
类:
public class Customer
{
public Customer()
{
Addressess = new List<Address>();
}
public virtual int CustomerID { get; set; }
public virtual int? BranchID { get; set; }
public virtual int? CustTypeID { get; set; }
public virtual string CompanyName { get; set; }
public virtual string Prefix { get; set; }
public virtual string FirstName { get; set; }
public virtual string MiddleName { get; set; }
public virtual string LastName { get; set; }
public virtual string PrimaryAddress1 { get; set; }
public virtual string PrimaryCity { get; set; }
public virtual List<Address> Addresses { get; set; }
}
public class Address
{
public Address()
{
}
public virtual int LocationID { get; set; }
public virtual int? CustomerID { get; set; }
public virtual string LocationName { get; set; }
public virtual string Address1 { get; set; }
public virtual string Address2 { get; set; }
public virtual string Address3 { get; set; }
public virtual string City { get; set; }
public virtual bool Primary { get; set; }
}
对应:
public TblCustomerMap()
{
Table("tblCustomers");
LazyLoad();
Id(x => x.CustomerID).GeneratedBy.Identity().Column("CustomerID");
Map(x => x.ProfileID).Column("ProfileID");
Map(x => x.BranchID).Column("BranchID");
Map(x => x.DateEntered).Column("DateEntered");
Map(x => x.DateTerminated).Column("DateTerminated");
Map(x => x.CustTypeID).Column("CustTypeID");
Map(x => x.CompanyName).Column("CompanyName").Not.Nullable().Length(50);
Map(x => x.Prefix).Column("Prefix").Not.Nullable().Length(50);
Map(x => x.FirstName).Column("FirstName").Not.Nullable().Length(50);
Map(x => x.MiddleName).Column("MiddleName").Not.Nullable().Length(50);
Map(x => x.LastName).Column("LastName").Not.Nullable().Length(50);
HasMany(x => x.Address).KeyColumn("CustomerID");
Map(x => x.PrimaryAddress1).Column("PrimaryAddress1") // from table tbladdress where address is primary and get data from address1 column
Map(x => x.PrimaryCity).Column("PrimaryCity") // from table tbladdress where address is primary and get data from city column
}
查询:
var query = session
.QueryOver<Customer>(() => customer)
.JoinQueryOver(() => customer.Addresses, () => address)
.Where(() => address.Primary)
.List();
foreach (var customer1 in query)
{
customer1.PrimaryAddress1 = customer1.Addresses[0].Address1;
customer1.PrimaryCity = customer1.Addresses[0].City;
customer1.PrimaryState = customer1.Addresses[0].StateOrProvince;
}
新查询:
var query = session.Query<Customer>()
.SelectMany(c => c.Addresses,
(c, a) => new {c, a})
.Where(cust => cust.a.Primary)
.Select(item => new CustomerView()
{
CustomerID = item.c.CustomerID,
CompanyName= item.c.CompanyName,
FirstName= item.c.FirstName,
LastName=item.c.LastName,
Address1=item.a.Address1,
Address2=item.a.Address2,
Address3= item.a.Address3,
City= item.a.City,
StateOrProvince= item.a.StateOrProvince
});
return query.ToList();
最佳答案
有(至少)两种方法可以实现此目的。
1)扩展DB和调整NHibernate映射更加直观和可读。我们将需要创建新的视图viewPrimaryAddress
。
SELECT ***columns*** FROM [tbladdress] WHERE Primary = true
客户映射将如下所示:
Join("[viewPrimaryAddress]",
{
m.Fetch.Join();
m.KeyColumn("CustomerID");
m.Map(t => t.PrimaryAddress1).Column("PrimaryAddress1");
m.Map(t => t.PrimaryCity).Column("PrimaryCity");
});
就是这样。将发出一条SQL语句,因此无需加载Address集合
2)第二种方法的概述
第二种方法将通过新的类映射来创建该视图,这有点复杂,但是只能在应用程序端完成(C#和NHiberante)。
将创建新的类
PrimaryAddress
,并在类级别定义中包含ilter(xml映射示例:<class name="PrimaryAddress" ... where="Primary = true" >
然后,我们可以使用与
Customer
的新的多对一关系扩展PrimaryAddress
。因此,将通过在WHERE子句中过滤的SQL select获得“ PrimaryAddress1”和“ PrimaryCity”列的属性。扩展:
后续步骤应指导您如何创建新映射,并将主地址定位为一对一属性
1)C#PrimaryAddress:
public class PrimaryAddress
{
public virtual Customer Customer { get; set; }
public virtual string Address { get; set; }
public virtual string City { get; set; }
}
2)对应:
public TblPrimaryAddressMap()
{
Table("tbladdress");
LazyLoad();
Where("Primary = 1");
// Id as AddressId
References(x => x.Customer).Column("CustomerID");
Map(x => x.Address).Column("PrimaryAddress1")
Map(x => x.PrimaryCity).Column("PrimaryCity")
}
3)
Customer
的新属性public class Customer
{
..
public virtual PrimaryAddress PrimaryAddress { get; set; }
4)
Customer
的新映射公共TblCustomerMap()
{
...
HasOne(x => x.PrimaryAddress)
此时,当您从会话获取
Customer
时。您可以访问customer.PrimaryAddress.Address
customer.PrimaryAddress.City
我主要使用XML映射。但是从这些方面来看,这个概念应该很清楚...使用某些
fetch="join"
,您可以在一个SQL select中加载Customer
及其PrimaryAddress
如果您甚至需要客户属性
Customer.PrimaryAddress1
,只需将PrimaryAddress.Address包装在getter中。您将获得的更多收益是,可以通过此新属性
PrimaryAddress
进行过滤和排序注意:这种方法对于缓存是脆弱的。原因是,您将更改实际的
Address
实体时,没有建立用于撤消PrimaryAddress
的机制。而且,您还应强制业务定位器仅允许将一个地址设置为primary = true的客户