Convert three tables PostgreSQL query to Entity Framework

Question

How can I convert next SQL query to Entity Framework

SELECT 
  customers."CustomerId",
  customers."CustomerName",
  "Orders"."OrderId",
  "Orders"."Description",
  "Orders"."CustomerId",
  "OrderDetails"."OrderDetailId",
  "OrderDetails"."Article",
  "OrderDetails"."OrderId"
FROM
  "Orders"
  RIGHT OUTER JOIN customers ON ("Orders"."CustomerId" = customers."CustomerId")
  LEFT OUTER JOIN "OrderDetails" ON ("Orders"."OrderId" = "OrderDetails"."OrderId")

What I have tried:

public List<customers> GetAll() 
{ 
    var custord= _dbContext.customers .Include(x => x.Order) .Include(x => x.OrderDetails) .ToList(); return custord;
    
}

The problem: If I have Order without OrderDetail information I can not get all records.

ER Diagram looks like

EF deals with entities and objects, not tables and joins. If you have Order, Customer and OrderDetail classes with navigation properties ie Order.OrderDetails, Customer.Orders, Order.Customer, then EF will generate the JOINs when you execute eg context.Customers.Include(c=>c.Orders).ThenInclude(o=>o.OrderDetails).ToList(). As long as you follow naming conventions all you have to do is define the DbSet<T> properties — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 20, 2024 at 15:16
ORMs like EF or NHibernate try to give the impression of working with in-memory objects instead of tables and rows. If you have to use JOINs, it means something is missing — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 20, 2024 at 15:18
What have you tried, and what result did you get versus what result did you expect? There are a ton of tutorials out there on how to query using EF. Please show your research and attempts. — devlin carnate
– devlin carnate, Commented Mar 20, 2024 at 15:20
public List<customers> GetAll() { var custord= _dbContext.customers .Include(x => x.Order) .Include(x => x.OrderDetails) .ToList(); return custord; } If I have Order without OrderDetail information I can not get all records — BoS
– BoS, Commented Mar 20, 2024 at 16:06

BoS · Accepted Answer · 2024-03-29 05:24:34Z

0

This is solution, thanks all:

var item = (
from Order in _dbContext.Orders
join Customer in _dbContext.Customers on Order.CustomerId equals Customer.CustomerId
join OrderDetail in _dbContext.OrderDetails on Order.OrderId equals OrderDetail.OrderId
select new
{
    CustomerId = Customer.CustomerId,
    CustomerName = Customer.CustomerName,
    OrderId = Order.OrderId,
    Description = Order.Description,
    OrderDetailId = OrderDetail.OrderDetailId,
    Article = OrderDetail.Article,

}).ToList();

edited Mar 29, 2024 at 5:24

answered Mar 29, 2024 at 5:24

BoS

71 silver badge5 bronze badges

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

Collectives™ on Stack Overflow

Convert three tables PostgreSQL query to Entity Framework

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related