Can Linq to SQL Query an Xml Field Db-Serverside

Can LINQ to SQL query an XML field DB-serverside?

Now that is an interesting question.

Right now, you cannot instruct SQL Server to perform XML functions directly from Linq.
However, you can get Linq to use user defined functions...
so, you could setup a udf to process the xml, get the right data, etc, and then use that in your Linq expresion. This will execute on the server and should do what you want. There's an important limitation, though: The XML path you're looking for (the first parameter to xmlColumn.value or similar) has to be built into the function because it has to be a string literal, it can't be built from an input parameter (for instance). So you can use UDFs for getting fields you know about when writing the UDF, but not as a general-purpose way to get data from XML columns.

Check out the Supporting User Defined Functions (UDFs) section of Scott Gutherie's excellent Blog series on Linq to SQL for more info on implementation.

Hope this helps.

SQL Database with XML fields, select according to XElement using LINQ

I am pretty sure that Linq 2 SQL does not support what you are trying to do. You would probably need to write a custom SQL statement or use a user defined function as discussed in the solution to this SO question: Can LINQ to SQL query an XML field DB-serverside?

Searching a tables XML filed using Linq, can it be done?

Yes, it is easily possible with LINQ:

var matchList = from t in transactions
                where XDocument.Load (new StringReader (t.Message))
                               .Descendants ("employee")
                               .Count (node => node.Value == employeeNr) > 0
                select t;

query xml string using LINQ

You can't call Contains("<" + i + "[^>]*>"+value+"</" + i + ">")) on XMLContent as sql type for that column is XML and not NVARCHAR.

Basically you can call Contains on something that is a string or implicitly converted to string by SQL Server.

But you can try to work around it on server side. Try to load XML from the DB first:

var xMLDatas = db.XMLDatas.Include(x => x.Form).Where(x=>x.FormId==id).ToList();

Specify field names in Linq to Sql

You will need dynamic LINQ in order to accomplish that. It does not come out of the box.

http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

http://blog.bvsoftware.com/post/2008/02/27/How-to-create-a-Dynamic-LINQ-Query-Programmatically.aspx

Why would a Database developer use LINQ

LINQ has one big advantage over most other data access methods: it centers around the 'query expression' metaphor, meaning that queries can be passed around like objects and be altered and modified, all before they are executed (iterated). In practice what that means is that code can be modular and better isolated. The data access repository will return the 'orders' query, then an intermediate filter in the request processing pipeline will decorate this query with a filter, then it gets passed on to a display module that adds sorting and pagination etc. In the end, when its iterated, the expression has totally transformed into very specific SELECT ... WHERE ... ORDER BY ... LIMIT ... (or the other back-end specific pagination like ROW_NUMBER). For application developers, this is priceless and there simply isn't any viable alternative. This is why I believe LINQ is here to stay and won't vanish in 2 years. It is definitely more than just a fad. And I'm specifically referring to LINQ as database access method.

The advantage of manipulating query expression objects alone is enough of a factor to make LINQ a winning bid. Add to this the multiple container types it can manipulate (XML, arrays and collections, objects, SQL) and the uniform interface it exposes over all these disparate technologies, consider the parallel processing changes coming in .Net 4.0 that I'm sure will be integrated transparently into LINQ processing of arrays and collections, and is really no way LINQ will go away. Sure, today it sometimes produces unreadable, poorly performant and undebuggable SQL, and is every dedicated DBA nightmare. It will get better.

Get Xml field from database, then get value from a particular element

You might create and dereference the XML-Linq on the fly in the select clause, like this:

select XElement.Parse(messages.MessageXML).Element("IDThatIWant")

However, to separate SQL and XML and including the restriction (only if element has attribute) it would be something like:

var messageXml = from messages in dbContext.Messages
                           join transactions in dbContext.Transactions
                           on messages.TransactionID equals transactions.TransactionID 
                           where transactions.CreatedOn >= StartDate
                           && transactions.CreatedOn <= EndDate
                           select messages.MessageXML;

var messages = from m in messageXml select XElement.Parse(m);

var ids = (from msg in messages
            let id = msg.Attribute("IDThatIWant")
            where !String.IsNullOrEmpty(id) 
            select Convert.ToInt32(id)).ToList<int>();

---

Related Topics