将 Linq 中的外键设置为 SQL [英] Setting Foreign keys in Linq to SQL

问题描述

众所周知，如果实体已经加载，则不能在 Linq to SQL 中直接设置外键 ID.但是，您可以通过它的外键查找实体，然后使用实体关系将实体设置为外部实体.(为了简单起见，我在这里去掉了枚举并使用了整数值).即如果我有一个加载的约会实体和一个关联的约会状态实体，我不能这样做:-

ExistingAppointment.AppointmentStatusID = 7

但我可以这样做:-

ExistingAppointment.AppointmentStatus = (来自 appstat In db.AppointmentStatus _其中 appstat.StatusID = 7 _选择 appstat).单

我的代码中有这种东西，我想重构.所以...

我显然可以在这样的模块中使用辅助方法:-

<代码>模块助手公共共享函数 GetAppointmentStatus(ByVal AppStatusID As Integer) As AppointmentStatusGetAppointmentStatus = (来自 appstat In db.AppointmentStatus _其中 appstat.AppointmentStatusID = AppStatus _选择 appstat).单结束函数终端模块

我什至可以把它变成一个扩展方法，就像这样.

<代码>导入 System.Runtime.CompilerServices模块助手扩展()> _公共共享函数 GetAppointmentStatus(ByVal db as DataClassesDataContext, ByVal AppStatusID As Integer) As AppointmentStatusGetAppointmentStatus = (来自 appstat In db.AppointmentStatus _其中 appstat.AppointmentStatusID = AppStatusID _选择 appstat).单结束函数终端模块

我也可以把它放在 Linq to SQL 部分类中，就像这样.

<代码>部分公共类 DataClassesDataContext公共函数 GetAppointmentStatus(ByVal AppStatusID As Integer) As AppointmentStatusGetAppointmentStatus =(来自 appstat In Me.AppointmentStatus _其中 appstat.AppointmentStatusID = AppStatusID _选择 appstat).单结束函数结束班

此外，我可以将代码放在 Linq to SQL Appointment Entity 部分类中，如下所示:-

<代码>部分公开课预约公共函数 GetAppointmentStatus(ByVal db as DataClassesDataContext, ByVal AppStatusID As Integer) As AppointmentStatusGetAppointmentStatus = (来自 appstat In db.AppointmentStatus _其中 appstat.AppointmentStatusID = AppStatusID _选择 appstat).单结束函数结束班

我应该做什么，为什么，或者有更好的选择吗?

解决方案

对此有两种主要的思想流派:

1. 将逻辑放入DataContext(部分类，如果您手动编写DataContext，则为实际类).这背后的基本原理是您的 DataContext 已经知道所有不同的实体，因此这不会创建任何额外的耦合，也不会导致类膨胀.

   当然，缺点是如果你有几百个这样的 API 方法(你可能最终会有)，那么你的 DataContext 很快就会变成一团泥球，充满对于任何程序员决定投入的每个随机查询 API.您可以尝试通过将相关函数分离到同一部分 DataContext 类的不同实例来清理它，但这实际上只是表面上的改进.>

2. 将逻辑放在存储库类中，即 AppointmentRepository.这种方法的两个优点是 (a) 能够在存储库和 IoC 框架上使用依赖注入，以防您决定更改数据模型，以及 (b) 您坚持 单一职责原则 - 它实际上有意义使方法处于它所在的位置.

   将这些放在存储库中的主要缺点是: (a) 它们可能会将您的 DataContext 中已经存在的非常相似的逻辑复制为存储过程；(b) 在交易管理方面，他们有一种让人头疼的方法(如果你也用它们来保存的话)；(c) 当您开始有大量自定义查询返回针对特定操作或报告的专门定制的 DTO 时，您将面临两个糟糕的选择，要么为每个 DTO 创建一个存储库，要么创建一个主实用程序"所有 DTO 或其中一些松散相关的组的存储库.两者最终都是一个相当糟糕的设计.

这些是权衡；只有您可以决定哪个更适合您自己的目的.

我会绝对建议反对扩展方法方法，因为扩展方法很难发现(你不能只输入方法然后让智能感知选择相关参考)，并且当您有能力直接修改或扩展(通过部分)原始类时，它们也根本没有必要.

我还建议不要扩展 Appointment 类；我们使用 Linq To SQL 之类的工具的原因之一是，我们可以处理不需要知道它们来自哪里的任何 POCO 实体.出于这个原因，我个人非常反对将实体类耦合到它们的 DataContext - 依赖应该只是一种方式.

