问Linq实体继承生成大型SQL语句
EN

Stack Overflow用户

提问于 2009-11-25 09:39:29

回答 1查看 381关注 0票数 0

我们正在开发一个具有超过10个子实体(继承自它)的基本实体的应用程序。

当我们使用Linq向基本实体发出任何请求时，我们都会得到一个SQL语句，每个子实体都有一个"UNION ALL“。要对基本实体执行Count()，需要近一秒的时间，而只获取一行可能需要两秒。

对于此代码：

public bool Exists(int appId, string loginName, DateTime userRegDate, long ahsayId)
    {
        var backupsets = from backupset in _entities.AhsayBackupSets
                         where
                            backupset.User.Appliance.Id == appId &&
                            backupset.User.LoginName == loginName &&
                            backupset.User.RegistrationDate == userRegDate &&
                            backupset.AhsayId == ahsayId
                         select backupset;
        return backupsets.Count() > 0;
    }

，我们得到这个SQL语句：

exec sp_executesql N'SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    COUNT(1) AS [A1]
    FROM    [dbo].[AhsayBackupSets] AS [Extent1]
    LEFT OUTER JOIN  (SELECT 
        [UnionAll9].[C1] AS [C1]
        FROM  (SELECT 
            [UnionAll8].[C1] AS [C1]
            FROM  (SELECT 
                [UnionAll7].[C1] AS [C1]
                FROM  (SELECT 
                    [UnionAll6].[C1] AS [C1]
                    FROM  (SELECT 
                        [UnionAll5].[C1] AS [C1]
                        FROM  (SELECT 
                            [UnionAll4].[C1] AS [C1]
                            FROM  (SELECT 
                                [UnionAll3].[C1] AS [C1]
                                FROM  (SELECT 
                                    [UnionAll2].[C1] AS [C1]
                                    FROM  (SELECT 
                                        [UnionAll1].[Id] AS [C1]
                                        FROM  (SELECT 
                                            [Extent2].[Id] AS [Id]
                                            FROM [dbo].[AhsayOracleBackupSets] AS [Extent2]
                                        UNION ALL
                                            SELECT 
                                            [Extent3].[Id] AS [Id]
                                            FROM [dbo].[AhsaySystemStateBackupSets] AS [Extent3]) AS [UnionAll1]
                                    UNION ALL
                                        SELECT 
                                        [Extent4].[Id] AS [Id]
                                        FROM [dbo].[AhsayMysqlBackupSets] AS [Extent4]) AS [UnionAll2]
                                UNION ALL
                                    SELECT 
                                    [Extent5].[Id] AS [Id]
                                    FROM [dbo].[AhsayMssqlBackupSets] AS [Extent5]) AS [UnionAll3]
                            UNION ALL
                                SELECT 
                                [Extent6].[Id] AS [Id]
                                FROM [dbo].[AhsayFileBackupSets] AS [Extent6]) AS [UnionAll4]
                        UNION ALL
                            SELECT 
                            [Extent7].[Id] AS [Id]
                            FROM [dbo].[AhsayExchangeServerBackupSets] AS [Extent7]) AS [UnionAll5]
                    UNION ALL
                        SELECT 
                        [Extent8].[Id] AS [Id]
                        FROM [dbo].[AhsayDominoBackupSets] AS [Extent8]) AS [UnionAll6]
                UNION ALL
                    SELECT 
                    [Extent9].[Id] AS [Id]
                    FROM [dbo].[AhsayNotesBackupSets] AS [Extent9]) AS [UnionAll7]
            UNION ALL
                SELECT 
                [Extent10].[Id] AS [Id]
                FROM [dbo].[AhsayShadowProtectBackupSets] AS [Extent10]) AS [UnionAll8]
        UNION ALL
            SELECT 
            [Extent11].[Id] AS [Id]
            FROM [dbo].[AhsayWindowsSystemBackupSets] AS [Extent11]) AS [UnionAll9]
    UNION ALL
        SELECT 
        [Extent12].[Id] AS [Id]
        FROM [dbo].[AhsayExchangeMailBackupSets] AS [Extent12]) AS [UnionAll10] ON [Extent1].[Id] = [UnionAll10].[C1]
    LEFT OUTER JOIN [dbo].[AhsayUsers] AS [Extent13] ON [Extent1].[AhsayUserId] = [Extent13].[Id]
    INNER JOIN [dbo].[AhsayUsers] AS [Extent14] ON [Extent1].[AhsayUserId] = [Extent14].[Id]
    WHERE ([Extent13].[ApplianceId] = @p__linq__0) AND ([Extent13].[LoginName] = @p__linq__1) AND ([Extent14].[RegistrationDate] = @p__linq__2) AND ([Extent1].[AhsayId] = @p__linq__3)
)  AS [GroupBy1]',N'@p__linq__0 int,@p__linq__1 nvarchar(4000),@p__linq__2 datetime,@p__linq__3 bigint',@p__linq__0=2,@p__linq__1=N'antonio',@p__linq__2='2009-10-22 18:07:17',@p__linq__3=1256305376226