Linq Entity Inheritance makes BIG SQL Sentences

Question

We are developing an application with a base entity with more than 10 childs (which inherited from it).

When we make any request with Linq to the base entity we get a SQL statement with a "UNION ALL" for each child. To make a Count() over the base entity it takes near one second and getting only one row can takes two seconds.

For this code:

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;
    }

, we get this SQL sentence:

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

As you can imagine, it takes a lot of time (in this case, 1 second, but there is another sentence a lot bigger which takes 4 seconds), and this query is made many times.

Is there some way to reduce the SQL overhead? We know we can use stored procedures for heavy sentences but we don't want to lose the Linq flexibility.

Thanks in advance.

Answer 1

Wow..that is a long query string..Have you tried this in .NET 4.0 to see what happens. maybe you will get a much shorter sql string. EF Team made many performance improvements