Ray Ray - 1 month ago 15
ASP.NET (C#) Question

SQL query to LINQ to join 5 tables

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 common
environment
. 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();

Answer

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.