Union query randomly becomes null for some results?

[aloksharma1]

Hello,

this is the live page to check the result of union query : click here scroll down and you will see a slider with images. images etc are selected through union using this query:

return await QueryData.Select(x => new BlogMinimalInfo
                {
                    //omitted columns with no issue 
                    BlogHeroes = _imageRepository.NoTrackingQuery()
                            .Where(pi => _blogImagesRepository.NoTrackingQuery().Where(pim => pim.IsActive == true && pim.BlogId == x.Id).Select(pim => pim.PostImageId).Contains(pi.Id)
                            && pi.isPublished == true && pi.IsActive==true)
                            .Select(pi => new BlogHero
                            {
                                HeroPath = pi.ImagePath,
                                HeroAltText = pi.ImageAltText,
                                HeroSortOrder = pi.SortOrder,
                                HeroTitle = "",
                                HeroData = "",
                                HeroType = "Images",
                                FileType = null
                            }).Union(_fileRepository.NoTrackingQuery()
                            .Where(pi => _blogFilesRepository.NoTrackingQuery()
                            .Where(f => f.IsActive == true && f.BlogId == x.Id)
                            .Select(f => f.PostFileId).Contains(pi.Id) && pi.isPublished == true)
                            .Select(fi => new BlogHero
                            {
                                HeroPath = fi.FilePath,
                                HeroAltText = "",
                                HeroSortOrder = fi.SortOrder ?? 0,
                                HeroTitle = fi.FileName,
                                HeroData = fi.FileExtension,
                                HeroType = "Files",
                                FileType = fi.FileType
                            }).Union(_blogLinksRepository.NoTrackingQuery().Where(l => l.IsActive == true && l.BlogId == x.Id)
                            .Select(l =>
                                   new BlogHero
                                   {
                                       HeroPath = l.Link,
                                       HeroAltText = "",
                                       HeroSortOrder = l.SortOrder,
                                       HeroTitle = l.Title,
                                       HeroData = "",
                                       HeroType = "Links",
                                       FileType = null
                                   }).Union(_blogHeroHtmlRepository.NoTrackingQuery().Where(h => h.IsActive == true && h.SiteId == x.SiteId && h.BlogId == x.Id).
                            Select(h => new BlogHero
                            {
                                HeroPath = "",
                                HeroAltText = "",
                                HeroSortOrder = h.SortOrder,
                                HeroTitle = "",
                                HeroData = h.HeroElementHtml,
                                HeroType = "Htmls",
                                FileType = null
                            })))).ToList()
                }).ToLinqToDB().ToListAsync();

this query works ok sometimes but sometimes it just randomly skips BlogHeroes (the Union Query Part) & sends null result in it.

here is the generated query, but its missing the union query:

--  SqlServer.2017
DECLARE @take Int -- Int32
SET     @take = 5
DECLARE @take_1 Int -- Int32
SET     @take_1 = 1
DECLARE @take_2 Int -- Int32
SET     @take_2 = 1
DECLARE @take_3 Int -- Int32
SET     @take_3 = 1
DECLARE @BlogTypeId UniqueIdentifier -- Guid
SET     @BlogTypeId = 'f9dbe49c-51e0-4549-9b2b-35952050d6d3'
DECLARE @SiteId BigInt -- Int64
SET     @SiteId = 5
DECLARE @UtcNow DateTimeOffset
SET     @UtcNow = '2022-02-24 13:39:17.6274623 +00:00'

SELECT TOP (@take)
    [x].[Id],
    [x].[PostTitle],
    [x].[PageSlugUrl],
    [t1].[Name],
    [t1].[is_empty],
    [x].[DateCreated],
    [x].[PublishDate],
    [x].[PostType],
    [t2].[DateModified],
    [t2].[DateCreated],
    [t2].[IsActive],
    [t2].[SiteId],
    [t2].[CategoryBlogTypeId],
    [t2].[CategoryParentTrail],
    [t2].[CategoryParentId],
    [t2].[CategoryPageId],
    [t2].[CategorySlug],
    [t2].[CategoryName],
    [t2].[Id],
    [t3].[TotalViews],
    (
        SELECT
            Count(*)
        FROM
            [BlogComments] [bc]
        WHERE
            ([bc].[IsActive] = 1 AND [bc].[IsActive] IS NOT NULL) AND
            [bc].[SiteId] = [x].[SiteId] AND [bc].[BlogId] = [x].[Id]
    ),
    (
        SELECT
            Count(*)
        FROM
            [BlogLikeDislikeCount] [bl]
        WHERE
            [bl].[LikeDislike] = 1 AND
            [bl].[BlogId] = [x].[Id] AND
            ([bl].[IsActive] = 1 AND [bl].[IsActive] IS NOT NULL) AND
            [bl].[SiteId] = [x].[SiteId]
    ),
    (
        SELECT
            Count(*)
        FROM
            [BlogLikeDislikeCount] [bl_1]
        WHERE
            [bl_1].[LikeDislike] = 0 AND
            [bl_1].[BlogId] = [x].[Id] AND
            ([bl_1].[IsActive] = 1 AND [bl_1].[IsActive] IS NOT NULL) AND
            [bl_1].[SiteId] = [x].[SiteId]
    )
