LINQ connection request
I'm trying to do multiple left joins in a linq query, but I would say I don't know how to implement this idea.
Basically there are 3 database structures here that I want to play with.
<tags>
id | name
<events_tags>
tag_id | event_id
<events>
id | name | some-other-fields
therefore for each event there is a one-to-many relationship with tags, an event can have one or more tags.
I would like to know how to search for an event based on a tag or how do I, based on the event ID, know the associated tags?
a source to share
To find an 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 tags for an event:
var tagsByEvent = from et in myEvent.EventTags
select et.Tag;
For the latter, for convenience, you can put it in the Events property:
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.
a source to share
You want many of the many to join here, it looks like this ... Linq to sql doesn't support this ... here's a great article
And this one from Scott Guthrie is helpful for understanding the basics
http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx
hope it helps
a source to share
To find the event names for a specified tag name, you can 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 a join table, joined and filtered on a table with a known value, and then joined a table with the values I was looking for.
Joe