Linq nested select new not working

Question

Hi All,

I'm trying to get eager loading working with Subsonic, and it's been returning null for me.

In the method below, I'm trying to hydrate a domain model (UserModel) which contains another domain model (CompanyModel). However, with the code below, UserModel.Company is always null.

What am I missing here. Any help would be appreciated.

public IList<UserModel> GetUsers()
{             
    return (from u in SubsonicSqlServer.Users.All()
            select new UserModel
                       {
                           UserId= u.UserId,
                           Company = (from c in u.Companies
                                    select new CompanyModel
                                               {
                                                   CompanyId = c.CompanyId,
                                                   CompanyName = c.CompanyName
                                               }).SingleOrDefault(),
                           FirstName = u.FirstName,
                           LastName = u.LastName,
                           BirthDate = u.BirthDate
                       }).ToList();

}

Update (08/11/09):

More toying around with the code, I found out that setting CompanyId in the following example doesn't work either. I initially thought this was an issue with Subsonic, but if the code below doesn't work, I'm guessing it has something to do with my Linq statement. Any ideas?

public IList<UserModel> GetUsers()
{             
    return (from u in SubsonicSqlServer.Users.All()
            select new UserModel
                       {
                           UserId= u.UserId,                            
                           CompanyId = Guid.NewGuid(),
                           FirstName = u.FirstName,
                           LastName = u.LastName,
                           BirthDate = u.BirthDate
                       }).ToList();

}

Update (11/17/2009):

Still haven't found a solution. But we are switching to nHibernate (not because of this issue).

Answer 1

Two things

1. You're returning a List<UserModel> when your method's signature line says IList<User> does UserModel inherit from User?

2. Am I missing something, where does e come from?

FirstName = e.FirstName, LastName = e.LastName, BirthDate = e.BirthDate Blockquote

Answer 2

"UserModel.Company is always null."

since you are setting this with an expression that ends with .SingleOrDefault(), I'm going to suggest that the query isn't returning a single item. Start investigating there. If you are expecting exactly one item in u.Companies, change to .Single() and force an early failure.

You can do the .Single() before creating the new CompanyModel object, I think.

As for style, I like the query comprehension syntax ("from x in y select") but find it awkward when combined with traditional dot-notation syntax. It's just hard to read. (http://stackoverflow.com/questions/214500/which-linq-syntax-do-you-prefer-fluent-or-query-expression/214610#214610).

Consider using let in the query comprehension to make it clearer.

Also, since a query already returns an IEnumerable<T>, and calling ToList() forces all items to be realized, I would modify my method to return IEnumerable<T> if possible.

So, in your case, I would refactor the first to say:

public IEnumerable<User> GetUsers()
{ 
    return from u in SubsonicSqlServer.Users.All()
     let c = u.Companies.Single()
     select new UserModel
     {
      UserId = u.UserId,
      Company = new CompanyModel
      {
       CompanyId = c.CompanyId,
       CompanyName = c.CompanyName
      },
      FirstName = e.FirstName,
      LastName = e.LastName,
      BirthDate = e.BirthDate
     };
}

If it makes sense in your object model, you could modify User to have a constructor that takes whatever type u is, and it gets even simpler:

return from u in SubsonicSqlServer.Users.All()
 select new UserModel (u);

or even

return SubsonicSqlServer.Users.All().Select(u => new UserModel (u));

Answer 3

Please check out my fork @ github (http://github.com/funky81/SubSonic-3.0/commit/aa7a9c1b564b2667db7fbd41e09ab72f5d58dcdb) for this solution, actually there's a problem when subsonic try to project new type class, so there's nothin wrong with your code actually :D

Answer 4

I've come across this same problem, tried Funky81's fork, but still ended up with projection issues. Do you have any recommendations on setting up using code from your fork?