Defaultifempty seems to work in linq to entities

Question

I'm new to linq and linq to entities so I might have gone wrong in my assumptions, but I've been unknowingly trying to use DefaultIfEmpty in L2E.

For some reason if I turn the resultset into a List, the Defaultifempty() works I don't know if I've inadvertantly crossed over into Linq area. The code below works, can anyone tell me why? And if it does work great, then it'll be of help to other people.

               var results = (from u in rv.tbl_user 
                    .Include("tbl_pics") 
                    .Include("tbl_area") 
                    .Include("tbl_province") 
                    .ToList() 
                    where u.tbl_province.idtbl_Province == prov 
                   select new { u.firstName, u.cellNumber, 
                   u.tbl_area.Area, u.ID,u.tbl_province.Province_desc, 
                                pic = (from p3 in u.tbl_pics 
                                       where p3.tbl_user.ID == u.ID 
                    select p3.pic_path).DefaultIfEmpty("defaultpic.jpg").First() 
                                           }).ToList(); 

Answer 1

This collects way more data than necessary, because you call .ToList() without filtering. This means you select all provinces from the database

Instead consider this:

var results = (from u in rv.tbl_user 
               where u.tbl_province.idtbl_Province == prov 
               select new { 
                   u.firstName, 
                   u.cellNumber, 
                   u.tbl_area.Area,
                   u.ID,
                   u.tbl_province.Province_desc, 
                   pic = (from p3 in u.tbl_pics 
                          where p3.tbl_user.ID == u.ID 
                          select p3.pic_path).DefaultIfEmpty("defaultpic.jpg").First() 
               }).ToList();

To answer your question, .DefaultIfEmpty selects the value returned by the query, and if it's null or empty, it selects the string/object passed in as a parameter. "defaultpic.jpg" in this case. The .First after that is just to make sure the enumeration (that will probably always consist of just one element) is collapsed to a single instance.