c# - LINQ : Reading objects from database, 出现异常时遇到问题

Question

我是 LINQ 的新手，遇到了一些问题。我已经尝试谷歌搜索了一段时间，但我仍然没有找到我的问题的任何精确答案，所以我也会问。

我在 Microsoft SQL Server 上建立了一个测试数据库，其中包含两个表“Person2”和“Department2”。 Person2 与 Department2 具有多对一关系。许多人只属于一个部门。

Person2 具有以下属性:

• id(整数，主键)
• 姓名(nchar(50))
• 电话号码(nchar(10))
• DepartmentID(整数，名称为“fk_Department2_DepartmentID”的外键)

Department2 具有以下属性:

• 部门 ID(整数，主键)
• DepartmentDesc (nchar(50))

C# 代码由两个项目文件上的四个类组成:Person、Department 和 Program(运行测试)在一个文件上，TabellTest 在另一个文件上。这是我要运行的代码:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace DBTest
{
    [Database]
    public class TableTest : DataContext 
    {
        public Table<Person> persons;
        public Table<Department> departments;

        public TabellTest(String ConnectionString):
            base(ConnectionString){}

    }

}


using System;
using System.Collections;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace DBTest
{
    [Table(Name = "Person2")]
    public class Person
    {

        public Person() { }

        [Column(IsPrimaryKey = true)]
        private int id;
        public int Id
        {
            get { return this.id; }
            set { this.id = value; }
        }

        [Column]
        private string name;
        public string Name
        {
            get { return this.name; }
            set { this.name = value; }
        }

        [Column]
        private string phoneNumber;
        public string PhoneNumber
        {
            get { return this.phoneNumber;}
            set { this.phoneNumber = value;}
        }

        [Column (Name="DepartmentID")]
        private int? personDepartmentID;
        public int? PersonDepartmentID
        {
            get { return this.personDepartmentID; }
            set { this.personDepartmentID = value; }
        }

        [Association(Name = "FK_Person_PersonDepartment",
        IsForeignKey = true, Storage = "_department", ThisKey = "personDepartmentID")]
        private EntityRef<Department> _department;
        public Department Department
        {
            get { return _department.Entity; }
            set { _department.Entity = value; }
        }



    }

    [Table (Name = "Department2")]
    public class Department {

        public Department() { }

        public Department(int departmentID, string departmentDesc)
        {
            this.departmentID = deparmentID;
            this.departmentDesc = departmentDesc;
        }

        [Column(IsPrimaryKey = true)]
        private int departmentID;
        public int DepartmentID
        {
            get {return this.departmentID; }
            set {this.departmentID = value; }
        }

        [Column]
        private string departmentDesc;
        public string DepartmentDesc
        {
            get { return this.departmentDesc; }
            set { this.departmentDesc = value; }
        }

        private EntitySet<Person> _person = new EntitySet<Person>();
        [Association(Name = "FK_Person_PersonDepartment",
        IsForeignKey = true, Storage = "_person", ThisKey = "departmentID", OtherKey="personDepartmentID")]
        public EntitySet<Person> person
        {
            get { return _person; }
            set { _person = value; } 
        }


    }

    class Program
    {
        static void Main(string[] args)
        {
            TableTest Test = new TableTest("REMOVED FOR STACKOVERFLOW.COM, JUST ASSUME IT WORKS");

            foreach (Person pers in Test.persons)
            {
                Console.WriteLine(pers.Name + " " + pers.Id + " " + pers.PhoneNumber + " " + pers.Department.DepartmentID + " " + pers.Department.DepartmentDesc);
            }

            Console.WriteLine("\n\n");
            foreach (Department dep in Test.department)
            {
                Console.WriteLine(dep.DepartmentID + " " + dep.DepartmentDesc);
                foreach (Person pers in dep.person)
                {
                    Console.WriteLine(pers.Name + " " + pers.Id + " " + pers.PhoneNumber);
                }
            }

        }
    }
}

问题的核心是Person类中的这段代码:

    [Association(Name = "FK_Person_PersonDepartment", IsForeignKey = true, 
    Storage = "_department", ThisKey = "personDepartmentID")]
    private EntityRef<Department> _department;
    public Department Department
    {
        get { return _department.Entity; }
        set { _department.Entity = value; }
    }

每当我尝试运行该程序时，我都会收到此异常:

Unhandled Exception: System.InvalidOperationException: The number of ThisKey columns is different from the number of OtherKey columns for the association property '_department' in the type 'Person' at ....... etc etc etc

我在谷歌搜索问题时发现的一个解决方案建议我将 OtherKey 属性添加到关联中，在这种情况下，关联代码将变成这样:

[Association(Name = "FK_Person_PersonDepartment", IsForeignKey = true, 
Storage = "_department", ThisKey = "personDepartmentID", OtherKey = "departmentID")]

(我也尝试过使用大写字母 D:OtherKey = "DepartmentID")]

当我这样做时，我得到了这个异常:

Unhandled Exception: System.InvalidOperationException: Could not find key member 'departmentID' of key 'departmentID' on type ´EntityRef 1´. The key may be wrong or the field or property on ´EntityRef1´ has changed names. at ....etc etc etc

具有讽刺意味的是，Department 的 Association-segment 与 EntitySet 一起使用两个键(只是打开 ThisKey 和 OtherKey)，并且可以工作。换句话说:我无法从 Person 类的数据库中获取 Department 的对象，而在 Department 代码中获取 Person 的对象集是可行的。

现在，亲爱的读者和程序员，你们建议我做什么？

Answer 1

看起来它可能只是顺序 - 您正在修改私有(private)成员而不是公共(public)成员；你试过这个吗？

private EntityRef<Department> _department;
[Association(Name = "FK_Person_PersonDepartment", IsForeignKey = true,
   Storage = "_department", ThisKey = "personDepartmentID", OtherKey = "departmentID")]
public Department Department
{
  get { return _department.Entity; }
  set { _department.Entity = value; }
}