How to use Linq to join entity tables with a cross

Question

Let me preface this by saying I'm relatively new to Linq, but I seem to have grasped most of it pretty quickly. But this problem has me stumped. I've searched and searched to no avail.

I am using code-first and have created have 2 models, "Items" and "Keywords". They have a many-to-many relationship on them. My entity models look like this:

public class MyContext : DbContext
{
    public MyContext() : base("name=DefaultDatabase") { }

    protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // Mapping Xref table to the correct DB table
        modelBuilder.Entity<Item>()
          .HasMany(i => i.Keywords)
          .WithMany(k => k.Items)
          .Map(ik =>
          {
              ik.ToTable("ItemKeywords", "MySchema");
              ik.MapLeftKey("ItemId");
              ik.MapRightKey("KeywordId");
          });

    }

    public virtual DbSet<Item> Items { get; set; }
    public virtual DbSet<Keyword> Keywords { get; set; }

}

public class Item
{

    public Item()
    {
        Keywords = new List<Keyword>();
    }

    public int ItemId { get; set; }
    public string Text { get; set; }
    public virtual List<Keyword> Keywords { get; set; }

}

public class Keyword
{

    public Keyword()
    {
        Items = new HashSet<Item>();
    }

    public int KeywordId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Item> Items { get; set; }

}

As far as I can tell, that's pretty straight forward. When my migrations generate a database, I get 3 tables, "Items", "Keywords", and a cross-reference table of "ItemKeywords" (which I mapped in the "OnModelCreating" method).

What I'm having trouble with is creating Linq query (query-syntax or method-syntax, I don't care) to reproduce this:

SELECT DISTINCT
    i.*
FROM
    Items i
    INNER JOIN ItemKeywords ik
        ON i.ItemId = ik.ItemId
    INNER JOIN Keywords k
        ON ik.KeywordId = k.KeywordId
WHERE
    k.KeywordId IN (1, 3, 5, 7, 9)

So, basically, I want a distinct List of Items that have one of the specified Keywords assigned to them.

I think I can get it working by creating a ItemKeyword model, but that seems like it would defeat the point of code-first. I'd be adding an extra model in there just for the benefit of being able to query against it.

Is there a way to get this working without adding that 3rd Xref model?

Thanks!

Answer 1

I think what you looking for is a query like this:

 var keywordIds = new List<int> {1, 3, 5, 7, 9};
 var items =   (from s in db.Items
                from c in s.Keywords
                where keywordIds.Contains(c.KeywordId)
                select s).Distinct();

If you want to bring all the selected items to memory, then call the ToList method at the end of this query.