Where condition with linq dynamic built at runtime to read xml

Question

I've a problem with a query in linq (lambda expression) to read a complex and large xml file embedded in my software. At bottom of post can find the structure of single field "PRODOTTO", but there's about 2800 entries like this in the xml.

The problem is the where condition. I need to add more condition dynamically (depends on user interaction).

I tried to use System.Linq.Dynamic

I need to select all product with the fields called "CAPITOLO" and "GR_MERC", only one selected by user (before) with certain values. The values for "CAPITOLO" are 1,2,3,4,5,6,7,8,9,10,11,12,99 (13 types) and for "GR_MERC" are 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,32,82,88,89 (31 types) The user can select one field and one or more values of selected field. I need to build the where-clause at runtime.

Here's the snippet of code that doesn't work.

using System.Linq.Dynamic;

var query = XElement.Parse(Properties.Resources.CATALOGO).Elements("PRODOTTO");

// Start of where part
string clauses = string.Empty;  //String for the clauses
int n_clauses = cat.Count();  //Count the number of clauses
int counter = 0;
if (TypeOfFilter == 0)  //TypeOfView = 0 Show the product to user by CAPITOLO (he select type of filter before)
{
    foreach (var p in cat)  //Can be only one "CAPITOLO" condition or maximum 13
    {
        clauses += string.Format("\"CAPITOLO\" == \"{0}\"", p);  //<-- here is the problem. Return always 0 results.
        counter++;
        if (counter < n_clauses)
            clauses += " || ";  //Add the OR condition
    }
}
else //TypeOfView = 1 Show the product to user by GR_MERC (he select type of filter before)
{
    foreach (var p in cat)  //Can be only one "GR_MERC" condition or maximum 31
    {
        clauses += string.Format("\"GR_MERC\" == \"{0}\"", p);  //<-- here is the problem. Return always 0 results.
        counter++;
        if (counter < n_clauses)
            clauses += " || ";  //Add the OR condition
    }
}
query = query.Where(clauses).ToList();
// End of where part

The last part works well if I exclude the "where" part.

var query2 = query
            .GroupBy(x => x.Element("CODICE"))
            .Select(x => new
            {
                ARTICOLO = x.Elements("ARTICOLO").First().Value,
                CODICE = x.Elements("CODICE").First().Value,
                DESCRIZIONE = x.Elements(language).First().Value, //Can be one of IT, EN, DE, FR, RU, RO, ZH selected before from the user
                MISURA = x.Elements("MISURA").First().Value,
                PREZZO = decimal.Parse(x.Elements("PREZZO").First().Value),
                SC1 = decimal.Parse(x.Elements("SC_BASE").First().Value),
                SC2 = decimal.Parse(x.Elements(sc).First().Value),  //One value between SC_IT and SC_ES
                CAPITOLO = int.Parse(x.Elements("CAPITOLO").First().Value),
                PAGINA = int.Parse(x.Elements("PAGINA").First().Value),
                RIQUADRO = int.Parse(x.Elements("RIQUADRO").First().Value),
                SUBORD = int.Parse(x.Elements("SUBORD").First().Value)
            })
            .ToList()
            .OrderBy(b => b.CAPITOLO).ThenBy(b => b.PAGINA).ThenBy(b => b.RIQUADRO).ThenBy(b => b.SUBORD)
            .Select(x => new Article
            {
                Art = x.ARTICOLO,
                Cod = x.CODICE,
                Des = x.DESCRIZIONE,
                Mis = x.MISURA,
                Prz = x.PREZZO,
                Sc1 = x.SC1,
                Sc2 = x.SC2
            });

UPDATED THE STRUCTURE OF XML

