Querying a join table using LINQ

Question

Okay this could very likely be a silly question. I am using Entity Framework Code First. I have two classes, User and Event, that have a Many-To-Many relationship. When EF generates my database tables, it creates a join table, which I call Users_Events. This table has two columns, User_ID and Event_ID. Everything is fine so far.

I want to pull an Event from my database and serialize it to JSON. This also works perfectly except I cannot pull an Event's Users because this would create a circular reference. What I want to do here is query my join table and get all the User_IDs that have an associated Event_ID equal to the ID of the Event I am serializing.

How can I do this?

I don't know that the serialization works quite like you might anticipate. If you've already tried it first and found that it serialized navigable collections, then my apologies, but I experienced no such behavior from LINQ-to-SQL (although I realize EF4.1 is much different) due to lazy loading/deferred execution. I had to explicitly specify load options to retrieve the related collection before serialization. Check out this MSDN article for more information about correctly shaping related records for your serialized data. — lsuarez
– lsuarez, Commented Jun 22, 2011 at 21:13
It does not serialize related objects without explicitly specifying. I want Events to be serialized. I want Events' Users to be serialized. I don't want Events' Users' Events to be serialized (which creates the circular reference). If I explicity include Users (using .Include("Users")), Users and all of its navigable properties are also serialized, including the Users' Events. — Alec
– Alec, Commented Jun 22, 2011 at 21:21

Tim Rogers · Accepted Answer · 2011-06-23 08:12:19Z

3

I don't know exactly what you want to end up with in your JSON, but I suspect you want to select into a new anonymous type and serialize that instead. Something along these lines maybe:

from e in myContext.Events
where e.ID = 123
select new { 
    Event = e,
    UserIDs = (from u in e.Users select u.ID)
}

edited Jun 23, 2011 at 8:12

answered Jun 22, 2011 at 20:53

Tim Rogers

21.8k8 gold badges55 silver badges69 bronze badges

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

4 Comments

Alec Over a year ago

My classes are rather large and creating anonymous types is very tedious and code heavy, but maybe.

Tim Rogers Over a year ago

I've edited my answer to provide another approach that wraps the event. Again, it depends how you want your JSON to look, but this is the sort of approach I'd use.

Tim Rogers Over a year ago

The other answer is to look at the features of the JSON serializer. I'm not sure about the .NET version, but the Newtonsoft one has a feature to deal with self-referencing loops.

Alec Over a year ago

I have used Newtonsoft in the past but didn't like it because it serialized far too much for me (I prefer to be selective about what gets serialized). The anonymous types option is pretty good and similar to what I did. I simply looped through the Users' Events before serialization and set them to empty lists and it worked. Pretty poor code, maybe, but similar in concept to anonymous types I think.

Collectives™ on Stack Overflow

Querying a join table using LINQ

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related