Searching EDMX metadata using C# and LINQ Yields no results

Question

I'm trying to parse out and search thru this metadata for an project I'm working on in C#. The end result, is to load the xml file (below), find the EntityType with the name "docChemicalReport" and then loop thru the table structure. Easy goal. The problem I'm having is I can't get it to return anything.

The Metadata is here: https://apps.fielddirect.com/DataServices/OData/$metadata Sample Code:

<edmx:Edmx Version="1.0">
<edmx:DataServices m:DataServiceVersion="3.0" m:MaxDataServiceVersion="3.0">
<Schema Namespace="IHSFD.Database.Context">
<EntityType Name="CorpPurchaser">
<Key>
<PropertyRef Name="CorpPurchaserID"/>
</Key>
<Property Name="CorpPurchaserID" Type="Edm.Int32" Nullable="false"/>
<Property Name="CorpID" Type="Edm.Int32" Nullable="false"/>
<Property Name="CorpPurchaserName" Type="Edm.String" Nullable="false"/>
<Property Name="Email" Type="Edm.String"/>
<Property Name="PhoneNumber" Type="Edm.String"/>
<Property Name="PhoneExt" Type="Edm.String"/>
<Property Name="CreatedDate" Type="Edm.DateTime" Nullable="false"/>
<Property Name="CreatedBy" Type="Edm.String"/>
<Property Name="ModifiedDate" Type="Edm.DateTime" Nullable="false"/>
<Property Name="ModifiedBy" Type="Edm.String"/>
</EntityType>
<EntityType Name="docChemicalReport">
<Key>
<PropertyRef Name="DocIDChemicalReports"/>
</Key>
<Property Name="DocIDChemicalReports" Type="Edm.Int32" Nullable="false"/>
<Property Name="UserID" Type="Edm.String"/>
<Property Name="EntityID" Type="Edm.Int32" Nullable="false"/>
<Property Name="EntityTypeID" Type="Edm.Int16" Nullable="false"/>
<Property Name="docDate" Type="Edm.DateTime" Nullable="false"/>
<Property Name="ChemCoCode" Type="Edm.Int16"/>
<Property Name="ChemicalName" Type="Edm.String"/>
<Property Name="ChemTypeCode" Type="Edm.Int16"/>
<Property Name="InjectPointTypeID" Type="Edm.Int16"/>
<Property Name="BeginInven" Type="Edm.Single"/>
<Property Name="EndInven" Type="Edm.Single"/>
<Property Name="UnitsDelivered" Type="Edm.Single"/>
<Property Name="UnitsDelivCode" Type="Edm.Int16"/>
<Property Name="UnitsApplied" Type="Edm.Single"/>
<Property Name="UnitsAppliedCode" Type="Edm.Int16"/>
<Property Name="ApplicationCost" Type="Edm.Decimal"/>
<Property Name="AppMethodCode" Type="Edm.Int16"/>
<Property Name="UnitsFlush" Type="Edm.Single"/>
<Property Name="UnitsFlushCode" Type="Edm.Int16"/>
<Property Name="FlushTypeCode" Type="Edm.Int16"/>
<Property Name="Stamp" Type="Edm.DateTime" Nullable="false"/>
<Property Name="Notes" Type="Edm.String"/>
<Property Name="InputByID" Type="Edm.String"/>
<Property Name="DocSourceCode" Type="Edm.Int16"/>
</EntityType>

The example I'm using is from MS: https://learn.microsoft.com/en-us/dotnet/standard/linq/find-element-specific-attribute

So, I grab the name space from root, throw together a quick query, but it yields no results.

    TextReader tr = new StringReader(responseFromServer);
    XDocument xmlDoc2 = XDocument.Load(tr);
   
    XElement root = xmlDoc2.Root;

    XElement entityType = root;
    XNamespace ns = entityType.GetDefaultNamespace();

    IEnumerable<XElement> et =
        from el in root.Elements(ns + "EntityType")
        where (string)el.Attribute(ns + "Name") == "docChemicalReport"
        select el;
    foreach (XElement el in et)
        Console.WriteLine(el);

My question is, am I over complicating this? Should I use a different xml technology to search and read the properties? What part of my code is incorrect...

