如何使用Linq连接实体表与交叉引用表 [英] How to use Linq to join entity tables with a cross-reference table

问题描述

让我先说说我对林奇来说比较新，但我似乎很快就抓住了大部分的东西。但是这个问题让我失望了。我搜索和搜索无效。

我使用的是代码优先，已经创建了2个模型，项目和关键字。他们有很多关系。我的实体模型看起来像这样：

  public class MyContext：DbContext 
 {
 public MyContext（） base（name = DefaultDatabase）{} 
 
 protected override void OnModelCreating（System.Data.Entity.DbModelBuilder modelBuilder）
 {
 base.OnModelCreating（modelBuilder）; 
 
 //将Xref表映射到正确的数据库表
 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>物品{get;组; } 
 public virtual DbSet< Keyword>关键词{get;组; } 
 
} 
 
 public class Item 
 {
 
 public Item（）
 {
关键字=新列表与LT;关键字>（）; 
} 
 
 public int ItemId {get;组; } 
 public string Text {get;组; } 
 public virtual List< Keyword>关键词{get;组; 
 
} 
 
 public class关键字
 {
 
 public Keyword（）
 {
 Items = new的HashSet<项目>（）; 
} 
 
 public int KeywordId {get;组; } 
 public string Name {get;组; } 
 public virtual ICollection< Item>物品{get;组; } 
 
} 
  

据我所知，这很简单。当我的迁移生成数据库时，我得到3个表项目，关键字和项目关键字的交叉引用表（我在OnModelCreating方法中映射）。

我有麻烦的是创建Linq查询（查询语法或方法语法，我不在乎）来重现：

  SELECT DISTINCT 
 i。* 
 FROM 
项目i 
 INNER JOIN ItemKeywords ik 
 ON i.ItemId = ik。 ItemId 
 INNER JOIN关键字k 
 ON ik.KeywordId = k.KeywordId 
 WHERE 
 k.KeywordId IN（1，3，5，7，9）
  

所以，基本上，我想要一个不同的项目列表，其中有一个指定的关键字分配给他们。

我想我可以通过创建一个ItemKeyword模型来获得它的工作，但这似乎会打破代码优先级。我会在那里添加一个额外的模型，只是为了能够对其进行查询。

有没有办法让这个工作没有添加第3次外部参照模型？

谢谢！

解决方案

我想你在找什么是一个这样的查询：

  var keywordIds = new List< int> {1，3，5，7，9}; 
 var items =（from s in db.Items 
 from c in s.Keywords 
其中，keywordIds.Contains（c.KeywordId）
 select s）.Distinct（）; 
  

如果要将所有选定的项目带入内存，请调用 ToList 方法在此查询结尾。

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!

解决方案

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.

这篇关于如何使用Linq连接实体表与交叉引用表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！