join query with linq

Question

I am trying here to make a few left joins into a linq query but I'd say I rather have no idea how to materialize this idea.

Basically here is the 3 database structures I want to play with.

<tags>
id | name

<events_tags>
tag_id | event_id

<events>
id | name | some-other-fields

so for each events there is a one-to-many relation with tags, an event can then have one or more tags.

I'd like to know how to search an event based on a tag or how can I, based from an event id know the associated tags ?

Answer 1

Are you wanting to do a many to many join here, looks that way.... Linq to sql does not support this...here is a great article

http://blogs.msdn.com/mitsu/archive/2007/06/21/how-to-implement-a-many-to-many-relationship-using-linq-to-sql.aspx

And this one from Scott Guthrie is useful in getting to grips with the basics

http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx

hope that helps

Answer 2

To search event by tag, I think you can write something like:

var tagsIds = from t in DataContext.Tags
              where t.Name == "sometag"
              select t.id;

var eventsByTag = from et in DataContext.EventTags
                  where tagsIds.Contains(et.tag_id)
                  select et.Event;

To get the tags for an event:

var tagsByEvent = from et in myEvent.EventTags
                  select et.Tag;

For the latter, for convenience, you can put it in a property of Events:

public List<Tag> Tags
{
   get
   {
      List<Tag> tags = (from et in this.EventTags
                        select et.Tag).ToList();
      return tags;
   }
}

And just refer to myEvent.Tags where you need them.

Answer 3

Hi,

To find the event names for a specified tag name, you could do this:

Console.WriteLine("\nEvents tagged as .NET:\n");

(from evtTag in ctx.EventsTags
 join tag in ctx.Tags on evtTag.TagID equals tag.ID
 where tag.Name == ".NET"
 join evt in ctx.Events on evtTag.EventID equals evt.ID
 select evt)
 .ToList()
 .ForEach(evt => Console.WriteLine(evt.Name));

Similarly, you can search for tags with a specific event name like this:

Console.WriteLine("\nTags for TechEd:\n");

(from evtTag in ctx.EventsTags
 join evt in ctx.Events on evtTag.EventID equals evt.ID
 where evt.Name == "TechEd"
 join tag in ctx.Tags on evtTag.TagID equals tag.ID
 select tag)
 .ToList()
 .ForEach(tag => Console.WriteLine(tag.Name));

Notice how I started with the join table, joined and filtered on the table with the known value, and then joined to the table with the values searched for.

Joe