none
Linq to SQL and MSSQL XML datatype

    Question

  • Hello,

    I'm trying to make a linq to sql query to query a db table based on a field that is of the XML datatype.  I want to get all the HandReceipts that have Assets whos specifications (Stored in XML) match search fields.

    the code that I have so far is
    var src = from h in _handReceipt.HandReceipts
                          where (from a in h.Assets
                                 where (from s in a.Specifications.Elements()
                                        where s.Attribute("Name").Value == "RAM" && s.Value == "1"
                                        select s).Count() > 0
                                 select a)
                                 .Count() > 0
                          select h;
    a.Specifications is an XElement type that has the form of:
    <specs>
        <spec Name="CPU">3.2</spec>
        <spec Name="RAM">1</spec>
    </specs>
    There are several other <spec Name="NAME">#</spec> elements, these are just some samples.  Some <specs /> don't have subElements <spec Name="RAM">#</spec> at all

    The error that I am receiving is: The argument 'value' was the wrong type. Expected 'System.Xml.Linq.XElement'. Actual 'System.Collections.Generic.IEnumerable`1[System.Xml.Linq.XElement]'.

    Any help would be wonderful

    I'm newish to linq, so if my code is an eye sore, I appologize.
    Monday, October 19, 2009 5:43 PM

Answers

  • Thanks!  I actually ended up separating it into another method and returning an IEnumerable<T> object that has the parts split into foreach statements.  I did it for testing purposes, but it worked and it's pretty fast (fast enough) so I'm just going to keep it like that. 

    Here's the code:

            private IEnumerable<HandReceipt.Models.HandReceipt> getSearchResults(string search)
            {
                string name = search.Split(' ')[0];
                string val = search.Split(' ')[1];
    
                bool cont = false;
                foreach (var h in _handReceipt.HandReceipts)
                {
                    foreach (var a in h.Assets)
                    {
                        if (cont == true)
                            break;
    
                        var src = (from s in a.Specifications.Descendants("spec")
                                   where (string)s.Attribute("Name") == name && (string)s == val
                                   select s);
    
                        if (src.Count() > 0)
                        {
                            cont = true;
                            yield return h;                        
                        }
                    }
                    cont = false;
                }
            }
    
    • Marked as answer by joe_coolish Thursday, October 22, 2009 2:43 PM
    Thursday, October 22, 2009 2:42 PM

All replies

  • Hi Joe,


    Could you try to delete the ".value"?

    For example,
    var q = from c in loaded.Descendants("contact")
            where (int)c.Attribute("contactId") < 4
            select (string)c.Element("firstName") + “ “ +
       (string)c.Element("lastName");

    You can also try to load the xml filed first then use LINQ to XML to query it. This aritcle is for your reference,
    http://www.hookedonlinq.com/LINQtoXML5MinuteOverview.ashx

    Does this work for you?

    Best Regards
    Yichun Feng


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, October 21, 2009 5:59 AM
  • Thanks!  I actually ended up separating it into another method and returning an IEnumerable<T> object that has the parts split into foreach statements.  I did it for testing purposes, but it worked and it's pretty fast (fast enough) so I'm just going to keep it like that. 

    Here's the code:

            private IEnumerable<HandReceipt.Models.HandReceipt> getSearchResults(string search)
            {
                string name = search.Split(' ')[0];
                string val = search.Split(' ')[1];
    
                bool cont = false;
                foreach (var h in _handReceipt.HandReceipts)
                {
                    foreach (var a in h.Assets)
                    {
                        if (cont == true)
                            break;
    
                        var src = (from s in a.Specifications.Descendants("spec")
                                   where (string)s.Attribute("Name") == name && (string)s == val
                                   select s);
    
                        if (src.Count() > 0)
                        {
                            cont = true;
                            yield return h;                        
                        }
                    }
                    cont = false;
                }
            }
    
    • Marked as answer by joe_coolish Thursday, October 22, 2009 2:43 PM
    Thursday, October 22, 2009 2:42 PM