C#•5mo ago

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();

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.

4 Replies

HtmlCompiler•5mo ago

usually i'm not against this syntax, but here using navigation properties i think it would help

Anton•5mo ago

EF Core doesn't handle grouping well. Even if it worked, the generated query would be insanely complex. Use linq2db, it should manage EF Core just straight up doesn't work for expressions that seem valid

XourOP•5mo ago

@network problem unfortunately, adding navigation properties is not an option @Anton , I am open to use other ORMs/alternatives. This is a read-only project, maybe Dapper could be more flexible/useful? (I have zero experience/exposure to Dapper!)

Anton•5mo ago

Dapper doesn't offer anything EF Core can't handle already at this point I think Go ahead and write a raw query though if that suits you

Gaming

Programming

Issues with LINQ query to left join three tables