LINQ query to fetch bus number taking two records

Question

Here is my table sample

Busnumber   st1  st2    st3     st4     st5     st6
1           abc  xyz    hvh     cdff    dfds    dfds
2           abc  efg    vv      vhv     cfg     vvv
3           uyt  ggg    xyz     hhh     saa     pok
4           uyr  abc    xyz     iii     ppp     wer

i want to select all busnumber having both stations "abc" and "xyz" together in individual busnumber here the output i want is busnumber 1 and 4

so how to how to achieve this in xml to linq query in windows phone using c# i am trying below code

XDocument loadedCustomData = XDocument.Load("best.xml");
        var filteredData =
           from c in loadedCustomData.Descendants("record")
           ///what to do below here in were clause statement ??????
          where (string)c.Element ("st1") == froms.Text && (string)c.Element("st2") == to.Text ..........

           select new words()
           {
               Busnumber = (string)c.Element("bus")

           };
        listBox1.ItemsSource = filteredData;

my xml sample

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<data-set xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<record>
    <Busnumber>1</Busnumber>
    <st1>Santacruz Depot</st1>
    <st2>abc</st2>
    <st3>Vile Parle</st3>
    <st4>Mithibai College</st4>
    <st5>xyz</st5>
    <st6>Juhu Shopping Centre</st6>
</record>
<record>
    .......
</record>

i want to fetch all busnumbers having staions code abc and xyz i.e busnumber 1 and 4 in above sample table .the code is wrong not working — SD7, Aug 12 '14 at 08:21

score 1 · Accepted Answer · edited May 23 '17 at 11:57

You can use technique explained in this thread : Determine if a sequence contains all elements of another sequence using Linq

Use the same to check if list of <record>'s child contain all criteria you want to check. Implementation in your case is about like this :

var criteriaArray = new string[]{ "abc", "xyz" };
var filteredData = 
        from c in Busnumber.Descendants("record")
        where !criteriaArray.Except(c.Elements().Select(o => (string)o))
                            .Any() 
        select new
        {
           Busnumber = (string)c.Element("Busnumber")
        };

You may want to change where clause to check only child elements starting with "st" :

where !criteriaArray.Except(c.Elements()
                             .Where(o => o.Name.LocalName.StartsWith("st"))
                             .Select(o => (string)o))
                    .Any()

UPDATE :

I'll try to explain, I assumed you already carefully read the question I linked above. This snippet subset.Except(superset).Any() checks if subset contains element that isn't contained in superset.

So this one !subset.Except(superset).Any() checks if subset doesn't contain element that isn't contained in superset. In other words, subset only contain elements that is also contained in superset. In different words, superset contains all element of subset. So this is what you want; check if child of record node (the superset) contains all member of criteriaArray (the subset).

wow awesome it worked for me but i did not understand the code specially where clause u showed above can u explain little bit @har07 — SD7, Aug 12 '14 at 09:16
Tried to explain in update section, I assumed you know LINQ `Except()` and `Any()` already — har07, Aug 12 '14 at 09:28

LINQ query to fetch bus number taking two records

1 Answers1