I'm using EF4.1 code first,
I'm trying to to create a query which returns a collection with an eager loaded child-collection. Both has to be ordered by Position and IsActive == true
These Classes are mapped.
public class Type
{
public int Id { get; set; }
public int AreaId { get; set; }
public bool IsActive { get; set; }
public int Position { get; set; }
.......
public virtual IList<Category> Categories { get; set; }
}
public class Category
{
public int Id { get; set; }
public int TypeId { get; set; }
public bool IsActive { get; set; }
public int Position { get; set; }
.......
public virtual Type Type { get; set; }
}
DTOs:
public class TypeDTO
{
public string Name { get; set; }
public string UniqueName { get; set; }
public virtual IList<CategoryDTO> Categories { get; set; }
}
public class CategoryDTO
{
public string Name { get; set; }
public string UniqueName { get; set; }
public virtual TypeDTO Type { get; set; }
}
What I have got:
With DTO:
var result = _db.Types
.Where(x => x.IsActive == true)
.Where(x => x.AreaId== areaId)
.Select(x => new TypeDTO()
{
Name = x.Name,
UniqueName = x.UniqueName,
Categories = x.Categories.Where(c => c.IsActive == true)
.OrderBy(c => c.Position)
.Select(c => new CategoryDTO()
{
Name = c.Name,
UniqueName = c.UniqueName
})
.ToList()
})
.ToList();
This throws :
LINQ to Entities does not recognize the method 'System.Collections.Generic.List
1[...CategoryDTO] ToList[...CategoryDTO](System.Collections.Generic.IEnumerable
1[...CategoryDTO])' method, and this method cannot be translated into a store expression.
Without DTO:
var result2 = _db.Categories
.Where(x => x.IsActive == true)
.OrderBy(x => x.Position)
.Select(x => x.Type)
.Distinct()
.Where(x => x.IsActive == true && x.AreaId== areaId)
.OrderBy(x => x.Position)
.ToList();
It runs without Error, but does not gives the right result, and does not ordering the child-collection
Other unsuccessful approach :
var result = _db.Types
.Where(t => t.IsActive == true)
.Where(t => t.SiteId == siteId)
.Include(t => t.Categories
.Where(c=>c.IsActive == true)
.OrderBy(c=>c.Position))
.OrderBy(t => t.Position)
.ToList();
What am i missing?
Thanks.
EDIT The final Solution:
public class TypeDTO
{
public Type Type { get; set; }
public IEnumerable<Category> Categories { get; set; }
}
var result = _db.Types
.Where(x => x.IsActive == true)
.Where(x => x.AreaId== areaId)
.OrderBy(x=>x.Position)
.Select(x => new TypeDTO()
{
Type = x,
Categories = x.Categories.Where(c => c.IsActive == true).OrderBy(c => c.Position)
})
.ToList();
You can use the following approach..
using (var context = new DbContext())
{
var activeTypes = context.Types.Where(t => t.IsActive)
.Select(t => new
{
Type= t,
ActiveCategories = t.Categories.Where(c => c.IsActive)
.OrderBy(c => c.Position)
}).ToList();
}