Entity Framework separating selection, count and filtering

Question

I want to separate filtering, count and selection operation from each but I get an error

The entity or complex type 'Parent' cannot be constructed in a LINQ to Entities query.

from the GetData function.

I know the problem but I couldn't solve it. What is the right architecture to separate these kinds of complex queries?

string string name="Any Name";

int childId = 4;
int page = 2, maxDispItem = 10;

public IQueryable<Parent> GetFilteredData()
{   
    GetData().Where(o=>o.Name==name).SelectMany(o=>o.Children).
            Where(o=>o.ID>childId).Select(o=>o.Parent).AsQueryable();
}

public IQueryable<Parent> GetData()
{
    return context.Set<Parent>.AsNoTracking().Select(o => new { o.ID,o.Name, 
          Childs = o.Children.Select(t => new { t.ID }) }).
        Select(o => new Parent { ID = o.ID, Name = o.Name, 
          Children = o.Childs.Select(t => new Child { ID = o.ID }).ToList()}).
       AsQueryable();        
}

public int GetDataCount()
{
    return GetFilteredData().SelectMany(o=>o.Children).Count();
}

public List<Parent> GetModelData()
{
    return GetFilteredData().OrderBy(o => o.ID).
           Skip(maxDispItem * (page -1)).Take(maxDispItem).ToList();
}

score 0 · Answer 1 · edited May 23 '17 at 12:29

You first have to learn what it means and what's the difference between IQueryable<>, IOrderedQueryable<> and IEnumerable<>

Here's how i would separate the logic:

public int GetCount(IQueryable<Parent> query)
{
    return query.Count();
}

public IQueryable<Parent> GetQuery(string name, int? childId)
{
     var query = context.Set<Parent>().AsQueryable();

     if (!string.IsNullOrEmpty(name))
     {
          query = query.Where(x => x.Name == name);
     }

     if (childId.HasValue)
     {
         query = query.Where(x => x.Children.Any(y => y.Id == childId.Value));
     }

     return query;
}

public IOrderedQueryable<Parent> GetOrderedQuery(IQueryable<Parent> query)
{
      return query.OrderBy(x => x.Id);
}

public IEnumerable<Parent> GetPageData(IOrderedQueryable<Parent> query, int page, int pageSize)
{
      return query.Skip((page - 1) * pageSize).Take(pageSize).ToList();
}

public void Example()
{
     // filter
     var query = GetQuery("Test", 4);

     // total count on filtered query
     var count = GetCount(query);

     // order
     var orderedQuery = GetOrderedQuery(query);

     // paginate and execute query
     var pagedData = GetPageData(orderedQuery, page: 1, pageSize: 2);
 }

What is the difference between IQueryable<T> and IEnumerable<T>?

Entity Framework separating selection, count and filtering

1 Answers1