Xml Columns

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?

Comments

  • 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.
  • Okay, thanks Joe. Much appreciated!
  • Any word on this? While I prefer Linqpad for querying, this often bites me as I work.
  • I've not had any new ideas. Let me know if you have any thought with regards a workaround.
  • edited February 2015
    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
  • 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
  • edited February 2015
    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.
  • edited March 2015
    A new beta is now available which implements this hack. Let me know how you get along.
  • 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!)
  • 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!
  • Any chance you can share the fix? I'd love to see if I can apply it to our LinqtoSql contexts
  • 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.
  • 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.
  • 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.
  • 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 =)
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!