LINQ join query with relational entities

Question

I am using ef-core 2.1, I have the following simplified entities where one Account maps to zero or more Attribute objects:

public class Account
{
    public int Id { get; set; }
    public int LongId { get; set; }
    public List<Attribute> Attributes { get; set; }
}

public class Attribute
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Value { get; set; }
    public Account Account { get; set; }
}

I have an initial collection of strings that resemble an Attribute.Value for a given fixed Attribute.Name, I want to find a second associated Attribute object from the same parent Account and obtain its Attribute.Value.

I want to left join the ef entities against the initial string collection so I can easily infer:

1. Whether no corresponding Account exists or an Account exists without the related Attribute objects (both equate to the same use case).
2. If an Account exists and it contains all the required Attribute objects, I want to obtain the value of the secondary Attribute.

Without LINQ and ef, I run the following SQL query which ignores the parent Account and produces the result set I want:

CREATE TABLE #Temp
(
    id nvarchar(20) not null
);
INSERT INTO #Temp (id) VALUES ('cejawq'), ('issokq'), ('cqlpjq'), ('mbgzvi'), ('wqwlff'), ('iedifh');
SELECT t.[Id], attr2.[Value]
FROM #Temp t
LEFT OUTER JOIN [dbo].[Attributes] attr1
    ON t.[Id]=attr1.[Value]
    AND attr1.[Name]='uid'
LEFT OUTER JOIN [dbo].[Attributes] attr2
    ON attr1.[AccountId]=attr2.[AccountId]
    AND attr2.[Name]='objType';

I get the following result set:

id|objType
-----------
cejawq|ext
issokq|ext
cqlpjq|int
mbgzvi|int
wqwlff|ext
iedifh|null

I am struggling with mapping this to efficient LINQ such that the SQL generated produces the result set remotely and ships back data that I can project to an equivalent anonymous type. Do I need to care about the parent objects in the LINQ case? I don't have an index on the Attribute.Value column.

The Attributes table contains the following data:

Id|Name   |Value |AccountId
1 |uid    |cejawq|1
2 |objType|ext   |1
3 |uid    |issokq|2
4 |objType|ext   |2
5 |uid    |cqlpjq|3
6 |objType|int   |3
7 |uid    |mbgzvi|4
8 |objType|int   |4
9 |uid    |wqwlff|5
10|objType|ext   |5

Answer 1

Since the EF Core does not support joins with in memory sequences (yet), you can split the query in two parts - one which takes the data server side ([Attributes to [Attributes join) using in memory collection as filter (SQL IN through LINQ Contains method), and second which performs left join in memory with the result of the db query:

DbContext db = ...;
var uids = new [] { "cejawq", "issokq", "cqlpjq", "mbgzvi", "wqwlff", "iedifh" };

var dbQuery =
    from attr1 in db.Set<Attribute>()
    where attr1.Name == "uid" && uids.Contains(attr1.Value)
    join attr2 in db.Set<Attribute>()
    on new { AccountId = attr1.Account.Id, Name = "objType" }
    equals new { AccountId = attr2.Account.Id, attr2.Name }
    into attr2Group from attr2 in attr2Group.DefaultIfEmpty() // left outer join
    select new { uid = attr1.Value, objType = attr2.Value };

var query =
    from uid in uids
    join dbResult in dbQuery on uid equals dbResult.uid
    into dbResultGroup from dbResult in dbResultGroup.DefaultIfEmpty() // left outer join
    select new { uid, dbResult?.objType };

var result = query.ToList();

It translates to a single db query like this:

SELECT [attr1].[Value] AS [uid], [attr2].[Value] AS [objType]
FROM [Attributes] AS [attr1]
LEFT JOIN [Attributes] AS [attr2] ON ([attr1].[AccountId] = [attr2].[AccountId]) AND (N'objType' = [attr2].[Name])
WHERE ([attr1].[Name] = N'uid') AND [attr1].[Value] IN (N'cejawq', N'issokq', N'cqlpjq', N'mbgzvi', N'wqwlff', N'iedifh')