how to join table1 - table2 and table1 - table3 and return DTO variable

Question

In my Web API project I have this query which joins 2 table (Table1 and Table2) and return a DTO variable:

int my_id=1;
var sample = db.Table1.Where(s1 => s1.Id == my_id)
                      .Join(db.Table2, s1 => s1.Id, s2 => s2.id_s1,
                       (s1, s2) => new sampleDTO()
                       {
                             User_Id = s1.User.Id,
                             ProdId = s2.ProdId
                       }));

Now I would like to join also Table3 which joins with Table1 and add parameters to existing "sample" variable.

I have tried to add a Join clause after the code above but doesn't work, so I have tried to create a new DTO variable with Join clause with Table1 and Table3 and than merge the 2 variables but doesn't work.

Any ideas? Thanks.

Using linq syntax is much easier when working with multiple joins. — user1620220, Oct 07 '16 at 12:08
Possible duplicate of [Multiple table join using lambda/linq c# with DTO](http://stackoverflow.com/questions/33269836/multiple-table-join-using-lambda-linq-c-sharp-with-dto) — Priya, Oct 07 '16 at 12:09
@Cyr - for next time please also add the code that you say that doesn't work so we can help you fix it. In this case I believe that what I suggested is better that using multiple `joins` in method syntax but still — Gilad Green, Oct 07 '16 at 12:11

score 2 · Answer 1 · edited Sep 29 '17 at 17:22

Using the Method Syntax you will have to project a new object. However if you switch to the query syntax:

var result = from s1 in db.Table1
             where s1.Id == my_id
             join s2 in db.Table2 on s1.Id equals s2.id_s1
             join s3 in db.Table3 on s2.SomeProperty equals s3.SomeProperty
             select new sampleDTO
             {
                  User_Id = s1.User.Id,
                  ProdId = s2.ProdId,
                  //PropertyFromTable3 = s3.SomePropertyFromTable3
             };

If you are working with some ORM like Entity Framework look into navigation properties - will save all the trouble of joins all together

score 0 · Answer 2 · answered Oct 07 '16 at 12:15

Try something like this:

int my_id=1;

var sample = (from s1 in db.Table1
            join s2 in db.Table2
            on s1.id equals(s2.id)
            join s3 in db.Table3
            on s2.id equals(s3.id)
            .
            .
            .
            into aux from subquery in aux.DefaultIfEmpty()
            where s1.Id == my_id                      
            select new sampleDTO
            {
                User_Id = s1.User.Id,
                ProdId = s2.ProdId
            }).ToList<sampleDTO>();

how to join table1 - table2 and table1 - table3 and return DTO variable

2 Answers2