I'm coming from TSQL + C# land and have been trying to adapt to linq and EF. Many-to-many relationships have been tripping me up. I have models with many-to-many relationships that I want to query from a database. Such as:

     class Product{
       public int ID {get;set;}
       public string ProductName {get;set;}
       public virtual ICollection<Tag> Tags {get;set;}
     }

   class Tag {
       public int ID {get;set;}
       public string TagName {get;set;}
       public virtual ICollection<Product> Products {get;set;}

    }

I'm able to get a product itself out of the DbContext, and then later fetch it's associated Tags like this:

// product exists in memory as a Product with an empty product.Tags

var query = from p in db.Product
            from t in db.Tags
            where p.ID == product.ID
            select p.Tags;

Then I can assign the product.Tags with the fetched Tags. Obviously, this is very inefficient when dealing with multiple products if I have to query for every product.

With linq and EF, I want to be able to get a Product with all of its associated Tags in one round trip to the database. Also, I want to be able to get all Products and their associated Tags (or a filtered list of Products). How do would the linq look?

Edit:

Ok, after some more fiddling around, I've got this:

var query = db.Product.Include("Tags")
            .Where(p => p.Tags.Any(t => t.Products.Select(m => m.ID).Contains(p.ID)));

This is almost what I need. The results are all products with tags. Missing are the products that don't have tags. I think of this as the equivalent of a SQL inner join. I want to left outer join the tags to the product, and return all products with tags optional. How to get all products with their associated tags without excluding products that have no tags?

Edit:

This was easier than I thought.

var query2 = db.Product.Include("Tags").DefaultIfEmpty();

This gets all the products and their respective tags, including products without tags. Hopefully it works for the right reasons...

• linq
• entity-framework
• many-to-many

The purpose of using an object-relational mapper like EF is that it maps relationships for you. If you are manually joining objects that have foreign keys in the database, you are doing it wrong.

See my question Why use LINQ Join on a simple one-many relationship?

The correct answer is simply context.Products.Include("Tags"), which will auto-magically join Products and Tags for you. This is literally the biggest (only?) benefit of using an ORM.