有了SubSonic有没有外键表达关系的方法？

问题描述

我们的数据库没有外键，但我们希望能够使用SubSonic。除了外键来表示表之间的关系外，还有什么办法吗？

另见

解决方案

有三种可能的解决方案出现在我的脑海里：

a）困难的方法：改变自己

由SubSonic生成的每个类都是一个部分类。你可以创建一个新的文件，并为你自己添加ForeignKey关系的属性方法：

$ p $ partial class Order
{
私人OrderDetailCollection orderDetails;
public OrderDetailCollection OrderDetails
{
get
{
if（orderDetailCollection == null）
orderDetailCollection = new OrderDetailCollection（）
.Where OrderDetailCollection.Columns.OrderId，this.Id）.Load（）;

返回orderDetailCollection;




$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ orderDetail.OrderId = this.Id;
}
orderDetailCollection = value;




$ b公共类OrderDetail
{
private Order order;
公共订单订单
{
获得{return order？从< Order>（）
.Where（Id）.IsEqualTo（this.OrderId）
.ExecuteSingle< Order>（）; }
set
{
this.OrderId = value == null？ 0：value.Id;
this.Order = value;





这是从内存写出来的未经测试。您可以查看生成的代码或模板应该如何（SubSonic添加了一些事件处理程序，用于添加和删除项目，以处理ParentId更新并在BindingContext中维护DeleteList）
$ b b）简单的方法：建立一个使用外键代码生成的数据库（我推荐这个解决方案），让SubSonic为你生成外键部分。
不要碰你的生产分贝。

在运行时SubSonic（至少2.x）不依赖任何真正的外键存在。信息模式只在DAL生成期间被查询。

c）面向对象的方式：编写您自己的SubSonic DataProvider， GetTableSchema（）方法

pre $ $ $ $ $ $ $ $ （TableName ==Orders）
{
tbl.ForeignKeys = new TableSchema.ForeignKeyTableCollection（） ;

tbl.Columns.GetColumn（Id）。ForeignKeyTableName =OrderDetails;
TableSchema.ForeignKeyTable fkTable = new TableSchema.ForeignKeyTable（this）;
fkTable.ColumnName =OrderId;
fkTable.TableName =OrderDetails;
tbl.ForeignKeys.Add（fkTable）;

else if（tableName ==SomethingElse）
{
....
}
}

（MySql Data Providers是一个很好的例子，因为默认的MySqlDataProviderdoesn即使与InnoDb数据库一起使用，MySqlInnoDbDataProvider也会继承它并覆盖所需的部分。

在您的app.config / web .config你可以定义DataProvider进行生成。

这些建议适用于SubSonic2，但可能是应用程序SubSonic3电缆。

Our database does not have foreign keys, but we wish to be able to use SubSonic. Are there any ways except for foreign keys to express relationships between tables?

Also see this related question

解决方案

There are three possible solutions that come to my mind:

a) The hard way: Do everthing yourself

Every class that is generated by SubSonic is a partial class. You can create a new file and add the properties methods for ForeignKey relation yourself:

partial class Order
{
    private OrderDetailCollection orderDetails;
    public OrderDetailCollection OrderDetails
    {
        get
        {
            if (orderDetailCollection == null)
                orderDetailCollection = new OrderDetailCollection()
                 .Where(OrderDetailCollection.Columns.OrderId, this.Id).Load();

           return orderDetailCollection;
        }
        set
        {               
            if (value != null)
            {
                foreach(OrderDetail orderDetail in value)
                    orderDetail.OrderId = this.Id;
            }
            orderDetailCollection = value;
        }
   }

}

public Class OrderDetail
{
    private Order order;
    public Order Order
    {
        get { return order ?? DB.Select().From<Order>()
                                .Where(Id).IsEqualTo(this.OrderId)
                                .ExecuteSingle<Order>(); }
        set
        {
            this.OrderId = value == null ? 0 : value.Id;
            this.Order = value;
        }
    }
}

That was written from memory and not tested. You could look at the generated code or the templates how it should be (SubSonic adds some eventhandlers for items added and removed to handle the ParentId updates and maintaining a DeleteList for usage in a BindingContext right)

b) The easy way: Set up a database for the code generation that uses foreign keys (I would recommend this solution) and let SubSonic generate the foreign key parts for you.Don't touch your production db.
At runtime SubSonic (at least 2.x) does not rely on any real foreign keys to exist. The information schema is only queried during the DAL generation.

c) The object oriented way: Write your own SubSonic DataProvider that inherits from the one you use at the moment and override the GetTableSchema() method

public override TableSchema.Table GetTableSchema(string tableName, TableType tableType)
{
     TableSchema.Table tbl = base.GetTableSchema(tableName, tableType)

     if (tableName == "Orders")
     {
          tbl.ForeignKeys = new TableSchema.ForeignKeyTableCollection();

          tbl.Columns.GetColumn("Id").ForeignKeyTableName = "OrderDetails";
          TableSchema.ForeignKeyTable fkTable = new TableSchema.ForeignKeyTable(this);
          fkTable.ColumnName = "OrderId";
          fkTable.TableName = "OrderDetails";
          tbl.ForeignKeys.Add(fkTable);
     }
     else if (tableName == "SomethingElse)
     {
          ....
     } 
}

In this method all the information schema data is pulled from the Database and used for setting up an in memory representation of your db. Here is the MySqlInnoDbDataProvider's source: http://github.com/subsonic/SubSonic-2.0/blob/master/SubSonic/DataProviders/MySqlInnoDBDataProvider.cs

(The MySql Data Providers are a great example because the default "MySqlDataProvider" doesn't generate FK-Relations, even if used with an InnoDb DB and the "MySqlInnoDbDataProvider" inherits from it and overrides the needed parts.

In your app.config/web.config you can define the DataProvider for generation.

These suggestions are for SubSonic2 but are probably applicable for SubSonic3.

这篇关于有了SubSonic有没有外键表达关系的方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！