CODEWITHSUNDEEP
c#entity-frameworkentity-framework-5
Dennis
Dennis

Reputation: 37770

Strange conditions in the generated SQL query

I'm working on some EF query optimization. To better understand query performance, I'm looking into generated SQL, and some conditions makes me crazy:

WHERE (cast(1 as bit) <> cast(0 as bit))...

I can't imagine a case, when this condition will evaluate to false. Also:

CASE WHEN ([Extent9].[Id] IS NULL) THEN...

Obviously, [Extent9].[Id] means primary key column in the corresponding table, which, of course, can't be NULL.

Why these conditions present in SQL? Here's LINQ query text, if it can help answer the question:

var query = _context
    .Objects
    .Where(o => o.DatawareVersionId == _datawareId);
    .SelectMany(t => t.ComplexSubTasks)
    .Where(st => st.Variant.Order.CurrentAlgoVersion != null)
    .Select(st => st.Variant.Order.CurrentAlgoVersion)
    .OfType<MonitoringAlgoVersion>()
    .Where(version => version != null && version.Applicabilities.Any(applicability => applicability.SymbolicCircuitTypeId == _typeId))
    .Any());
    .Select(o => o.Group)
    .Distinct();

UPDATE. Entire SQL statement:

SELECT 
[Distinct1].[Id] AS [Id], 
[Distinct1].[GenericGroup] AS [GenericGroup], 
[Distinct1].[Number] AS [Number], 
[Distinct1].[Group] AS [Group]
FROM ( SELECT DISTINCT 
    [Extent2].[Id] AS [Id], 
    [Extent2].[GenericGroup] AS [GenericGroup], 
    [Extent2].[Number] AS [Number], 
    [Extent2].[Group] AS [Group]
    FROM  [dbo].[Objects] AS [Extent1]
    INNER JOIN [dbo].[ObjectGroups] AS [Extent2] ON [Extent1].[GroupId] = [Extent2].[Id]
    WHERE ([Extent1].[DatawareVersionId] = @p__linq__0) AND ( EXISTS (SELECT 
        1 AS [C1]
        FROM ( SELECT 
            [Extent9].[Id] AS [Id]
            FROM       [dbo].[Tasks] AS [Extent3]
            INNER JOIN [dbo].[SubTasks] AS [Extent4] ON [Extent3].[Id] = [Extent4].[TaskId]
            LEFT OUTER JOIN [dbo].[Variants] AS [Extent5] ON [Extent4].[VariantId] = [Extent5].[Id]
            LEFT OUTER JOIN [dbo].[Orders] AS [Extent6] ON [Extent5].[OrderId] = [Extent6].[Id]
            INNER JOIN [dbo].[AlgoVersions] AS [Extent7] ON [Extent6].[CurrentAlgoVersion] = [Extent7].[Id]
            LEFT OUTER JOIN [dbo].[Orders] AS [Extent8] ON [Extent5].[OrderId] = [Extent8].[Id]
            LEFT OUTER JOIN [dbo].[AlgoVersions] AS [Extent9] ON [Extent8].[CurrentAlgoVersion] = [Extent9].[Id]
            WHERE (cast(1 as bit) <> cast(0 as bit)) AND ([Extent1].[Id] = [Extent3].[ObjectId]) AND (CASE WHEN ([Extent9].[Id] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE '10X0X' END LIKE '10X0X%')
        )  AS [Project1]
        WHERE  EXISTS (SELECT 
            1 AS [C1]
            FROM [apot].[draw_algo_versions_applicability] AS [Extent10]
            WHERE ([Project1].[Id] = [Extent10].[algo_version_id]) AND ([Extent10].[schema_type_id] = @p__linq__1)
        )
    ))
)  AS [Distinct1]

UPDATE 2. Here's entity types, mapped to AlgoVersions table:

public abstract class AlgoVersion : VersionedEntity
{           
    public virtual int OrderId { get; set; }
    public virtual Order Order { get; set; }
    public virtual bool IsErrorFixed { get; set; }
    public virtual bool IsFunctionalityExtended { get; set; }
    public virtual ObservableCollection<AlgoVersionStatus> Statuses { get; set; }
}

public class MonitoringAlgoVersion : AlgoVersion
{
    public virtual ObservableCollection<TemplateGroup> TemplateGroups { get; set; }
    public virtual ObservableCollection<MonitoringAlgoVersionApplicability> Applicabilities { get; set; }
}

where VersionedEntity is a base class for entities, which have an Id and ObjectVersion (both Entity and VersionedEntity are not mapped to any table):

public abstract class Entity
{
    public virtual int Id { get; set; }
}

public abstract class VersionedEntity : Entity
{
    public virtual byte[] ObjectVersion { get; set; }
}

Upvotes: 2

Views: 236

Answers (1)

undefined
undefined

Reputation: 34248

I'm not sure why cast(1 as bit) <> cast(0 as bit) is there but there is a work item in the EF backlog to remove this. I would recommend voting for it. I imagine this means its completely useless as the item is posted by RoMiller. Its pretty common in queries, I've seen it before often.

Upvotes: 1

Related Questions