Issue getting correct results from LINQ query with LEFT JOIN

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

ray mak9y ago
I think I was missing multiple condition in LINQ left join. Here is the solution. http://stackoverflow.com/questions/40589693/issue-getting-correct-results-from-linq-query-with-left-join/40592046#40592046
Manas Mohapatra9y ago
Every record looks unique. Could you please mention which are duplicate records and secondly query looks fine..