Entity Framework and Repository Pattern (problem with IQueryable)

Question

I just switched from Linq 2 SQL to Entity Framework, and I'm seeing some strange behaviors in EF that I'm hoping someone can help with. I tried Googling around, but I wasn't able to find other people with this same problem. I've mocked up a scenario to explain the situation.

If I work directly with an EF context, I'm able to do a select within a select. For example, this executes perfectly fine:

        // this is an Entity Framework context that inherits from ObjectContext
        var dc = new MyContext();

        var companies1 = (from c in dc.Companies
                          select new {
                              Company = c,
                              UserCount = (from u in dc.CompanyUsers
                                           where u.CompanyId == c.Id
                                           select u).Count()
                          }).ToList();

However, if I use a repository pattern where the repository is returning IQueryable (or even ObjectSet or ObjectQuery), I get a NotSupportedException (LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1)...

Here is an example of my repository:

public class Repository {
    private MyContext _dc;

    public Repository() {
        _dc = new MyContext();
    }

    public IQueryable<Company> GetCompanies() {
        return _dc.Companies;
    }

    public IQueryable<CompanyUser> GetCompanyUsers() {
        return _dc.CompanyUsers;
    }
}

// I'm using the repository inside another class (e.g. in my Services layer)

        var repository = new Repository();

        var companies2 = (from c in repository.GetCompanies()
                          select new {
                              Company = c,
                              UserCount = (from u in repository.GetCompanyUsers()
                                           where u.CompanyId == c.Id
                                           select u).Count()
                          }).ToList();

The above code throws a NotSupportedException.

I realize that if there's an association between Companies and CompanyUsers, then I can simply do this and it will work fine:

        var companies3 = (from c in repository.GetCompanies()
                          select new {
                              Company = c,
                              UserCount = (from u in c.CompanyUsers
                                           select u).Count()
                          }).ToList();

...but my example is just a simplified version of a more complicated scenario where I don't have an association between the entities.

So I'm very confused why Entity Framework is throwing the NotSupportedException. How is it that the query works perfectly fine when I'm working with the EF context directly, but it's not supported if I'm working with IQueryable returned from another method. This worked perfectly fine with Linq 2 SQL, but it doesn't seem to work in Entity Framework.

Any insight would be greatly appreciated.

Thanks in advance.

Answer 1

I suspect that what's happening is that EF sees the expression for repository.GetCompanyUsers() inside the lambda for the first select and doesn't know what to do with it because repository isn't an EF context. I think that if you pass in the IQueryable directly instead of an expression that returns it, it should work.

How about if you do this:

    var companyUsers = repository.GetCompanyUsers();
    var companies2 = (from c in repository.GetCompanies() 
                      select new { 
                          Company = c, 
                          UserCount = (from u in companyUsers 
                                       where u.CompanyId == c.Id 
                                       select u).Count() 
                      }).ToList(); 

Answer 2

This is one of those strange quirks with Linq to SQL/EF. Apparently they implemented a way to translate from a getter property to SQL, but not a way to translate from a getter function to SQL.

If instead of a function GetCompanyUsers() you use a property like CompanyUsers, it should work.

Weird eh?

So instead of

public IQueryable<CompanyUser> GetCompanyUsers() {
    return _dc.CompanyUsers;
}

You might do

public IQueryable<CompanyUser> CompanyUsers {
    get { return _dc.CompanyUsers; }
}

As far as parameterized queries go (which you can't do with a property, obviously), see my question answered here: Custom function in Entity Framework query sometimes translates properly, sometimes doesn't

You can also have wheres and selects in the property too; they'll translate fine. For instance, if I had a blog with an Articles table that contains some articles that aren't online:

public IQueryable<Article> LiveArticles {
    get { return _dc.Articles.Where(a => !a.IsDraft); }
}

That'll also reduce the number of parameterized queries that you need.