Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
  • Jan 31 2019 21:30
    roji commented #2307
  • Jan 31 2019 20:35
    YohDeadfall commented #2307
  • Jan 31 2019 10:20
    capan starred npgsql/npgsql
  • Jan 31 2019 06:10
    SeanFarrow synchronize #2308
  • Jan 30 2019 20:37
    austindrenski commented #2308
  • Jan 30 2019 20:37
    pentagra commented #1445
  • Jan 30 2019 20:33
    SeanFarrow commented #2308
  • Jan 30 2019 20:31
    austindrenski commented #2308
  • Jan 30 2019 20:27
    SeanFarrow commented #2308
  • Jan 30 2019 20:09
    austindrenski labeled #2308
  • Jan 30 2019 20:05
    roji commented #1520
  • Jan 30 2019 19:50
    gyzod commented #1445
  • Jan 30 2019 19:06
    SeanFarrow commented #1520
  • Jan 30 2019 18:54
    roji commented #1520
  • Jan 30 2019 18:53
    roji commented #1520
  • Jan 30 2019 18:52
    roji commented #2050
  • Jan 30 2019 18:45
    Trolldemorted commented #2050
  • Jan 30 2019 18:21
    SeanFarrow commented #1520
  • Jan 30 2019 18:16
    SeanFarrow review_requested #2308
  • Jan 30 2019 18:16
    SeanFarrow review_requested #2308
Yoh Deadfall
@YohDeadfall
@pha3z, there are two ways to get it: per connection TypeMapper or global GlobalTypeMapper, both a connection properties. Later look at Mappings and ClrTypes for each of them.
Emill
@Emill
Entity Framework can create pretty complex (blown-up) queries with many joins for LINQ-queries that requests data to be returned in something similar to a big json-structure. Those queries get quite complex resultset layout and due to the heavy use of joins data can easily be duplicated many times when sent over the wire. The json-functions in PostgreSQL can be used to return the data in a json-structure instead, which will be more on the form what the user wants, and hence not include any duplicate data. Can Entity Framework Core take advantage of this?
Shay Rojansky
@roji
@Emill general JSON support is one of the specific themes being looked at for EF Core 6.0 - not just for PG (other databases also have good JSON capabilities). We've specifically discussed projecting data out of the database as a JSON document, it's indeed a very interesting direction. But right now nothing like that is supported. Note that EF also has split query mode, which mitigates a lot of the issues with many joins in a single query (https://docs.microsoft.com/en-us/ef/core/querying/single-split-queries)
Emill
@Emill
I wonder how to deal with fields that are not representable by json though
I'd like to see an extension of jsonb that could be used with arbitrary postgresql data types
i.e. something like record and arrays
Emill
@Emill
hmm seems like array_agg could solve this too
something like select tbl1.*, array_agg(tbl2) from tbl1 left join tbl2 on tbl1.id = tbl2.tbl1_id group by tbl1.id can be used to list all tbl1 items including all tbl2 items belonging to a tbl1 item
Emill
@Emill
to remove the need for npgsql to know about table type oids, select tbl1.*, array_agg(row(tbl2.id, tbl2.col1, tbl2.tbl1_id)) from tbl1 left join tbl2 on tbl1.id = tbl2.tbl1_id group by tbl1.id can be used to expand tbl2's columns into a generic record that Npgsql can handle
Emill
@Emill
or something more similar like select *, (select array_agg(tbl2) from tbl2 where tbl2.tbl1_id = tbl1.id) from tbl1 to avoid the group by
Shay Rojansky
@roji
Re extension of jsonb, records and arrays specifically seem pretty simple: json already has the concept of arrays, and records are basically heterogeneous arrays too, no?
Yeah, I've looked at array_agg, and also at making it work with EF Core: npgsql/efcore.pg#727, dotnet/efcore#22957
But you're right that the basic problem with this approach, is that a JSON document simply isn't typed in the same way as data coming back directly (in PG binary encoding, decoded by handlers). So handing off the JSON document directly to the user is one thing - it bypasses the ORM capability completely, and it's the user's responsibility to parse and create entity instances out of the JSON, if that's what they want.
Making EF Core actually materialize from JSON would be different, and probably quite problematic (e.g. do we just parse timestamp string representations? What about weirder types?)
Emill
@Emill
Making EF Core materialize data from arrays and records should be simpler, since data is then encoded using the binary types as usual.
My idea was that the user shouldn't need to explicitly ask for array_agg etc. but should be a strategy inside EF to avoid "cartesian explosion" and split query.
If json_agg turns out to be more efficient for some reason than array_agg and row(...), maybe that can be used instead if EF can validate that only text, integers etc. are included in the data to be returned.
Shay Rojansky
@roji
Ah no - you're right that array_agg could indeed be a great PG materialization strategy that avoids cartesian explosion and split query at the same time
It's definitely something that's there in the back of my mind
First EF Core would need to add general support for provider-specific aggregate methods though
In any case, there's no reason to supposed json_agg is better than array_agg - quite the opposite, since parsing the JSON and the textual representation of the values within is useless overhead
Emill
@Emill
Is this materialization strategy something that has been brought up with the MS EF Core team?
Shay Rojansky
@roji
I mentioned it at some point, but the problem is that the array_agg strategy is very PG-specific
This is something that I hope to look at one day, once the provider-specific aggregate function support is done on the EF Core side (the issues above)
Emill
@Emill
it's a very strong feature of PG I would say :)
Shay Rojansky
@roji
Me too :)
Though writing an EF materializer is not an easy thing to do :) Just look at the regular one...
Emill
@Emill
if this is implemented and works fine, PG + EF can be a very strong competitor, for those who write complex queries
Shay Rojansky
@roji
I very much agree. I think it can be a real killer feature
Emill
@Emill
yeah I'm trying to read up on EF Core
I'm kind of left where EF 6 was at 5 years ago right now
Shay Rojansky
@roji
Ah yeah, EF Core is very very different from EF6 - complete rewrite, very different architecture
BTW there's already a lot of interesting array support in the PG provider, where PG can do things far better than other databases
Emill
@Emill
I mean EF 6 + MySQL is kind of broken, buggy and slow if you write slightly more complex queries than simple CRUD
cool
Shay Rojansky
@roji
EF6 was really not made for extensibility for other databases... for example, the type system is completely closed
In EF Core I could add all the special PG types
And the fact you can map an array/List property to PG arrays is quite powerful. Then you can do stuff like blog.SomeArrayProperty.Any(p => p > 3), which will translate into the correct PG construct and execute efficiently
With other databases you have to either have a comma-separated string column, or a one-to-many relationship...
If you like expression trees and the idea of translating constructs from C# to SQL, then EF Core is really fun to work on
Emill
@Emill
Newer MySQL seems to have some json aggregator function like postgres. Maybe the materialization approach, when only simple data types are involved can be used there as well. Or maybe MySQL only have simple types and hence always works with json parsing?
Shay Rojansky
@roji
I have no idea...
Emill
@Emill
I'm thinking if the record type handler can be improved to be more flexible when the user knows what kind of data to expect inside it.
right now you more or less get an object[]
with records with no db-defined composite type
I'm not sure what would be most efficient considering EF as the user.
Shay Rojansky
@roji
I'm not sure records are needs for array_agg-based materialization - composite types may very well do the trick as well
One (current) issue is that Npgsql requires mapping them in advance to CLR types, but we may be able to do something nice with that
But maybe records could also be the thing
Emill
@Emill
I mean EF should be able to dynamically select the data to return, for example using array_agg(row(tbl.thing_a, tbl.thing_b, 'hi', 3))