How to perform a join in c# .net core Entity Framework using

Question

I have this relation ship.

In both of this model I have a navigation properties, however I still don't know how to use them.

The Customer Model

 public class Customer
{
    public int Id { get; set; }
    [Required]
    public string FirstName { get; set; }
    [Required]
    public string LastName { get; set; }
    [Required]     
    public int CustomerGroupId { get; set; }
    public CustomerGroup CustomerGroup { get; set; }

}

Customer Group Model

public class CustomerGroup
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Customer> Customers { get; set; }
}

I want to display a list of customers along with their customer group name.

I have this query but its only giving the customer groups list but not the customers.

Changes to new query executing.

what im getting as a return

The CustomerGroup is null.

Your question/tags are confusing, can you please clarify it for the sake of others who come across it? Is the question about Entity Framework Core or Entity Framework on .NET Core (which shouldn't be possible to run anyways, since EF doesn't run on .NET Core/.NET Standard). Also asp.net-mvc is for the legacy ASP.NET MVC 1-5, not for ASP.NET Core, there it's asp.net-core-mvc. May also be useful: What are tags, and how should I use them? and why you should avoid stuffing tags into the question (C#, .NET Core) — Tseng
– Tseng, Commented Aug 23, 2017 at 19:03

SLaks · Accepted Answer · 2017-08-23 17:39:59Z

4

If you want both groups and their customers, you need to tell EF to include the navigation property:

db.CustomerGroups.Include(g => g.Customers).ToListAsync()

answered Aug 23, 2017 at 17:39

SLaks

891k182 gold badges1.9k silver badges2k bronze badges

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

1 Comment

Andy Over a year ago

Been searching for this solution for hours. Thank you @slaks.

Shyju · Accepted Answer · 2017-08-23 18:28:33Z

2

Get all customers and in the view, when you loop through the list of customers , simply access the CustomerGroup navigational property of each item.

var customers = db.Customers.ToList();
return View(customers);

and in the view

@model List<Customer>
@foreach(var c in Model)
{
  <p>@c.FirstName </p>
  <p>@c.CustomerGroup.Name</p>
}

Make sure your CustomerGroup property is virtual

public class Customer
{
    public int Id { get; set; }
    [Required]
    public string FirstName { get; set; }
    [Required]
    public string LastName { get; set; }
    [Required]     
    public int CustomerGroupId { get; set; }
    public virtual CustomerGroup CustomerGroup { get; set; }    
}

Keep in mind that this will do lazy loading. To do eager loading (a single query to get everything), you may consider using a view model and project to that.

So create a view model

public class CustomerVm
{
  public string FirstName { set;get;}
  public string GroupName { set;get;}
}

and in your GET action project your LINQ query result to a collection of this view model.

 var customerVms = db.Customers
                    .Select(x=> new CustomerVm { FirstName = x.Name, 
                                                 GroupName =x.CustomerGroup.Name})
                    .ToList();
 return View(customerVms );

Make sure your view is now strongly typed to a list of CustomerVm's.

@model List<CustomerVm>
@foreach(var c in Model)
{
  <p>@c.FirstName </p>
  <p>@c.GroupName</p>
}

Add other properties to your view model as needed by the view.

Take a look at this post for understanding the difference between eager loading vs lazy loading

edited Aug 23, 2017 at 18:28

answered Aug 23, 2017 at 17:39

Shyju

220k106 gold badges421 silver badges499 bronze badges

4 Comments

Millenial2020 Over a year ago

If I do var customers = db.Customers.ToList(); return View(customers); I get "NullReferenceException". I don't want to do a view model. I just want to simple do a join using my navigatioins. ToList() looks good but only return values of the customers the customer groups is null.

Shyju Over a year ago

With the entity class definition you provided, EF will generate table with CustomerGroupId column as not null. With that, every customer should have a customer group id stored with it. Where are you getting null reference exception ? Is db initialized (not null) ?

Millenial2020 Over a year ago

I edit my post so you can see the query executing(basically the one you gave me) and what im getting in return.

Shyju Over a year ago

For EF, to properly lazy load stuff. your CustomerGroup property should be virtual. See the updated answer.

Collectives™ on Stack Overflow

How to perform a join in c# .net core Entity Framework using

2 Answers 2

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related