The structure of the xml file is this (only 2 products, they're about 2800):

<DATA xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <PRODOTTO>
        <PRODOTTO_ID>2540</PRODOTTO_ID>
        <ARTICOLO>208CTP</ARTICOLO>
        <CODICE>208CTP-06-04</CODICE>
        <CAPITOLO>99</CAPITOLO>
        <PAGINA>41</PAGINA>
        <RIQUADRO>1</RIQUADRO>
        <SUBORD>4</SUBORD>
        <MISURA>1”x4</MISURA>
        <PREZZO>51.80</PREZZO>
        <SC_BASE>0.51</SC_BASE>
        <SC_IT>0.10</SC_IT>
        <SC_ES>0.10</SC_ES>
        <QUANTITA>1</QUANTITA>
        <CONFEZIONE>5</CONFEZIONE>
        <GR_MERC>20</GR_MERC>
        <UM>N°</UM>
        <IT>Collettore di andata F.F. con attacchi derivati 3/4”M Eurocono, regolatori e misuratori di portata. Interasse 50 mm.</IT>
        <EN>F.F. delivery manifold with 3/4”M Eurocone connections and flow regulators and meters. Centre distance 50 mm.</EN>
        <DE>Vorlaufverteiler IG/IG, Anschluss 3/4”AG Eurokonus, mit Durchflussmessern. Achsabstand 50 mm.</DE>
        <RU>Коллектор подачи (внутр.-внутр. резьба) со штуцерами для отводов на 3/4” (наружн. резьба) «евроконус», регуляторами и расходомерами. Межосевое расстояние 50мм</RU>
        <FR>Collecteur de départ F.F. avec raccords dérivés 3/4”M Eurocone et débitmètres. Entraxe 50 mm.</FR>
        <RO> Tur colector cu FI.FI din inoxcu conexiuni 3/4” FE Eurocon echipat cu supape de inchidere cu debitmetre cu scala. Distanţa interaxiala 50 mm.</RO>
        <ZH>带流量计分水器</ZH>
        <CATEGORIA_IT>FUORI LISTINO</CATEGORIA_IT>
        <CATEGORIA_EN>OTC</CATEGORIA_EN>
        <CATEGORIA_DE>OTC</CATEGORIA_DE>
        <CATEGORIA_RU>OTC</CATEGORIA_RU>
        <CATEGORIA_FR>OTC</CATEGORIA_FR>
        <CATEGORIA_RO>OTC</CATEGORIA_RO>
        <CATEGORIA_ZH>OTC</CATEGORIA_ZH>
        <SOTTOCATEGORIA_EN xsi:nil="true" />
        <SOTTOCATEGORIA_DE xsi:nil="true" />
        <SOTTOCATEGORIA_RU xsi:nil="true" />
        <SOTTOCATEGORIA_FR xsi:nil="true" />
        <SOTTOCATEGORIA_RO xsi:nil="true" />
        <SOTTOCATEGORIA_ZH xsi:nil="true" />
        <GR_MERC_DESCR_IT>COLLETTORI DI DISTRIBUZIONE IN OTTONE CROMATO</GR_MERC_DESCR_IT>
        <GR_MERC_DESCR_EN>BRASS CHROME DISTRIBUTION MANIFOLDS</GR_MERC_DESCR_EN>
        <GR_MERC_DESCR_DE>VERTEILER AUS MESSING CHROMIERT</GR_MERC_DESCR_DE>
        <GR_MERC_DESCR_RU>ХРОМИРОВАННЫЕ КОЛЛЕКТОРЫ ИЗ ЛАТУНИ</GR_MERC_DESCR_RU>
        <GR_MERC_DESCR_FR>COLLECTEURS DE DISTRIBUTION EN LAITON CHROMÉ</GR_MERC_DESCR_FR>
        <GR_MERC_DESCR_RO xsi:nil="true" />
        <GR_MERC_DESCR_ZH xsi:nil="true" />
        <L14_2016 xsi:nil="true" />
        <AUMENTO_14_15 xsi:nil="true" />
    </PRODOTTO>
    <PRODOTTO>
        <PRODOTTO_ID>2432</PRODOTTO_ID>
        <ARTICOLO>286HT</ARTICOLO>
        <CODICE>286HT-053B</CODICE>
        <CAPITOLO>8</CAPITOLO>
        <PAGINA>40</PAGINA>
        <RIQUADRO>2</RIQUADRO>
        <SUBORD>1</SUBORD>
        <MISURA>3/4”x3/8”</MISURA>
        <PREZZO>8.15</PREZZO>
        <SC_BASE>0.51</SC_BASE>
        <SC_IT>0.10</SC_IT>
        <SC_ES>0.10</SC_ES>
        <QUANTITA>1</QUANTITA>
        <CONFEZIONE xsi:nil="true" />
        <GR_MERC>3</GR_MERC>
        <UM>N°</UM>
        <IT>Calotta con codolo e O-Ring di battuta per valvole Design.</IT>
        <EN>Union connection with O-Ring seal for Design valves.</EN>
        <DE>Überwurfmutter mit Rohrstutzen und O-Ring für Design Ventile.</DE>
        <RU>Гайка с хвостовиком и упорным уплотнительным кольцом O-Ring</RU>
        <FR>Ecrou avec douille et joint torique pour robinets Design.</FR>
        <RO>Conexiune cu O-ring pentru robineti Design.</RO>
        <ZH xsi:nil="true" />
        <CATEGORIA_IT>VALVOLE E DETENTORI</CATEGORIA_IT>
        <CATEGORIA_EN>VALVES AND LOCKSHIELDS</CATEGORIA_EN>
        <CATEGORIA_DE>VENTILE UND RÜCKLAUFVENTILE</CATEGORIA_DE>
        <CATEGORIA_RU>КЛАПАНЫ И ЗАПОРНЫЕ ВЕНТИЛИ</CATEGORIA_RU>
        <CATEGORIA_FR>ROBINETS ET TÉS DE RÉGLAGE</CATEGORIA_FR>
        <CATEGORIA_RO>ROBINETE TUR RETUR</CATEGORIA_RO>
        <CATEGORIA_ZH>锁闭阀和温控阀</CATEGORIA_ZH>
        <SOTTOCATEGORIA_IT>VALVOLE DESIGN</SOTTOCATEGORIA_IT>
        <SOTTOCATEGORIA_EN>DESIGN VALVES</SOTTOCATEGORIA_EN>
        <SOTTOCATEGORIA_DE>DESIGN VENTILE</SOTTOCATEGORIA_DE>
        <SOTTOCATEGORIA_RU>ДИЗАЙНЕРСКИЕ ВЕНТИЛИ</SOTTOCATEGORIA_RU>
        <SOTTOCATEGORIA_FR>ROBINETS DESIGN</SOTTOCATEGORIA_FR>
        <SOTTOCATEGORIA_RO>ROBINETE DESIGN</SOTTOCATEGORIA_RO>
        <SOTTOCATEGORIA_ZH xsi:nil="true" />
        <GR_MERC_DESCR_IT>RACCORDI PER VALVOLE HI-TECH</GR_MERC_DESCR_IT>
        <GR_MERC_DESCR_EN>DESIGN FITTINGS</GR_MERC_DESCR_EN>
        <GR_MERC_DESCR_DE>DESIGN ANSCHLUSSSTÜCKE</GR_MERC_DESCR_DE>
        <GR_MERC_DESCR_RU>ФИТИНГИ ДИЗАЙНА</GR_MERC_DESCR_RU>
        <GR_MERC_DESCR_FR>RACCORDS HIGH-TECH</GR_MERC_DESCR_FR>
        <GR_MERC_DESCR_RO xsi:nil="true" />
        <GR_MERC_DESCR_ZH xsi:nil="true" />
        <L14_2016 xsi:nil="true" />
        <AUMENTO_14_15 xsi:nil="true" />
    </PRODOTTO>
</DATA>

Answer 1

Use a dictionary along with Xml Linq :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;

namespace ConsoleApp2
{

    class Program
    {
        const string FILENAME = @"c:\temp\test.xml";
        static void Main()
        {
            XDocument doc = XDocument.Load(FILENAME);

            List<XElement> PRODOTTO = doc.Descendants().Where(x => x.Name.LocalName == "PRODOTTO").Select(x => x.Elements()).SelectMany(x => x).ToList();

            Dictionary<string, string> products = PRODOTTO
                .GroupBy(x => x.Name.LocalName, y => (string)y)
                .ToDictionary(x => x.Key, y => y.FirstOrDefault());

            //use following if more than one item with same tag
            Dictionary<string, List<string>> products2 = PRODOTTO
              .GroupBy(x => x.Name.LocalName, y => (string)y)
              .ToDictionary(x => x.Key, y => y.ToList());

            Dictionary<string, Dictionary<string,string>> products3 = doc.Descendants().Where(x => x.Name.LocalName == "PRODOTTO")
                .GroupBy(x => (string)x.Element("PRODOTTO_ID"), y => y.Elements()
                    .GroupBy(a => a.Name.LocalName, b => (string)b)
                    .ToDictionary(a => a.Key, b => b.FirstOrDefault()))
                    .ToDictionary(x => x.Key, y => y.FirstOrDefault());
        }
    }
}