Xml Columns

neilejgreen

Based on the Linqpad Challenge I now use LinqPad to do all my adhoc sql queries. However I hit a wall in a lot of places where we have xml columns in our tables.

In a number of places we have empty strings instead of null values in these columns. However this renders linqpad useless as if any of these values are returned in my query. I am met with a simple:

XmlException
Root element is missing.

Is there any way around this? Anyway to have linqpad treat all xml fields as strings for example?

JoeAlbahari

This seems to be a limitation of LINQ-to-SQL. I'm not sure whether it's possible to map these columns to strings - I'll investigate.

neilejgreen

Okay, thanks Joe. Much appreciated!

KWood

Any word on this? While I prefer Linqpad for querying, this often bites me as I work.

JoeAlbahari

I've not had any new ideas. Let me know if you have any thought with regards a workaround.

MaslowB

I'm not sure if this can be internalized into LinqPad, but this generates the proper F# code to read a type in without the Xml column assignment (I'm sure it could be adapted to C#, probably going to do that myself next) http://share.linqpad.net/jx25gm.linq

It is relying on a nuget package to make the generated code a little cleaner, but that's not required for functionality

MaslowB

Tested this one's generated code for use in C# and F#
added options to control camelizing and fixing of ID to Id on column names http://share.linqpad.net/xxnani.linq

JoeAlbahari

It's certainly possible to ignore the XML columns entirely, if that's what you're suggesting.

I'd rather incorporate a fix to make it work properly, though. This means hacking around the bug in LINQ to SQL. I'm working on something, check out the next beta.

JoeAlbahari

A new beta is now available which implements this hack. Let me know how you get along.

MaslowB

so far it's working great! thanks a ton. I'm over excited, tried it with only one of the tables that used to fail so far. (Also VERY excited about your tweet saying F# gets love next!)

MaslowB

If I do this

from dfx in DrFirstXMLLogs.Where(x => x.APICall =="update_medication" && x.Response != null)
let number = dfx.Response.XPathSelectElement("//Number")
select number

I get an exception ArgumentNullException ->

at System.Xml.XPath.Extensions.XPathSelectElements(XNode node, String expression, IXmlNamespaceResolver resolver)
   at Read_XElement(ObjectMaterializer`1 )
   at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at UserQuery
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()

instead I have to do

from dfx in DrFirstXMLLogs.Where(x => x.APICall =="update_medication" && x.Response != null && x.Response.ToString() != "")
let number = dfx.Response.XPathSelectElement("//Number")
select number

very much something I can work around, but if it is fixable, I'd be even happier.

Thanks a ton btw!

MaslowB

Any chance you can share the fix? I'd love to see if I can apply it to our LinqtoSql contexts

JoeAlbahari

I'm not sure there's anything I can do to hack around this. Your query translates into something like:

...where x.Response != null...

which includes the blank records as well as the null ones. To make this work as you'd like, I'd have to instruct LINQ to SQL to modify the SQL that it generates and I don't think this can be done.

MaslowB

can you tell me more about what you did do in the beta? I'd love to see if I can apply it to my project's use case.

JoeAlbahari

I created a subclass of XElement called LINQPadXElement which defines a static Parse method with the same signature as XElement.Parse. It works in the same way, except that it returns null instead of throwing an exception if the input is empty or whitespace. LINQ to SQL will find this overload via reflection by virtue of its signature.

To hook it in requires some nasty hacks - open LINQPad in ILSpy and take a look at DataContextBase.Init and SubmitChanges.

MaslowB

Sweet, thanks. I had opened up linqpad to search, saw the LINQPadXElement, but couldn't figure out where it was hooking in, your comment should help me plenty