Linq2SQL-继承多个SQL语句

Question

在使用Linq2SQL的示例应用程序中实现AdventureWorksdb时，我遇到了一个问题，即Linq2Sql为DBML中定义的继承映射中的派生类型生成SQL语句的方式。

我的DBML中有两个实体，Person和PersonPhone（一个Person对多个PersonPhone），没有定义任何继承映射（在后面的示例中，Person将是继承基类）。如果我运行以下Linq语句，我会注意到在进行跟踪时，会按预期生成以下SQL：

void Main()
{
this.DeferredLoadingEnabled = false;
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Person>(n => n.PersonPhones);
this.LoadOptions = dlo;
Person person = this.Persons.SingleOrDefault(n => n.BusinessEntityID == 291);
person.PersonPhones.Dump(); 
}
DECLARE @p0 Int = 291
SELECT [t0].[BusinessEntityID], [t0].[PersonType], [t0].[NameStyle], [t0].[Title], [t0].[FirstName], [t0].[MiddleName], [t0].[LastName], [t0].[Suffix], [t0].[EmailPromotion], [t0].[AdditionalContactInfo], [t0].[Demographics], [t0].[rowguid], [t0].[ModifiedDate], [t1].[BusinessEntityID] AS [BusinessEntityID2], [t1].[PhoneNumber], [t1].[PhoneNumberTypeID], [t1].[ModifiedDate] AS [ModifiedDate2], (
    SELECT COUNT(*)
    FROM [Person].[PersonPhone] AS [t2]
    WHERE [t2].[BusinessEntityID] = [t0].[BusinessEntityID]
    ) AS [value]
FROM [Person].[Person] AS [t0]
LEFT OUTER JOIN [Person].[PersonPhone] AS [t1] ON [t1].[BusinessEntityID] = [t0].[BusinessEntityID]
WHERE [t0].[BusinessEntityID] = @p0
ORDER BY [t0].[BusinessEntityID], [t1].[PhoneNumber], [t1].[PhoneNumberTypeID]

结果是SQL是在一条语句中生成的。请忽略这样一个事实，即这可能是低效的SQL，我要说的是，它在一条语句中运行。

但是，如果我在DBML中包含继承映射信息，并定义一个名为StoreContact（从Person派生）的新实体类型，并运行完全相同的查询，我实际上会得到不同的结果，如下所示。其中实际运行了两个SQL语句。

DECLARE @p0 Int = 291
SELECT [t0].[PersonType], [t0].[BusinessEntityID], [t0].[NameStyle], [t0].[Title], [t0].[FirstName], [t0].[MiddleName], [t0].[LastName], [t0].[Suffix], [t0].[EmailPromotion], [t0].[AdditionalContactInfo], [t0].[Demographics], [t0].[rowguid], [t0].[ModifiedDate]
FROM [Person].[Person] AS [t0]
WHERE [t0].[BusinessEntityID] = @p0
GO
DECLARE @x1 Int = 291
SELECT [t0].[BusinessEntityID], [t0].[PhoneNumber], [t0].[PhoneNumberTypeID], [t0].[ModifiedDate]
FROM [Person].[PersonPhone] AS [t0]
WHERE [t0].[BusinessEntityID] = @x1

这有点问题，因为这将大大降低我们的应用程序的速度，因为在实现继承功能的情况下，Linq2SQL需要对DB进行重复调用。看起来拥有OOP模型是有代价的。

对于这个问题，有没有一个变通方法可以让SQL在一条语句中运行？

Answer 1

不幸的是，这不是我想要的答案，但升级到实体框架可以在维护继承的同时解决问题。

如果我运行以下EF等效查询：

void Main()
{
    StoreContact person  =(StoreContact)this.People.Include("PersonPhones").SingleOrDefault(n => n.BusinessEntityID == 291);
    person.PersonPhones.Dump(); 
}

我得到以下SQL结果：

SELECT 
[Project2].[BusinessEntityID] AS [BusinessEntityID], 
[Project2].[C1] AS [C1], 
[Project2].[NameStyle] AS [NameStyle], 
[Project2].[Title] AS [Title], 
[Project2].[FirstName] AS [FirstName], 
[Project2].[MiddleName] AS [MiddleName], 
[Project2].[LastName] AS [LastName], 
[Project2].[Suffix] AS [Suffix], 
[Project2].[EmailPromotion] AS [EmailPromotion], 
[Project2].[AdditionalContactInfo] AS [AdditionalContactInfo], 
[Project2].[Demographics] AS [Demographics], 
[Project2].[rowguid] AS [rowguid], 
[Project2].[ModifiedDate] AS [ModifiedDate], 
[Project2].[C2] AS [C2], 
[Project2].[BusinessEntityID1] AS [BusinessEntityID1], 
[Project2].[PhoneNumber] AS [PhoneNumber], 
[Project2].[PhoneNumberTypeID] AS [PhoneNumberTypeID], 
[Project2].[ModifiedDate1] AS [ModifiedDate1]
FROM ( SELECT 
    [Limit1].[BusinessEntityID] AS [BusinessEntityID], 
    [Limit1].[NameStyle] AS [NameStyle], 
    [Limit1].[Title] AS [Title], 
    [Limit1].[FirstName] AS [FirstName], 
    [Limit1].[MiddleName] AS [MiddleName], 
    [Limit1].[LastName] AS [LastName], 
    [Limit1].[Suffix] AS [Suffix], 
    [Limit1].[EmailPromotion] AS [EmailPromotion], 
    [Limit1].[AdditionalContactInfo] AS [AdditionalContactInfo], 
    [Limit1].[Demographics] AS [Demographics], 
    [Limit1].[rowguid] AS [rowguid], 
    [Limit1].[ModifiedDate] AS [ModifiedDate], 
    [Limit1].[C1] AS [C1], 
    [Extent2].[BusinessEntityID] AS [BusinessEntityID1], 
    [Extent2].[PhoneNumber] AS [PhoneNumber], 
    [Extent2].[PhoneNumberTypeID] AS [PhoneNumberTypeID], 
    [Extent2].[ModifiedDate] AS [ModifiedDate1], 
    CASE WHEN ([Extent2].[BusinessEntityID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
    FROM   (SELECT TOP (2) 
        [Extent1].[BusinessEntityID] AS [BusinessEntityID], 
        [Extent1].[NameStyle] AS [NameStyle], 
        [Extent1].[Title] AS [Title], 
        [Extent1].[FirstName] AS [FirstName], 
        [Extent1].[MiddleName] AS [MiddleName], 
        [Extent1].[LastName] AS [LastName], 
        [Extent1].[Suffix] AS [Suffix], 
        [Extent1].[EmailPromotion] AS [EmailPromotion], 
        [Extent1].[AdditionalContactInfo] AS [AdditionalContactInfo], 
        [Extent1].[Demographics] AS [Demographics], 
        [Extent1].[rowguid] AS [rowguid], 
        [Extent1].[ModifiedDate] AS [ModifiedDate], 
        '0X0X' AS [C1]
        FROM [Person].[Person] AS [Extent1]
        WHERE 291 = [Extent1].[BusinessEntityID] ) AS [Limit1]
    LEFT OUTER JOIN [Person].[PersonPhone] AS [Extent2] ON [Limit1].[BusinessEntityID] = [Extent2].[BusinessEntityID]
)  AS [Project2]
ORDER BY [Project2].[BusinessEntityID] ASC, [Project2].[C2] ASC

SQL语句在单个查询中返回结果。

这不是我想要的答案的原因是，对于现有应用程序来说，从Linq到SQL到实体框架的转换是相当大的一步。