SQL query to LINQ to join 5 tables

Question

I have five tables which I am trying to get results from. Below are my table designs.

Application and Environment tables:

Id, Name

NOTE - BOTH above tables have same properties.

ResourceGroup table:

Id, Name, Application_Id

ResourceGroupEnvironment table:

Id, ResourceGroup_Id, Environment_Id, VIP, URL

Servers table:

Id, ServerName, Alias, IPAddress, Network

ServerResourceGroup table:

Id, Server_Id, ResourceGroup_Id, Environment_Id

What results I am trying to get?

Based on above table design, I am expecting to get all servers that are part of ResourceGroup and Application table. ResourceGroupEnvironment and ServerResourceGroup table shares environment table where ResourceGroupEnvironment will have one common environment, but ServerResourceGroup can have multiple servers in that environment.

So from ResourceGroupEnvironment I am trying to join VIP, URL in to servers, but I get duplicate entries for servers, but each entry with different vip and URL. Please see below duplicate record in JSON for an example:

{
    "serverId": 1,
    "applicationName": "TestApp",
    "resourceName": "Test AppFabric",
    "serverName": "Server1",
    "aliasName": null,
    "os": "Windows Server 2008",
    "ipAddress": "192.168.1.1",
    "vip": "10.1.1.1",
    "url": "www.google.com",
    "environmentName": "DEV",
},
{
    "serverId": 1,
    "applicationName": "TestApp",
    "resourceName": "Test AppFabric",
    "serverName": "Server1",
    "aliasName": null,
    "os": "Windows Server 2008",
    "ipAddress": "192.168.1.1",
    "vip": "10.2.2.3",
    "url": "www.testui.com",
    "environmentName": "DEV",
}

So whats wrong with above records in JSON?

Well I should only get one record back from database for commonenvironment. If there are multiple servers in DEV environment, then it should return multiple servers, but should join VIP and URL in to each server record. If you notice, VIP and URL are unique, which they belong to two different environments. Such as 10.1.1.1 VIP belongs to DEV environment and 10.2.2.3 belongs to other environment PROD.

Here is the query I wrote in linq that is not working as excepted:

var query = from rg in _context.ResourceGroup
            join sr in _context.ServersResourceGroup on rg.Id equals sr.ResourceGroup_id
            join rge in _context.ResourceGroupEnvironment on sr.Environment_id equals rge.Environment_id into lrges
            from lrge in lrges.DefaultIfEmpty()
            join s in _context.Servers on sr.Server_id equals s.Id
            join e in _context.Environments on sr.Environment_id equals e.Id
            join a in _context.Applications on rg.Application_Id equals a.Id
            join d in _context.Domains on s.Domain_Id equals d.Id
            join t in _context.Types on rg.Type_Id equals t.Id
            join o in _context.OperatingSystems on s.OperatingSystem_Id equals o.Id
            join n in _context.NetworkZones on s.NetworkZone_Id equals n.Id
            join stat in _context.Status on s.Status.Id equals stat.Id
            where a.Name.ToLower() == applicationName.ToLower()
            select new SearchListViewModel()
            {
                serverId = s.Id,
                resourceName = rg.Name,
                applicationName = a.Name,
                serverName = s.ServerName,
                aliasName = s.Alias,
                os = o.OSVersion,
                ipAddress = s.IPAddress,
                vip = lrge.VIP,
                url = lrge.EndPointURL,
                domain = d.Name,
                network = n.Name,
                typeName = t.Name,
                environmentName = e.Name,
                status = stat.Name
            };

        return query.ToList();

SQL Query:

When I write my own SQL query, it works fine and gives me correct records. Please see below SQL query:

select rg.Name as ResourceName, s.ServerName, rge.VIP, rge.EndPointURL
from ResourceGroup as rg
join ServersResourceGroup as srg on rg.Id = srg.ResourceGroup_id
join Servers as s on srg.Server_id = s.Id
left join ResourceGroupEnvironment as rge on srg.Environment_id = rge.Environment_id

