Array Type Mapping not working

[julian-dimitroff]

Hi guys,
I have a strange problem, maybe it is something that I'm missing out, but I have the following LINQ Lambda query:

    var ss = ctx.ShipZones.SelectMany(
                    z => ctx.ShipDecks,
                    (z, d) => 
                        new
                        {
                            Zone = z.ZIndex,
                            Deck = d.DIndex,
                            Value = ctx.Tags
                                .AsExpandable()
                                .Include(s => s.TagSettings.Device.System)
                                .Where(s =>
                                     s.TagSettings.TagTypeId == 171
                                     && s.TagSettings.Device.System.Id == z.Id
                                     && s.TagSettings.Device.ControlArea.Contains(d.Id)
                                        )
                                .Average(s => s.Value)
                        }
                    ).ToList();

According to this article, this should be translated to this:

SELECT z.z_index AS "Zone", d.d_index AS "Deck", (
    SELECT AVG(t.value)
    FROM tags_current_data AS t
    INNER JOIN tags_settings AS t0 ON t.tag_id = t0.id
    INNER JOIN systems_devices AS s ON t0.device_id = s.id
    INNER JOIN systems AS s0 ON s.system_id = s0.id
    WHERE ((t0.tag_type_id = 171) AND (s0.id = z.id)) AND (d.id = ANY(s.control_area))) AS "Value"
FROM zones AS z
CROSS JOIN decks AS d

But somehow, the translated query is like this:

SELECT z.z_index AS "Zone", d.d_index AS "Deck", (
    SELECT AVG(t.value)
    FROM tags_current_data AS t
    INNER JOIN tags_settings AS t0 ON t.tag_id = t0.id
    INNER JOIN systems_devices AS s ON t0.device_id = s.id
    INNER JOIN systems AS s0 ON s.system_id = s0.id
    WHERE ((t0.tag_type_id = 171) AND (s0.id = z.id)) AND (TRUE = FALSE)) AS "Value"
FROM zones AS z
CROSS JOIN decks AS d

The difference is where it should be d.id = ANY(s.control_area) it is TRUE = FALSE
Can anyone tell me what am I doing wrong?

Thanks in advance,
Julian Dimitrov

[julian-dimitroff]

Hi all,
I was able to locate my problem.
It was in the entities configuration. The ControlArea property in the Device entity was of type List<long>. When I changed it to long[] (long array) it worked!
So will be possible (maybe in future, the List and others Enumerable types, to be translated the same way, or there is problems for that to happen?

Best Regards,
Julian

[roji]

@julian-dimitroff can you please confirm what version of EF are you using (3.1 or 5.0)? 5.0 significantly improved support for List (see #395) so there's a good change it would just work with 5.0.0-rc1.

Enumerable types in general aren't supported by design, since they cause multiple enumeration which can potentially be very bad for perf.

[julian-dimitroff]

Hello,
Thanks for the quick answer!
My apologies! Yes I'm using Microsoft.EntityFrameworkCore v.3.1.8.
I'll try with 5.0 when I'm able to install it via Nuget Package Manager.

BR,
Julian

[roji]

@julian-dimitroff you can install 5.0.0-rc1 right now with the nuget package manager - you may need to tell it to get prerelease versoins as well (a simple checkbox).

It's important for us to get confirmation ASAP, since if there's a bug I'd like to fix it for the 5.0.0 release which is quite close now.

[julian-dimitroff]

@roji yes it produces a query but it is a bit different. As no expert in postgresql I'll paste here the query that was produced when the entity has type List<long>

SELECT z.z_index AS "Zone", d.d_index AS "Deck", (
    SELECT AVG(t.value)
    FROM tags_current_data AS t
    INNER JOIN tags_settings AS t0 ON t.tag_id = t0.id
    INNER JOIN systems_devices AS s ON t0.device_id = s.id
    INNER JOIN systems AS s0 ON s.system_id = s0.id
    WHERE ((t0.tag_type_id = 171) AND (s0.id = z.id)) AND (s.control_area @> ARRAY[d.id]::bigint[])) AS "Value"
FROM zones AS z
CROSS JOIN decks AS d

The query works as expected when applied on the database server.

BR,
Julian

[roji]

Great, thanks for confirming. The new query uses array containment syntax because that allows for index use - if control_area has an index, the query will run much faster than with ANY. See #1372 for more details.