Invalid SQL generated with 3.1

In theory, any query that can be expressed using LINQ will be simpler than its SQL equivalent. This simplification has a price - underlying implementation complexity. As a result some scenarios might be not supported or have issues. ELINQ does not perform any logical transformations, - it's plain SQL developed in .NET. So it's more verbose in some cases, yet C#! Compared to an alternative - writing SQL as an embedded string, it's a clear winner (run to see the SQL):

public static void Test20505(MyContext dbContext)
{
    var floorId = 1;
    var t = dbContext.Set<SpaceGroup>().Query((SpaceGroup spaceGroup) =>
    {
        var spaceGroupIds = GetSpaceGroupIdsSimple(floorId);

        var result = SELECT(spaceGroup);
        FROM(spaceGroup);
        WHERE(spaceGroupIds.Contains(spaceGroup.Id));
        return result;
    })
        .OrderBy(sg => sg.Name)
        .Select(sg => new
        {
            SpaceGroups = sg.Id
        });

    Console.WriteLine(t.FirstOrDefault());
}

private static ICollection<int> GetSpaceGroupIdsSimple(int floorId)
{
    return SubQuery((Space space) =>
    {
        var allFloorSpaceGroups = SubQuery((SpaceGroup spaceGroup, SpaceGroupFloorSpace floorSpace, SpaceGroupWithSpace alias) =>
        {
            var r = SELECT<SpaceGroupWithSpace>(floorSpace.SpaceId.@as(alias.SpaceId), spaceGroup.Id.@as(alias.GroupId));
            FROM(spaceGroup).JOIN(floorSpace).ON(floorSpace.SpaceGroup == spaceGroup);
            return r;
        });

        var distinctSpaceGroups = SELECT(DISTINCT(allFloorSpaceGroups.GroupId));
        FROM(space).JOIN(allFloorSpaceGroups).ON(space.Id == allFloorSpaceGroups.SpaceId);
        WHERE(floorId == space.Floor.Id);

        return distinctSpaceGroups.AsCollection();
    });
}

If you have good eyes and the original issue opened, you could notice that the SQL generated by EF is much more complex. It's not a mistake. EF seems to always generate an OUTER APPLY construct, while it's not needed when there is a single floor. So the custom query is much simpler.

But if required the full variant can be coded. Here it is, for a reference:

private static ICollection<int> GetSpaceGroupIds(int floorId)
{
    return SubQuery(() =>
    {
        var floor = SubQuery((Floor f) =>
        {
            var r = SELECT(f);
            FROM(f);
            WHERE(f.Id == floorId);

            return r;
        });

        var spaceGroupsIds = SubQuery((Space space, Scalar alias) =>
        {
            var allFloorSpaceGroups = SubQuery((SpaceGroup spaceGroup, SpaceGroupFloorSpace floorSpace, SpaceGroupWithSpace alias) =>
            {
                var r = SELECT<SpaceGroupWithSpace>(floorSpace.SpaceId.@as(alias.SpaceId), spaceGroup.Id.@as(alias.GroupId));
                FROM(spaceGroup).JOIN(floorSpace).ON(floorSpace.SpaceGroup == spaceGroup);
                return r;
            });

            var distinctSpaceGroups = SELECT<Scalar>(DISTINCT(allFloorSpaceGroups.GroupId.@as(alias.Value)));
            FROM(space).JOIN(allFloorSpaceGroups).ON(space.Id == allFloorSpaceGroups.SpaceId);
            WHERE(floor == space.Floor);

            return distinctSpaceGroups;
        });

        var r = SELECT(spaceGroupsIds.Value);
        FROM(floor).OUTER_APPLY(spaceGroupsIds);

        return r.AsCollection();
    });
}

< BACK | HOME