Select data from multiple table with Entity Framework

Question

I am able to select data from two tables, But I am getting partial result only while retrieving data from three tables.

var items = from orders in nobleappDbContext.JobOrders.Where(m => m.OrderStat == 0)
            join users in nobleappDbContext.users on orders.Uid equals users.Uid
            join customers in nobleappDbContext.customers on users.Uid equals customers.Uid into customdata
            from m in customdata.DefaultIfEmpty()
            select new
                   { 
                       Eid = orders.Eid, 
                       Uid = orders.Uid, 
                       OrderStat = orders.OrderStat, 
                       Name = m.Name, 
                       Contact = (m.Contact == null) ? 0 : m.Contact 
                   };
return Ok(items);

With this code, I am getting only result (common result) from users table and I am looking for something like UNION or OUTER JOIN.

Any help will be highly appreciated

A UNION and OUTER JOIN are two different things. Let us know what output you want. — Salik Rafiq
I would like to get data from order tables and select matching user from either users tables or customers table. Matching user will be in either table as I am keeping separate table for local and online users. — Sulfy
This might help you. stackoverflow.com/questions/6483711/union-in-entity-framework — Salik Rafiq
Currently I’m able to get result from orders along with users table, but I need to get data from customers too — Sulfy

Sulfy Sulfy · Accepted Answer · 2020-11-20T14:37:28

finally I got exactly what I was looking for, here by posting the answer as I might help someone looking for same. LEFT JOIN with Entity Framework is the key

var query = from a in authurs
                         join b in books on a.Id equals b.AuthorId into auth_books
                         from left in auth_books.DefaultIfEmpty()
                         join bs in booksSeries on left.BookSeriesId equals bs.Id into books_bookseries
                         from left2 in books_bookseries.DefaultIfEmpty()
                         select new
                         {
                             Author = a.Name,
                             Book = left.Name,
                             BookSeries = left2.Description
                         };
                           
var resList2 = query.ToList();

refer this link for further clarification.

Select data from multiple table with Entity Framework

1 Answers