LinQ query m:n relation

Noah Aas 240 Reputation points
2024-04-25T17:41:11.5066667+00:00

Hello, My input is string vendor = "xxx3"; string vendorLocation = "bbb3";

The result should be which parameter is the right one.

<Label id="3" vendor="xxx3" vendorLocation="bbb3" />
<ParameterLabel id="3" fKParameter = "1" fKLabel = "3"/>
<Parameter id="1" value = "Para1"/>

It works, I think it can be solved better and ask for a better solution. I need your expertise. I guess maybe with join. How do I go about it?

Input should be vendor and vendorLocation Output --> Name of Parameter I have the following data model.

<MappingTable>
  <Parameters>     is a
    <Parameter id="1" value = "Para1"/>
    <Parameter id="2" value = "Para2"/>
    <Parameter id="3" value = "Para3"/>
    <Parameter id="4" value = "Para4"/>
    <Parameter id="5" value = "Para5"/>
  </Parameters>
  <Labels>         is b
    <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>    is c
    <ParameterLabel id="1" fKParameter = "1" 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>

// *** CLASSES

[XmlRoot(ElementName = "Parameter")]
public class Parameter
{

	[XmlAttribute(AttributeName = "id")]
	public int Id { get; set; }

	[XmlAttribute(AttributeName = "value")]
	public string Value { get; set; }
}

[XmlRoot(ElementName = "Parameters")]
public class Parameters
{

	[XmlElement(ElementName = "Parameter")]
	public List<Parameter> Parameter { get; set; }
}

[XmlRoot(ElementName = "Label")]
public class Label
{

	[XmlAttribute(AttributeName = "id")]
	public int Id { get; set; }

	[XmlAttribute(AttributeName = "vendor")]
	public string Vendor { get; set; }

	[XmlAttribute(AttributeName = "vendorLocation")]
	public string VendorLocation { get; set; }
}

[XmlRoot(ElementName = "Labels")]
public class Labels
{

	[XmlElement(ElementName = "Label")]
	public List<Label> Label { get; set; }
}

[XmlRoot(ElementName = "ParameterLabel")]
public class ParameterLabel
{

	[XmlAttribute(AttributeName = "id")]
	public int Id { get; set; }

	[XmlAttribute(AttributeName = "fKParameter")]
	public int FKParameter { get; set; }

	[XmlAttribute(AttributeName = "fKLabel")]
	public int FKLabel { get; set; }
}

[XmlRoot(ElementName = "ParametersLabels")]
public class ParametersLabels
{

	[XmlElement(ElementName = "ParameterLabel")]
	public List<ParameterLabel> ParameterLabel { get; set; }
}

[XmlRoot(ElementName = "MappingTable")]
public class MappingTable
{

	[XmlElement(ElementName = "Parameters")]
	public Parameters Parameters { get; set; }

	[XmlElement(ElementName = "Labels")]
	public Labels Labels { get; set; }

	[XmlElement(ElementName = "ParametersLabels")]
	public ParametersLabels ParametersLabels { get; set; }
}

Code:

private void btnTestRead_Click(object sender, EventArgs e)
{
	MappingTable currentMappingTable = new MappingTable();

	string file2 = @"C:\Users\noah\source\repos\database\MappingTable.XML";
	file2 = "..\\..\\MappingTable.XML";

	currentMappingTable = currentMappingTable.FromXMLFile(file2);

	Parameters currentParameters;
	Parameter currentParameter;

	Labels currentLabels;
	Label currentLabel;

	ParametersLabels currentParametersLabels;
	ParameterLabel currentParameterLabel;

	string vendor = "xxx3";
	string vendorLocation = "bbb3";

	int? pkIdLabel, pkidParameter;
	currentLabel = currentMappingTable.Labels.Label.Where(x => x.Vendor == vendor && x.VendorLocation == vendorLocation).FirstOrDefault();
	if (currentLabel != null)
	{
		pkIdLabel = currentLabel.Id;

		currentParameterLabel = currentMappingTable.ParametersLabels.ParameterLabel.Where(x => x.FKLabel == pkIdLabel).FirstOrDefault();
		if (currentParameterLabel != null)
		{
			pkidParameter = currentParameterLabel.FKParameter;

			if (pkidParameter != null)
			{
				currentParameter = currentMappingTable.Parameters.Parameter.Where(x => x.Id == pkidParameter).FirstOrDefault();

				if (currentParameter != null)
				{
					MessageBox.Show($"Founded= '{currentParameter.Value}'", "This parameter is allowed", MessageBoxButtons.OK);
				}
			}
			else
				MessageBox.Show($"Not found C", "Info", MessageBoxButtons.OK, MessageBoxIcon.Error);
		}
		else
			MessageBox.Show($"Not found B", "Info", MessageBoxButtons.OK, MessageBoxIcon.Error);
	}
	else
		MessageBox.Show($"Not found A", "Info", MessageBoxButtons.OK, MessageBoxIcon.Error);		
	

Other spelling with => goto operator

var res4 = from b in currentMappingTable.Labels.Label
where (b.Vendor == vendor && b.VendorLocation == vendorLocation)
join c in currentMappingTable.ParametersLabels.ParameterLabel on b.Id equals c.FKLabel into result1
from item in result1
join a in currentMappingTable.Parameters.Parameter on item.FKParameter equals a.Id
select new
{
   a.Value,
   b.Vendor,
   b.VendorLocation
};
var qryParameter =  res4.ToList().FirstOrDefault();

How can I implement the query with LinQ and would I have to write it? What do you say to a, b, c Here I can take what I want. Operator perhaps?

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,356 questions
0 comments No comments
{count} votes

Accepted answer
  1. Jiale Xue - MSFT 34,671 Reputation points Microsoft Vendor
    2024-04-26T05:28:50.84+00:00

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

    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
    ).FirstOrDefault();
    
    if (parameterName != null)
    {
        MessageBox.Show($"Parameter Name: '{parameterName}'", "Parameter Found", MessageBoxButtons.OK);
    }
    else
    {
        MessageBox.Show("Parameter not found!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    

    a, b, c are range variables in a LINQ query. They are used internally within LINQ queries to represent different data sources or elements of query results in a query.

    In the LINQ query above, a is for Parameters, b is for Labels, and c is for ParametersLabels.

    You don't need to use the goto operator. In fact, goto should be avoided as much as possible, as it can make the code difficult to understand and maintain. In your code, you can use an if-else statement instead of goto.

    Best Regards,

    Jiale


    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.

0 additional answers

Sort by: Most helpful