使用LinqDataSource的Where子句中的参数的ASCII值

I have a LinqDataSource. This data source reads from a table in SQL Server that contains a field called ID, which is of type CHAR. Some of the records have blanks in this field and I would like to filter these out. The "blank" is not NULL and not an empty string.

我有一个LinqDataSource。此数据源从SQL Server中的表中读取,该表包含名为ID的字段,其类型为CHAR。有些记录在这个领域有空白,我想过滤掉这些。 “blank”不是NULL而不是空字符串。

Using T-SQL, this clause fails to filter the records properly:

使用T-SQL,此子句无法正确过滤记录:

WHERE Employee_ID <> ''

...while this one succeeds:

......虽然这个成功了:

WHERE ASCII(Employee_ID) <> 0

Now my question is, how do I configure the LinqDataSource in design mode to filter by the ASCII value of the field or how can I otherwise filter so these records are not returned?

现在我的问题是,如何在设计模式下配置LinqDataSource以按字段的ASCII值进行过滤,或者如何进行过滤,以便不返回这些记录?

This is the markup of the data source:

这是数据源的标记:

 <asp:LinqDataSource ID="LinqDataSourcePersonnel" runat="server" ContextTypeName="exc009p.Owner_SummariesDataContext"
    EntityTypeName="" TableName="Personnels" Select='new(Employee_ID, Employee_Name, Employee_Title, (Employee_ID + " " + Employee_Name + " - " + Employee_Title) as employeeIDNameTitle)'
    OrderBy="Employee_Name, Employee_ID" Where="Employee_ID != @Employee_ID">
    <WhereParameters>
        <asp:Parameter DefaultValue="&quot;&quot;" Name="Employee_ID" Type="String" />
    </WhereParameters>
</asp:LinqDataSource>

1 个解决方案

#1

I believe the root of your issue is that you have non-printable characters in your column (tab, CR, LF etc...) When you query runs the result looks like a blank but it in fact has a non printable character.

我相信你的问题的根源是你的列中有不可打印的字符(tab,CR,LF等...)当你查询运行时,结果看起来像一个空白,但它实际上有一个不可打印的字符。

I think the best coarse of action would be to validate data (in the application) to make sure only valid characters are inserted into this column. This will prevent you from having to use a make shift filter.

我认为最好的粗略操作是验证数据(在应用程序中)以确保只有有效字符插入到此列中。这将防止您必须使用make shift过滤器。

If you cannot change the data or validate the data, I would probably try something like this,as it is SARGable (can be used for an index seek)

如果您无法更改数据或验证数据,我可能会尝试这样的事情,因为它是SARGable(可用于索引搜索)

select *
from MyTable
WHERE employee like '[a-z0-9]%'

#1