CODEWITHSUNDEEP
c#entity-frameworklinq
Eric Snyder
Eric Snyder

Reputation: 1924

Linq join - show all data in one table and supplement with data from two others

I can't imagine that this is a unique question, I apparently don't have the right knowledge to understand what to search for.

I have three tables. I want to show all data from the first table (ToolList) and fill in data from the second and third table:

ToolLists 
ToolLocation 
TemplateLocation

ToolLists has records for Tool A, Tool B and Tool C.

ToolLocation has a record for Tool A Location 123.

TemplateLocation has a record for Tool C Location 654

The list looks like this: Tool A, 123, - Tool B, -, - Tool C, -, 654

My Entity Framework Linq looks like this:

var test = (from t in db.ToolLists
    join l in db.ToolLocations on t.ToolNumber equals l.ToolNumber
    join temp in db.TemplateLocations on t.ToolNumber equals temp.ToolNumber

    orderby t.ToolNumber

    select new { ToolNumber = t.ToolNumber, ToolLocation = l.Location, TemplateLocation = temp.Location }
);

I get no results. I was getting results when I did not include the third table:

var test = (from t in db.SL24ToolLists
    join l in db.SL24ToolLocations on t.ToolNumber equals l.ToolNumber

    orderby t.ToolNumber

    select new { ToolNumber = t.ToolNumber, ToolLocation = l.Location }
);

Upvotes: 0

Views: 768

Answers (1)

Serge
Serge

Reputation: 43860

instead of inner joins try to make left joins


var test = (from t in db.ToolLists
    join l in db.ToolLocations on t.ToolNumber equals l.ToolNumber into lj
            from l in lj.DefaultIfEmpty()
    join temp in db.TemplateLocations on t.ToolNumber equals temp.ToolNumber into tempj
            from temp in tempj.DefaultIfEmpty()
    orderby t.ToolNumber
 select new { ToolNumber = t.ToolNumber, 
ToolLocation = l.Location, TemplateLocation = temp.Location }).ToList();

Upvotes: 1

Related Questions