.Net core entity framework LINQ expression couldn't be translated

Question

When I am trying to use a props.getValue method in my where clause I get an an error. This error occurrers because LINQ cant translate the call of this method in SQL, because SQL doesnt know this method. I have read something about expression trees, but I dont really understand how to use them, so it would be nice if someone could explain me how to use them correct.

My method from a controller:

  int end = pageNumber * filter; int start = end - filter;
            var query = GetRequestsWithoutFilter().Where(r => r.RequestStatus == 1);

            if (attribute != null && title!=null)
            {
                if (attribute.Equals("movieReleaseDate"))
                {
                    int i = Int32.Parse(title);
                    query = query.Where(r => (r.Movie.MovieReleaseDate) == i);
                }
                else
                {
                    Movie m = new Movie();
                    PropertyInfo props = m.GetType().GetProperties().Where(p => String.Equals(attribute, p.Name, StringComparison.OrdinalIgnoreCase)).FirstOrDefault();

                    if (props!=null)
                    {
                        query = query.Where(r => ((string)props.GetValue(r.Movie)).Substring(0, (title.Length < r.Movie.MovieTitle.Length ? title.Length : r.Movie.MovieTitle.Length)).ToUpper().Equals(title));
                    }
                }
            }

            try { 
             var result = await query.Skip(start).Take(filter).ToListAsync();
                if (result == null)
                {
                    return NotFound();
                }
                return result;
            } catch(Exception e)
            {
                return null;
            }
            
        }

GetRequestsWithoutFilter:

 [NonAction]
        private IQueryable<Request> GetRequestsWithoutFilter()
        {
            IQueryable<Request> query = _context.Requests.Include(r => r.User)
                                                .Include(r => r.Movie)
                                                .Select(e => new Request()
                                                {
                                                    RequestID = e.RequestID,
                                                    MovieID = e.MovieID,
                                                    Movie = new Movie()
                                                    {
                                                        MovieID = e.MovieID,
                                                        MovieTitle = e.Movie.MovieTitle,
                                                        MovieDescription = e.Movie.MovieDescription,
                                                        MovieDirector = e.Movie.MovieDirector,
                                                        MovieProducer = e.Movie.MovieProducer,
                                                        ImageSrc = String.Format("{0}://{1}{2}/Images/Movies/{3}", Request.Scheme, Request.Host, Request.PathBase, e.Movie.ImageName),
                                                        MovieReleaseDate = e.Movie.MovieReleaseDate, 
                                                    },
                                                    UserID = e.UserID,
                                                    User = new User()
                                                    {
                                                        UserID = e.User.UserID,
                                                        UserVorname = e.User.UserVorname,
                                                        UserNachname = e.User.UserNachname
                                                    },
                                                    RequestDate = e.RequestDate,
                                                    RequestStatus = e.RequestStatus
                                                });
            return query;
        }

Movie model:

using Microsoft.AspNetCore.Http;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;

namespace MoviesWebApi.Models
{
    public class Movie
    {
        public Movie()
        {
            Requests = new HashSet<Request>();
        }


        [Key]
        public int MovieID { get; set; }

        [Column(TypeName = "nvarchar(40)")]
        public string MovieTitle { get; set; }
        [Column(TypeName = "nvarchar(1000)")]
        public string MovieDescription { get; set; }
        [Column(TypeName = "nvarchar(40)")]
        public string MovieDirector { get; set; }
        [Column(TypeName = "nvarchar(40)")]
        public string MovieProducer { get; set; }

        public int MovieReleaseDate { get; set; }

        [Column(TypeName = "nvarchar(100)")]
        public string ImageName { get; set; }

        [NotMapped]
        public IFormFile ImageFile { get; set; }

        [NotMapped]
        public string ImageSrc { get; set; }

        public  ICollection<Request> Requests { get; set; }

    }
}

Answer 1

Reflection calls like this

((string)props.GetValue(r.Movie))

as part of the query expression tree are not translatable.

What you have here is retrieving a value of a property. The expression equivalent of it is Expression.Property, but then you need to either construct the whole predicate using the Expression class methods, which is tedious and error prone, or use some helper utility for composing expressions.

It's not that hard, but in EF Core there is a simpler solution, since it provides a special method called EF.Property, which is recognized and properly translated.

In order to use it, all you need is to know the property name and type. The later is a bit problematic in dynamic scenarios, but luckily you seem to be targeting string type properties, so just replace the above with

EF.Property<string>(r.Movie, props.Name)