Ray Ray - 1 month ago 20
C# Question

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

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:
enter image description here

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

Any help is really appreciated. :)

Thanks,

Ray

Answer

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