var parentIfaces = new Guid[0];
var entityInterfaces = entity.Interfaces.Select(m => m.Linked);
var qwe = entityInterfaces.Where(m => !parentIfaces.Contains(m.Id))
qwe.Count() == 0 // This will always be 0

This code gets translated into:

SELECT  [a].[#a.Id] ,
        [a].[#a.TypeId] ,
        [a].[#a.Description] ,
        [a].[#a.Name] ,
        [a].[#a.SysName] ,
        [a].[#a.Version] ,
        [a].[#a.Revision]
FROM    ( SELECT    [b].[Id] ,
                    112 AS [TypeId] ,
                    [b].[Linked_Id] ,
                    [b].[Owner_Id]
          FROM      [dbo].[DocEntity_MlInterfaces] [b]
          WHERE     ( [b].[Owner_Id] = @p0_0 )
        ) [c]
INNER JOIN ( SELECT [d].[Id] AS [#a.Id] ,
                    139 AS [#a.TypeId] ,
                    [d].[Description] AS [#a.Description] ,
                    [d].[Name] AS [#a.Name] ,
                    [d].[SysName] AS [#a.SysName] ,
                    [d].[Version] AS [#a.Version] ,
                    [d].[Revision] AS [#a.Revision]
             FROM   [dbo].[EntityInterface] [d]
           ) [a] ON ( [c].[Linked_Id] = [a].[#a.Id] )
WHERE   ( NOT [a].[#a.Id] IN ( NULL ) ) ;

So the last WHERE is always evaluated into FALSE

UPD: As for now, I fixed the problem with this

var parentIfaces = new Guid[0].AsQueryable();

So, this gets translated into the proper query

SELECT  [a].[#a.Id] ,
        [a].[#a.TypeId] ,
        [a].[#a.Description] ,
        [a].[#a.Name] ,
        [a].[#a.SysName] ,
        [a].[#a.Version] ,
        [a].[#a.Revision]
FROM    ( SELECT    [b].[Id] ,
                    112 AS [TypeId] ,
                    [b].[Linked_Id] ,
                    [b].[Owner_Id]
          FROM      [dbo].[DocEntity_MlInterfaces] [b]
          WHERE     ( [b].[Owner_Id] = @p0_0 )
        ) [c]
INNER JOIN ( SELECT [d].[Id] AS [#a.Id] ,
                    139 AS [#a.TypeId] ,
                    [d].[Description] AS [#a.Description] ,
                    [d].[Name] AS [#a.Name] ,
                    [d].[SysName] AS [#a.SysName] ,
                    [d].[Version] AS [#a.Version] ,
                    [d].[Revision] AS [#a.Revision]
             FROM   [dbo].[EntityInterface] [d]
           ) [a] ON ( [c].[Linked_Id] = [a].[#a.Id] )
WHERE   ( NOT EXISTS ( SELECT   *
                       FROM     [dbo].[DocEntity_MlInterfaces] [e]
                       WHERE    ( ( [e].[Owner_Id] = @p0_1 )
                                  AND ( [e].[Linked_Id] = [a].[#a.Id] )
                                ) )
        ) ;

UPDATE: More problems with this situation:

using (var tr = s.OpenTransaction())
{
    var ssss = (from r in Session.Current.Query.All<RegDeletedItem>()
               where r.DeletedItem.In(new List<GeneralLink>())
               select r).ToList();

    tr.Complete();
}

This code generates invalid SQL, so SQL server cannot execute the query at all. I've sent you email with tests.

asked Feb 16 '11 at 07:30

xumix's gravatar image

xumix
425737682

edited Aug 30 '11 at 10:05

new Guid[0] is translated to NULL in SQL?

(Feb 16 '11 at 07:44) Alex Yakunin Alex%20Yakunin's gravatar image

Btw, what is new Guid[0]? C# shouldn't handle this...

(Feb 16 '11 at 07:45) Alex Yakunin Alex%20Yakunin's gravatar image

new Guid[0] is translated to NULL in SQL?

yes

new Guid[0]

  • this is absolutely valid construct, just an empty array
(Feb 16 '11 at 07:57) xumix xumix's gravatar image

BTW, see UPD

(Feb 16 '11 at 08:06) xumix xumix's gravatar image

Ah, sorry ;) By some reason I decided you used square brackets instead of regular ones.

(Feb 16 '11 at 08:52) Alex Yakunin Alex%20Yakunin's gravatar image

2 Answers:

Fixed in 7763 (0ec3acdbdbeb)

answered Sep 23 '11 at 04:33

Denis%20Krjuchkov's gravatar image

Denis Krjuchkov
179325

It looks like this is one of NULL-related issues in SQL: something in (NULL) in SQL always evaluates to NULL, as well as something not in (NULL) and not something in (NULL).

Worse, something in (...) isn't a regular expression in SQL Server - e.g. we can't just use this fix:

select COUNT(*) 
from ...
where not ISNULL(Id in (null), 1=0)

because SQL Server doesn't allow using in expressions everywhere where boolean expression is required. So we'll think how to fix this better.

answered Feb 16 '11 at 09:23

Alex%20Yakunin's gravatar image

Alex Yakunin
29714412

edited Feb 16 '11 at 09:24

any progress?

(Jun 10 '11 at 07:17) xumix xumix's gravatar image

Unfortunately, no progress

(Jun 10 '11 at 07:19) Dmitri Maximov Dmitri%20Maximov's gravatar image
Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

powered by OSQA