I am quite new to LINQ, and I have some problems. I've tried googling for a while, but I still have not found any precice answer to my problem, so I'll ask as well.
I have put up a test database on Microsoft SQL Server, with the two tables "Person2" and "Department2". Person2 has a many-to-one relationship with Department2. Many persons belong to only one department.
Person2 has these attributes:
- id (int, Primary Key)
- Name (nchar(50))
- phoneNumber (nchar(10))
- DepartmentID (int, Foreign Key with name: "fk_Department2_DepartmentID")
Department2 has these attributes:
- DepartmentID (int, Primary Key)
- DepartmentDesc (nchar(50))
The C#-code consists of four classes on two project files: Person, Department and Program (which runs the test) on one, and TabellTest on another. This is the code I'm trying to run:
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);
}
}
}
}
}
The core of the problem is this code segment in the class 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; }
}
Whenever I try to run the program, I get this exception:
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
A solution I found when googling the problem suggested that I add the OtherKey attribute to the association, in which case, the Association code becomes like this:
[Association(Name = "FK_Person_PersonDepartment", IsForeignKey = true,
Storage = "_department", ThisKey = "personDepartmentID", OtherKey = "departmentID")]
(I've also tried with capital D : OtherKey = "DepartmentID")]
When I do this, I get this exception:
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
The irony is that the Association-segment from Department, which operates with an EntitySet uses both the keys (just switched on ThisKey and OtherKey), and works. In other words: I have trouble getting an object of Department from the database in the Person class, while it works to get set of objects of Person in the Department-code.
Now, dear readers and programmers, what do you suggest I do?
