none
Lazy loading collection omit where cause

    Question

  • public class Folder {
    	public virtual List<File> Files {get;set;}
    }
    var folder = (from f in context.Folders
    		where f.Id == 1
    		select f);
    var files = (from f in folders.Files
    		where f.Size > 1000
    		select f);
    context.Databse.Log = Console.Write;

    the query to files in output are without where cause Size > 1000

    mean the EF actually are fetch all data into memory and process in memory but not in sql server?

    var files = (from f in folders.Files.AsStreaming()
    		where f.Size > 1000
    		select f);

    I try AsStreaming, but the result are same.

    thank you very much.

    Friday, October 18, 2013 5:43 AM

Answers

  • After we do this:

    var folder = (from f in context.Folders
    		where f.Id == 1
    		select f).FirstOrDefault();
    

    We have already load the data to the "folder", and the "folder" is an object.

    So I think we cannot use Linq to Entities on "folder.Files", where "folder.Files" is a lazy loaded collection like codes as waht you have psot.

    In my opinion, it need to be writtn in one Linq to Entities query like below:

    db.Database.Log = Console.Write;
                    var files = from folder in db.Folders
                                where folder.FolderId == 1
                                select new
                                {
                                    files = from file in folder.Files
                                            where file.FileSize > 1000
                                            select file
                                };
    
                    files.ToList();

    Regards.

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by TakoLittle Friday, October 25, 2013 2:45 AM
    Wednesday, October 23, 2013 11:30 AM

All replies

  • Hello,

    It seems that you are using Entity Framework 6.

    >>mean the EF actually are fetch all data into memory and process in memory but not in sql server?

    Being confused about what the meaning of "but not in sql server".

    Could you please describe it more clearly?

    And could please share more codes as how you have define the object 'folders'.

    And is it that the type 'File' belongs to System.IO namespace or it is an entity class?

    With these information, I think we could help you better.

    If I have misunderstood, please let me know.

    Thanks &Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, October 18, 2013 9:53 AM
  • yes, its EF6

    both Folder and File is entity class,
    folders is context's DbSet<Folder>

    when 

    var files = (from f in folder.Files
    		where f.Size > 1000
    		select f);

    i expect it will generate sqlcmd 

    select * from files where size > 1000 and folderID == 1

    but it actually generate

    select * from files where folderId == 1

    the actual output will more complex like [Extend].[Id] something

    but you should have the idea, it just omit the where cause for file Size > 1000
    make me believe it fetch all records that folderId == 1 and filter the Size in memory instead of directly from sql server

    thank you


    • Edited by TakoLittle Friday, October 18, 2013 12:12 PM
    Friday, October 18, 2013 12:12 PM
  • Hi TakoLittle,
    So I guess that the codes should be like below:

     
     using (DataBaseFirstDBEntities db = new DataBaseFirstDBEntities())
                {
                    db.Database.Log = Console.Write;
    
                    Folder folders = db.Folders.Where<Folder>(f => f.FolderId == 2).FirstOrDefault();
    
                    var files = (from f in folders.Files
                                 where f.FileSize > 1000
                                 select f);
                }

    And the result is:

    Yes, it seems that it just omit the where cause for file Size > 1000.

    However, as far as I know, it because codes like below is not LinQ to Entities but LinQ to Object, so it will not be translated to be the sql command.

    from f in folders.Files
                                 where f.FileSize > 1000
                                 select f

    And if we change the codes to be:

    var files = (from f in db.Files
                                 where f.FileSize > 1000
                                 select f).ToList();

    We will see the translation command:

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, October 21, 2013 10:03 AM
  • mean lazy load collection cant use LinQ to Entities?

    thank you very much.

    Tuesday, October 22, 2013 3:02 AM
  • Hello,

    >>mean lazy load collection cant use LinQ to Entities?

    LinQ to Entities supports lazy load collecyion.

    We can split the query below into two parts:

    var files = (from f in folders.Files
    
                                 where f.FileSize > 1000
    
                                 select f);
    
    var files = folders.Files;

    This is LinQ to Entities,

    var result = from file in files
    
                                 where file.FileSize > 1000
    
                                 select file;
    

    This is LinQ to Object, it will not be translated to sql command.

       Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, October 22, 2013 6:50 AM
  • erm, i mean :

    var folder = (from f in context.Folders
    		where f.Id == 1
    		select f).FirstOrDefault();
    var files = (from f in folder.Files
    		where f.Size > 1000
    		select f);

    can we use Linq to Entities on "folder.Files", where "folder.Files" is a lazy loaded collection

    thank you very much.

    Wednesday, October 23, 2013 9:48 AM
  • After we do this:

    var folder = (from f in context.Folders
    		where f.Id == 1
    		select f).FirstOrDefault();
    

    We have already load the data to the "folder", and the "folder" is an object.

    So I think we cannot use Linq to Entities on "folder.Files", where "folder.Files" is a lazy loaded collection like codes as waht you have psot.

    In my opinion, it need to be writtn in one Linq to Entities query like below:

    db.Database.Log = Console.Write;
                    var files = from folder in db.Folders
                                where folder.FolderId == 1
                                select new
                                {
                                    files = from file in folder.Files
                                            where file.FileSize > 1000
                                            select file
                                };
    
                    files.ToList();

    Regards.

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by TakoLittle Friday, October 25, 2013 2:45 AM
    Wednesday, October 23, 2013 11:30 AM
  • thank you, now i got the idea
    Friday, October 25, 2013 2:45 AM