查询包含从LINQ到EF的XML列的SQL视图 [英] Querying a SQL View containing XML columns from LINQ to EF
问题描述
编辑：我一直在做一些测试，设置ANSI_NULLS OFF和ON我误将粘贴了ANSI_NULLS的create table语句关闭，我已经尝试了两种方式，我仍然得到错误，但想解决这个问题。希望没有其他粘贴错误。
编辑2 ：我忘了指出，如果我查询原始表（TblValues） ，则仅在使用数据库视图（TblValuesView）时才会出现关于ANSI_NULLS选项的错误。
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING，ANSI_WARNINGS，CONCAT_NULL_YIELDS_NULL，ARITHABORT，
QUOTED_IDENTIFIER，ANSI_NULLS ON;
GO
CREATE TABLE TblValues
（
上下文nvarchar（8）NOT NULL，
ParentID uniqueidentifier NOT NULL，
RowSeq int NOT NULL，
FieldValues xml NOT NULL，
FieldProperties xml NOT NULL，
）ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
然后，我将此表加载到我的数据模型中，并且可以使用LINQ to EF查询它。 FieldValues列中的XML内容旨在如下：
< FieldValues>
< FieldValue fieldName =txtName>
< DataType> String< / DataType>
< FldValue>字段值< / FldValue>
< / FieldValue>
...
< / FieldValues>
由于我需要以更简单的方式在xml列中提供值来创建视图对于这个表如下：
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING，ANSI_WARNINGS，CONCAT_NULL_YIELDS_NULL，ARITHABORT，
QUOTED_IDENTIFIER，ANSI_NULLS ON;
GO
CREATE VIEW TblValuesView
WITH SCHEMABINDING
AS
SELECT TblValuesView.Context，
TblValuesView.ParentID，
TblValuesView.RowSeq，
FldValue.value（'（@ fieldName）[1]'，'nvarchar（30）'）AS FieldName，
FldValue.value（'（DataType）[1]'，'nvarchar（13）'） AS DataType，
FldValue.value（'（FldValue）[1]'，'nvarchar（max）'）AS FldValue，
FROM TblValues CROSS APPLY FieldValues.nodes（'/ FieldValues / FieldValue'）AS FldValues（FldValue）
GO
然后我将此视图添加到数据模型。现在，如果我从SQL Server Management Studio查询此视图，我将获得正确的值：
上下文| ParentID | RowSeq | FieldName | DataType | FldValue
--------------------------------------------- -------------------
Ctx< id> 1 txtName字符串字段值
但是，如果执行以下LINQ查询：
var FldValueViewQuery =（从CTX.TblValuesView中的行选择行）;
foreach（FblValueViewQuery中的TblValuesView tblValue）
System.Diagnostics.Trace.WriteLine（tblValue.FieldName）;
然后，我会得到以下异常：
System.Data.EntityCommandExecutionException：执行命令定义时发生错误。查看内部例外情况。
System.Data.SqlClient.SqlException：由于以下SET选项具有不正确的设置：ANSI_NULLS，因此SELECT失败。
验证SET选项是否适用于计算列和/或过滤的索引和/或查询通知和/或XML数据类型方法和/或空间索引操作的索引视图和/或索引。
如果我这样做，我只能从SQL Server Mgmt studio得到这个错误：
SET ANSI_NULLS OFFpre>
select * from TblValuesView
我已经搜索过EF是否在运行时更改此选项，而且我已经检查过我的数据库是否启用了ANSI_NULLS，并且默认为true。还有什么可以检查的？没有太多资源处理这种情况。
提前感谢
解决方案我发现了罪魁祸首。
感谢一位知道如何在我们的数据模型中建立连接的同事，我发现一个SET ANSI_NULLS OFF命令在打开与数据库的连接时正在执行，导致我们的所有命令都以此设置运行，如下所示：
public class MyConnection：DbConnection
{
...
public override void Open（）
{
using（SqlCommand cmd = new SqlCommand（SET ANSI_NULLS OFF ，（SqlConnection）this.WrappedConnection））
{
cmd.ExecuteNonQuery（）;
}
}
...
}
为此我给出的原因是在.NET 4上有一个EF和ANSI_NULLS的问题，但是这样的查询似乎需要使用ANSI_NULLS ON。
总而言之：包含Xml类型的视图在使用节点（）
时，在实体框架中工作I have a table in SQL Server 2008 created as follows:
EDIT: I had been doing some tests setting ANSI_NULLS OFF and ON and i mistakenly pasted the create table statement with ANSI_NULLS OFF, i have tried both ways and i still get the error but wanted to fix this. Hopefully there is no other paste error.
EDIT 2: I forgot to point out that if i query the original table (TblValues) everything works, the error about the ANSI_NULLS option only occurs when using the db view (TblValuesView).
SET NUMERIC_ROUNDABORT OFF; SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON; GO CREATE TABLE TblValues ( Context nvarchar(8) NOT NULL, ParentID uniqueidentifier NOT NULL, RowSeq int NOT NULL, FieldValues xml NOT NULL, FieldProperties xml NOT NULL, ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]I then loaded this table to my data model and am able to query it using LINQ to EF. The XML content in the FieldValues column is intended to be like this:
<FieldValues> <FieldValue fieldName="txtName"> <DataType>String</DataType> <FldValue>field value</FldValue> </FieldValue> ... </FieldValues>Since i need to provide the values in the xml columns in an easier way to read i created a view for this table as follows:
SET NUMERIC_ROUNDABORT OFF; SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON; GO CREATE VIEW TblValuesView WITH SCHEMABINDING AS SELECT TblValuesView.Context, TblValuesView.ParentID, TblValuesView.RowSeq, FldValue.value('(@fieldName)[1]', 'nvarchar(30)') AS FieldName, FldValue.value('(DataType)[1]', 'nvarchar(13)') AS DataType, FldValue.value('(FldValue)[1]', 'nvarchar(max)') AS FldValue, FROM TblValues CROSS APPLY FieldValues.nodes('/FieldValues/FieldValue') AS FldValues(FldValue) GOThen i added this view to the data model as well. Now if i query this view from SQL Server Management Studio i get the proper values as:
Context | ParentID | RowSeq | FieldName | DataType | FldValue ---------------------------------------------------------------- Ctx <id> 1 txtName String Field ValueBut if perform the following LINQ query:
var FldValueViewQuery = (from row in CTX.TblValuesView select row); foreach(TblValuesView tblValue in FldValueViewQuery) System.Diagnostics.Trace.WriteLine(tblValue.FieldName);I then get the following exception:
System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. System.Data.SqlClient.SqlException: SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.I can only get this error from SQL Server Mgmt studio if i do this:
SET ANSI_NULLS OFF select * from TblValuesViewI have searched on whether if EF changes this option at runtime and i have already checked that my database has ANSI_NULLS enabled and defaulted to true. What other thing can i check? there are not many resources out there that deal with this scenario.
Thanks in advance!
解决方案I found the culprit.
Thanks to a colleague that knew how the connection was set up in our data model i found out that a SET ANSI_NULLS OFF command was being executed right when opening the connection to the database causing all of our commands to run with this setting, like this:
public class MyConnection : DbConnection { ... public override void Open() { using (SqlCommand cmd = new SqlCommand("SET ANSI_NULLS OFF", (SqlConnection)this.WrappedConnection)) { cmd.ExecuteNonQuery(); } } ... }The reason i was given for this was that there is an issue with EF and ANSI_NULLS on .NET 4. But queries like this seem to be required to use ANSI_NULLS ON.
So in conclusion: Views that contain Xml types DO WORK in Entity Framework when using nodes()
这篇关于查询包含从LINQ到EF的XML列的SQL视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
