sql linq, data duplication on 1 particular record
-
I wrote this linq statement, it worked fine until I added another join. Guess I got it wrong. So first this i s list of departments. The first join works fine for the avatars, it's the 2nd join which is a list of categories that belong to the department. So there are only 6 departments, but I get 11. One record, #5 gets repeated 5 times. I can't see any errors in it unless I used the wrong join type, in which I'm not sure how to write the correct one.
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()
join pc in context.PRODUCT_CATEGORY on d.DepartmentID equals pc.DepartmentID into categories
from pc in categories.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 categories
orderby c.Name
select new json_product_categories
{
text = c.Name,
value = c.CategoryID
}
).ToList()
}
).ToList();
pValue = pResults.Count(); -
I wrote this linq statement, it worked fine until I added another join. Guess I got it wrong. So first this i s list of departments. The first join works fine for the avatars, it's the 2nd join which is a list of categories that belong to the department. So there are only 6 departments, but I get 11. One record, #5 gets repeated 5 times. I can't see any errors in it unless I used the wrong join type, in which I'm not sure how to write the correct one.
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()
join pc in context.PRODUCT_CATEGORY on d.DepartmentID equals pc.DepartmentID into categories
from pc in categories.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 categories
orderby c.Name
select new json_product_categories
{
text = c.Name,
value = c.CategoryID
}
).ToList()
}
).ToList();
pValue = pResults.Count();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();