CODEWITHSUNDEEP
c#.netpostgresqlentity-framework
Than Htike Aung
Than Htike Aung

Reputation: 31

EF core list of string datatype to postgresql

I am using EF core 1.1.1 & postgresql with code-first implementation.

I have a model class with member variable for List as follow.

public class user : IdentityUser
{
    public int id { get; set; }
    [Column("devices", TypeName = "text[]")]
    public List<string> devices { get; set; };
}

I declared datatype as text[] and it shows as text array in database. Saving to database as follow works.

var device = "phone";
user.devices.Add(device);

It stores data in database but it returns an error when I try to call query like this.

if (user.Any(x => x.devices.Count > 0))

Showing error like this.

Can't cast database type _text to List`1'

How can I convert stored text data to List?

Thanks.

Upvotes: 3

Views: 2765

Answers (1)

rudolfdobias
rudolfdobias

Reputation: 1958

Late but still - use string[] instead of List<string>.

public string[] Devices { get; set; }

Bad news is that you cannot use the x => x.Devices.Count > 0 lambda anyway when querying directly from DB, since the EF+Npgsql still cannot convert these expressions into SQL (at least in versions <= 1.1.0...) You'll have to write this condition in raw SQL.

var result = db.YourTable
   .FromSql("select * from your_table where array_length(devices) > 0")
   .Where(...)
   .OrderBy(...)
   ...
   .ToList();

Filtering fetched rows in memory works normally, of course.

Upvotes: 6

Related Questions