FROM
    [BlogPostInfo] [x]
        OUTER APPLY (
            SELECT TOP (@take_1)
                [bt].[Name],
                1 as [is_empty]
            FROM
                [BlogTypes] [bt]
            WHERE
                [bt].[Id] = [x].[BlogPostTypeId]
        ) [t1]
        OUTER APPLY (
            SELECT TOP (@take_2)
                [c_1].[DateModified],
                [c_1].[DateCreated],
                [c_1].[IsActive],
                [c_1].[SiteId],
                [c_1].[CategoryBlogTypeId],
                [c_1].[CategoryParentTrail],
                [c_1].[CategoryParentId],
                [c_1].[CategoryPageId],
                [c_1].[CategorySlug],
                [c_1].[CategoryName],
                [c_1].[Id]
            FROM
                (VALUES
                    ('3395c7b5-3b5a-45ed-adf9-68ad1e42abf5','2022-02-24 09:11:34.9070464 +00:00','2022-01-04 06:38:57.9484106 +00:00',1,5,'24e5b757-5307-4d95-97de-46cb7927778c',NULL,NULL,'8efa9b49-ff9a-4b6e-9dff-5b8dc5f420dc',N'Food',N'Food '),
                    ('ab855a3e-8e27-4794-b3a8-80ac79596297','2022-02-24 09:10:18.9046115 +00:00','2022-01-04 06:39:24.9146900 +00:00',1,5,'24e5b757-5307-4d95-97de-46cb7927778c',NULL,NULL,'7ecc65c7-c9a5-485a-9462-42673926f839',N'Fashion',N'Fashion'),
                    ('5e699146-7771-44a0-b3e5-af6f51146b85','2022-02-24 09:10:28.3716428 +00:00','2022-01-04 06:39:40.4311098 +00:00',1,5,'f9dbe49c-51e0-4549-9b2b-35952050d6d3',NULL,NULL,'25debe46-f731-4a44-ac98-030ddf486ba7',N'Esports',N'Esports')
                ) [c_1]([Id], [DateModified], [DateCreated], [IsActive], [SiteId], [CategoryBlogTypeId], [CategoryParentTrail], [CategoryParentId], [CategoryPageId], [CategorySlug], [CategoryName])
            WHERE
                [c_1].[Id] = [x].[MainCategoryId]
        ) [t2]
        OUTER APPLY (
            SELECT TOP (@take_3)
                [bv].[UniqueCounter] as [TotalViews]
            FROM
                [BlogViews] [bv]
            WHERE
                [bv].[IsActive] = 1 AND
                [bv].[IsActive] IS NOT NULL AND
                [bv].[SiteId] = [x].[SiteId] AND
                [bv].[BlogId] = [x].[Id]
        ) [t3]
WHERE
    [x].[IsActive] = 1 AND
    [x].[IsActive] IS NOT NULL AND
    [x].[BlogPostTypeId] = @BlogTypeId AND
    [x].[SiteId] = @SiteId AND
    ([x].[RecordStatus] = 2 OR [x].[PublishDate] <= @UtcNow AND [x].[RecordStatus] = 6)
ORDER BY
    NewID()

i tried AsSubQuery() but it didnt worked, do you have any workaround that will ensure union query always gets called (or whatever is happing in here can be logged?).

thanks for the help

[sdanyliv]

it is SQL from not from this LNQ query. Are you sure that you have posted actual SQL?

[sdanyliv]

Small comment, if you use Select - you can totally remove AsNoTracking, EF do not track custom entities.

[aloksharma1]

yes SQL is from same linq query i redacted a lot of code for simplicity. also removing NoTracking Part as you suggested.

[aloksharma1]

Changing to Query from NoTracking didnt changed the random issue.

[aloksharma1]

hi, dont want to rush you or anything but were you able to look into this. i still think its happening due to how Linq2DB handles Union (lazy probably) if you can tell me where to look at maybe i can try debugging it locally and see whats happening? as i said if i fetch a list of 5 rows (2-3 gets null on union field randomly).

[aloksharma1]

@sdanyliv can you give me some instructions on how to debug it? i have free time from tomorrow for next 2 days, so i want to look into this issue.

[Shane32]

Does the query have inputs? Perhaps the intermittent failure is due to differing inputs, and so I would start logging all the inputs to the query along with the generated sql text from the query, and then try to determine if there was a way to reliably reproduce the issue.

[MaceWindu]

I'm closing it as we cannot reproduce it and it is probably obsolete anyways with all changes in v6 release.

[aloksharma1]

its ok, i moved this logic to raw query instead havent tested this with v6.