Procedure for a m:n data relationship

Question

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>

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);
}

Answer 1

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.