This query returns 4 records (2x is IdStandard = 2) - I need 3 records with unique id and PrimaryCalcMethod and SecondaryCalcMethod methods. If the latter method is not present, the SecondaryCalcMethod property is to return null. How to change the linq query?
Below I am sending the result in Json - as a result you can see the IdStandard returned twice for the country France
[
{
"idStandard": 1,
"idCountryStandard": 2,
"idMeasureDiameterOption": 0,
"description": null,
"idMeasureDiameterMethod": 0,
"idWalkingLineRadius": 0,
"idCalcMethodType": 0,
"idWalkingLineMeasure": 0,
"standardName": "Szwecja TLC Standard",
"isActive": true,
"freeHeadSpace": 2100.0,
"isTlcStandard": true,
"minLandingSize": 800.0,
"minStepHeight": 160.0,
"maxStepHeight": 240.0,
"minConvinience": 590.0,
"maxConvinience": 660.0,
"maxStepDeep": null,
"minStepDeep": null,
"widthLimit": null,
"primaryCalcMethod": {
"calcMethodName": "PrimaryCalcMethod",
"walkingLineMeasure": 0,
"calcMethod": 0,
"firstWidth": 300.0,
"secondWidth": 0.0,
"idMeasureDiameterOption": 1,
"description": "od krawędzi zewnętrznej 300mm",
"idMeasureDiameterMethod": 1,
"idWalkingLineRadius": 1,
"idCalcMethodType": 1,
"idWalkingLineMeasure": 2
},
"secondaryCalcMethod": null,
"maxStepWidth": null,
"startOverlap": 10.0,
"endOverlap": 0.0,
"walkLineOverlap": 0.0,
"internalHandrailMaxRadius": 131.0,
"maxStepsAmount": 0,
"nameCountry": "Szwecja",
"calcMethodName": null,
"walkingLineMeasureName": null,
"calcMethodNameType": null,
"firstWidth": 0.0,
"secondWidth": 0.0
},
{
"idStandard": 2,
"idCountryStandard": 3,
"idMeasureDiameterOption": 0,
"description": null,
"idMeasureDiameterMethod": 0,
"idWalkingLineRadius": 0,
"idCalcMethodType": 0,
"idWalkingLineMeasure": 0,
"standardName": "Francja NF E85-15 ewak.",
"isActive": true,
"freeHeadSpace": 2000.0,
"isTlcStandard": false,
"minLandingSize": 800.0,
"minStepHeight": 160.0,
"maxStepHeight": 200.0,
"minConvinience": 660.0,
"maxConvinience": 600.0,
"maxStepDeep": 280.0,
"minStepDeep": 180.0,
"widthLimit": 900.0,
"primaryCalcMethod": {
"calcMethodName": "PrimaryCalcMethod",
"walkingLineMeasure": 0,
"calcMethod": 0,
"firstWidth": 0.7,
"secondWidth": 0.0,
"idMeasureDiameterOption": 2,
"description": "do 900mm szerokości biegu",
"idMeasureDiameterMethod": 1,
"idWalkingLineRadius": 3,
"idCalcMethodType": 2,
"idWalkingLineMeasure": 3
},
"secondaryCalcMethod": {
"calcMethodName": "PrimaryCalcMethod",
"walkingLineMeasure": 0,
"calcMethod": 0,
"firstWidth": 0.7,
"secondWidth": 0.0,
"idMeasureDiameterOption": 2,
"description": "do 900mm szerokości biegu",
"idMeasureDiameterMethod": 1,
"idWalkingLineRadius": 3,
"idCalcMethodType": 2,
"idWalkingLineMeasure": 3
},
"maxStepWidth": 420.0,
"startOverlap": 80.0,
"endOverlap": 0.0,
"walkLineOverlap": 0.0,
"internalHandrailMaxRadius": 100.0,
"maxStepsAmount": 0,
"nameCountry": "Francja",
"calcMethodName": null,
"walkingLineMeasureName": null,
"calcMethodNameType": null,
"firstWidth": 0.0,
"secondWidth": 0.0
},
{
"idStandard": 2,
"idCountryStandard": 3,
"idMeasureDiameterOption": 0,
"description": null,
"idMeasureDiameterMethod": 0,
"idWalkingLineRadius": 0,
"idCalcMethodType": 0,
"idWalkingLineMeasure": 0,
"standardName": "Francja NF E85-15 ewak.",
"isActive": true,
"freeHeadSpace": 2000.0,
"isTlcStandard": false,
"minLandingSize": 800.0,
"minStepHeight": 160.0,
"maxStepHeight": 200.0,
"minConvinience": 660.0,
"maxConvinience": 600.0,
"maxStepDeep": 280.0,
"minStepDeep": 180.0,
"widthLimit": 900.0,
"primaryCalcMethod": {
"calcMethodName": "SecondaryCalcMethod",
"walkingLineMeasure": 0,
"calcMethod": 0,
"firstWidth": 600.0,
"secondWidth": 0.0,
"idMeasureDiameterOption": 3,
"description": "powyżej 900mm",
"idMeasureDiameterMethod": 2,
"idWalkingLineRadius": 2,
"idCalcMethodType": 1,
"idWalkingLineMeasure": 3
},
"secondaryCalcMethod": {
"calcMethodName": "SecondaryCalcMethod",
"walkingLineMeasure": 0,
"calcMethod": 0,
"firstWidth": 600.0,
"secondWidth": 0.0,
"idMeasureDiameterOption": 3,
"description": "powyżej 900mm",
"idMeasureDiameterMethod": 2,
"idWalkingLineRadius": 2,
"idCalcMethodType": 1,
"idWalkingLineMeasure": 3
},
"maxStepWidth": 420.0,
"startOverlap": 80.0,
"endOverlap": 0.0,
"walkLineOverlap": 0.0,
"internalHandrailMaxRadius": 100.0,
"maxStepsAmount": 0,
"nameCountry": "Francja",
"calcMethodName": null,
"walkingLineMeasureName": null,
"calcMethodNameType": null,
"firstWidth": 0.0,
"secondWidth": 0.0
},
{
"idStandard": 3,
"idCountryStandard": 5,
"idMeasureDiameterOption": 0,
"description": null,
"idMeasureDiameterMethod": 0,
"idWalkingLineRadius": 0,
"idCalcMethodType": 0,
"idWalkingLineMeasure": 0,
"standardName": "Norwegia TEK 17",
"isActive": true,
"freeHeadSpace": 2100.0,
"isTlcStandard": true,
"minLandingSize": 800.0,
"minStepHeight": 160.0,
"maxStepHeight": 210.0,
"minConvinience": 590.0,
"maxConvinience": 650.0,
"maxStepDeep": 280.0,
"minStepDeep": 160.0,
"widthLimit": null,
"primaryCalcMethod": {
"calcMethodName": "PrimaryCalcMethod",
"walkingLineMeasure": 0,
"calcMethod": 0,
"firstWidth": 400.0,
"secondWidth": 0.0,
"idMeasureDiameterOption": 4,
"description": "400 mm od pochwytu",
"idMeasureDiameterMethod": 1,
"idWalkingLineRadius": 4,
"idCalcMethodType": 1,
"idWalkingLineMeasure": 1
},
"secondaryCalcMethod": null,
"maxStepWidth": null,
"startOverlap": 10.0,
"endOverlap": 0.0,
"walkLineOverlap": 0.0,
"internalHandrailMaxRadius": null,
"maxStepsAmount": 0,
"nameCountry": "Norwegia",
"calcMethodName": null,
"walkingLineMeasureName": null,
"calcMethodNameType": null,
"firstWidth": 0.0,
"secondWidth": 0.0
}
]
Below, connections from the database in SQL between arrays
What I have tried:
public List<StandardEnt> ListStandardsWithMethodList()
{
var listModel =
(
from dane in _dbContext.Standard
join kraj in _dbContext.CountryStandard
on dane.IdCountryStandard equals kraj.IdCountryStandard
join opcja in _dbContext.MeasureDiameterOption
on dane.IdStandard equals opcja.IdStandard
join metoda in _dbContext.MeasureDiameterMethod
on opcja.IdMeasureDiameterMethod equals metoda.IdMeasureDiameterMethod
join sposob in _dbContext.WalkingLineRadius
on opcja.IdWalkingLineRadius equals sposob.IdWalkingLineRadius
join linia in _dbContext.CalcMethodType
on sposob.IdCalcMethodType equals linia.IdCalcMethodType
join wyznaczanie in _dbContext.WalkingLineMeasure
on sposob.IdWalkingLineMeasure equals wyznaczanie.IdWalkingLineMeasure
select new StandardEnt()
{
IdStandard = dane.IdStandard,
IdCountryStandard = dane.IdCountryStandard,
StandardName = dane.StandardName,
IsActive = dane.IsActive,
NameCountry = kraj.NameCountryStandard,
FreeHeadSpace = (double)dane.FreeHeadSpace,
IsTlcStandard = dane.IsTlcStandard,
MinLandingSize = (double)dane.MinLandingSize,
MinStepHeight = (double)dane.MinStepHeight,
MaxStepHeight = (double)dane.MaxStepHeight,
MinConvinience = (double)dane.MinConvinience,
MaxConvinience = (double)dane.MaxConvinience,
MaxStepDeep = (double)dane.MaxStepDeep,
MinStepDeep = (double)dane.MinStepDeep,
MaxStepWidth = (double)dane.MaxStepWidth,
WidthLimit = (double)dane.WidthLimit,
StartOverlap = (double)dane.StartOverlap,
InternalHandrailMaxRadius = (double)dane.InternalHandrailMaxRadius,
PrimaryCalcMethod = (from metody in _dbContext.MeasureDiameterOption
where dane.IdStandard == metody.IdStandard && metody.MeasureDiameterMethod.CalcMethodName == "PrimaryCalcMethod"
select new MeasureDiameterMethodEnt()
{
IdMeasureDiameterOption = opcja.IdMeasureDiameterOption,
Description = opcja.Description,
IdMeasureDiameterMethod = metoda.IdMeasureDiameterMethod,
IdWalkingLineRadius = sposob.IdWalkingLineRadius,
IdCalcMethodType = linia.IdCalcMethodType,
IdWalkingLineMeasure = wyznaczanie.IdWalkingLineMeasure,
CalcMethodName = metoda.CalcMethodName,
FirstWidth = (double)sposob.FirstWidth,
SecondWidth = (double)sposob.SecondWidth
}).FirstOrDefault(),
SecondaryCalcMethod = (from metody in _dbContext.MeasureDiameterOption
where dane.IdStandard == metody.IdStandard && metody.MeasureDiameterMethod.CalcMethodName == "SecondaryCalcMethod"
select new MeasureDiameterMethodEnt()
{
IdMeasureDiameterOption = opcja.IdMeasureDiameterOption,
Description = opcja.Description,
IdMeasureDiameterMethod = metoda.IdMeasureDiameterMethod,
IdWalkingLineRadius = sposob.IdWalkingLineRadius,
IdCalcMethodType = linia.IdCalcMethodType,
IdWalkingLineMeasure = wyznaczanie.IdWalkingLineMeasure,
CalcMethodName = metoda.CalcMethodName,
FirstWidth = (double)sposob.FirstWidth,
SecondWidth = (double)sposob.SecondWidth
}).FirstOrDefault()
}
).ToList();
return listModel;
}
SELECT Standard.IdStandard, Standard.StandardName, Standard.IsActive, Standard.FreeHeadSpace, Standard.IsTlcStandard, Standard.MinLandingSize, Standard.MinStepHeight, Standard.MaxStepHeight,
Standard.MinConvinience, Standard.MaxConvinience, Standard.MaxStepDeep, Standard.MinStepDeep, Standard.WidthLimit, Standard.MaxStepWidth, Standard.StartOverlap, Standard.EndOverlap, Standard.WalkLineOverlap,
Standard.InternalHandrailMaxRadius, Standard.MaxStepsAmount, Standard.IdCountryStandard, CalcMethodType.CalcMethodNameType, WalkingLineRadius.FirstWidth, WalkingLineRadius.SecondWidth,
MeasureDiameterOption.Description, Standard_1.IdStandard AS Expr1, Standard_1.StandardName AS Expr2, Standard_1.FreeHeadSpace AS Expr3, Standard_1.IsTlcStandard AS Expr4, Standard_1.MinLandingSize AS Expr5,
Standard_1.MinStepHeight AS Expr6, Standard_1.MaxStepHeight AS Expr7, Standard_1.MinConvinience AS Expr8, Standard_1.MaxConvinience AS Expr9, Standard_1.MaxStepDeep AS Expr10,
Standard_1.MinStepDeep AS Expr11, Standard_1.WidthLimit AS Expr12, Standard_1.MaxStepWidth AS Expr13, Standard_1.StartOverlap AS Expr14, Standard_1.EndOverlap AS Expr15, Standard_1.WalkLineOverlap AS Expr16,
Standard_1.InternalHandrailMaxRadius AS Expr17, Standard_1.MaxStepsAmount AS Expr18, Standard_1.IdCountryStandard AS Expr19, WalkingLineMeasure.WalkingLineMeasureName,
MeasureDiameterMethod.CalcMethodName, CountryStandard.NameCountryStandard, WalkingLineMeasure_1.WalkingLineMeasureName AS Expr20
FROM Standard INNER JOIN
CountryStandard ON Standard.IdCountryStandard = CountryStandard.IdCountryStandard INNER JOIN
MeasureDiameterOption ON Standard.IdStandard = MeasureDiameterOption.IdStandard INNER JOIN
MeasureDiameterMethod ON MeasureDiameterOption.IdMeasureDiameterMethod = MeasureDiameterMethod.IdMeasureDiameterMethod INNER JOIN
Standard AS Standard_1 ON CountryStandard.IdCountryStandard = Standard_1.IdCountryStandard AND MeasureDiameterOption.IdStandard = Standard_1.IdStandard INNER JOIN
WalkingLineRadius ON MeasureDiameterOption.IdWalkingLineRadius = WalkingLineRadius.IdWalkingLineRadius INNER JOIN
CalcMethodType ON WalkingLineRadius.IdCalcMethodType = CalcMethodType.IdCalcMethodType INNER JOIN
WalkingLineMeasure ON WalkingLineRadius.IdWalkingLineMeasure = WalkingLineMeasure.IdWalkingLineMeasure INNER JOIN
WalkingLineMeasure AS WalkingLineMeasure_1 ON WalkingLineRadius.IdWalkingLineMeasure = WalkingLineMeasure_1.IdWalkingLineMeasure
Submit an article or tip