How to have a LINQ to SQL query use an index containing a BIT column on SQL Server Compact?

Question

I'm using the following LINQ to SQL query on a SQL Server Compact DB ...

from article in context.OutboundArticles
where !article.IsDeleted
select article

... which generates the following SQL:

SELECT [t0].[Id], [t0].[Text], [t0].[IsDeleted]
FROM [OutboundArticle] AS [t0]
WHERE NOT ([t0].[IsDeleted] = 1)

This would be perfectly fine if it weren't for the fact that there's an index on the IsDeleted column and SQL Server Compact will not use the index unless the SQL looks like this:

SELECT [t0].[Id], [t0].[Text], [t0].[IsDeleted]
FROM [OutboundArticle] AS [t0]
WHERE [t0].[IsDeleted] = CONVERT(BIT, 0)

So the question is: How do I convince LINQ to SQL to generate the "CONVERT(BIT, 0)"? I've already tried the following ...

from article in context.OutboundArticles
where article.IsDeleted == Convert.ToBoolean(0)
select article

... but the generated SQL looks the same.

Answer 1

After a lot of digging it seems LINQ to SQL cannot be convinced to generate the CONVERT(BIT, 0) into the query. However, it can be forced to use a parameter instead of a literal in the WHERE clause, namely by compiling the query first, as follows:

private static string QueryCompiled(Context context)
{
    var compiled = CompiledQuery.Compile(
        (Context c, bool isDeleted) =>
            (from article in c.OutboundArticles
             where article.IsDeleted == isDeleted
             select article.Text).Single());
    return compiled(context, false);
}

When we run this query, the following SQL is generated:

SELECT [t0].[Text]
FROM [OutboundArticle] AS [t0]
WHERE [t0].[IsDeleted] = @p0
-- @p0: Input Boolean (Size = 0; Prec = 0; Scale = 0) [False]
-- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 4.0.30319.1

Note the comment, @p0 seems to be typed appropriately for SQL Server Compact to actually use the index. I've verified this with the program below. The program first fills a new DB with 1000000 rows and then queries it with either the compiled or the ordinary query. On my machine, the timings are obvious (average from 3 runs, first discarded):

Ordinary query on DB with index: ~670ms

Compiled query on DB with index: ~30ms

In both cases the query is only executed exactly once, so the compiled query does not have any advantage from the actual compilation. Further evidence that the compiled query actually uses the index while the ordinary does not comes when we manually delete the index in the DB and then run the same queries again (average from 3 runs, first discarded):

Ordinary query on DB without index: ~680ms

Compiled query on DB without index: ~630ms

using System;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Diagnostics;
using System.IO;
using System.Linq;

internal static class Program
{
    private static void Main()
    {
        var dataFile = CreateDatabase();

        using (var context = new Context(dataFile))
        {
            Console.WriteLine("Executing query:");

            // Modify this to see the difference between compiled and uncompiled queries
            const bool compiled = true;

            Stopwatch watch = new Stopwatch();
            context.Log = Console.Out;
            watch.Start();

            if (compiled)
            {
                Console.WriteLine("Result: " + QueryCompiled(context));
            }
            else
            {
                Console.WriteLine("Result: " + QueryNormal(context));
            }

            watch.Stop();
            Console.WriteLine("Elapsed milliseconds: " + watch.ElapsedMilliseconds);
        }
    }

    private static string CreateDatabase()
    {
        var dataFile = Path.Combine(".", "DB.sdf");
        bool databaseExists;

        using (var context = new Context(dataFile))
        {
            databaseExists = context.DatabaseExists();

            if (!databaseExists)
            {
                Console.WriteLine("Creating database (only done on the first run)...");
                context.CreateDatabase();
            }
        }

        if (!databaseExists)
        {
            const int articleCount = 1000000;
            const int batchSize = 10000;
            var random = new Random();

            for (int batchStart = 0; batchStart < articleCount; batchStart += batchSize)
            {
                using (var context = new Context(dataFile))
                {
                    for (int number = batchStart; number < batchStart + batchSize; ++number)
                    {
                        context.OutboundArticles.InsertOnSubmit(
                            new OutboundArticle()
                            {
                                Text = new string((char)random.Next(32, 128), random.Next(32)),
                                IsDeleted = number != articleCount / 2
                            });
                    }

                    context.SubmitChanges();
                }
            }

            using (var context = new Context(dataFile))
            {
                context.ExecuteCommand(
                    "CREATE INDEX IX_OutboundArticle_IsDeleted ON OutboundArticle(IsDeleted)");
            }
        }

        return dataFile;
    }

    private static string QueryNormal(Context context)
    {
        return 
            (from article in context.OutboundArticles
             where !article.IsDeleted
             select article.Text).Single();
    }

    private static string QueryCompiled(Context context)
    {
        var compiled = CompiledQuery.Compile(
            (Context c, bool isDeleted) =>
                (from article in c.OutboundArticles
                 where article.IsDeleted == isDeleted
                 select article.Text).Single());
        return compiled(context, false);
    }
}

[Table]
internal sealed class OutboundArticle
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    private int Id;

    [Column(CanBeNull = false, DbType = "NVARCHAR(32) NOT NULL")]
    internal string Text;

    [Column]
    internal bool IsDeleted;
}

internal sealed class Context : DataContext
{
    internal Table<OutboundArticle> OutboundArticles;

    internal Context(string fileName) : base(fileName)
    {
        this.OutboundArticles = this.GetTable<OutboundArticle>();
    }
}

Answer 2

The problem (I think) may be that it's converting your index column to a different datatype.

I'd try this first

where !(article => article.isdeleted = true)

Otherwise, maybe these...

where !article.isdeleted.toboolean

OR

where !article.isdeleted = true