CODEWITHSUNDEEP
.netlinqentity-frameworkcollectionslinq-to-entities
James
James

Reputation: 7533

LINQ to Entities select all entries in many-to-many relationship

I have 3 MySql tables: Students, Classes and StudentsInClasses.

The Entity Framework translates these into two entities Student and Class, each linking to the other with a many-to-many navigation property (e.g. Student.Classes).

But there is no StudentsInClasses entity, so what's the best way to call, using LINQ to Entities, the equivalent of SQL:

SELECT StudentId, ClassId FROM StudentsInClasses;

I'm looking for a HashSet (or equivalent) of { StudentId, ClassId } pairs so I can quickly look up whether a given student is in a given class. (What's the best way of storing this?)

Many thanks.

Upvotes: 1

Views: 1269

Answers (1)

Jon Skeet
Jon Skeet

Reputation: 1500645

How about:

var query = from @class in db.Classes
            from student in @class.Students
            select new { ClassId = @class.ID, Student = student };

var lookup = query.ToLookup(x => x.ClassId,
                            x => x.Student);

(I suspect a Lookup is more appropriate than a HashSet here.)

EDIT: If you don't want to use query expressions:

var query = db.Classes
              .SelectMany(@class => @class.Students,
                          (@class, student) => new { ClassId = @class.ID,
                                                     Student = student });

Upvotes: 3

Related Questions