C#C
C#•2y ago•
7 replies
Xour

Issues with LINQ query to left join three tables

Hi everyone,

I’m running into some issues with a LINQ query. I need to join three tables, but I’m having trouble getting the query to work as expected. The constraints are:

- No navigation properties between the tables
- Two tables (RobotNetworkStatus and RobotSystemStatus) contains either zero or one row for a given RobotSerialNumber
- RobotNetworkInterfaces contains zero or more rows for any given RobotSerialNumber

After poking at the docs (here and here), this is what I come up with, which is of course not working (cannot be evaluated property, and an exception is thrown):

var robotNetworkData = await (
        from ni in _dbContext.RobotNetworkInterfaces
        join ns in _dbContext.RobotNetworkStatus
            on ni.RobotSerialNumber equals ns.RobotSerialNumber into nsGroup
        from ns in nsGroup.DefaultIfEmpty()
        join ss in _dbContext.RobotSystemStatus
            on ni.RobotSerialNumber equals ss.RobotSerialNumber into ssGroup
        from ss in ssGroup.DefaultIfEmpty()
        where ni.RobotSerialNumber == request.SerialNumber
        group new { ni, nsGroup, ssGroup } by new { ni, ns, ss } into g
        select new
        {
            RobotNetworkStatus = g.Key.ns,
            RobotSystemStatus = g.Key.ss,
            RobotNetworkInterfaces = g.Select(nd => nd.ni).ToList()
        }
    )
    .AsNoTracking()
    .ToListAsync();


I suspect the issue relies in the grouping. NGL, I was more or less guessing what to do here.

Ideally I would like to get this fixed, but more importantly, I would love to understand how to fix it and how does it work. Any hit/help/advise/suggestions are most welcomed! Thanks!

EDIT: If I remove the grouping it works, but for each row of RobotNetworkInterfaces I get all other rows as well.
Was this page helpful?