It's well known that you cannot set foreign key IDs directly in Linq to SQL if the entities have already been loaded. You can however look up the entity by it's foreign key and then set the entity as the foreign entity using the entity relationship. (I've taken out the enum here and used integer values for simplicity). i.e. If I have a loaded Appointment entity and an associated AppoinmentStatus Entity I can't do this:-

ExistingAppointment.AppointmentStatusID = 7

But I can do this:-

ExistingAppointment.AppointmentStatus = (From appstat In db.AppointmentStatus _
                                        Where appstat.StatusID = 7 _
                                        Select appstat).Single

I have this kind of thing littering my code and I'd like to refactor. So...

I could obviously use a helper method in a module like this:-

Module Helper
    Public Shared Function GetAppointmentStatus(ByVal AppStatusID As Integer) As AppointmentStatus
        GetAppointmentStatus = (From appstat In db.AppointmentStatus _
                                       Where appstat.AppointmentStatusID = AppStatus _
                                       Select appstat).Single
    End Function
End Module

I could even make this into an extension method, like this.

Imports System.Runtime.CompilerServices
Module Helper
Extension()> _
    Public Shared Function GetAppointmentStatus(ByVal db as DataClassesDataContext, ByVal AppStatusID As Integer) As AppointmentStatus
        GetAppointmentStatus = (From appstat In db.AppointmentStatus _
                                       Where appstat.AppointmentStatusID = AppStatusID _
                                       Select appstat).Single
    End Function
End Module

I could also put this in the Linq to SQL partial class, like this.

Partial Public Class DataClassesDataContext    
    Public Function GetAppointmentStatus(ByVal AppStatusID As Integer) As AppointmentStatus
        GetAppointmentStatus = (From appstat In Me.AppointmentStatus _
                                       Where appstat.AppointmentStatusID = AppStatusID _
                                       Select appstat).Single
    End Function
End Class

Further I could put the code in the Linq to SQL Appointment Entity partial class like this:-

Partial Public Class Appointment    
    Public Function GetAppointmentStatus(ByVal db as DataClassesDataContext, ByVal AppStatusID As Integer) As AppointmentStatus
            GetAppointmentStatus = (From appstat In db.AppointmentStatus _
                                       Where appstat.AppointmentStatusID = AppStatusID _
                                       Select appstat).Single
    End Function
End Class

Which should I do and why, or is there a better alternative?

解决方案

There are two main schools of thought on this:

1. Put the logic in the DataContext (partial class, or actual class if you code your DataContext by hand). The rationale behind this is that your DataContext already knows about all of your different entities, so this isn't creating any additional coupling and isn't leading to class bloat.

   The disadvantage, of course, is that if you have a few hundred of these API methods (and you probably will, eventually) then your DataContext will quickly start turning into a ball of mud, filled with every random query API any programmer decides to throw in. You can try to clean this up by separating related functions into different instances of the same partial DataContext class, but that's really only a cosmetic improvement.

2. Put the logic in a repository class, i.e. an AppointmentRepository. Two advantages to this approach are (a) the ability to use dependency injection on the repository and an IoC framework, in case you decide to change your data model, and (b) the fact that you're sticking to the Single Responsibility Principle - it actually makes sense for the method to be where it is.

   The main disadvantages to putting these in repositories are: (a) They may be duplicating very similar logic that's already in your DataContext as Stored Procedures; (b) they have a way of creating headaches when it comes to transaction management (if you also use them to save); and (c) when you start to have a lot of custom queries that return specially-tailored DTOs for specific operations or reports, you're left with the two crummy choices of either creating one repository for each and every DTO, or creating one master "utility" repository for all the DTOs or some loosely-related group of them. Both end up being a rather poor design.

Those are the tradeoffs; only you can decide which is better for your own purposes.

I would definitely advise against the extension-method approach, as extension methods are difficult to discover (you can't just type the method and have Intellisense pick up the relevant reference), and they're also simply not necessary when you have the ability to directly modify or extend (via partials) the original class.

I'd also advise against extending the Appointment class; one of the reasons we use tools like Linq To SQL is so we can deal with POCO entities that don't need to know anything about where they came from. For this reason I personally am very much against the coupling of entity classes to their DataContext - the dependency should be only one-way.

这篇关于将 Linq 中的外键设置为 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！