Querying a SQL View containing XML columns from LINQ to EF

Question

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)
GO

Then 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 Value

But 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 TblValuesView

I 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 believe ANSI_NULLS will no longer be supported and defaulted to ON, so you might wat to get rid of them anyway. Do you have to use a view or would you be allowed to access the XML content directly from EF? — Darek, Aug 01 '12 at 00:50
I have tried without ANSI_NULLS too but it does not really make a difference. I need to do both, access the XML content directly from EF and provide an 'easy' way through the view for reporting purposes... — dmiranda84, Aug 01 '12 at 00:55
OK, will be posting solution shortly ... Keep the view for whoever needs it ... — Darek, Aug 01 '12 at 01:09

Darek · Answer 1 · 2012-08-01T01:32:26.043

0

First, bring the table as is into EF, but declare FieldValues as private (getter and setter). Add the following code:

 public partial class TblValues
 {
     private XElement fldXmlValues = null;
     public XElement FieldXmlValues
     {   
        get{
           if (fldXmlValues == null){
              fldXmlValues = XElement.Parse(this.FieldValues);
              fldXmlValues.Changed += (s,e) => this.FieldValues = fldXmlValues.ToString();
           }
           return fldXmlValues;
        }
        set{
           fldXmlValues = value;
           fldXmlValues.Changed += (s,e) => this.FieldValues = fldXmlValues.ToString();
           this.FieldValues = fldXmlValues.ToString();
        }
     }
  }

Then you should have access through:

from t in context.TblValues
select t.FieldXmlValues.Attribute("fieldName").Value as FieldName

and so on....

edited Aug 01 '12 at 01:32

answered Aug 01 '12 at 01:24

Darek

4,687
31
47

The current release of Entity Framework does not support the XML data type. Given the importance of XML, it is likely that some future version will provide full support. – Darek Aug 01 '12 at 01:36
I give you a +1 because this answer could work as an alternative, but i think that my question is still effective because views DO work with EF, it is only when a view has to pull data from XML types and based on the error i suspect it could be a settings issue... **EDIT:** Turns out i can't +1 your post :( (not enough rep) but thanks! – dmiranda84 Aug 01 '12 at 15:05

score 0 · Accepted Answer · answered Aug 02 '12 at 23:59

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

Querying a SQL View containing XML columns from LINQ to EF

2 Answers2