linq2sql: Explicit construction of entity type '#some type#' in query is not allowed - also using join

Question

Hi there,

I am having a problem mapping. I was reading scottGU post of "data shaping features" - http://weblogs.asp.net/scottgu/archive/2007/06/29/linq-to-sql-part-3-querying-our-database.aspx

but i have tried this

      IQueryable<AccessRights> accessRights = 
                            from t1 in this.db.AccessRights
                            join t2 in this.db.AccessRightsExtra
                            on t1.IdAccessRights equals t2.IdAccessRights 
                            where t2.IdUser== userId
                            select new AccessRights
                            {
                                IdAccessRights = t1.IdAccessRights,
                                Description= t2.Description
                            };

but produces this error "Explicit construction of entity type '#some type#' in query is not allowed"

As per scottgus post in link above i have also tried ( notice missing type after the the new in select)

     IQueryable<AccessRights> accessRights = 
                            from t1 in this.db.AccessRights
                            join t2 in this.db.AccessRightsExtra
                            on t1.IdAccessRights equals t2.IdAccessRights 
                            where t2.IdUser== userId
                            select new
                            {
                                IdAccessRights = t1.IdAccessRights,
                                Description= t2.Description
                            };

but this produces

Cannot implicitly convert type 'System.Linq.IQueryable' to 'System.Linq.IQueryable'. An explicit conversion exists (are you missing a cast?)

Really appreciate any insight that anyone has.

Answer 1

How about:

 IEnumerable<AccessRights> accessRights = 
    // This bit works in the database
    (from t1 in this.db.AccessRights
    join t2 in this.db.AccessRightsExtra
    on t1.IdAccessRights equals t2.IdAccessRights 
    where t2.IdUser== userId
    select new
    {
        IdAccessRights = t1.IdAccessRights,
        Description= t2.Description
    })
    .AsEnumerable() // From here on it's in-process
    .Select(x => new AccessRights
            {
                IdAccessRights = x.IdAccessRights,
                Description= x.Description
            });

Note that the result is IEnumerable<T> rather than IQueryable<T>, but it will still be lazily evaluated. Is that going to cause a problem?

Alternatively, just use the anonymous type using an implicitly typed local variable:

var accessRights = from t1 in this.db.AccessRights
                   join t2 in this.db.AccessRightsExtra
                   on t1.IdAccessRights equals t2.IdAccessRights 
                   where t2.IdUser== userId
                   select new
                   {
                       IdAccessRights = t1.IdAccessRights,
                       Description= t2.Description
                   };

This will still be an IQueryable<T> but where T is an anonymous type. You won't be able to use this as an IQueryable<AccessRights> but if all you need is the IdAccessRights and Description properties, and you only need them in the same method, it might be good enough for you...