left join failing in query with multiple joins

Question

Before this gets called a duplicate, I have looked around on SO and found a way to do this but it isn't working.

My query is

 var GetAllProjects = from f in dc.vw_gmi_all_projects
 join mc in dc.gmi_maintenance_classes on f.maintenance_classID equals mc.maintenance_classID
 join ms in dc.gmi_maintenance_subclasses on f.maintenance_subclassID equals ms.maintenance_subclassID
 join pm in dc.master_project_milestones on f.pmID equals pm.pmID
 join ac in dc.vw_master_Countries on f.country_display_name equals ac.country_display_name
 join pd in dc.gmi_project_details on f.project_dataID equals pd.project_dataID
 join md in dc.vw2_master_districts on f.country_display_name equals md.element_display_name
 join ml in dc.vw2_master_lmus on pd.dataID equals ml.elementID into gl from sub in gl.DefaultIfEmpty()
 where (mc.maintenance_classID == 3 && ms.maintenance_subclassID != 11)
 select new
 {
     f.project_dataID,
     f.projectID,
     f.project_title,
     f.local_projectID,
     f.pm_display_name,
     f.reu_name,
     f.reuID,
     f.sectorID,
     f.sector_display_name,
     f.country_display_name,
     f.maintenance_classID,
     f.maintenance_subclassID,
     mc.maintenance_class_display_name,
     ms.maintenance_subclass_display_name,
     pm.pm_name,
     ac.region_display_name,
     pd.dataID,
     md.element_display_name,
     ac.cluster_display_name,
     display_name = sub.element_display_name
 };

foreach (var a in GetAllProjects)
{
lst.Add(new ReportFilter
{
    project_dataID = (int)a.project_dataID,
    projectID = a.projectID,
    project_title = a.project_title,
    local_projectID = a.local_projectID,
    pm_display_name = a.pm_display_name,
    reu_name = a.reu_name,
    reuID = a.reuID,
    country_display_name = a.country_display_name,
    sectorID = a.sectorID,
    sector_display_name = a.sector_display_name,
    maintenance_classID = a.maintenance_classID,
    maintenance_subclassID = a.maintenance_subclassID,
    maintenance_class_display_name = a.maintenance_class_display_name,
    maintenance_subclass_display = a.maintenance_subclass_display_name,
    pm_name = a.pm_name,
    region_display_name = a.region_display_name,
    dataID = a.dataID,
    district = a.element_display_name,
    cluster_display_name = a.cluster_display_name
});
}

This is where the left join is supposed to take place..

 join ml in dc.vw2_master_lmus on pd.dataID equals ml.elementID into gl from sub in gl.DefaultIfEmpty()

This query runs fine if I leave out the attempt at left join and leave that join out entirely, but I need to get the left join to work so I can get the rest of the records. A typical join won't work like the others because it doesn't return any records.

So where am I going wrong with my query, or doing wrong? I know where its going wrong just not sure how to fix it.

Thanks

More Details This query creates a list and then I query against this list. However, this query won't return any records because of my attempt at creating a left join.

EDIT Here is the SQL that I wrote and trying to recreate it using Linq

select * from [vw_gmi_all_projects] f
inner join [gmi_maintenance_classes] mc on f.maintenance_classID = mc.maintenance_classID
inner join [gmi_maintenance_subclasses] ms on f.maintenance_subclassID =   ms.maintenance_subclassID
inner join [master_project_milestones] pm on f.pmID = pm.pmID
inner join [vw_master_Countries] ac on f.country_display_name = ac.country_display_name
inner join [gmi_project_details] pd on f.project_dataID = pd.project_dataID
inner join [vw2_master_district] md on f.country_display_name = md.element_display_name
left join [vw2_master_lmu] ml on pd.dataID = ml.elementID 
where (mc.maintenance_classID = 3 and ms.maintenance_subclassID != 11)

I hope this helps.

Please, provide more details. That piece of code is not enough... — Maciej Los, Jan 05 '15 at 07:53
@EhsanSajjad, the issue is coming where I pointed out my attempt at creating a left join on dc.vw2_master_lmus. Since I need that table to be the only left join, I'm stuck. Without it my queries against the list works perfectly fine. Until I try to left join that table. — Chris, Jan 05 '15 at 08:02
I just double checked and this query only returns 3 records and there well over 200 records. If I write this query using SQL then it works. — Chris, Jan 05 '15 at 08:05
@EhsanSajjad, I think the where clause is fine, unless you see something that I am not. I added the SQL statement that I wrote which is the one I am trying to recreate using Linq — Chris, Jan 05 '15 at 23:04
Your C#: `join ml in dc.vw2_master_lmus`; your SQL: `left join [vw2_master_lmu]`. Is the former supposed to be plural? — Chrsjkigs99, Jan 05 '15 at 23:14
@raney, Yes that is pluralized. I used Linq to Sql to create the classes. I originally wrote it in SQL but moved everything over to L2S. — Chris, Jan 05 '15 at 23:16
@InvernoMuto, the SQL returns 28 records. The Linq returns nothing — Chris, Jan 05 '15 at 23:22
sorry, I mean can you profile and add the trace against sql server of your c# query? — ale, Jan 05 '15 at 23:23

score 0 · Accepted Answer · edited May 23 '17 at 11:57

0

Try the syntax from this answer instead: https://stackoverflow.com/a/4739738/1869660
I think that's an easier syntax when creating LEFT JOINs (note: from, not join).

var GetAllProjects = from f in dc.vw_gmi_all_projects
        join mc in dc...
        join ms in dc...
        join pm in dc...
        join ac in dc...
        join pd in dc...
        join md in dc...

        from ml in dc.vw2_master_lmus.Where(ml => ml.elementID == pd.dataID).DefaultIfEmpty()

        where ...

edited May 23 '17 at 11:57

Community

1
1

answered Jan 05 '15 at 23:45

Sphinxxx

12,484
4
54
84

I'm going to give it a try right now, I just looked over it and I took note of the from not join. I'll let you know how it goes. – Chris Jan 05 '15 at 23:58
It worked! I started looking at the records returned from the SQL and the Linq query, the records weren't adding up. So there is more problems with my query than I thought. I got the general idea on what I need to fix. But one thing that I have noticed with this part of query 'join pm in dc.master_project_milestones on f.pmID equals pm.pmID' it looks backwards, shouldnt it be pm.pmID equal f.pmID?when i try to change it to that way i get an error saying something about f not being in scope. – Chris Jan 06 '15 at 00:28
1

In a `join` expression, the previously selected table (`f`) needs to be to the left of `equals`, and the newly joined table (`pm`) needs to be to the right of equals (or else you get a scope error). But even if you were allowed to swap them, the end result would be the same (as you're only comparing to IDs), so any problems with your query must be something else. – Sphinxxx Jan 06 '15 at 13:14
1

To debug further, you may want to see the SQL that Linq2Sql generates. When you create your DataContext, add a log: `dc.Log = new System.IO.StringWriter();`. Then after you have run your query (in this case, *after the foreach loop*): `string sql = dc.Log.ToString()` – Sphinxxx Jan 06 '15 at 13:42

left join failing in query with multiple joins

1 Answers1