How to use where in LINQ with json column postgreSQL-CodePudding

I have this kind of model.

public class Person
{
    Id { get; set; }
    public string Name { get; set; }
    // This has jsonbType
    public List<long> FriendsIds { get; set; }
}
 modelBuilder.Entity<Person>().Property(e => e.FriendsIds)
                .HasColumnType("jsonb")
                .HasConversion(
                    v => JsonConvert.SerializeObject(v ?? new List<long>()),
                    v => JsonConvert.DeserializeObject<List<long>>(v));

And I want to write query to get people which have specific Friends. For example something like this:

var friendIds = new List<long>;
var people = await Context.People.Where(x => x.FriendsIds.Intersect(friendIds).Any()).ToListAsync();

How can I do it?

CodePudding user response：

why you use Intersect?

I want to write query to get people which have specific Friends - one to many relationship

class design

// One-To-Many Relatioship
public ICollection<long> FriendsIds { get; set; }

// and One-To-Many relationships from Person-To-FriendsIds

        modelBuilder.Entity<Person>()
                    .WithMany(c => c.FriendsIds) // One-to-Many
                    .HasForeignKey(f => f.FriendsIds_Id); // Foreign Key

CodePudding user response：

I have much more complex table structure - then use object

public class ResourceRoot
{
        public string _id { get; set; }
        public object FriendsIds { set; get; }
}

object is better for any complex structure.