在LINQ使用GLOB功能实体 [英] Using the GLOB function in LINQ to Entities

问题描述

我需要在一个（C＃）的SQLite的水珠函数方法，它必须返回防爆pression＆LT; Func键＆LT; RandomEntity，布尔＆GT;＆GT ; - 我需要水珠，因为只有使用索引（已签使用 EXPLAIN查询计划[.. ] ）。

I need SQLite's glob function in a (C#) method which has to return Expression<Func<RandomEntity, bool>> -- and I need glob because only then the index is used (already checked using EXPLAIN QUERY PLAN [..]).

所以我增加了以下功能映射到＆LT; EDMX：StorageModels＆GT;＆LT;模式＆GT; （SSDL）：

So I added the following function mapping to <edmx:StorageModels><Schema> (SSDL):

    <Function Name="glob" Aggregate="false" BuiltIn="true" NiladicFunction="false" IsComposable="true" ReturnType="bit">
        <Parameter Name="pattern" Mode="In" Type="nvarchar"/>
        <Parameter Name="target" Mode="In" Type="nvarchar"/>
    </Function>

和C＃存根方法：

and a C# stub method:

public static class SQLiteFunctions
{
    [DbFunction("Model.Store", "glob")]
    public static bool Glob(string pattern, string target)
    {
        throw new NotImplementedException("Only exists for IQueryable/Expression<Func<T,bool>>!");
    }
}

和使用情况（只是一个样本产生微小的SQL）：

and the usage (just a sample producing tiny SQL):

var count = context.Users.Count(u => SQLiteFunctions.Glob("admin*", u.Name));

在这个工程中产生的SQL使用了愚蠢的 = 1 比较，因为该模型只知道数据类型位（即0或1），我发现没有真正的布尔类型，虽然本机的glob功能似乎是真实的布尔值：

While this works the resulting SQL uses a "silly" = 1 comparison since the model only knows the datatype "bit" (which is 0 or 1) and I found no real boolean type although the native glob function seems to be true boolean:

SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    Count([Filter1].[A1]) AS [A1]
    FROM ( SELECT 
        1 AS [A1]
        FROM [Users] AS [Extent1]
        WHERE (glob('admin*', [Extent1].[Name])) = 1
    )  AS [Filter1]
)  AS [GroupBy1]

问题：这种方式（用= 1）SQLite不使用索引和查询是非常缓慢的。如果我删除了3个字符= 1的查询变快，也从扫描表查询计划更改为搜索表使用索引。

The problem: This way (with "= 1") SQLite doesn't use the index and the query is badly slow. If I remove the 3 characters "= 1" the query becomes fast and also the query plan changes from "SCAN TABLE" to "SEARCH TABLE USING INDEX".

任何想法如何使功能的真正的布尔函数？

Any ideas how to make the function a true boolean function?

增加：

我也尝试过使用的CSDL部分（）：

I also tried using the CSDL section ():

    <Function Name="GlobMatch" ReturnType="Edm.Boolean">
        <Parameter Name="globPattern" Type="Edm.String" />
        <Parameter Name="target" Type="Edm.String" />
        <DefiningExpression> glob(globPattern, target) </DefiningExpression>
    </Function>

和

    [DbFunction("DoPiMo", "GlobMatch")]
    public static bool Glob2(string globPattern, string target)
    {
        throw new NotImplementedException("Only exists for IQueryable/Expression<Func<T,bool>>!");
    }

但这只是产生一个运行时错误说水珠没有定义/知。

but this only produces a runtime error saying "glob" is not defined/known.

推荐答案

在做了preFIX搜索与LIKE或GLOB，SQLite的将其重写为两个比较。

When doing a prefix search with LIKE or GLOB, SQLite rewrites it as two comparisons.

您可以只直接写两个比较：

You could just write the two comparisons directly:

u.Name >= "admin" and u.Name < "admio"

这篇关于在LINQ使用GLOB功能实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！