Issue getting correct results from LINQ query with LEFT JOIN

Question

Hello,

 

I am writing a query in LINQ, but having issues getting correct record from database. When I execute LINQ query, I get duplicate records. Please see below records with duplicate results:

ServerName ResourceGroup Env EndPointURL VIP
Server1 Test AppFabric  DEV www.testapp.com 10.1.1.5
Server1 Test AppFabric  DEV www.testappui.com   10.1.1.6
Server2 Test AppFabric  DEV www.testapp.com 10.1.1.5
Server2 Test AppFabric  DEV www.testappui.com   10.1.1.6
Server3 Test AppFabric UI   DEV www.testapp.com 10.1.1.5
Server3 Test AppFabric UI   DEV www.testappui.com   10.1.1.6
Server4 Test AppFabric UI   DEV www.testapp.com 10.1.1.5
Server4 Test AppFabric UI   DEV www.testappui.com   10.1.1.6
Server5 Test AppFabric  INT NULL    NULL
server6 Test AppFabric  INT NULL    NULL

 Here is the query I am using in my ASPT.NET Core application:

 

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.Where(r => r.ResourceGroup_id == rg.Id).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,
                    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();

 

 Also, here is the native SQL query that just works fine.

 

SELECT s.ServerName, rg.Name as ResourceGroup, e.Name as Env,
rge.EndPointURL, rge.VIP
FROM ResourceGroup as rg
JOIN ServersResourceGroup as srg on rg.Id = srg.ResourceGroup_id
JOIN Servers as s on srg.Server_id = s.Id
JOIN Environments as e on srg.Environment_id = e.Id
LEFT JOIN ResourceGroupEnvironment as rge on srg.Environment_id = rge.Environment_id and rg.Id = rge.ResourceGroup_Id

 Not sure if my LINQ query is wrong, but I am trying to accomplish above native query in LINQ,

 

Here is the linq to entity model http://www.sqlservercentral.com/Forums/Attachment19610.aspx 

 

Any help is really appreciated.

 

Thank you,

 

Ray