CODEWITHSUNDEEP
c#xmlt-sqllinq-to-entities
Space Programmer
Space Programmer

Reputation: 78

XQuery in Linq To Entities for Sql Server XML Data Type

I am trying to figure out how to do a Linq To Entities query on a SQL Server Database which contains an XML datatype:

I want to do a database level query that returns only a specified XML node that contains over 2000 Characters

Is this possible in Linq To Entities?

Update

well i want to do something like this in linq-to-entities but I don't think its possible

var q = from c in Changes
 where c.Content.XPathSelectElement("OpsNotes").Value.Length >= 2000;
 select c;

inside the sql server database the XML content is a node called "OpsNotes" 

 <Content Type="AP" Version="1">
  <Name>CPU FAIL-OVER</Name>
  <Bands>
    <Band>BAND DATA</Band>
  </Bands>
  <StartTime IsCritical="false" IsTimeSet="true">2009-12-06T14:00:00.0000000Z</StartTime>
  <Duration>00:30:00</Duration>
  <RecurrenceRule>RRULE:[List]{340/2009}</RecurrenceRule>
  <OpsNotes>Rationale: Support standard weekly DVIS CPU fail-over</OpsNotes>
   </Content>

Upvotes: 3

Views: 3245

Answers (2)

marc_s
marc_s

Reputation: 754220

I would be surprised if that were possible, directly.

You will most like need to do one of two workarounds:

  • retrieve the XML from the SQL Server field as a string, and put it into an XDocument (Linq-to-XML) or XmlDocument (DOM-based handling) and do your tricks on it

or:

  • handle the requests on SQL Server itself, using the SQL Server XQuery, and return results as string, ints etc. - whatever you need to do

I personally would prefer the option to handle the XQuery stuff on the server itself - in order to help you with your concrete problem, you'll have to provide some sample XML and an explanation of what you want to do....

Update: for your requirement, in SQL Server XQuery, you'd have to write something like:

var q = from c in Changes where c.Content.XPathSelectElement("OpsNotes").Value.Length >= 2000; select c;

SELECT (list of fields)
FROM dbo.Changes c
WHERE c.Content.value('(string-length(string((/Content/OpsNotes)[1])))', 'int') >= 2000

or something like that.

As an added bonus, in this case, you'd only transfer a smaller amount of rows back from the server to your client app since the server is already filtering out all the non-matching rows.

Upvotes: 1

Craig Stuntz
Craig Stuntz

Reputation: 126547

You'll have to write SQL for this.

In EF 4, you can use ExecuteStoreQuery to execute arbitrary SQL with entity-typed results.

Upvotes: 0

Related Questions