LINQ to SQL: How to setup associations between two tables to populate a list of strings

I have a simple database with two tables: 开发者_运维知识库Photo and Tag. There is a one-to-many (a photo can have many tags) relationship between the two tables. Here is a diagram:

Now I have made a Photo class and set it up for LINQ-to-SQL using attributes. The code for that class is below:

[Table]
public class Photo
{

    [Column(IsDbGenerated = true, IsPrimaryKey = true, CanBeNull = false)]
    public int ID { get; set; }

    [Column(CanBeNull = false)]
    public string Filename { get; set; }

    [Column(CanBeNull = false)]
    public string Description { get; set; }

    [Column(CanBeNull = false)]
    public DateTime DateTaken { get; set; }


    public List<string> Tags { get; set; }

    public override string ToString()
    {
        string result = String.Format("File: {0}, Desc: {1}, Date: {2}, Tags: ", 
            Filename, Description, DateTaken);
        if (Tags != null)
            foreach (string tag in Tags)
                result += tag + ", ";
        return result;
    }
}

You will notice that currently I do not have any attributes for the Tags list. I would like to be able to setup the attributes (associations) for the Tags list so that it would be populated with Name field of the Tag table for all entries in the Tag table of a particular PhotoID. It would be preferable if I could do this directly (i.e. without having to setup a Tag class mimicking/relating to the Tag table). Since I'm only interested in one field (the Name in the Tag table) rather than many fields, I would think there is a way to do this.

Is this possible, and if so how would I further decorate the class with attributes and what would be the syntax for a simiple Select query using LINQ-to-SQL?

If it helps, here is the code I am using to simply add a new photo and then grab all of the photos out of the database (obviously the tag information is not pulled out as the code stands now).

DataContext context = new DataContext(connectionString);

// add new photo
Photo newPhoto = new Photo { Filename = "MyImage1.jpg", Description = "Me", DateTaken = DateTime.Now };
context.GetTable<Photo>().InsertOnSubmit(newPhoto);
context.SubmitChanges();

// print out all photos
var photoQuery = from m in context.GetTable<Photo>() select m;
foreach (Photo myPhoto in photoQuery)
    textBox1.Text += Environment.NewLine + myPhoto.ToString();

---

First I'd suggest you to use a tool to generate your entity classes (the classes that correspond to the database tables). We'r using sqlmetal and it does the job very well.
Next, (if you have a Tag entity) than write a function that fetches the tags for some photos:

void GetTags(IEnumerable<Photo> photos)
{
var ids = photos.Select(p=>p.ID).ToList();
var tagsG = (from tag in context.GetTable<Tag>() where ids.Contains(tag.PhotoID) select new {PhotoID, Name}).GroupBy(tag=>tag.PhotoID);
foreach(ph in photos){
ph.Tags = tagsG[ph.ID].Select(tag=>tag.Name).ToList();
}
}

Note, the code might not compile I've written it in the browser...

---

You should refer to the Attribute Based Mapping article on msdn.

Also, this article shows how to decorate an EntitySet property with an Association attribute to accomplish the relationship modeling.

It would be preferable if I could do this directly (i.e. without having to setup a Tag class mimicking/relating to the Tag table).

Not possible. LinqToSql needs to know what is or isn't a table, so it can generate the proper sql text.

What you can do instead, is make one set of types for representing database structure, and another set of types for use elsewhere in your program. The second set of types could have a single class representing Photo and Tag data.

Write a query with the first set of types, then use them to construct instances of the second set.