How to write a LINQ query?

Question

ASP.NET

The database has an Actors field. The names and surnames of the actors are written separated by commas. When querying, it is necessary to catch all exact matches of names or surnames.
There are three options

if (!string.IsNullOrEmpty(movieActors))
            {
                //1. movies = movies.Where(x => x.Actors.Contains(movieActors));
                //2. movies = movies.Where(x => arr.Contains(movieActors));
                //3. movies = movies.Where(x => x.Actors.Split(' ').Contains(movieActors));    
            }

The first one works, but not well. Returns any character match.
In the second variant, it went through all the lines from the database in a loop and separated the names and surnames, saving them in the arr array. The request doesn't work.
The third gives an error - something with type casting.
Tell me how to write a request?

Answer 1

If the names are separated by commas, then you need to break them by commas:
With a check for emptiness:
Alternative option:

movies.Where
(
  x => 
  !String.IsNullOrEmpty(x.Actors) &&
  x.Actors.Split(',').Any((actor) => actor.Equals(movieActors))
);

Another:
If the names contain spaces, an even more resource-intensive query to remove spaces would be something like this:

movies.Where
(
  x => 
  !String.IsNullOrEmpty(x.Actors) && 
  x.Actors.Split(',').Select(a => a.Trim()).Any((actor) => actor.Equals(movieActors))
);

With an error: does not recognize the method 'System.String[] Split(Char[])'
you will probably have to get the data (into an array):

movies.ToList().Where
(
  x => 
  !String.IsNullOrEmpty(x.Actors) && 
  x.Actors.Split(',').Select(a => a.Trim()).Any((actor) => actor.Equals(movieActors))
);

Bad decision, it's better to change the data structure, store the actors in their normal form, so that they do not have to be divided into arrays. The substring search option is the best in terms of performance:

movies = movies.Where(x => x.Actors.Contains(movieActors));

Answer 2

Thanks for the help, but in all cases, an error occurs when searching. The error is the same as in my third option.
LINQ to Entities does not recognize the method 'System.String[] Split(Char[])' method, and this method cannot be translated into a store expression.
Occurs when a value is returned
return View(movies.ToList().ToPagedList(pageNumber, pageSize));
Here is the method

public ActionResult Index(string movieTitle, string movieGenre, string movieActors, int? page)
        {
            int pageSize = 10; int pageNumber = (page ?? 1); //Настройки постраничной навигации (кол-во страниц/страница по умолчанию)

            var movies = from m in db.FilmsTables
                         select m;

            if (!string.IsNullOrEmpty(movieActors))
            {
                //movies = movies.Where(x => x.Actors.Contains(movieActors));
                //movies = movies.Where(x => arr.Contains(movieActors));
                //movies = movies.Where(x => x.Actors.Split(' ').Contains(movieActors));    

                movies = movies.Where
                (
                  x =>
                  !String.IsNullOrEmpty(x.Actors) &&
                  x.Actors.Split(',').Select(a => a.Trim()).Any((actor) => actor.Equals(movieActors))
                );
            }

            return View(movies.ToList().ToPagedList(pageNumber, pageSize));
        }

Answer 3

It's bad like that.
The strings must be compared case-insensitively so that "Ivan" is equal to "IVAN".
The string is obtained in a database of this type:
"Ivanov Ivan, Petrov, Sidorov S., P.D. Sergeev ..."
I remember that it is not good to store data in SQL separated by commas, transfer them to another table and migrate.
Now the problem is to find it, then the problem is to change the surname, then you will need to add some data, split them into groups, etc.
One actor can play in several films, and now copies are stored in each entity, it will be easy to change his name, I think not.
You need a many-to-many relationship. And then you can find all actors by movie, and all movies by actor.
The error occurs in the ToList() method, because it is he who makes the pending request, which was only configured in the code above but not executed.
To quickly solve the problem, here and now there is a solution on SA -> LINQ to Entities does not recognize the method 'Sy...

Answer 4

Above wrote - create a list of actors.
If the names are comma -separated
then the three checks
actor == value
or
actor.contains("," + value + "," (may be comparision ignore case))
or
actor .endwiths("," + value)
with spaces included.
looked at the link,
there is this option
stackoverflow.com/questions/23929801/linq-to-entit...