I have the following method:
public IEnumerable<MyRosterDTO> MyRosterGetCustomers(
IEnumerable<Guid> SalesRepIds,
IEnumerable<Guid> escrowOfficerIds,
IEnumerable<int> targetTypes,
IEnumerable<int> tagIds,
IEnumerable<Guid> custTypes,
IEnumerable<int> distListIds,
bool myExceptions)
{
customerStatusLog cslAlias = null;
customer custAlias = null;
customerOptions coAlias = null;
employee salesRepAlias = null;
Office officeAlias = null;
ListTags tagsAlias = null;
MyRosterDTO dto = null;
contactInformation contactInfo = null;
var myRosterQuery = _sms.CurrentSession.QueryOver<customer>(() => custAlias)
.JoinAlias(c => c.CustomerOptions, () => coAlias)
.Left.JoinAlias(c => c.SalesRep, () => salesRepAlias)
.JoinAlias(c => coAlias.Company, () => officeAlias)
.Left.JoinAlias(c => c.ContactInfo, () => contactInfo)
.Where(x => contactInfo.ContactTypeID == 8);
#region Where Clauses for parameters
if (myExceptions)
{
myRosterQuery.Where(c => salesRepAlias.Id == _ctx.User.Id && _ctx.User.Id != officeAlias.RepId);
}
else
{
if (SalesRepIds != null)
{
if (SalesRepIds.Contains(Guid.Empty))
{
if (SalesRepIds.Count() > 1) { myRosterQuery.Where(c => salesRepAlias.Id.IsIn(SalesRepIds.ToArray()) || salesRepAlias.Id == null); }
else
{ myRosterQuery.Where(c => salesRepAlias.Id == null); }
}
else
{ myRosterQuery.Where(c => salesRepAlias.Id.IsIn(SalesRepIds.ToArray())); }
}
}
if (escrowOfficerIds != null
&& escrowOfficerIds.Any())
{
myRosterQuery.Where(c => coAlias.PreferredEscrowOfficer.IsIn(escrowOfficerIds.ToArray()));
}
if (targetTypes != null
&& targetTypes.Any())
{
myRosterQuery.JoinAlias(c => c.CustomerStatusLog, () => cslAlias)
.Where(() => cslAlias.StatusId.IsIn(targetTypes.ToArray()));
}
if (tagIds != null
&& tagIds.Any())
{
myRosterQuery.JoinAlias(c => c.Tags, () => tagsAlias)
.Where(() => tagsAlias.Id.IsIn(tagIds.ToArray()));
}
if (custTypes != null
&& custTypes.Any())
{
myRosterQuery.Where(c => coAlias.cusTypeID.IsIn(custTypes.ToArray()));
}
if (distListIds != null
&& distListIds.Any())
{
var distCustIds = _sms.CurrentSession.Query<ListofAgents>()
.Where(loa => distListIds.Contains(loa.ListId))
.Select(loa => loa.AgentId)
.Distinct();
myRosterQuery.Where(c => c.Id.IsIn(distCustIds.ToArray()));
}
#endregion
return myRosterQuery.SelectList(list => list
.SelectGroup(c => c.Id).WithAlias(() => dto.Id)
.SelectGroup(c => c.FirstName).WithAlias(() => dto.FirstName)
.SelectGroup(c => c.LastName).WithAlias(() => dto.LastName)
.SelectGroup(() => officeAlias.Name).WithAlias(() => dto.CompanyName)
.SelectGroup(() => officeAlias.Address1).WithAlias(() => dto.Address1)
.SelectGroup(() => officeAlias.Address2).WithAlias(() => dto.Address2)
.SelectGroup(() => officeAlias.City).WithAlias(() => dto.City)
.SelectGroup(() => officeAlias.State).WithAlias(() => dto.State)
.SelectGroup(() => officeAlias.Zip).WithAlias(() => dto.Zip)
.SelectGroup(() => contactInfo.ContactData).WithAlias(() => dto.Phone)
.SelectGroup(() => salesRepAlias.FirstName).WithAlias(() => dto.SalesRepFirstName)
.SelectGroup(() => salesRepAlias.LastName).WithAlias(() => dto.SalesRepLastName)
)
.TransformUsing(Transformers.AliasToBean<MyRosterDTO>())
.List<MyRosterDTO>();
}
The query is nice and fast, but the problem arises where if the record doesn't have a contactInfo with a ContactTypeID of 8, then that record gets tossed out of the final results. (8 equates to Phone Number, fyi.)
What I need to be able to do is get the customer record, show all the customers, but provide phone numbers where available, and nothing where it's not.
The trick is that the contactInfo contains multiple types of contact information (email, phone, fax, etc), and without the .Where clause above, the output data explodes, showing a record on the screen for each contactInfo record a user has - so a user with 3 contactInfo types in the DB shows as 3 rows of output data.
This method is used to get a list of customers based on the input parameters, but instead of showing customers who don't have phone numbers, along side those who do, I only get those who have them.
If this were SQL, I'd just have a nice
LEFT JOIN ContactInfo as CI on customer.UserId = CI.UserId AND CI.ContactTypeID = 8
and my resultant query would show customers both with and without phone numbers.