HERE IS THE SOLUTION THAT WORKED AFTER EVERYONE'S HELP:

 var query = from rg in _context.ResourceGroup
                    join srg in _context.ServersResourceGroup on rg.Id equals srg.ResourceGroup_id
                    join s in _context.Servers on srg.Server_id equals s.Id
                    join a in _context.Applications on rg.Application_Id equals a.Id
                    join e in _context.Environments on srg.Environment_id equals e.Id
                    join d in _context.Domains on s.Domain_Id equals d.Id
                    join t in _context.Types on rg.Type_Id equals t.Id
                    join n in _context.NetworkZones on s.NetworkZone_Id equals n.Id
                    join o in _context.OperatingSystems on s.OperatingSystem_Id equals o.Id
                    join stat in _context.Status on s.Status_Id equals stat.Id
                    join rge in _context.ResourceGroupEnvironment on srg.Environment_id equals rge.Environment_id into lrges
                    from lrge in lrges.DefaultIfEmpty()
                    select new SearchListViewModel()
                    {
                        serverId = s.Id,
                        serverName = s.ServerName,
                        aliasName = s.Alias,
                        domain = d.Name,
                        environmentName = e.Name,
                        network = n.Name,
                        os = o.OSVersion,
                        ipAddress = s.IPAddress,
                        vip = lrge == null ? string.Empty : lrge.VIP,
                        url = lrge == null ? string.Empty : lrge.EndPointURL,
                        typeName = t.Name,
                        applicationName = a.Name,
                        resourceName = rg.Name,
                        status = stat.Name
                    };

        return query.ToList();

Did you set your navigation properties? if so, you should try querying through them. I find it much easier than joining a bunch of tables. — Gabriel GM, Oct 28 '16 at 02:06
I count 11 joins in your Linq statement and 4 in your "hand written" sql statement that works. I do not really see how those are considered the same if you are testing to ensure you get a result using similar circumstances. Also if your database collation is not case sensitive then there is no reason to use a comparison with `.ToLower()` — Igor, Oct 28 '16 at 12:32
thats true, there are more joins in linq query and hand written query, but mostly I am focusing on first 4 joins, all others are just inner join that doesnt really impact query execution. — Ray, Oct 28 '16 at 12:36

score 1 · Accepted Answer · edited May 23 '17 at 11:48

Your query

SELECT rg.Name AS ResourceName, s.ServerName, rge.VIP, rge.EndPointURL
FROM ResourceGroup AS rg
JOIN ServersResourceGroup AS srg ON rg.Id = srg.ResourceGroup_id
JOIN Servers AS s ON srg.Server_id = s.Id
LEFT JOIN ResourceGroupEnvironment AS rge ON srg.Environment_id = rge.Environment_id

Can be translated to this Linq statement

var query = from rg in _context.ResourceGroup
    join srg in _context.ServersResourceGroup on rg.Id equals srg.ResourceGroup_id
    join s in _context.Servers on srg.Server_id equals s.Id
    from rge in _context.ResourceGroupEnvironment.Where(x => srg.Environment_id = x.Environment_id).DefaultIfEmpty()
    select new{
        ResourceName = rg.Name, 
        s.ServerName, 
        rge.VIP, 
        rge.EndPointURL
    };
var queryResults = query.ToList();

Again you should really compare apples to apples. In your query you are joining 11 tables and you have a where clause which are both omitted from your example query which could be the reason for you different results. As per your comment though this answer just focuses on the 4 tables included in your SQL script.

About the last (outer) join: See answer on question LEFT OUTER JOIN in LINQ by @Stefan Steiger. This is an easier to read syntax IMO but either that or the other way using the join syntax would work.

Hello, your example did help me make progress and I am able to make left join successfully. I will provide my answer to my question in Answer bulletin in my question. — Ray, Oct 28 '16 at 18:22

SQL query to LINQ to join 5 tables

1 Answers1