Is AsQueryable () on ICollection really doing lazy execution?

Question

I am using Entity Framework CodeFirst, where I used parent child relationship using ICollection as

public class Person { public string UserName { get;set} public ICollection<Blog> Blogs { get; set;} } public class Blog { public int id { get; set; } public string Subject { get; set; } public string Body { get; set; } } 

Ok, so far everything is working fine, but I'm worried that whenever I want to get person blogs, I get it as

 var thePerson = _context.Persons.Where(x => x.UserName = 'xxx').SingleOrDefault(); var theBlogs = thePerson.Blogs.OrderBy(id).Take(5); 

Now I understand that when a line is executed, all blogs for this person are loaded into memory, and then sorting and selection is performed from memory. This is not ideal for recording a Person who has a large number of blogs. I want to make Blog Child as IQueryable so that sorting and selection is done in the SQL database before pulling it into memory.

I know that I can declare blogs as IQueryable in my context so that I can query directly

 var theBlogs = _context.Blogs.Where(.....) 

but this is impossible for me because of the choice of design, I want to avoid circular reference as much as possible due to the serialization problem. So, I did not make any reference to the parent in my child.

I found that I can call the AsQueryable () method on blogs as

 var theBlogs = thePerson.Blogs.AsQueryable().OrderBy(id).Take(5); 

It seems like magic to me and seems too good to be true. So my question is. Does this AsQueryable really make ICollection as IQueryable in reality and does the whole query process in SQL Server (Lazy loading) OR is it just a casting in which blogs are loaded into memory, as before, but change the interface from ICollection to IQueryable?

Answer 1

So it actually seems that writing your navigation property as an IQueryable<T> not possible .

What you can do is add a navigation property to Blog :

 public class Blog { public int id { get; set; } public string Subject { get; set; } public string Body { get; set; } public virtual Person Owner { get; set; } } 

From this, you can request the following so that it does not load everything into memory:

 var thePerson = _context.Persons.Where(x => x.UserName = 'xxx').SingleOrDefault(); var results = _context.Blogs.Where(z => z.Person.Name = thePerson.Name).OrderBy(id).Take(5) 

I suggest you try LINQPad to see how LINQ is translated into SQL, and what is actually being requested from DB.

Answer 2

The best approach is described in the Ladislav answer . In your case:

 var theBlogs = _context.Entry(thePerson) .Collection(x => x.Blogs) .Query() .OrderBy(x => x.id) .Take(5);