Dotnet EF Core Linq string contains in a list string split by comma-CodePudding

I have a model like this in the database:

Post (PostId int, UserIds varchar(MAX)), example Post (12, "1,2,3,7,9,20")

I want to query it by UserId, for now, I use this:

DBContext.Posts.Where(_ => _.UserIds.Contains(targetId)).ToList();

But problem is that if target is 1, it also return Post with UserIds = "15,16" I try to use Regex like Regex.IsMatch(_.UserIds, $"\\b{targetId}\\b") but SQL can't translate it.

Is any way to solve this case?

CodePudding user response：

So your database has a table filled with Posts. Every Post seems to be posted by zero or more (maybe one or more) Users. It seems to me, that you also have a table of Users. Every User has posted zero or more Posts.

It seems to me that there is a many-to-many relation between Users and Posts: Every User has posted zero or more Posts; every Post has been posted by zero (one?) or more Users.

Normally in a database you would implement a many-to-many relation with a special table: the junction table.

You don't use the junction table. Your database is not normalized. Maybe your current problem can be solved without changing the database, but I see so many problems you will have to solve, maybe not now, but in the near future: what immense work would you need to do if you want to delete a user? How do you get all "Posts that user [10] has posted" And what if User [10] doesn't want to be mentioned anymore in the publication list of Post [23]? How to prevent that User [10] is mentioned twice in Post[23]:

UserIds = 10, 3, 5, 10, 7, 10

Normalize the database

Consider to update the database with a junction table and get rid of the string column Post.UserIds. This would solve all these problems at once.

class User
{
    public int Id {get; set;}
    public string Name {get; set;}
    ...

    // every user has posted zero or more Posts:
    public virtual ICollection<Post> Posts {get; set;}
}

class Post
{
    public int Id {get; set;}
    public string Title {get; set;}
    public Datetime PublicationDate {get; set;}
    ...

    // every Post has been posted by zero or more Users:
    public virtual ICollection<User> Users {get; set;}
}

And the junction table:

public UsersPost
{
    public int UserId {get; set;}
    public int PostId {get; set;}
}

Note: [UserId, PostId] is unique. Use this a the primary key

In entity framework the columns of tables are represented by non-virtual properties. The virtual properties reflect the relations between the tables (one-to-many, many-to-many)

Note: a foreign key is a real column in a table, hence a foreign key is non-virtual.

To configure many-to-many, you can use Fluent API:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    // User - Post: many-to-many
    modelBuilder.Entity<User>()
            .HasMany<Post>(user => user.Posts)
            .WithMany(post => post.Users)
            .Map(userpost =>
                    {
                        userpost.MapLeftKey(nameof(UserPost.UserId));
                        userpost.MapRightKey(nameof(UserPost.PostId));
                        userpost.ToTable(nameof(UserPost));
                    });

    // primary key of UserPost is a composite key:
    modelBuilder.Entity<UserPost>()
        .HasKey(userpost => new {userpost.UserId, userpost.PostId});
}

Back to your problem

Once you've implemented the junction table your data request will be easy:

int userId = ...

// get this User with all his Posts:
var userWithPosts= dbContext.Users
    .Where(user => user.Id == userId)
    .Select(user => new
    {
         // Select only the user properties that you plan to use
         Name = user.Name,
         ...

         Posts = user.Posts.Select(post => new
         {
             // Select only the Post properties that you plan to use
             Id = post.Id
             PublicationDate = post.PublicationDate,
             ...
         })
         .ToList(),
    });

Or, if you don't want any user data, start with the Posts:

var postsOfUser = dbContext.Posts
    .Where(post => post.Users.Any(user => user.Id == userId))
    .Select(post => new {...});

Some people don't like to use the virtual ICollections, or they use a version of entity framework that doesn't support this. In that case, you'll have to do the Join yourself:

int userId = ...
var postsOfThisUser = dbContext.UserPosts

    // keep only the UserPosts of this user:
    .Where(userPost => post.UserId == userId)

    // join the remaining UserPosts with Posts
    .Join(dbContext.Posts,

    userpost => userpost.PostId,    // from every UserPost get the foreign key to Post
    post => post.Id,                // from every Post, get the primary key

    // parameter resultSelector: from every UserPost with matching Post make one new
    (userPost, post) => new
    {
        Title = post.Title,
        PublicationDate = post.PublicationDate,
        ...
    }
}

Solution without normalized database

If you really can't convince your project leader that a proper database will prevent a lot of problems in the future, consider to create a SQL text that get the proper posts for you.

Your DbContext represents the current implementation of your database. It described the tables and the relations between the tables. Adding a method to fetch the Posts of a user seems to me a legit method for the DbContext.

My SQL is a bit rusty, you'll know way better than I how to do this in SQL. I guess you'll get the gist:

public IEnumerable<Post> GetPostsOfUser(int userId)
{
    const string sqlText = "Select Id, ... from Posts where ..."

    object[] parameters = new object[] {userId};
    return this.Database.SqlQuery(sqlText, parameters);
}

CodePudding user response：

Here is possible solution if you cannot normalize it:

var sql = "select PostId,UserIds from Post";
sql  = $" outer apply string_split(UserIds,',') where value={targetId}";

DBContext.Posts.FromSqlRaw(sql).ToList();