LINQ syntax : how to have the same purpose?

Question

SQL queries generated by LINQ with query syntax are better compared to method syntax, but how do you achieve the same end?

method syntax:

var query = __repository.GetContext().Set<ObjectModel>()
                                .Include(obj => obj.ObjectTypeObjectAttributes)
                                .ThenInclude(otoa => otoa.TypeObjectAttribute)
                                .ThenInclude(toa => toa.Attribute);

query syntax:

var query = from obj in _repository.GetContext().Set<ObjectModel>()
                    join otoa in _repository.GetContext().Set<ObjectTypeObjectAttributeModel>() on obj.Id equals otoa.ObjectId
                    join toa in _repository.GetContext().Set<TypeObjectAttributeModel>() on otoa.TypeObjectAttributeId equals toa.Id
                    join att in _repository.GetContext().Set<AttributeModel>() on toa.AttributeId equals att.Id
                    orderby toa.Attribute.Name ascending
                    select new { obj, otoa, toa, att };

The use of the method syntax creates instances of ObjectModel with the navigation property of the ObjectTypeObjectAttributeModel type, which is not the case with the query syntax. Is it possible to have the same result with the query syntax?

I focused on the query syntax because it is possible to perform an orderby clause on a property of ObjectTypeObjectAttributes which is of type collection, which is not possible with the method syntax in ef core 3.x.

SQL generated with method syntax:

    SELECT [o].[Id], [o].[TypeObjectId], [t0].[Id], [t0].[AttributeValue], [t0].[ObjectId], [t0].[TypeObjectAttributeId], [t0].[Id0], [t0].[AttributeId], [t0].[TypeObjectId], [t0].[Unicity], [t0].[Id1], [t0].[Description], [t0].[Name], [t0].[Type]
FROM [ru].[Object] AS [o]
LEFT JOIN (
    SELECT [o0].[Id], [o0].[AttributeValue], [o0].[ObjectId], [o0].[TypeObjectAttributeId], [t].[Id] AS [Id0], [t].[AttributeId], [t].[TypeObjectId], [t].[Unicity], [a].[Id] AS [Id1], [a].[Description], [a].[Name], [a].[Type]
    FROM [ru].[ObjectTypeObjectAttribute] AS [o0]
    INNER JOIN [ru].[TypeObjectAttribute] AS [t] ON [o0].[TypeObjectAttributeId] = [t].[Id]
    INNER JOIN [ru].[Attribute] AS [a] ON [t].[AttributeId] = [a].[Id]
) AS [t0] ON [o].[Id] = [t0].[ObjectId]
ORDER BY [o].[Id], [t0].[Id], [t0].[Id0], [t0].[Id1]

SQL generated with query syntax:

    SELECT [o].[Id], [o].[TypeObjectId], [o0].[Id], [o0].[AttributeValue], [o0].[ObjectId], [o0].[TypeObjectAttributeId], [t].[Id], [t].[AttributeId], [t].[TypeObjectId], [t].[Unicity], [a].[Id], [a].[Description], [a].[Name], [a].[Type]
FROM [ru].[Object] AS [o]
INNER JOIN [ru].[ObjectTypeObjectAttribute] AS [o0] ON [o].[Id] = [o0].[ObjectId]
INNER JOIN [ru].[TypeObjectAttribute] AS [t] ON [o0].[TypeObjectAttributeId] = [t].[Id]
INNER JOIN [ru].[Attribute] AS [a] ON [t].[AttributeId] = [a].[Id]
INNER JOIN [ru].[Attribute] AS [a0] ON [t].[AttributeId] = [a0].[Id]
ORDER BY [a0].[Name]

Answer 1

Your queries are really very different!

Your method syntax will return all ObjectModels, each with their zero or more ObjectTypeAttributes.

Your query syntax will return for every ObjectTypeObjetAttribute its one and only ObjectModel.

So if you have:

• ObjectModel [10] has ObjectTypeAttributes [20], [21], [22],
• ObjectModel [11] has ObjectTypeAttribute [23]
• ObjectModel [12] has no ObjectTypeAttributes,

then your method syntax will return a sequence of three items, similar to the above. This is because it uses a GroupJoin, which is similar to a left outer join followed by a GroupBy.

Your query syntax will return a sequence of four items, similar to the one below:

• ObjectModel [10] - ObjectTypeAttribute [20]
• ObjectModel [10] - ObjectTypeAttribute [21]
• ObjectModel [10] - ObjectTypeAttribute [22]
• ObjectModel [11] - ObjectTypeAttribute [23]

This is because you did an inner join. Appart from that the data of ObjectModel [10] is transferred several times, you'll miss ObjectModel [12].

Back to your question

If you want an inner join in method syntax, similar to your query syntax:

var result = dbContext.ObjectModels.Join(
    dbContext.ObjectTypeAttributes,

    // join on the following keys:
    objectModel => objectModel.Id,                       // from every ObjectModel take the primary key in Id
    objectTypeAttribute => objectTypeAttribute.ObjectId, // from every ObjectTypeAttribute take the foreign key in ObjectId

    // parameter resultSelector
    // for every objectModel and a matching typeAttribute make one new
    (objectModel, objectTypeAttribute) => new
    {
        // In your query: select the complete ObjectModel:
        ObjectModel = objectModel,
        TypeAttribute = objectTypeAttribute,

        // Or if you want, select only the properties that you plan to use:
        ObjectModel = new
        {
            Id = objectModel.Id,
            Name = objectModel.Name,
            ...
       }

       TypeAttribute = new
       {
           Id = objectTypeAttribute.Id,
           Name = objectTypeAttribute.Name,

           // Not needed: the foreign key, you already know the value!
           // ObjectId = objectTypeAttribute.ObjectId
       });

So there is some room for improvement: apart from that you transfer the same ObjectModel [10] several times, you also send the foreign key in the ObjectTypeAttribute several times, while you already know the value.

Consider whether a GroupJoin wouldn't be more suitable for your problem.