Get Value from other Table by ID with Entity Framework

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();

Karan · Accepted Answer · 2018-07-04T15:55:53.103

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.

Invalid object name 'Customer'. The table Customer is in another DB then Projects is that the problem? — Ezak, Jul 04 '18 at 12:09
I moved now the customer table to the same database as the project table. Now it works with your code. Thank you very much! If you know how to handle it with two databases let me know it (for interest) Thank You — Ezak, Jul 04 '18 at 12:21

score 3 · Answer 2 · answered Jul 04 '18 at 09:04

3

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; }

answered Jul 04 '18 at 09:04

Obay Abd-Algader

1,079
12
25

with that i also get an sql exception see edit in my question please. Thank your for reply – Ezak Jul 04 '18 at 09:48

score 0 · Answer 3 · answered Jul 04 '18 at 08:59

First, your model classes should be as follows:

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

   public string Name { get; set; };
}

public class Project {
   public int Id { get; set; };

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

   public string Name{ get; set; };

   public Customer Customer { get; set; };
}

Then your DbContext classes should be as follows:

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

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

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

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

Now you can use Entity Framework Core and LINQ query to fetch your desired data.

hey thank you for your fast reply. I try to use it but the Customer Object is null . ProjectList = await _context.Project.ToListAsync(); — Ezak, Jul 04 '18 at 09:25

Get Value from other Table by ID with Entity Framework

3 Answers3