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.

Charlie · Accepted Answer · 2009-12-09 14:33:27Z

4

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!

answered Dec 9, 2009 at 14:33

Charlie

10.3k10 gold badges53 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

James Black · Accepted Answer · 2009-12-09 14:35:17Z

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;
    }
}

edited Dec 9, 2009 at 14:35

James Black

41.8k10 gold badges92 silver badges172 bronze badges

answered Dec 9, 2009 at 14:29

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

1 Comment

Kyle Over a year ago

I think this would have worked well, but the way I was doing the rest of my LINQ code (i.e. conditional wheres), the syntax just didn't seem to work out.

Collectives™ on Stack Overflow

LINQ to SQL Join

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related