Procedure for a m:n data relationship

Noah Aas 260 Reputation points
2024-05-09T07:56:03.26+00:00

Hello!

Are there any tools or tips on how to create a data model? Once the data model is there, how best to deal with primary and foreign keys. The based on XML or JSON format. Save and read XML or JSON files.

<MappingTable>
  <Parameters>
    <Parameter id="1" value="Para1" />
    <Parameter id="2" value="Para2ggg" />
    <Parameter id="3" value="Para3" />
    <Parameter id="4" value="Para4" />
    <Parameter id="5" value="Para5" />
  </Parameters>
  <Labels>
    <Label id="1" vendor="xxx1" vendorLocation="bbb1" />
    <Label id="2" vendor="xxx2" vendorLocation="bbb2" />
    <Label id="3" vendor="xxx3" vendorLocation="bbb3" />
    <Label id="4" vendor="xxx4" vendorLocation="bbb4" />
    <Label id="5" vendor="xxx5" vendorLocation="bbb5" />
  </Labels>
  <ParametersLabels>
    <ParameterLabel id="1" fKParameter="5" fKLabel="1" />
    <ParameterLabel id="2" fKParameter="1" fKLabel="1" />
    <ParameterLabel id="3" fKParameter="1" fKLabel="3" />
    <ParameterLabel id="4" fKParameter="3" fKLabel="1" />
    <ParameterLabel id="5" fKParameter="4" fKLabel="1" />
    <ParameterLabel id="6" fKParameter="4" fKLabel="1" />
    <ParameterLabel id="7" fKParameter="4" fKLabel="3" />
  </ParametersLabels>
</MappingTable>

enter image description here

This is understandable and readable.

var parameterName = (
	from label in currentMappingTable.Labels.Label
	join parameterLabel in currentMappingTable.ParametersLabels.ParameterLabel
		on label.Id equals parameterLabel.FKLabel
	join parameter in currentMappingTable.Parameters.Parameter
		on parameterLabel.FKParameter equals parameter.Id
	where label.Vendor == vendor && label.VendorLocation == vendorLocation
	select parameter.Value
	// select label.Vendor
).FirstOrDefault();


 //var query = currentMappingTable.Labels.Label.Join(currentMappingTable.ParametersLabels.ParameterLabel, label => label.Id, parameter => parameter.FKParameter,
 //            (label, parameter) => new { label, parameter }).Join(currentMappingTable.Parameters.Parameter, label => label.parameter.FKLabel).where

I'm trying to convert above query to method syntax. Without success. Are there simple rules for this? Or is this syntax not applied? What do you recommend? Maybe someone can solve it to see the difference. I should still be able to read this after 9 months. Maybe the original spelling is better after all.

What is the original spelling called?

That called 'Method syntax' isn't?

   var query = students.Join(departments, student => student.DepartmentID, department => department.ID,
                                 (student, department) => new { Name = $"{student.FirstName} {student.LastName}", DepartmentName = department.Name });

How should I read it? Maybe then it will be more understandable?

Is that good and right?

// Data save an read as XML or as JSON
string json = JsonConvert.SerializeObject(currentMappingTable, Newtonsoft.Json.Formatting.Indented);
//Write to a file
using (StreamWriter writer = new StreamWriter(currentMappingTableGoal))
{
	writer.WriteLine(json);
}
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,390 questions
0 comments No comments
{count} votes

