Entity Framework - Join on many to many

Question

I have a simple many to many relationship and I am wondering how you get data out of it. Here is the setup

Tables

Media Media_Keyword (many to many map) Keyword

Here is the code I have:

    public List<Keyword> GetFromMedia(int mediaID)
    {
        var media = (from m in Connection.Data.Media
                   where m.id == mediaID
                   select m).First();

        var keys = (from k in media.Media_Keyword
                    select new Keyword {ID = k.Keywords.id, Name = k.Keywords.keyword});

        return keys.ToList();
    }

Is there a way to do this better?

Answer 1

Usually, I select right from the many-to-many map.

var keys = from k in Connection.Data.Media_Keyword
       where k.MediaID == mediaID
       select k.Keywords;

Answer 2

I've not used the entity framework specifically, but can't you just combine them like this?

public List<Keyword> GetFromMedia(int mediaID)
{
    return (from m in Connection.Data.Media
               from k in m.Media_Keyword
               where m.id == mediaID
               select new Keyword {ID = k.Keywords.id, Name = k.Keywords.keyword}).ToList();
}

Answer 3

Response to Kleinux (Don't know why i can't add a comment to your question)

Sure you can, but it's not necessarly a good things, because context giving you a new "keyword". Then, if you try to update this or something thinking that you will update, context gonna see it as a new keyword and would create a new one instead of updating it.

** UPDATE Sorry for my english, i'm french, well not french but from Quebec. I'm giving my 110%!!