[Solved] Linq join query returning less records than Sql Query in ASP.Net Core MVC

Last Reply 26 days ago By dharmendr

Posted one month ago

I am working in Asp.Net Core MVC project with MYSQL database.

Issue: LINQ query does not return the correct number of rows compared to MYSQL query. 

Repository Class Code:

public IEnumerable<MappingCost> GetAllMappingCost()
{
    return context.MappingCost;
}

public IEnumerable<HdrPearlProject> GetProjectDDL()
{
    return context.HdrPearlProject;
}

public IEnumerable<HdrFdpScenarios> GetScenarioDDL()
{
    return context.HdrFdpScenarios;
}

Controller LINQ Query:

             var data = (from mc in _repository.GetAllMappingCost().ToList()
			join pp in _repository.GetProjectDDL().ToList() on new { mc.ProjectId } equals new { pp.ProjectId }
			join hfs in _repository.GetScenarioDDL().ToList() on new { mc.StudyId, mc.ScenarioId } equals new { hfs.StudyId, hfs.ScenarioId }
			select new
			{
				mc.MappingCapexId,
				mc.MappingId,
				hfs.StudyId,
				hfs.StudyName,
				hfs.ScenarioId,
				hfs.CombinedName,
				mc.FdpCategory,
				mc.FdpProduct,
				mc.Allocation,
				pp.ProjectId,
				pp.ProjectName,
				mc.PearlCosttype,
				mc.Parent1,
				mc.Parent2,
				mc.Parent3,
				mc.Item
			}).ToList();

Above Linq query returns a total of 18 rows

Whereas, on running below query in MYSQL workbench, I get total of 81 rows.

SELECT *
FROM mapping_cost mc, hdr_fdp_scenarios hsc, hdr_pearl_project hpp
WHERE mc.study_id = hsc.study_id and mc.scenario_id = hsc.scenario_id AND mc.project_id = hpp.project_id;

OR

SELECT *
FROM mapping_cost mc
inner join hdr_fdp_scenarios hsc on mc.study_id = hsc.study_id
inner join hdr_pearl_project hpp on mc.scenario_id = hsc.scenario_id AND mc.project_id = hpp.project_id;

NOTE:

1. Mappingcost table has total of 121 rows (in which some "study_id" and "scenario_id" column data is null) ---- Controller screen table

2. HdrPearlProject table has total of 41 rows ---- master/parent table

3. HdrFdpScenarios table has total of 125 rows ---- master/parent table.

I am first time facing this issue and after checking several times, I don't understand why the LINQ count doesn't match with MYSQL query count.

Please let me know what is wrong in my LINQ query.

Thank you in advance.

You are viewing reply posted by: dharmendr 26 days ago.
Posted 26 days ago

Use below query and check. This will resolve your issue.

var data = (from pp in _repository.GetProjectDDL().ToList()
            join mc in _repository.GetAllMappingCost().ToList() on pp.project_id.ToString().ToUpper() equals mc.project_id.ToString().ToUpper()
            join hfs in _repository.GetScenarioDDL().ToList() on new { mc.study_id, mc.scenario_id } equals new { hfs.study_id, hfs.scenario_id }
            select new
            {
                mc.mapping_capex_id,
                mc.mapping_id,
                hfs.study_id,
                hfs.study_name,
                hfs.scenario_id,
                hfs.combined_name,
                mc.fdp_category,
                mc.fdp_product,
                mc.allocation,
                pp.project_id,
                pp.project_name,
                mc.pearl_costtype,
                mc.parent1,
                mc.parent2,
                mc.parent3,
                mc.item
            }).ToList();