查询自动生成的EF联结表 [英] Querying an auto-generated EF junction table

问题描述

我有两个模型，Benefit和SchemeName

I have two models, Benefit and SchemeName

好处-

[Key]
public int BenefitID { get; set; }
public string BenefitName { get; set; }
public string BenefitDescription { get; set; }
public virtual ICollection<SchemeName> SchemeNames { get; set; }

SchemeName

SchemeName

[Key]
public int SchemeNameID { get; set; }
public string Name { get; set; }
public virtual ICollection<Benefit> Benefits { get; set; }

这已在数据库Benefits，SchemeNames和一个名为SchemeNameBenefits的联接表中创建了三个表.

This has created three tables in the database Benefits, SchemeNames and a joining table called SchemeNameBenefits.

我正在尝试填充一个仅包含与某个Benefit相关的SchemeName的droplownlist，但是不确定如何执行此操作，我可以在我的代码中引用联接表吗?

I am trying to populate a droplownlist that contains only the SchemeNames associated with a certain Benefit but am not sure how I can do this, can I reference the join table in my code?

我从以下内容开始(返回所有SchemeName)

I started with the following (which returns all SchemeNames)

private void PopulatePensionSchemeName(object selectedPensionSchemeName = null)
        {
            var schemeNameQuery = from d in db.SchemeNames
                                  orderby d.SchemeNameID
                                  select d;

            ViewBag.PensionSchemeNameID = new SelectList(schemeNameQuery, "SchemeNameID", "Name", selectedPensionSchemeName);
        }

但是我不确定如何添加此子句.有指针吗?

But I'm not sure how I can add this clause. Any pointers?

推荐答案

您将需要想要SchemeNames的Benefit对象的键.您可能正在寻找的查询是:-

You'll need the key of the Benefit object you want the SchemeNames for. The query you're probably looking for is:-

var benefitId = // However you get your benefit Id

var schemaNameQuery = from b in db.Benefits
                      from s in b.SchemeNames
                      where b.BenefitId == benefitId
                      select s;

或使用扩展方法语法:-

Or in the extension method syntax:-

var schemaNameQuery = db.Benefits.Where(b.BenefitId == benefitId)
                                 .SelectMany(b => b.SchemeNames);

哪个会产生以下SQL:-

Which produces the following SQL:-

SELECT ...
FROM [dbo].[SchemeNameBenefits] AS [Extent1]
INNER JOIN [dbo].[SchemeNames] AS [Extent2]
  ON [Extent1].[SchemeName_Id] = [Extent2].[SchemeNameId]
WHERE [Extent1].[Benefit_Id] = @p__linq__0

或者，您可以使用:-

var benefitId = // However you get your benefit Id

var schemeNameQuery = from d in db.SchemeNames
                      where d.Benefits.Any(x => x.Id == benefitId)
                      orderby d.SchemeNameId
                      select d;

这将产生以下SQL:-

This produces the following SQL:-

SELECT ...
FROM ( SELECT ... FROM [dbo].[SchemeNames] AS [Extent1]
       WHERE EXISTS (SELECT 1 AS [C1]
                     FROM [dbo].[SchemeNameBenefits] AS [Extent2]
                     WHERE ([Extent1].[SchemeNameId] = [Extent2].[SchemeName_Id])
                     AND ([Extent2].[Benefit_Id] = @p__linq__0)))
AS ...
ORDER BY [Project2].[Id] ASC

请注意，在这两种情况下，生成的SQL均引用您的联结表，即使它不是EF模型的一部分.

Note that in both cases the generated SQL references your junction table even though it isn't part of your EF model.

当然，如果您已经拥有Benefit对象，则可以使用以下命令更简单地获取其SchemeName:

If you already have the Benefit object, of course, you can get its SchemeNames more simply by using:-

var schemeNameQuery = benefit.SchemeNames;

这篇关于查询自动生成的EF联结表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！