EDMX ICollection vs DBSet

Question

EDMX uses ICollection for 1:n relation ship like this

public partial class Customer
{
   ...
   public virtual ICollection<User> Users { get; set; }

ICollection implements IEnumerable and not IQueryable so using property navigation on customer to filter by sites, retrieve all records from the DB server and filter them on the client side (it's an example not a real case) :

var users = myContext.Customer.Users.Where(u.Enabled).FirstOrDefault();

Linq query :

SELECT [Extent1].[IdCustomer]       AS [IdCustomer],
       [Extent1].[Enabled]          AS [Enabled],
       ...
FROM   [Framework].[User] AS [Extent1]

I have try to "force" EF to set users as queryable at runtime but that doesn't work :

var users = myContext.Customer.Users.AsQueryable().Where(u.Enabled).FirstOrDefault();

Two questions about this problem :

Why AsQueryable doesn't work in this case ?
Why does EDMX use ICollection and not a DbSet instead ?
Is it possible to use a DbSet or a specialized collection with an implementation of IQueryable ?

CodeCaster CodeCaster · Accepted Answer · 2015-02-03T10:04:06

"so using property navigation on customer to filter by sites, retrieve all records from the DB server and filter them on the client side "

This assumption is where you go wrong. Where() returns a predicate, which Entity Framework converts to an SQL query upon materialization.

When Lazy Loading is enabled, Entity Framework will override the virtual property and do its magic on it to enable lazy loading.

EDMX ICollection vs DBSet

1 Answers