EntityFramework从延迟加载的外键关系中选择特定列。

Question

我有三张很简单的桌子连接在一起。其中两个表各有三列，主要是整数。第三列约有15列，大部分为varchar。

表是这样的：Spellbooks，Spells，SpellbookSpells。对于Spellbooks中的每条记录，SpellbookSpells中都有零到多条记录，其中有一列用于Spellbook_Id和Spell_Id。

所以当我得到Spellbook book = db.Spellbooks.Find(id);，

我得到一个具有SpellbookSpells属性的对象，我可以使用它来有效地获取List<Spell>。这样，当我通过JSON返回数据时，它看起来有点像这样：

{
  "SpellbookSpells": [
    {
      "Spell": {
        "SpellbookSpells": [],
        "Id": 1,
        "Name": "Abi-Dalzim's Horrid Wilting",
        "Description": "<p>You draw the moisture from every creature in a 30-foot cube centered on a point you choose within range. Each creature in that area must make a Constitution saving throw. Constructs and undead aren't affected, and plants and water elementals make this saving throw with disadvantage. A creature takes 10d8 necrotic damage on a failed save, or half as much damage on a successful one.You hurl a bubble of acid. Choose one creature within range, or choose two creatures within range that are within 5 feet of each other. A target must succeed on a Dexterity saving throw or take 1d6 acid damage.</p><p>This spells damage increases by 1d6 when you reach 5th Level (2d6), 11th level (3d6) and 17th level (4d6).</p>",
        "Page": "ee pc 15",
        "Range": "150 feet",
        "Components": "V, S, M",
        "Ritual": false,
        "Duration": "Instantaneous",
        "Concentration": false,
        "CastingTime": "1 action",
        "Level": 8,
        "School": "Necromancy",
        "Classes": "Sorcerer, Wizard",
        "Archetype": null,
        "Domains": null,
        "Oaths": null,
        "Circles": null
      },
      "id": 1,
      "spellbook_id": 5,
      "spell_id": 1
    },
    {
      "Spell": {
        "SpellbookSpells": [],
        "Id": 2,
        "Name": "Absorb Elements",
        "Description": "<p>The spell captures some of the incoming energy, lessening its effect on you and storing it for your next melee attack. You have resistance to the triggering damage type until the start of your next turn. Also, the first time you hit with a melee attack on your next turn, the target takes an extra 1d6 damage of the triggering type, and the spell ends.</p>",
        "Page": "ee pc 15",
        "Range": "Self",
        "Components": "S",
        "Ritual": false,
        "Duration": "1 round",
        "Concentration": false,
        "CastingTime": "1 action",
        "Level": 1,
        "School": "Abjuration",
        "Classes": "Druid, Ranger, Wizard",
        "Archetype": null,
        "Domains": null,
        "Oaths": null,
        "Circles": null
      },
      "id": 3,
      "spellbook_id": 5,
      "spell_id": 2
    },
    {
      "Spell": {
        "SpellbookSpells": [],
        "Id": 4,
        "Name": "Aganazzar's Scorcher",
        "Description": "<p>A line of roaring flame 30 feet long and 5 feet wide emanates from you in a direction you choose. Each creature in the line must make a Dexterity saving throw. A creature takes 3d8 fire damage on a failed save, or half as much damage on a successful one.</p>",
        "Page": "ee pc 15",
        "Range": "30 feet",
        "Components": "V, S, M",
        "Ritual": false,
        "Duration": "Instantaneous",
        "Concentration": false,
        "CastingTime": "1 action",
        "Level": 2,
        "School": "Evocation",
        "Classes": "Sorcerer, Wizard",
        "Archetype": null,
        "Domains": null,
        "Oaths": null,
        "Circles": null
      },
      "id": 4,
      "spellbook_id": 5,
      "spell_id": 4
    },
    {
      "Spell": {
        "SpellbookSpells": [],
        "Id": 6,
        "Name": "Alarm",
        "Description": "<p>You set an alarm against unwanted intrusion. Choose a door, a window, or an area within range that is no larger than a 20-foot cube. Until the spell ends, an alarm alerts you whenever a Tiny or larger creature touches or enters the warded area. When you cast the spell, you can designate creatures that won’t set off the alarm. You also choose whether the alarm is mental or audible.</p><p>A mental alarm alerts you with a ping in your mind if you are within 1 mile of the warded area. This ping awakens you if you are sleeping.</p><p>An audible alarm produces the sound of a hand bell for 10 seconds within 60 feet.</p>",
        "Page": "phb 211",
        "Range": "30 feet",
        "Components": "V, S, M",
        "Ritual": true,
        "Duration": "8 hours",
        "Concentration": false,
        "CastingTime": "1 minute",
        "Level": 1,
        "School": "Abjuration",
        "Classes": "Ranger, Ritual Caster, Wizard",
        "Archetype": null,
        "Domains": null,
        "Oaths": null,
        "Circles": null
      },
      "id": 5,
      "spellbook_id": 5,
      "spell_id": 6
    }
  ],
  "Id": 5,
  "Name": "Krud",
  "UserId": "6922cf7c-6a86-4b7b-89d9-32ebbae43b8f"
}

但是，我想要的Spell的唯一属性是Id、Name、Level和Classes

如何接受Spellbook book = db.Spellbooks.Find(id);而只选择Spells的特定列

Answer 1

生成所需数据的基本查询是

var spellData = from sbs in db.SpellBookSpells
                where sbs.SpellBook_Id == id
                select new
                {
                    sbs.Id,
                    sbs.SpellBook_Id,
                    sbs.Spell_Id,
                    Spell = new
                    {
                        sbs.Spell_Id,
                        sbs.Spell.Name,
                        sbs.Spell.Level,
                        sbs.Spell.Classes
                    }
                };

现在，这取决于您是否希望将其序列化为匿名类型、DTO或原始实体。

如果这是第一个选择，你就完蛋了。对于DTO，您应该将DTO类名添加到查询中：

var spellData = from sbs in db.SpellBookSpells
                where sbs.SpellBook_Id == id
                select new SpellBookSpellDto
                {
                    sbs.Id,
                    sbs.SpellBook_Id,
                    sbs.Spell_Id,
                    Spell = new SpellDto
                    {
                        sbs.Spell_Id,
                        sbs.Spell.Name,
                        sbs.Spell.Level,
                        sbs.Spell.Classes
                    }
                };

为了获得原始实体，您应该这样做

var spellDataEntities = from sbs in spellData.AsEnumerable()
                        select new SpellBookSpell
                        {
                            sbs.Id,
                            sbs.SpellBook_Id,
                            sbs.Spell_Id,
                            Spell = new Spell
                            {
                                sbs.Spell_Id,
                                sbs.Spell.Name,
                                sbs.Spell.Level,
                                sbs.Spell.Classes
                            }
                        };

AsEnumerable()调用是必要的，因为您不能直接在entity ( IQueryable)中创建实体类型。

通过使用这些选项之一，您可以在一个查询中加载所有必需的数据，而不是多一个字节。通过使用Find，首先加载一个不再真正使用的SpellBook对象，然后延迟加载它的SpellbookSpells。即使您只从此集合中选择有限数量的属性，也会首先加载完整的实体。