.net - 如何让 LINQ to SQL 查询使用包含 SQL Server Compact 上的 BIT 列的索引？

Question

我在 SQL Server Compact DB 上使用以下 LINQ to SQL 查询 ...

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

... 生成以下 SQL:

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

如果 IsDeleted 列上没有索引并且 SQL Server Compact 不会使用该索引，除非 SQL 看起来像这样，否则这将是完美的:

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

所以问题是:如何说服 LINQ to SQL 生成“CONVERT(BIT, 0)”？我已经尝试过以下...

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

...但是生成的 SQL 看起来是一样的。

Answer 1

经过大量挖掘，似乎无法说服 LINQ to SQL 将 CONVERT(BIT, 0) 生成到查询中。但是，可以强制在 WHERE 子句中使用参数而不是文字，即首先编译查询，如下所示:

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);
}

当我们运行这个查询时，会生成以下 SQL:

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

请注意注释，@p0 似乎是为 SQL Server Compact 正确键入以实际使用索引。我已经用下面的程序验证了这一点。该程序首先用 1000000 行填充一个新数据库，然后使用编译查询或普通查询对其进行查询。在我的机器上，计时很明显(3 次运行的平均值，首先被丢弃):

Ordinary query on DB with index: ~670ms

Compiled query on DB with index: ~30ms

在这两种情况下，查询只执行一次，因此编译后的查询与实际编译相比没有任何优势。当我们手动删除数据库中的索引然后再次运行相同的查询时，编译查询实际上使用索引而普通查询不会出现的进一步证据(3 次运行的平均值，首先丢弃):

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>();
    }
}