none
Crazy CASE on SQL instruction

    Question

  • Hi, 

    This is more of a curiosity - why does this happen?

    I have a table called Notifications.

    This table receives 3 different kinds of records, inherited classes, let's say A, B, C. The base class BASE is abstract.

    Records of type B have some relations with other tables and I need to recover an information that is two tables in distance. Not a problem, it's something like b.Table1.relationfield1.Table2.<g class="gr_ gr_323 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="323" id="323">FieldIWant</g>

    The problem is that when I do this, <g class="gr_ gr_512 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" data-gr-id="512" id="512">entity</g> framework <g class="gr_ gr_513 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" data-gr-id="513" id="513">insert</g> some crazy CASE statements in the query. It's not really hurting the query because it's in the field list, not in the where, but it's ugly and I would like to know why is this happening.

    TThis is part of the query with the crazy CASE statement. Mind that some fields (DateSent from Extent2) are even duplicated:

     SELECT 
            [Extent1].[Id] AS [Id], 
            [Extent2].[DateSent] AS [DateSent], 
            [Extent3].[SPUserId] AS [SPUserId], 
            [Extent4].[ReferringSPFirmId] AS [ReferringSPFirmId], 
            [Join5].[ClientName] AS [ClientName], 
            CASE WHEN (''0X0X0X'' LIKE ''0X0X0X%'') THEN [Extent1].[Id] END AS [C1], 
            CASE WHEN (''0X0X0X'' LIKE ''0X0X0X%'') THEN [Extent1].[Title] END AS [C2], 
            CASE WHEN (''0X0X0X'' LIKE ''0X0X0X%'') THEN [Extent2].[SenderSPFirmId] END AS [C3], 
            CASE WHEN (''0X0X0X'' LIKE ''0X0X0X%'') THEN [Extent2].[TargetSPFirmId] END AS [C4], 
            CASE WHEN (''0X0X0X'' LIKE ''0X0X0X%'') THEN [Extent2].[IsOnBehafOf] END AS [C5], 
            CASE WHEN (''0X0X0X'' LIKE ''0X0X0X%'') THEN [Extent2].[DateSent] END AS [C6], 
            CASE WHEN (''0X0X0X'' LIKE ''0X0X0X%'') THEN [Extent2].[Message] END AS [C7], 
            CASE WHEN (''0X0X0X'' LIKE ''0X0X0X%'') THEN [Extent2].[IsEmailSent] END AS [C8], 
            CASE WHEN (''0X0X0X'' LIKE ''0X0X0X%'') THEN [Extent1].[ReferralId] END AS [C9], 
            CASE WHEN (''0X0X0X'' LIKE ''0X0X0X%'') THEN [Extent1].[NotificationStatusId] END AS [C10], 
            CASE WHEN (''0X0X0X'' LIKE ''0X0X0X%'') THEN [Extent1].[Subject] END AS [C11]

            FROM      [Notifications].[Notification] AS [Extent1]
            INNER JOIN [Notifications].[BaseNotification] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]
            INNER JOIN  (SELECT [Var_9].[Id] AS [Id], [Var_9].[SPUserId] AS [SPUserId]
                FROM [dbo].[People] AS [Var_9]
                WHERE [Var_9].[IsDeleted] <> 1 ) AS [Extent3] ON [Extent2].[SenderId] = [Extent3].[Id]
            LEFT OUTER JOIN  (SELECT [Var_10].[Id] AS [Id], [Var_10].[ReferringSPFirmId] AS [ReferringSPFirmId], [Var_10].[ReceivingClientId] AS [ReceivingClientId]
                FROM [Referrals].[Referral] AS [Var_10]
                WHERE [Var_10].[IsDeleted] <> 1 ) AS [Extent4] ON [Extent1].[ReferralId] = [Extent4].[Id]
            LEFT OUTER JOIN [Referrals].[ReferralsClient] AS [Extent5] ON ([Extent4].[ReceivingClientId] = [Extent5].[ClientId]) AND ([Extent5].[ClientType] IN (1,2))
            LEFT OUTER JOIN  (SELECT [Extent6].[ClientName] AS [ClientName], [Extent7].[ClientId] AS [ClientId]
                FROM  [ConnectPlus].[Client] AS [Extent6]
                INNER JOIN [Referrals].[ReferralsClient] AS [Extent7] ON ([Extent7].[ClientType] IN (1,2)) AND ([Extent6].[Id] = [Extent7].[ClientId]) ) AS [Join5] ON [Extent5].[ClientId] = [Join5].[ClientId]
            WHERE ([Extent1].[NotificationType] = 2) AND (4 = [Extent1].[NotificationStatusId]) AND ([Extent2].[TargetSPFirmId] = @p__linq__0)
        )  AS [Project1]
        ORDER BY [Project1].[DateSent] DESC',N'@p__linq__0 int',@p__linq__0=16


    This is the <g class="gr_ gr_1069 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="1069" id="1069">linq</g> query:

                    var resultReferral= (from n in query.OfType<Data.Model.Referrals.ReferralNotification>()
                            select new NotificationModel()
                            {
                                Id = n.Id,
                                Title = n.Title,
                                SenderFirm = n.SenderSPFirmId,
                                TargetFirm = n.TargetSPFirmId,
                                IsOnBehalfOf = n.IsOnBehafOf,
                                DateSent = n.DateSent,
                                Message = n.Message,
                                SenderId = n.Sender.SPUserId,
                                IsEmailSent = n.IsEmailSent,
                                ReferralId = n.ReferralId,
                                ReferralClientName = n.Referral.ReceivingClient.Client.ClientName,
                                ReferralReferingFirmId = n.Referral.ReferringSPFirmId,
                                NotificationStatus = n.NotificationStatus.Id,
                                Subject = n.Subject,
                            }).ToList();

    If I comment the line of ReferralClientName, the crazy CASE's disappears. Why does this happen?

    Regards,

    Dennes

    • Edited by Dennes Wednesday, September 19, 2018 10:53 AM
    Wednesday, September 19, 2018 10:52 AM

All replies