linq - EF Core Linq 查询语法

Question

我正在尝试在 EF 中复制一个视图。原始视图使用了 UNION 运算符，因此将我的初始查询拆分为两个以便更好地匹配。在解决我的语法时遇到一些问题。我正在尝试查询这些组，获取他们的主要联系方式。同时获取所有车辆及其关联的组。我相信我已经在第一个查询中正确指定了 ContactLink 要求。

[BindProperty]
public IList<OrgChartNode> OrgChartNodes { get; set; }

public JsonResult OnGet(string OrgCode)
{
            IList<OrgChartNode> OrgChartGroups = _db.Groups
                                                .Include(g => g.ContactLinks)
                                                    .ThenInclude(gtc => gtc.Contact)
                                                .Include(g => g.Organisation)
                                                .Where(g => g.OrgCode.Equals(OrgCode))
                                                .Where(g => g.ContactLinks.Any(cl => cl.LinkTypeId == 1))
                                                .Select(g => new OrgChartNode
                                                {
                                                    Id = g.Id,
                                                    Name = g.Name,
                                                    TierId = g.TierId,
                                                    ParentGroupId = g.ParentGroupId,
                                                    OrgName = g.Organisation.Name,
                                                    OrgCode = g.OrgCode,
                                                    ContactName = g.ContactLinks.**Contact**.Name,
                                                    ContactEmail = g.ContactLinks.**Contact**.Phone,
                                                    ContactPhone = g.ContactLinks.**Contact**.Email,
                                                    ContactId = g.ContactLinks.**Contact**.ContactId,
                                                })
                                                .OrderByDescending(g => g.TierId)
                                                .ThenBy(g => g.Name)
                                                .AsNoTracking()
                                                .ToList();

        IList<OrgChartNode> OrgChartUnits = _db.Units
                                                .Include(u => u.GroupLinks)
                                                .Include(u => u.Organisation)
                                                .Where(u => u.OrgCode.Equals(OrgCode))
                                                .Select(u => new OrgChartNode
                                                {
                                                    Id = u.NodeId,
                                                    Name = u.Name,
                                                    TierId = 0,
                                                    ParentGroupId = u.GroupLinks.**GroupId**,
                                                    OrgName = u.Organisation.Name,
                                                    OrgCode = u.OrgCode,
                                                    ContactName = "",
                                                    ContactEmail = "",
                                                    ContactPhone = "",
                                                    ContactId = 0,
                                                })
                                                .OrderBy(u => u.Name)
                                                .AsNoTracking()
                                                .ToList();

        OrgChartNodes = OrgChartGroups.Concat(OrgChartUnits)
                                .ToList();

        return new JsonResult(OrgChartNodes);
}

四个联系人字段已标记为 **。它们在 Visual Studio 中有红色的智能感知线。ThenInclude()尽管它在上面，但它看起来像猫跟随联系人。第二个查询中的 GroupId 也是如此。将鼠标悬停在首字母缩写词/别名上告诉我它们似乎是正确的类。

下面列出了三个数据类。您可以看到导航属性。它们配置正确吗？

如何在.Select()通话中包含联系方式？

组.cs

[Table("Report_Group")]
public class Group
{
    [Key, Required, Column("GroupId")]
    public int Id { get; set; }

    [Required, MaxLength(5)]
    public string OrgCode  { get; set; }

    [Required, MaxLength(100)]
    public string Name { get; set; }

    public int TierId { get; set; }

    public int? ParentGroupId { get; set; }

    public int? CostCenter { get; set; }

    [Required]
    public int ExcludeFromAlertStats { get; set; }

    [Required]
    public int GroupTypeId { get; set; }

    [ForeignKey("ParentGroupId")]
    public virtual Group Parent { get; set; }

    [ForeignKey("OrgCode")]
    public virtual Organisation Organisation { get; set; }

    [ForeignKey("TierId")]
    public virtual Tier Tier { get; set; }

    [ForeignKey("GroupTypeId")]
    public virtual GroupType GroupType { get; set; }

    [InverseProperty("GroupId")]
    public virtual IList<GroupToUnitLink> UnitLinks { get; set; }

    [InverseProperty("GroupId")]
    public virtual IList<GroupToContactLink> ContactLinks { get; set; }
}

GroupToContactLink.cs

这个类键是使用 fluent in 设置的，OnModelCreating()因为它是一个复合键。

[Table("Report_Link_Group_to_Contact")]
public class GroupToContactLink
{
    public GroupToContactLink()
    {
    }

    public GroupToContactLink(int contactId, int groupId, int linkTypeId)
    {
        this.ContactId = contactId;
        this.GroupId = groupId;
        this.LinkTypeId = linkTypeId;
    }

    [Required]
    public int ContactId { get; set; }

    [Required]
    public int GroupId { get; set; }

    [Required]
    public int LinkTypeId { get; set; }

    [ForeignKey("ContactId")]
    public virtual Contact Contact { get; set; }

    [ForeignKey("GroupId")]
    public virtual Group Group { get; set; }

    [ForeignKey("LinkTypeId")]
    public virtual ContactLinkType LinkType { get; set; }

}

联系人.cs

[Table("Report_Contact")]
public class Contact
{
    /// <summary>
    /// Primary Key for Contact in the database
    /// </summary>
    [Key, Column("ContactId")]
    public int Id { get; set; }

    /// <summary>
    /// Foreign Key indicating <see cref="Data.Organisation"/>
    /// </summary>
    [Required, StringLength(5)]
    public string OrgCode { get; set; }

    /// <summary>
    /// Name of Contact
    /// </summary>
    [Required, StringLength(100)]
    public string Name { get; set; }

    /// <summary>
    /// Phone number of contact.  Needs to be in +614 format for SMS to work
    /// </summary>
    [StringLength(12)]
    public string Phone { get; set; }

    /// <summary>
    /// Email Address for Contact
    /// </summary>
    [StringLength(255), EmailAddress]
    public string Email { get; set; }

    /// <summary>
    /// Navigation property to <see cref="Data.Organisation"/>
    /// </summary>
    [ForeignKey("OrgCode")]
    public virtual Organisation Organisation { get; set; }

    [InverseProperty("ContactId")]
    public virtual IList<GroupToContactLink> GroupLinks { get; set; }
}

标签： linqentity-framework-coreef-core-2.0