通过外键关联两个以上的表

问题描述

create table Customer
(
 ID int not null primary key,
 Name varchar(30) not null
)
create table Purchase
(
 ID int not null primary key,
 CustomerID int references Customer (ID),
 Description varchar(30) not null,
 Price decimal not null
)

上面的第一个是通过我的SQL Server Management Studio创建一个SQL脚本以创建两个表(Customer和Purchase).然后将以下类添加到C#代码中，如下所示.

First above is an sql script through my sql server management studio to create two tables (Customer and Purchase).Then the following classes is added to the C# code as follows.

[Table(Name = "Customer")]
public class Customer
{
    [Column(IsPrimaryKey = true)]
    public int ID;
    [Column(Name = "Name")]
    public string Name;
}

[Table(Name = "Purchase")]
public class Purchase
{
    [Column(IsPrimaryKey = true)]
    public int ID;
    [Column]
    public int CustomerID;
    [Column]
    public string Descriptions;
    [Column]
    public decimal Price;
}

这是主要功能.

DataContext dataContext = new DataContext(@"Server=.\SQLEXPRESS;Database=master;Trusted_Connection=True;");
Table<Customer> customers = dataContext.GetTable<Customer>();

foreach (Purchase p in customers.Purchases)
    Console.WriteLine(p.Price);

这给了我关于foreach语句的错误.

And it's giving me an error on the foreach statement.

推荐答案

您在LINQ表定义中没有定义关联.根据您的情况，看起来像这样:

You have no association defined on your LINQ Table definitions. It would look something like this for your situation:

[Table(Name = "Customer")]
public class Customer
{
    [Column(IsPrimaryKey = true, Name = "ID")]
    public int ID { get; set; }

    [Column(Name = "Name")]
    public string Name { get; set; }

    [Association(Name = "Customer_Purchases", ThisKey = "ID", OtherKey = "CustomerID")]
    public EntitySet<Purchase> PurchaseList { get; set; }

    public List<Purchase> Purchases
    {
        get
        {
            return new List<Purchase>(PurchaseList.AsEnumerable());
        }
    }

}

[Table(Name = "Purchase")]
public class Purchase
{
    [Column(IsPrimaryKey = true, Name = "ID")]
    public int ID { get; set; }

    [Column(Name = "CustomerID")]
    public int CustomerID { get; set; }

    [Column(Name = "Description")]
    public string Description { get; set; }

    [Column(Name = "Price")]
    public decimal Price { get; set; }

}

然后您可以像这样枚举客户:

You can then enumerate your customers like so:

var customers = customerTable.ToList();

foreach (Customer customer in customers)
{
    foreach (Purchase purchase in customer.Purchases)
    {
        Console.WriteLine("My data here...");
    }
}

这篇关于通过外键关联两个以上的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！