评估“图像"查询中的SQL列 [英] Evaluate "image" SQL column in a query

问题描述

我有一个数据库，其中包含一个带有"Image"列的表:

I have a database containing a table with an "Image" colum:

此列实际上包含一个编码为十六进制字节值的长字符串.

This column actually contains a long string encoded as HEX byte values.

我需要选择所有记录，其中此列编码的字符串包含某个子字符串.伪代码为:

I need to select all records where the string encoded by this column contains a certain substring. Pseudocode would be:

Select *
From SomeTable
Where dataColumnofTypeImage.ToString().Contains("somesubstring")

我尝试使用以下方法在Linq(LinqPad)中进行此操作:

I tried to do this in Linq (LinqPad) using:

from z in Zanus
let p = z.Udata.ToArray() // z.Udata will be of type System.Linq.Binary so I want to     make a byte array out of it...
where System.Text.ASCIIEncoding.ASCII.GetString(p).Contains("EXED")
select new
{
    z.Idnr,
    z.Udatum,
    z.Uzeit,
    z.Unr,
    z.Uart,
    z.Ubediener,
    z.Uzugriff,
    z.Ugr,
    z.Uflags,
    z.Usize,
    z.Udata
}

但这根本不起作用:

NotSupportedException:方法'Byte [] ToArray()'不支持SQL转换.

NotSupportedException: Method 'Byte[] ToArray()' has no supported translation to SQL.

我只是不敢相信不可能像检查其他数据类型一样在Where子句中检查二进制数据类型...

I just cannot believe it would not be possible to check a binary datatype in a Where clause just as I might check some other datatype...

有人可以帮我吗?

推荐答案

我知道的唯一方法是使用直接sql和Substring

The only way I know of doing this is to use direct sql and the Substring

例如，类似:

string str1 = @"
    select 
        Idnr, 
        Udatum,
        Uzeit, 
        Unr, 
        Uart, 
        Ubediener, 
        Uzugriff, 
        Ugr,
        Uflags, 
        Usize, 
        Udata 
    from  Zanus 
    where Udata is not null 
       and SubString(Udata, 1 , 2147483647) like '%EXED%'" ;

    var query1 = this.ExecuteQuery<Zanus>(str1);
    (from z in query1 
    select new
    {
        z.Idnr,
        z.Udatum,
        z.Uzeit,
        z.Unr,
        z.Uart,
        z.Ubediener,
        z.Uzugriff,
        z.Ugr,
        z.Uflags,
        z.Usize,
        z.Udata
    }    

请注意，如果搜索字符串位于一列中，而不是前2,147,483,647个字节中，则此操作将失败(但是，如果这些图像是图像，则我不认为这是要关注的问题).

Note, this will fail if the search string is in a column, but not in the first 2,147,483,647 bytes (but if these are images, I don't think that should be a concern).

这篇关于评估“图像"查询中的SQL列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！