LinQ query m:n relation

Question

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

The result should be which parameter is the right one.

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.

// *** 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 { 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 { 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 { 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
oah\source
epos\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?

Accepted Answer

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.

LinQ query m:n relation

0 additional answers