Do you have sample xml? You do not usually have namespaces on attributes and the Tag Name may not be under the root. So try : from el in root.Descendants(ns + "EntityType") where (string)el.Attribute("Name") == "docChemicalReport" — jdweng, Oct 12 '20 at 17:26
@jdweng, I posted a sample of the xml at the top of the post, and a link to the actual xml. Changing it to root.Descendants didn't help tho. Still doesn't return anything. — Dregalia, Oct 12 '20 at 17:54
For anyone to play with, I loaded this into dotnetfiddle. https://dotnetfiddle.net/15t2jV — Dregalia, Oct 12 '20 at 18:42
You can do this by reading EF's meta data: https://stackoverflow.com/q/6056597/861716 — Gert Arnold, Oct 12 '20 at 18:51
@GertArnold, this looks a bit over my skill level. I appreciate your comment, but I don't understand how I would use it in my case. I think just a simple linq query will help me get to where I need to go. — Dregalia, Oct 12 '20 at 20:25
All you posted where schemas and no real xml with data. You will notice the schema tag on line 3. — jdweng, Oct 12 '20 at 21:13
@jdweng, I'm querying the Schema... which is the XML. Sorry if I didn't make that clear enough. I'm trying to pull out the table definition for a specific table, and loop thru it in my code. That's the overall goal. — Dregalia, Oct 12 '20 at 22:35
The schema is always xml but contains no data (unless you have an embedded schema in xml which you do not). You are not getting any data base there is no data in the schema. — jdweng, Oct 13 '20 at 00:53
@jdweng, Let me re-rephrase. I'm trying to query the XML document that currently represents the Schema, select the entitytype that has a name "docChemicalReport", and then loop thru the properties underneath it. I'm not trying to query the data behind it. I'm trying to pick apart the XML document that is the Schema file. — Dregalia, Oct 13 '20 at 01:04
@Jdweng, thanks for trying. I just am unable to explain it to you... I have read most of your posts, I know you're more than capable. Back in the day, I would load that Schema document in XML format, Select Single Node on Return that whole node, and loop thru the properties, one by one, which is literally what I'm trying to do. There is no data in this project. There is only querying the Schema Document to verify that the database is right before I do a sql bulkcopy into it. — Dregalia, Oct 13 '20 at 17:53

jdweng · Accepted Answer · 2020-10-13T22:02:20.580

See if following helps :

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

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

            XElement schema = doc.Descendants().Where(x => x.Name.LocalName == "Schema").FirstOrDefault();
            int v = 0;
            string z = v.GetType().ToString(); 
            XNamespace ns = schema.GetDefaultNamespace();
            Dictionary<string, Entity> entityTypes = schema.Descendants(ns + "EntityType")
                .Select(x => new Entity() {
                    name = (string)x.Attribute("Name"),
                    key = (string)x.Descendants(ns + "PropertyRef").FirstOrDefault().Attribute("Name"),
                    properties = x.Elements(ns + "Property").Select(y => new Property()
                    {
                        name = (string)y.Attribute("Name"),
                        _type = Type.GetType("System." + ((string)y.Attribute("Type")).Split(new char[] {'.'}).Last()),
                        nullable = (y.Attribute("Nullable") == null)? (Boolean?)null : ((string)y.Attribute("Nullable") == "false")? false : true
                    }).ToList()
                }).GroupBy(x => x.key, y => y)
                .ToDictionary(x => x.Key, y => y.FirstOrDefault());
        }

    }
    public class Entity
    {
        public string name { get; set; }
        public string key { get; set; }
        public List<Property> properties { get; set; }
    }
    public class Property
    {
        public string name { get; set; }
        public Type _type { get; set; }
        public Boolean? nullable { get; set; }
    }

}

Yes, so the part that I need from this, is how to I select just the one that says 'docChemicalReport' and then loop thru the properties of it? That is the part I'm missing (btw, thank you for your patience). So, Select the node that has the EntityType name='docChemicalReport' and then loop thru the property elements and read the attributes from it into the list. — Dregalia, Oct 13 '20 at 20:22
thanks for the update. got the code to work, I see it's all loaded up into a dictionary that has all the information into it, now I just have to cycle thru the dictionary, and pick out the item properties I assume. I'll mess with this, but this is further than I could have gotten on my own. — Dregalia, Oct 14 '20 at 03:24
Hey @jdweng, so, dumb question... I ran the code, it worked, but it left off the last 2 entries from the file.. not complaining because I was able to finish out my solution and just manually handle the rest.. but when I look at the entity created, they're not in dictionary at all. It has me curious as to why that would happen, for future use. — Dregalia, Oct 14 '20 at 19:04
The dictionary may not be in order or a schema issue. Which items are missing? There is a warning message : Warning 3 The element 'DataServices' in namespace 'http://schemas.microsoft.com/ado/2007/06/edmx' has invalid child element 'Schema' in namespace 'http://schemas.microsoft.com/ado/2009/11/edm'. List of possible elements expected: 'Schema' in namespace 'http://schemas.microsoft.com/ado/2006/04/edm'. C:\Users\Joel\AppData\Local\Temporary Projects\ConsoleApplication1\XMLFile1.xml 4 6 ConsoleApplication1 — jdweng, Oct 15 '20 at 04:28

Searching EDMX metadata using C# and LINQ Yields no results

1 Answers1