EntityFramework Core get multiple results after join

.net-core asp.net asp.net-core entity-framework entity-framework-core

Question

The database has tables Machines, Wheels, Characteristics. 1 record of the car includes 4 records of a wheel and several characteristics. In the database, it looks something like this:enter image description here

I want to get the last 5 entries about cars, including a complete set of parameter data and wheel information. And I would like to get all the data in one request.

 var result = (from Machines in db.Machines.Where(x => x.param == 2) orderby Machines.ID descending
                      join wheel in db.Wheels on Machines.ID equals wheel.MachineId
                      join param in db.Characteristcs on Machines.ID equals param.MachineId
                      select new { Machines, param, wheel } 
                      ).Take(5).ToList();

but it returns only one wheel record and one characteristic.

1
1
1/17/2020 3:05:41 PM

Accepted Answer

In EF Core, you could refer to the relationship and load raleted data for more details.

Here is my working demo :

Model

public class Machines
{
    public int Id { get; set; }
    public string Description { get; set; }
    public string Color { get; set; }
    public int Weight { get; set; }
    public int Param { get; set; }
}
public class Characteristics
{
    public int Id { get; set; }
    public string Parameter { get; set; }
    public string Value { get; set; }
    public string Description { get; set; }

    public int MachineId { get; set; }
}
public class Wheels
{
    public int Id { get; set; }
    public int Radius { get; set; }
    public int Weight { get; set; }
    public int MachineId { get; set; }
}

Controller

public IActionResult GetCars()
{
        var collection = (from Machines in _context.Machines.Where(x => x.Param == 2)
                      join wheel in _context.Wheels on Machines.Id equals wheel.MachineId
                      join param in _context.Characteristics on Machines.Id equals param.MachineId
                      select new { Machines, param, wheel }
                  ).ToList();

        //get the last 5 entries about cars
        var result= collection.Skip(Math.Max(0, collection.Count() - 5)).ToList();
        return new JsonResult(result);
}
1
1/20/2020 7:55:26 AM

Popular Answer

  1. In SQL Server, create Machine's relationships with Wheels and Characteristic tables.
  2. Re-run scaffolding-dbcontext to update your EF dbcontext and models
  3. Use Include linq. var result = db.Machines.Where(x => x.Id == 2).Include(x => x.Wheels).Include(x => x.Characteristics);

See screenshot



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