LINQ join query with multiple fields using left outer join

Question

I have Product table and Officer table as follow:

Product

ProductID | ProductName | Officer1ID | Officer2ID | Officer3ID
--------- | ----------- | ---------- | ---------- | ----------
12        | Mouse       | 123        | 124        | 125
13        | Keyboard    | 234        | 235        | 0

Officer

OfficerID | OfficerName 
--------- | ----------- 
123       | John       
124       | Andy    
125       | Mark

I need to join 3 columns (Officer1ID, Officer2ID, Officer3ID) from Product table with OfficerID in Officer table to produce result like this:

ProductID | ProductName | Officer1Name | Officer2Name | Officer3Name
--------- | ----------- | ------------ | ------------ | ------------
12        | Mouse       | John         | Andy         | Mark
13        | Keyboard    | Dave         | Fred         | Leon

This is my attempt. I know how to join 1 field, but not multiple. Can anyone help? Thanks!

List<Product> lstProduct = GetProducts();

List<Officer> lstOfficer = GetOfficers();

var merge = from p in lstProduct
   join from o in lstOfficers on p.Officer1ID equals o.OfficerID
   select new { ProductID = p.ProductID, ProductName = p.ProductName, OfficerName = o.OfficerName };

EDIT
OfficerIDs in Product table could be 0(not exist in Officer table).

Columns called Foo1, Foo2, etc... are generally a sign that your data model is wrong. — Mark Byers
– Mark Byers, Commented Oct 22, 2010 at 8:00
@mark byers: some kind of relation-table should be supported instead (just to complete your comment) — user57508
– user57508, Commented Oct 22, 2010 at 8:01
Our client has various products. Different product will have different product officer, finance officer project officer assigned to take care of the product. This is the requirement. — bla
– bla, Commented Oct 22, 2010 at 8:12

Yakimych · Accepted Answer · 2010-10-22 08:43:35Z

4

Just apply the join 3 times (once for each OfficerID):

var merge = from p in lstProduct
            join o1 in lstOfficer on p.Officer1ID equals o1.OfficerID
            join o2 in lstOfficer on p.Officer2ID equals o2.OfficerID
            join o3 in lstOfficer on p.Officer3ID equals o3.OfficerID
            select new
                     {
                         ProductID = p.ProductID,
                         ProductName = p.ProductName,
                         Officer1Name = o1.OfficerName,
                         Officer2Name = o2.OfficerName,
                         Officer3Name = o3.OfficerName
                     };

answered Oct 22, 2010 at 8:43

Yakimych

17.8k7 gold badges55 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bla Over a year ago

Thanks for the answer. This is quite close to what I am looking for. I forgot to mention that some OfficerIDs in Product table are null/empty.

Community · Accepted Answer · 2017-05-23 11:47:32Z

3

You can do this using multiple joins.

You should reconsider your data model. I suggest using junction table to make many to many relationship:

Product

ProductID | ProductName | ProductOfficiersID
--------- | ----------- | ---------- 
12        | Mouse       | 1        
13        | Keyboard    | 2

ProductOfficiers

ProductOfficiersID | ProductID | OficierId 
------------------ | --------- | -----------
1                  | 12        | 123     
1                  | 12        | 124  
1                  | 12        | 125
2                  | 13        | 234
...

Officer ...

edited May 23, 2017 at 11:47

CommunityBot

11 silver badge

answered Oct 22, 2010 at 8:44

Branimir

4,4171 gold badge24 silver badges34 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 09:58:07Z

0

I ended up using subquery, inspired from this thread.

var merge = from p in lstProduct
            select new
            {
                p.ProductID,
                p.ProductName,
                Officer1Name = (from o in lstOfficer
                               where o.OfficerID == p.Officer1ID
                               select o.OfficerName).FirstOrDefault(),
                Officer1Name = (from o in lstOfficer
                               where o.OfficerID == p.Officer2ID
                               select o.OfficerName).FirstOrDefault(),
                Officer2Name = (from o in lstOfficer
                               where o.OfficerID == p.Officer3ID
                               select o.OfficerName).FirstOrDefault()
            };

Thanks guys for your help!

edited May 23, 2017 at 9:58

CommunityBot

11 silver badge

answered Oct 25, 2010 at 6:06

bla

5,4703 gold badges27 silver badges26 bronze badges

Collectives™ on Stack Overflow

LINQ join query with multiple fields using left outer join

Product

Officer

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Product

Officer

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related