C#•3y ago

[EntityFramework Core] Populate complex object from query with join

Hi, I've the following objects representing tables:

    [Table("ProductionOrder", Schema = "public")]
    public class ProductionOrder
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public string Code { get; set; }
        
        [ForeignKey("ProductId")]
        public Product Product { get; set; }
        public int ProductId { get; set; }

        [ForeignKey("CustomerId")]
        public Customer Customer { get; set; }
        public int CustomerId { get; set; }
    }

    [Table("ProductionOrder", Schema = "public")]
    public class ProductionOrder
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public string Code { get; set; }
        
        [ForeignKey("ProductId")]
        public Product Product { get; set; }
        public int ProductId { get; set; }

        [ForeignKey("CustomerId")]
        public Customer Customer { get; set; }
        public int CustomerId { get; set; }
    }

    [Table("Product", Schema = "public")]
    public class Product
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public string Name { get; set; }
    }

    [Table("Product", Schema = "public")]
    public class Product
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public string Name { get; set; }
    }

    [Table("Customer", Schema = "public")]
    public class Customer
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public string Name { get; set; }
    }

    [Table("Customer", Schema = "public")]
    public class Customer
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public string Name { get; set; }
    }

I'd like to retrieve the list of all ProductionOrder items together with their Product and Customer. From what I've experimented, the following code returns the ProductionOrder list but Customer and Product fields are both null:

    public async Task<List<ProductionOrder>> GetProductionOrdersAsync() {
        return await _dbContext.ProductionOrder.ToListAsync();
    }

    public async Task<List<ProductionOrder>> GetProductionOrdersAsync() {
        return await _dbContext.ProductionOrder.ToListAsync();
    }

Of course I could manually perform a couple of query for each ProductionOrder, retrieving its Product and Customer. But it seems a waste of resources, since with JOIN I can achieve the same result with a single query. So I'm trying to understand if the above code can somehow perform JOIN and retrieve Product and Customer automatically or if I'm required to perform JOIN manually. Thanks

1 Reply

alkasel#159OP•3y ago

In the end I was able to solve the problem: I just need to use

List<ProductionOrder> orders = await _dbContext.ProductionOrder.Include(x => x.Product).Include(x => x.Customer).ToListAsync();

List<ProductionOrder> orders = await _dbContext.ProductionOrder.Include(x => x.Product).Include(x => x.Customer).ToListAsync();

et voilà, Customer and Product fields are filled automatically. That's very nice. It took me about an entire working day to figure it out 😓

Gaming

Programming

[EntityFramework Core] Populate complex object from query with join