LINQ to SQL Join

Question

I'm new to LINQ so apologies if this is a simple answer. I'm trying to do a SQL join and have the following code based on examples I've seen on SO and elsewhere:

var query = from e in db.Events
    join ec in db.EventCategories on e.ID equals ec.EventID
    join c in db.Categories on ec.CategoryCode equals c.CategoryCode
    join ep in db.EventParticipants on e.ID equals ep.EventID
    join p in db.Participants on ep.ParticipantCode equals p.ParticipantCode
    select new { e, ec, c, ep, p };

This executes fine and when I run the debugger I can expand the Object and see that the query ran successfully. However, when I try to execute query.ToList() I can't cast this into anything usable because it says that the list returned by query.ToList() is System.Collections.Generic.List.

Method #2: Based on my own thoughts I tried creating the following struct:

public struct CalendarItem
    {
        public Event e;
        public EventCategory ec;
        public Category c;
        public EventParticipant ep;
        public Participant p;

        public CalendarItem(Event E, EventCategory EC, Category C, EventParticipant EP, Participant P)
        {
            e = E;
            ec = EC;
            c = C;
            ep = EP;
            p = P;
        }
    }

And then modifying the LINQ command to the following:

var query = from e in db.Events
    join ec in db.EventCategories on e.ID equals ec.EventID
    join c in db.Categories on ec.CategoryCode equals c.CategoryCode
    join ep in db.EventParticipants on e.ID equals ep.EventID
    join p in db.Participants on ep.ParticipantCode equals p.ParticipantCode
    select new CalendarItem(e, ec, c, ep, p);

In Visual Studio this checks out and it allows me to compile and everything looks great (i.e. I can loop over the list of CalendarItems), but I get a runtime error on query.ToList(): The member 'e' has no supported translation to SQL.

Answer 1

One option is to call AsEnumerable after the anonymous projection:

var query = (from e in db.Events
    join ec in db.EventCategories on e.ID equals ec.EventID
    join c in db.Categories on ec.CategoryCode equals c.CategoryCode
    join ep in db.EventParticipants on e.ID equals ep.EventID
    join p in db.Participants on ep.ParticipantCode equals p.ParticipantCode
    select new { e, ec, c, ep, p })
    .AsEnumerable() // Do the rest in process
    .Select(x => new CalendarItem(x.e, x.ec, x.c, x.ep, x.p)
    .ToList();

By the way, if you are going to take this approach, I'd strongly advise against using a mutable struct, or public fields. Both are potential areas of trouble. You can easily change it into an immutable class with properties, and in any event give them usable names :)

public class CalendarItem
{
    public Event Event { get; private set; }
    public EventCategory EventCategory { get; private set; }
    public Category Category { get; private set; }
    public EventParticipant EventParticipant { get; private set; }
    public Participant Participant { get; private set; }

    public CalendarItem(Event event,
                        EventCategory eventCategory,
                        Category category,
                        EventParticipant eventParticipant,
                        Participant participant)
    {
        Event = event;
        EventCategory = eventCategory;
        Category = category;
        EventParticipant = eventParticipant;
        Participant = participant;
    }
}

Answer 2

I'd make my CalandarItem a class with automatic properties:

public class CalendarItem
{
        public Event E{get;set;}
        public EventCategory EC{get;set;}
        public Category C{get;set;}
        public EventParticipant EP{get;set;}
        public Participant P{get;set;}
}

and use a object initializer on the select:

var query = from e in db.Events
    join ec in db.EventCategories on e.ID equals ec.EventID
    join c in db.Categories on ec.CategoryCode equals c.CategoryCode
    join ep in db.EventParticipants on e.ID equals ep.EventID
    join p in db.Participants on ep.ParticipantCode equals p.ParticipantCode
    select new CalendarItem{E=e, EC=ec, C=c, EP=ep, P=p};

I know this will work because I just happened to write a very similar bit of code about 2 minutes ago!