LinqToSQl and the Member access not legal on type exception

Question

The basic problem...

I have a method which executes the following code:

IList<Gig> gigs = GetGigs().WithArtist(artistId).ToList();

The GetGigs() method gets Gigs from my database via LinqToSql...

So, when GetGigs().WithArtist(artistId).ToList() is executed I get the following exception:

Member access 'ListenTo.Shared.DO.Artist Artist' of 'ListenTo.Shared.DO.Act' not legal on type 'System.Collections.Generic.List`1[ListenTo.Shared.DO.Act] 

Note that the extension function "WithArtist" looks like this:

    public static IQueryable<Gig> WithArtist(this IQueryable<Gig> qry, Guid artistId)
    {
        return from gig in qry
               where gig.Acts.Any(act => (null != act.Artist) && (act.Artist.ID == artistId))
               orderby gig.StartDate
               select gig;
    }

If I replace the GetGigs() method with a method that constructs a collection of gigs in code (rather than from the DB via LinqToSQL) I do NOT get the exception.

So I'm fairly sure the problem is with my LinqToSQl code rather than the object structure.

However, I have NO IDEA why the LinqToSQl version isnt working, so I've included all the associated code below. Any help would be VERY gratefully receivced!!

The LinqToSQL code....

    public IQueryable<ListenTo.Shared.DO.Gig> GetGigs()
    {
        return from g in DBContext.Gigs
               let acts = GetActs(g.ID)
               join venue in DBContext.Venues on g.VenueID equals venue.ID
               select new ListenTo.Shared.DO.Gig
               {
                   ID = g.ID,
                   Name = g.Name,
                   Acts = new List<ListenTo.Shared.DO.Act>(acts),
                   Description  = g.Description,
                   StartDate    = g.Date,
                   EndDate      = g.EndDate,
                   IsDeleted    = g.IsDeleted,
                   Created      = g.Created,
                   TicketPrice  = g.TicketPrice,
                   Venue        =  new ListenTo.Shared.DO.Venue { 
                                    ID = venue.ID, 
                                    Name = venue.Name, 
                                    Address = venue.Address,
                                    Telephone = venue.Telephone,
                                    URL = venue.Website 
                   }

               };
    }



    IQueryable<ListenTo.Shared.DO.Act> GetActs()
    {
        return from a in DBContext.Acts

               join artist in DBContext.Artists on a.ArtistID equals artist.ID into art
               from artist in art.DefaultIfEmpty()

               select new ListenTo.Shared.DO.Act
               {
                    ID = a.ID,
                    Name = a.Name,
                    Artist = artist == null ? null : new Shared.DO.Artist
                    {
                       ID =  artist.ID,
                       Name = artist.Name
                    },
                    GigId = a.GigID

               };
    }

    IQueryable<ListenTo.Shared.DO.Act> GetActs(Guid gigId)
    {
        return GetActs().WithGigID(gigId);
    } 

I have included the code for the Act, Artist and Gig objects below:

public class Gig : BaseDO
{

    #region Accessors

    public Venue Venue
    {
        get;
        set; 
    }

    public System.Nullable<DateTime> EndDate
    {
        get;
        set;
    }

    public DateTime StartDate
    {
        get;
        set;
    }

    public string Name
    {
        get;
        set;
    }

    public string Description
    {
        get;
        set;
    }

    public string TicketPrice
    {
        get;
        set;
    }

    /// <summary>
    /// The Act object does not exist outside the context of the Gig, therefore,
    /// the full act object is loaded here.
    /// </summary>
    public IList<Act> Acts
    {
        get;
        set;
    }

    #endregion
}

public class Act : BaseDO
{
    public Guid GigId { get; set; }
    public string Name { get; set; }
    public Artist Artist { get; set; }
}

public class Artist : BaseDO
{
    public string Name { get; set; }
    public string Profile { get; set; }
    public DateTime Formed { get; set; }
    public Style Style { get; set; }
    public Town Town { get; set; }
    public string OfficalWebsiteURL { get; set; }
    public string ProfileAddress { get; set; }
    public string Email { get; set; }
    public ImageMetaData ProfileImage { get; set; }

}

public class BaseDO: IDO
{
    #region Properties

    private Guid _id;

    #endregion

    #region IDO Members

    public Guid ID
    {
        get
        {
            return this._id;
        }
        set
        {
            this._id = value;
        }
    }




}

}

Answer 1

I had the same issue and what seemed to do the trick for me was separating out an inline static method call that returned IQueryable<> so that I stored this deferred query into a variable and referenced that.

I think this is a bug in Linq to SQL but at least there is a reasonable workaround. I haven't tested this out yet but my assumption is that this problem may arise only when referencing static methods of a different class within a query expression regardless of whether the return type of that function is IQueryable<>. So maybe it's the class that holds the method that is at the root of the problem. Like I said, I haven't been able to confirm this but it may be worth investigating.

UPDATE: Just in case the solution isn't clear I wanted to point it out in context of the example from the original post.

public IQueryable<ListenTo.Shared.DO.Gig> GetGigs()
{
    var acts = GetActs(g.ID); // Don't worry this call is deferred

    return from g in DBContext.Gigs
           join venue in DBContext.Venues on g.VenueID equals venue.ID
           select new ListenTo.Shared.DO.Gig
           {
               ID = g.ID,
               Name = g.Name,
               Acts = new List<ListenTo.Shared.DO.Act>(acts),
               Description  = g.Description,
               StartDate    = g.Date,
               EndDate      = g.EndDate,
               IsDeleted    = g.IsDeleted,
               Created      = g.Created,
               TicketPrice  = g.TicketPrice,
               Venue        =  new ListenTo.Shared.DO.Venue { 
                                ID = venue.ID, 
                                Name = venue.Name, 
                                Address = venue.Address,
                                Telephone = venue.Telephone,
                                URL = venue.Website 
               }

           };
}

Note that while this should correct the issue at hand there also seems to be another issue in that the deferred acts query is being accessed in each element of the projection which I would guess would cause separate queries to be issued to the database per row in the outer projection.

Answer 2

I think the problem is the 'let' statement in GetGigs. Using 'let' means that you define a part of the final query separately from the main set to fetch. the problem is that 'let', if it's not a scalar, results in a nested query. Nested queries are not really Linq to sql's strongest point as they're executed deferred as well. In your query, you place the results of the nested query into the projection of the main set to return which is then further appended with linq operators.

When THAT happens, the nested query is buried deeper into the query which will be executed, and this leads to a situation where the nested query isn't in the outer projection of the query to execute and thus has to be merged into the SQL query ran onto the DB. This is not doable, as it's a nested query in a projection nested inside the main sql query and SQL doesn't have a concept like 'nested query in a projection', as you can't fetch a set of elements inside a projection in SQL, only scalars.