Entity Framework 7 Lambda expression for 3 table joins

Question

I am trying to built an API that will return all the data for a server. A server can have multiple endpoints and they are stored in separate table with many-to-many relationship.

I have 3 tables:

Servers table contains all the server details
Applications table that contains application names
application_endpoint table that contains foreign keys to both tables Servers and Applications

Here is my database model:

Here are my models:

public class Servers
{
        public int id { get; set; }
        public string server_name { get; set; }
        public string alias { get; set; }
        public string ip_address { get; set; }
        public Status status { get; set; }
        public virtual ICollection<Application_endpoints> endpoint { get; set; }
}

public class Application_endpoints
{
        public int id { get; set; }
        public int? server_id { get; set; }
        [ForeignKey("server_id")]
        public Servers server { get; set; }
        public int? application_id { get; set; }
        [ForeignKey("application_id")]
        public Applications application { get; set; }
}

public class Applications
{
        public int id { get; set; }
        public string app_name { get; set; }
}

public class ServerDbContext : DbContext
{
        public DbSet<Applications> Applications { get; set; }
        public DbSet<Application_endpoints> Application_endpoints { get; set; }
        public DbSet<Servers> Servers { get; set; }
}

In my Api Controller, I have created HttpGet method that will query database and return data for each server. Here is simple API for GET:

private ServerDbContext _context;

public ServersController (ServerDbContext context)
{
    _context = context;
}

// GET: api/values
[HttpGet]
public JsonResult Get()
{
    var servers = _context.Servers
        .Include(endpoint => endpoint.endpoint)
        .ToList();
    return Json(servers);
}

Now when I make a get request, I get data back from the database, however the application object in below JSON is returned null. I am trying to figure it out how to add left join in my above query so I can get application name from applications table.

{
  "id": 6,
  "server_name": "server1",
  "alias": "",
  "ip_address": "192.168.1.7",
  "endpoint": [
    {
      "id": 23,
      "server_id": 6,
      "application_id": 10,
      "application": null
    }
  ]
}

Any help is really appreciated. :)

Have you tried '.Include(endpoint => endpoint.endpoint.Select(e=> e.application))'? For what i rember this is how you do it in EF6 — omar.ballerani, Feb 13 '16 at 19:11

score 2 · Answer 1 · answered Feb 13 '16 at 19:30

To all,

Thank you for guiding me to right path. I did quick google search and it appears that syntax has changed in EF7. Below worked for me :)

var servers = _context.Servers
            .Include(e => e.endpoint).ThenInclude(a => a.application)
            .ToList();

score 1 · Answer 2 · answered Feb 13 '16 at 19:12

1

You need to change your include in your query:

from

   .Include(endpoint => endpoint.endpoint)

to

    .Include(s => s.endpoint.Select(e => e.application))

Or you can use string path as

    .Include("endpoint.application")

answered Feb 13 '16 at 19:12

vittore

17,449
6
44
82

Vittore and omar.ballerani, I just tried your suggestion, however, I get an exception when I make a get request. Here is the exception: `Additional information: Unable to cast object of type 'Remotion.Linq.Clauses.Expressions.SubQueryExpression' to type 'System.Linq.Expressions.MemberExpression'.` – Ray Feb 13 '16 at 19:21
@vittore - The above query throws an exception in my case "make sure you do not have an infinite loop or infinite recursion". I have shown the db diagram and the complete query here: http://stackoverflow.com/questions/42453123/get-selected-values-after-joining-multiple-tables-in-entity-framework-6-using-la Could you please have a look at!? – sandiejat Feb 25 '17 at 07:21

Entity Framework 7 Lambda expression for 3 table joins

2 Answers2