New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

WHERE .. = ANY() Clause with composite objects not working #2154

Open
smblee opened this Issue Sep 13, 2018 · 5 comments

Comments

Projects
None yet
4 participants
@smblee

smblee commented Sep 13, 2018

Disclaimer: The issue is regarding Npgsql.EntityFrameworkCore.PostgreSQL 2.1.1 but I am posting it here since I am often times using .FromSql with raw sqls (including regarding this issue). If this is still in the wrong project, i will move it.

Steps to reproduce

The query I want to produce:

SELECT * FROM table WHERE (itemauthor, itemtitle) = ANY (('bob', 'hello'), ('frank', 'hi')...) OR
SELECT * FROM table WHERE (itemauthor, itemtitle) IN (VALUES ('bob', 'hello'), ('frank', 'hi')...)

Code I have / problems:

var parameters = new NpgsqlParameter[] {
   new NpgsqlParameter("@items", items.Select(i => new { items.Author, items.Title }).ToList())
};

var query = "Select [...bunch of columns...] from table where (itemauthor, itemtitle) = ANY (:items)";
_context.Query<MyRow>().FromSql(query, parameters).ToList();

This will say something like the type AnonymousType{string, string} is not registered.

I have also tried things like

  new NpgsqlParameter("@items", items.Select(i => $"('{i.Author}', '{i.Title}')").ToList()),
AND...
  new NpgsqlParameter("@items", string.Join(',', items.Select(i => $"('{i.Author}', '{i.Title}')"))

But this throws like an invalid argument at $4 exception.

The issue

Exception message:

System.NotSupportedException: The CLR array type System.Collections.Generic.List`1[<>f__AnonymousType0`2[System.String,System.String]] isn't supported by Npgsql or your PostgreSQL. If you wish to map it to an  PostgreSQL c
omposite type array you need to register it before usage, please refer to the documentation.

Stack trace:

         at Npgsql.TypeMapping.ConnectorTypeMapper.GetByClrType(Type type) in C:\projects\npgsql\src\Npgsql\TypeMapping\ConnectorTypeMapper.cs:line 140
         at Npgsql.NpgsqlParameter.ResolveHandler(ConnectorTypeMapper typeMapper) in C:\projects\npgsql\src\Npgsql\NpgsqlParameter.cs:line 529
         at Npgsql.NpgsqlParameter.Bind(ConnectorTypeMapper typeMapper) in C:\projects\npgsql\src\Npgsql\NpgsqlParameter.cs:line 538
         at Npgsql.NpgsqlCommand.ValidateParameters() in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 801
         at Npgsql.NpgsqlCommand.<ExecuteDbDataReader>d__100.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1233

Further technical details

Npgsql version: NpgSql EntityFramework Core 2.1.1
PostgreSQL version: 9.6
Operating system: Windows

Other details about my project setup: dotnet core 2.0

@YohDeadfall

This comment has been minimized.

Member

YohDeadfall commented Sep 13, 2018

As the exception says the corresponding composite is required in the database. This is because anonymous types are not mapped to records.

So, you should create a type and a struct which must be mapped to the type.

FYI, there is a similar issue #2097 to track mapping composites to value tuples.

@austindrenski

This comment has been minimized.

Member

austindrenski commented Sep 13, 2018

This would be nice to have, assuming it doesn't break anything else.

I'm hoping to make some progress on #2097 this weekend. If that goes well, I'll take a look at this too.

@smblee

This comment has been minimized.

smblee commented Sep 14, 2018

@YohDeadfall How can i use a non-anonymous object? If i try creating a different object with 2 fields in it, it still gives the same error. How do i "register" this object ? It would be nice to just be able to do new { field1, field2 } or new Tuple<string,string>(field1, field2)

@YohDeadfall

This comment has been minimized.

Member

YohDeadfall commented Sep 14, 2018

You should CREATE TYPE first, make the corresponding CLR class and map it using the MapComposite<T> method as it's said in the docs.

You can map the type to ValueTuple<string, string>, but then it's not possible to map another PostgreSQL type to that struct. There is a one-to-one relation.

CREATE TYPE book_info AS (author text, title text);
public class BookInfo
{
    public string Author { get; set; }
    public string Title { get; set; }
}
// in the static ctor of your DbContext
NpgsqlConnection.GlobalTypeMapper.MapComposite<BookInfo>();
new NpgsqlParameter("@items", items.Select(i => new BookInfo { Author = i.Author, Title = i.Title }).ToList())
@roji

This comment has been minimized.

Member

roji commented Nov 18, 2018

Unless I'm mistaken, the expression = ANY (('bob', 'hello'), ('frank', 'hi')...) actually accepts an array of records, and not of composite types. PostgreSQL records are totally ad-hoc, weakly-typed bundles of values, and Npgsql does not provide any way of sending these at the moment. see #2097 for a discussion on mapping CLR value tuples, either to composites or to record types.

Putting this on the backlog for now, we're unlikely to get around to this anytime soon.

@roji roji added this to the Backlog milestone Nov 18, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment