I forgot about joining a table that has multiple records that match, which creates multiple primary records, so you have to group. Anyways, I did this for the time being, don't know why I didn't think of it yesterday. I keep thinking that I didn't know the value of d.departmentID in order to make a match.
pResults =
(
from d in context.PRODUCT_DEPARTMENT
join da1 in context.PRODUCT_DEPARTMENT_AVATARS on d.Avatar_Primary equals da1.AvatarID into avatars
from da1 in avatars.DefaultIfEmpty()
where d.Deleted == false
orderby d.Name
select new model_departments_index
{
DepartmentID = d.DepartmentID,
Enabled = d.Enabled,
Deleted = d.Deleted,
Name = d.Name,
Description = d.Description,
Featured = d.Featured,
Rollback = d.Rollback,
Avatar_Primary_ID = d.Avatar_Primary,
Avatar_Primary_Image = new model_type_avatar
{
Name = da1.Name,
Alt = da1.Alt,
Data = da1.Data,
Type = da1.Type,
Url = da1.Url
},
Categories =
(
from c in context.PRODUCT_CATEGORY
where c.DepartmentID == d.DepartmentID
select new json_product_categories
{
text = c.Name,
value = c.CategoryID
}
).ToList()
}
).ToList();
pValue = pResults.Count();
