How do I get all the records from Categories that have at least one Variable EndDate null? (so if all Varibles are end dated, the Category should not be selected)
I have these 3 entities
public class CategoryType
{
public int CategoryTypeId { get; set; }
public string? Name { get; set; }
public virtual ICollection<Category> Categories { get; set; }
}
public class Category
{
public int CategoryId { get; set; }
public string? Name { get; set; }
public DateTime? EndDate { get; set; }
public int CategoryTypeId { get; set; }
public virtual CategoryType CategoryType { get; set; }
public virtual ICollection<Variable> Variables { get; set; }
}
public class Variable
{
public int VariableId { get; set; }
public string? Name { get; set; }
public DateTime? EndDate { get; set; }
public string Type { get; set; }
public int CategoryId { get; set; }
public virtual Category Category { get; set; }
public virtual ICollection<SearchFilter> SearchFilters { get; set; }
}
I have a mapper that gets called from the service GetCategoryTypes
private async Task<List<FilterDataCategoryTypeDTO>> GetCategoryTypes()
{
List<FilterDataCategoryTypeDTO> result = new();
var categoryTypeListEntity = await _context.CategoryTypes
.OrderBy(o=> o.Name)
.ToListAsync();
var catTypes = categoryTypeListEntity
.Select(s => ManualMapper.ConvertFilterDataCategoryTypeEntityToCategoryDTO(s))
.ToList();
return catTypes;
}
public static DTO.FilterDataCategoryTypeDTO? ConvertFilterDataCategoryTypeEntityToCategoryDTO(Entities.CategoryType entity)
{
return entity == null ? null : new DTO.FilterDataCategoryTypeDTO
{
CategoryTypeId = entity.CategoryTypeId,
Name = entity.Name,
Categories = entity.Categories
.OrderBy(o=> o.Name)
.Where(c => c.EndDate == null && c.Variables.Where(r => r.CategoryId == c.CategoryId).Count() == 0)
.Select(s => ConvertFilterDataCategoryEntityToCategoryDTO(s))
.ToList()
}
}
For example CategoryId=24 should not be select because all EndDates are filled, should only return it if at least one row has EndDate NULL
VariableId Name CategoryId EndDate
33 Tweet Id 24 2022-04-15
34 Id 24 2022-04-15
35 Url 24 2022-04-15
36 Type 24 2022-04-15
37 Width 24 2022-04-15
38 Height 24 2022-04-15
39 Media Key 24 2022-04-15
40 Preview Image Url 24 2022-04-15
CodePudding user response:
Try below snipped, you probabbly need to use Any()
like:
Categories = entity.Categories
.OrderBy(o => o.Name)
.Where(c => c.EndDate == null && c.Variables.Any(r => r.CategoryId == c.CategoryId && r.EndDate == Null))
.ToList()
This will return all Categories that theirs EndDate
are null and at leat have one Variable
with Null
EndDate.
But below query will get all categories that their Variable
list at least have one item with EndDate = Null
entity.Categories.OrderBy(o => o.Name)
.Where(c => c.Variables.Any(r => r.CategoryId == c.CategoryId && r.EndDate == Null))
.ToList()
If your Model relations established correctly then don't need the first partition of sub query like:
entity.Categories.OrderBy(o => o.Name)
.Where(c => c.Variables.Any(r => r.EndDate == Null))
.ToList()
CodePudding user response:
Try following. You need a groupby :
class Program
{
static void Main(string[] args)
{
Entity entity = new Entity();
var results = entity.Variable
.GroupBy(x => x.CategoryId)
.Where(x => x.Any(y => y.EndDate.ToString().Length == 0))
.SelectMany(x => x.Select(y => new { VariableId = y.VariableId, Name = y.Name, CategoryId = y.CategoryId, EndDate = y.EndDate }))
.ToList();
}
}
public class Entity
{
public List<CategoryType> CategoryTypeId { get; set; }
public List<Category> Category { get; set; }
public List<Variable> Variable { get; set; }
}
public class CategoryType
{
public int CategoryTypeId { get; set; }
public string? Name { get; set; }
public virtual ICollection<Category> Categories { get; set; }
}
public class Category
{
public int CategoryId { get; set; }
public string? Name { get; set; }
public DateTime? EndDate { get; set; }
public int CategoryTypeId { get; set; }
public virtual CategoryType CategoryType { get; set; }
public virtual ICollection<Variable> Variables { get; set; }
}
public class Variable
{
public int VariableId { get; set; }
public string? Name { get; set; }
public DateTime? EndDate { get; set; }
public string Type { get; set; }
public int CategoryId { get; set; }
public virtual Category Category { get; set; }
public virtual ICollection<SearchFilter> SearchFilters { get; set; }
}
public class SearchFilter
{
}