Accepted answer
  1. Hongrui Yu-MSFT 415 Reputation points Microsoft Vendor
    2024-05-10T07:58:09.1366667+00:00

    Hi,@Noah Aas. Welcome to Microsoft Q&A. 

    1.Your data values are written in the attribute properties of the tag, and it is best to load the data in a primitive way

    
    public class Parameter
    
    {
    
        public string id { get; set; }
    
        public string name { get; set; }
    
    }
    
    
    
    public class Label
    
    {
    
        public string id { get; set; }
    
        public string vendor { get; set; }
    
        public string vendorLocation { get; set; }
    
    }
    
    
    
    public class ParametersLabels
    
    {
    
        public string id { get; set; }
    
        public string fKParameter { get; set; }
    
        public string fKLabel { get; set; }
    
    }
    
    
    
    public class ParametersLabels_Label_Parameter
    
    {
    
        public string id { get; set; }
    
        public string fKParameter { get; set; }
    
        public string fKLabel { get; set; }
    
        public string vendor { get; set; }
    
        public string vendorLocation { get; set; }
    
        public string name { get; set; }
    
    }
    
     
    
    public void GetData(string filePath)
    
    {
    
            //Load XML
    
            XmlDocument dom = new XmlDocument();
    
            dom.Load(filePath);
    
    
    
            //Read the data and save it in parameters、labels、parametersLabels
    
            XmlElement root = dom.DocumentElement;
    
            foreach (XmlNode node in root.ChildNodes[0].ChildNodes)
    
            {
    
                parameters.Add(new Parameter() { id = node.Attributes["id"].Value.ToString(), name = node.Attributes["value"].Value.ToString() });
    
            }
    
            foreach (XmlNode node in root.ChildNodes[1])
    
            {
    
                labels.Add(new Label() { id = node.Attributes["id"].Value.ToString(), vendor = node.Attributes["vendor"].Value.ToString(), vendorLocation = node.Attributes["vendorLocation"].Value.ToString() });
    
            }
    
            foreach (XmlNode node in root.ChildNodes[2])
    
            {
    
                parametersLabels.Add(new ParametersLabels() { id = node.Attributes["id"].Value.ToString(), fKParameter = node.Attributes["fKParameter"].Value.ToString(), fKLabel = node.Attributes["fKLabel"].Value.ToString() });
    
            }
    
    }
    
    

    2. The syntax you described is a Linq expression, please refer to Query expression basics (LINQ in C#) - C# | Microsoft Learn for more information about Linq expressions. It is recommended to use the Linq syntax instead of the Linq method.

    The following is the use of the Linq method

    
    public List<string> quert_Linq_method_string(string vendor, string vendorLocation)
    
    {
    
        var query = labels.Join(parametersLabels, label => label.id, parameterLabel => parameterLabel.fKLabel,
    
        (label, parameterLabel) => new { label, parameterLabel })
    
        .Join(parameters, temp1 => temp1.parameterLabel.fKParameter, parameter => parameter.id, (temp1, parameters) => new { temp1.label, parameters, temp1.parameterLabel })
    
        .Where(p => p.label.vendor.Equals(vendor) && p.label.vendorLocation.Equals(vendorLocation))
    
        .Select(p => p.parameters.name);
    
        return query.ToList<string>();
    
    }
    
    public List<ParametersLabels_Label_Parameter> quert_Linq_method(string vendor, string vendorLocation)
    
    {
    
        var query = labels.Join(parametersLabels, label => label.id, parameterLabel => parameterLabel.fKLabel, (label, parameterLabel) => new { label, parameterLabel })
    
        .Join(parameters, temp1 => temp1.parameterLabel.fKParameter, parameter => parameter.id, (temp1, parameters) => new { temp1.label, parameters, temp1.parameterLabel })
    
        .Where(p => p.label.vendor.Equals(vendor) && p.label.vendorLocation.Equals(vendorLocation))
    
        .Select(p =>
    
        new ParametersLabels_Label_Parameter()
    
        {
    
            id = p.parameterLabel.id,
    
            fKParameter = p.parameterLabel.fKParameter,
    
            fKLabel = p.parameterLabel.fKLabel,
    
            name = p.parameters.name,
    
            vendor = p.label.vendor,
    
            vendorLocation = p.label.vendorLocation
    
        }
    
        );
    
        return query.ToList<ParametersLabels_Label_Parameter>();
    
    }
     
    

    3.The query results are converted and stored using JsonConvert:

    
     public void SaveData(string path, string data)
    
    {
    
        FileInfo fi = new FileInfo(path);
    
        if (!fi.Exists)
    
        {
    
            using (StreamWriter sw = fi.CreateText())
    
            {
    
                sw.WriteLine(data);
    
            }
    
        }
    
    }
    
    

    4.      The full code of the console program is as follows(Read XML data + Linq multi-table query + JSON save data)

    
    using Newtonsoft.Json;
    
    using System.Net.Http.Json;
    
    using System.Numerics;
    
    using System.Reflection.Metadata;
    
    using System.Text.Json.Serialization;
    
    using System.Xml;
    
    using System.Xml.Linq;
    
     
    
    string filePath = Path.Combine(Environment.CurrentDirectory,"XMLFile1.xml")
    var pathBase = Environment.CurrentDirectory;
    
    //Get Data
    
    Class1 class1 = new Class1();
    
    class1.GetData(filePath);
    
    //Query
    
    var list = class1.quert_Linq_method("xxx3", "bbb3");
    
    var list_stringData = class1.quert_Linq_method_string("xxx3", "bbb3");
    
    //Convert to Json
    
    string json = JsonConvert.SerializeObject(list, Newtonsoft.Json.Formatting.Indented);
    
    string json_stringData = JsonConvert.SerializeObject(list_stringData, Newtonsoft.Json.Formatting.Indented);
    
     
    
    //Save Data
    
    class1.SaveData(Path.Combine(pathBase, "myText1.txt"),json);
    
    class1.SaveData(Path.Combine(pathBase, "myText2.txt"),json_stringData);
    
       
    
     
    
     
    
    public class Class1
    
    {
    
        public List<Parameter> parameters = new List<Parameter>();
    
        public List<Label> labels = new List<Label>();
    
        public List<ParametersLabels> parametersLabels = new List<ParametersLabels>();
    
        public void GetData(string filePath)
    
        {
    
            //Load XML
    
            XmlDocument dom = new XmlDocument();
    
            dom.Load(filePath);
    
     
    
            //Read the data and save it in parameters、labels、parametersLabels
    
            XmlElement root = dom.DocumentElement;
    
            foreach (XmlNode node in root.ChildNodes[0].ChildNodes)
    
            {
    
                parameters.Add(new Parameter() { id = node.Attributes["id"].Value.ToString(), name = node.Attributes["value"].Value.ToString() });
    
            }
    
            foreach (XmlNode node in root.ChildNodes[1])
    
            {
    
                labels.Add(new Label() { id = node.Attributes["id"].Value.ToString(), vendor = node.Attributes["vendor"].Value.ToString(), vendorLocation = node.Attributes["vendorLocation"].Value.ToString() });
    
            }
    
            foreach (XmlNode node in root.ChildNodes[2])
    
            {
    
                parametersLabels.Add(new ParametersLabels() { id = node.Attributes["id"].Value.ToString(), fKParameter = node.Attributes["fKParameter"].Value.ToString(), fKLabel = node.Attributes["fKLabel"].Value.ToString() });
    
            }
    
            Console.WriteLine();
    
        }
    
     
    
        public void SaveData(string path,string data)
    
        {
    
            FileInfo fi = new FileInfo(path);
    
            if(!fi.Exists)
    
            {
    
                using(StreamWriter sw = fi.CreateText())
    
                {
    
                    sw.WriteLine(data);
    
                }
    
            }
    
        }
    
     
    
        public List<string> quert_Linq_method_string(string vendor, string vendorLocation)
    
        {
    
            var query = labels.Join(parametersLabels, label => label.id, parameterLabel => parameterLabel.fKLabel,
    
                        (label, parameterLabel) => new { label, parameterLabel })
    
                        .Join(parameters, temp1 => temp1.parameterLabel.fKParameter, parameter => parameter.id, (temp1, parameters) => new { temp1.label, parameters, temp1.parameterLabel })
    
                        .Where(p => p.label.vendor.Equals(vendor) && p.label.vendorLocation.Equals(vendorLocation))
    
                        .Select(p => p.parameters.name);
    
            return query.ToList<string>();
    
        }
    
        public List<ParametersLabels_Label_Parameter> quert_Linq_method(string vendor, string vendorLocation)
    
        {
            var query = labels.Join(parametersLabels, label => label.id, parameterLabel => parameterLabel.fKLabel,(label, parameterLabel) => new { label, parameterLabel })
    
                        .Join(parameters, temp1 => temp1.parameterLabel.fKParameter, parameter => parameter.id, (temp1, parameters) => new { temp1.label, parameters, temp1.parameterLabel })
    
                        .Where(p => p.label.vendor.Equals(vendor) && p.label.vendorLocation.Equals(vendorLocation))
    
                        .Select(p =>
                               new ParametersLabels_Label_Parameter()
                                {
                                    id = p.parameterLabel.id,
                                    fKParameter = p.parameterLabel.fKParameter,
                                    fKLabel = p.parameterLabel.fKLabel,
                                    name = p.parameters.name,
                                    vendor = p.label.vendor,
                                    vendorLocation = p.label.vendorLocation
                                }
                        );
    
            return query.ToList<ParametersLabels_Label_Parameter>();
    
        }
    
     
    
    }
    
    public class Parameter
    
    {
    
        public string id { get; set; }
    
        public string name { get; set; }
    
    }
    
     
    
    public class Label
    
    {
    
        public string id { get; set; }
    
        public string vendor { get; set; }
    
        public string vendorLocation { get; set; }
    
    }
    
     
    
    public class ParametersLabels
    
    {
    
        public string id { get; set; }
    
        public string fKParameter { get; set; }
    
        public string fKLabel { get; set; }
    
    }
    
     
    
    public class ParametersLabels_Label_Parameter
    
    {
    
        public string id { get; set; }
    
        public string fKParameter { get; set; }
    
        public string fKLabel { get; set; }
    
        public string vendor { get; set; }
    
        public string vendorLocation { get; set; }
    
        public string name { get; set; }
    
    }
    

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.


    Comments have been turned off. Learn more