Get Value from other Table by ID with Entity Framework

asp.net-core entity-framework-core fluent

Question

i have two already existings tables (no foreignkey):

Customer (Id, Name, .....) Projects (Id, CustomerId, name)

And in my asp.net core application i have two model:

public class Customer {
   public int Id { get; set; };
   public String Name { get; set; };
}

public class Project {
   public int Id { get; set; };
   public Customer Customer{ get; set; };
   public String Name{ get; set; };
}

And the datacontext classes for this

public class CustomerContext: DbContext
{
    public CustomerContext(DbContextOptions<CustomerContext> options) : base(options)
    {
    }

    public DbSet<CustomerContext> Customer { get; set; }
}

public class ProjectContext: DbContext
{
    public ProjectContext(DbContextOptions<ProjectContext> options) : base(options)
    {
    }

    public DbSet<ProjectContext> Project{ get; set; }
}

But i cant find out how to fetch the Customer object in the Projectclass by the customerId

Can someone help me please? Thank you

Edit: Now i change my Model Classes like in the answer below

but with the following i get an SQL Exception while loading the page SqlException: Invalid object name 'Customer'.

        projectList = await (from project in _context.Project
                                     join customer in _customerContext.Customer on project.CustomerId equals customer.Id into tmp
                                     from m in tmp.DefaultIfEmpty()

                                     select new Project
                                     {
                                         Id = sollIst.Id,
                                         CustomerId = sollIst.CustomerId,
                                         Customer = m,
                                         Name = sollIst.Name,
                                     }
                      ).ToListAsync();
1
2
7/4/2018 12:07:03 PM

Accepted Answer

Update your model classes as below:

public class Customer {
   public int Id { get; set; };
   public String Name { get; set; };
}

public class Project {
   public int Id { get; set; };
   public String Name{ get; set; };
   public int CustomerID { get; set; }
   [ForeignKey("CustomerID")]
   public Customer Customer{ get; set; };
}

Merger both DbContext into one.

public class ProjectContext: DbContext
{
    public ProjectContext(DbContextOptions<ProjectContext> options) : base(options)
    {
    }

    public DbSet<Project> Projects { get; set; }
    public DbSet<Customer> Customers { get; set; }
}

Then execute

projectList = await (from project in _context.Project
                 join customer in _context.Customer on project.CustomerId equals customer.Id into tmp
                 from m in tmp.DefaultIfEmpty()

                 select new Project
                 {
                     Id = sollIst.Id,
                     CustomerId = sollIst.CustomerId,
                     Customer = m,
                     Name = sollIst.Name,
                 }
  ).ToListAsync();

I hope following links will help you to know how to join two tables across different database.

  1. Joining tables from two databases using entity framework.
  2. Entity framework join across two databases
4
7/4/2018 3:55:53 PM

Popular Answer

You will have to create a property in Project class that represent the "foreign key".

Lets say in Project table in the database the "foreign key" is CustomerID, add this to Project class:

public int CustomerID { get; set; }

Then add the ForeignKey attribute to the Customer property:

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow