Entity Framework Core Lambda Expression join 4 tables and filter with where

Question

I have six tables as shown below. I am trying to get data based on Application_name, Environment_name, and Status.

Status table:

Id, Name

Application table:

Id, Name

Servers table:

Id, ServerName, Status

Environments table:

Id, Name

ResourceGroup table:

Id, Name, Application_Id, Environment_Id

ServersResourceGroup:

Id, Server_Id, Resource_Id

What I am trying to do is join all the require table and use where to filter data by Application_name, Environment_name, and Status

Here is my query I built, which returns all the data back by filtering Application_name but I am no way near to fulfill above requirement by adding additional filters by Environment_name and Status :(

So below is the query that returns all the data with Application_name

public IEnumerable<ResourceGroup> GetAllServersByApplication(string application_name, string environment_name, string status)
{
    var query = _context.ResourceGroup
            .Include(a => a.Application)
            .Include(t => t.Type)
            .Include(e => e.ServersGroup).ThenInclude(e => e.Environment)
            .Include(s => s.ServersGroup).ThenInclude(s => s.Server)
            .Include(s => s.ServersGroup).ThenInclude(s => s.Server).ThenInclude(s => s.Status)
            .Where(a => a.Application.Name == application_name)
            .ToList();

    return query;
}

Here is the query that I am trying to write that will filter based on all three filters:

    public IEnumerable<ResourceGroup> GetAllServersByApplication(string application_name, string environment_name, string status)
    {
        var query = _context.ResourceGroup
            .Include(a => a.Application)
            .Include(t => t.Type)
            .Include(e => e.ServersGroup).ThenInclude(e => e.Environment)
            .Include(s => s.ServersGroup).ThenInclude(s => s.Server)
            .Include(s => s.ServersGroup).ThenInclude(s => s.Server).ThenInclude(s => s.Status)
            .Where(a => a.Application.Name == application_name)
            .Select(e => e.ServersGroup.Where(s => s.Environment.Name == environment_name && s.Server.Status.Name == status)
            .ToList();

        return query;
    }

I get a red line under return query. Please see below image:

Is there any easier way to write lambda query then what I am trying to do?

Any help is really appreciated. :)

Thanks,

Ray

Just a bit of advice: You don't need to use Include() on everything if you are not going to Select it. What I mean is if you don't need that data in your application then don't include it. The Where will work fine without the includes. — Michael Coxon
– Michael Coxon, Commented Oct 14, 2016 at 3:23

Igor · Accepted Answer · 2016-10-14 13:29:50Z

1

There error is because you are now returning an collection of ServersGroup which is different than in your method signature. This is due to the fact that you added a Select clause and selected property ServersGroup from ResourceGroup. Filtering should happen in the Where clause. I updated the code below and moved your filter in the Select to the Where, also the included where was changed to an Any as a Where expects boolean clauses.

If it was your intention to only include certain ServersGroup within your ResourceGroup then you should filter those out in a join or outer join statement.

Also you probably do not need Include's everywhere. Includes are used for eager loading which ensures that only one roundtrip is necessary to the database to get the associated relationships. If you do not plan on accessing all these related properties then do not include them.

I do assume that your relationships are mapped correctly either in fluent mapping files or using attributes on the models.

public IEnumerable<ResourceGroup> GetAllServersByApplication(string application_name, string environment_name, string status)
{
    var query = _context.ResourceGroup
        .Include(a => a.Application)
        .Include(t => t.Type)
        .Include(e => e.ServersGroup).ThenInclude(e => e.Environment)
        .Include(s => s.ServersGroup).ThenInclude(s => s.Server)
        .Include(s => s.ServersGroup).ThenInclude(s => s.Server).ThenInclude(s => s.Status)
        .Where(a => a.Application.Name == application_name && a.ServersGroup.Any(s => s.Environment.Name == environment_name && s.Server.Status.Name == status))
        .ToList();

    return query;
}

edited Oct 14, 2016 at 13:29

answered Oct 14, 2016 at 13:24

Igor

62.5k10 gold badges111 silver badges181 bronze badges

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

1 Comment

Ray Over a year ago

I do plan to access properties from include to access Name property from each table. I will try out join to see if I can add those properties to ResourceGroup. Thank you so much. Your query did help me understand what I was doing wrong with. :)

Collectives™ on Stack Overflow

Entity Framework Core Lambda Expression join 4 tables and filter with where

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related