Inline comparison works, but if I wrap that in a method, LINQ expression cannot be translated

Question

Full source code is at the bottom, but here is the highlight.

//Works
if (mydb.Articles.Any(x => (x.ArticleId == demo.ArticleId && x.Title == demo.Title)))

public bool IsSame(WebArticle other)
{
    return (ArticleId   == other.ArticleId && Title == other.Title);
}

//Doesn't work
if (mydb.Articles.Any(x => x.IsSame(demo)))

Is there any way to avoid the repeated code of x.ArticleId == demo.ArticleId && x.Title == demo.Title and reuse one source?

Program.cs

using Microsoft.EntityFrameworkCore.Storage;
using System.Diagnostics;

namespace EntityTest
{
    internal class Program
    {
        static void Main(string[] args)
        {
            var mydb = new MyDbContext();

            var article1 = new Article()
            {
                ArticleId = 1234,
                Title = "First",
            };
            var article2 = new Article()
            {
                ArticleId = 5678,
                Title = "Second",
            };
            var article3 = new Article()
            {
                ArticleId = 9012,
                Title = "Third",
            };


            mydb.Articles.AddRange(article1, article2, article3);
            mydb.SaveChanges();

            var demo = new WebArticle()
            {
                ArticleId = 5678,
                Title = "Second",
            };

            //use inline code
            if (mydb.Articles.Any(x => (x.ArticleId == demo.ArticleId && x.Title == demo.Title)))
            {
                Console.WriteLine("Exists");
            }
            else
            {
                Console.WriteLine("Doesn't exist");
            }

            //use method
            if (mydb.Articles.Any(x => x.IsSame(demo)))
            {
                Console.WriteLine("Exists");
            }
            else
            {
                Console.WriteLine("Doesn't exist");
            }
        }
    }

    class WebArticle
    {
        public int ArticleId { get; set; }
        public string Title { get; set; }
    }
}

MyDbContext.cs

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EntityTest
{
    internal class MyDbContext:DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseInMemoryDatabase("memory");
            base.OnConfiguring(optionsBuilder);
        }

        public DbSet<Article> Articles { get; set; }
        public DbSet<ArticleImage> ArticleImages { get; set; }
    }

    class Article
    {
        [Key]
        public int Id { get; set; }
        public int ArticleId { get; set; }
        public string Title { get; set; }       

        public bool IsSame(WebArticle other)
        {
            return (ArticleId   == other.ArticleId && Title == other.Title);
        }
    }

    class ArticleImage
    {
        public int Id { get; set; }
        public int ArticleId { get; set; }
        public string Url { get; set; }
    }
}

You can't. Linq needs to be able to generate real SQL that's supported by your RDBMS - it does this by mapping _basic_ operations, like `==` and `.Contains` to SQL operators `=` and `LIKE` or `IN`, and so on. It's not possible to convert arbitrary C# methods to SQL: Linq can only translate `Expression>` code because that allows it to inspect the C# expression-tree and _try_ to convert it. — Dai, Nov 22 '22 at 05:36
Does this answer your question? [The LINQ expression could not be translated and will be evaluated locally](https://stackoverflow.com/questions/57872910/the-linq-expression-could-not-be-translated-and-will-be-evaluated-locally) — Dai, Nov 22 '22 at 05:38
@Dai So I have to use inline code like the first one every time? — Damn Vegetables, Nov 22 '22 at 05:39
Yep, pretty-much. Though [you _can_ use both scalar and tabular UDF in queries too](https://learn.microsoft.com/en-us/ef/core/querying/user-defined-function-mapping), but using scalar UDFs are generally a bad-idea for high-performance application queries unless you're careful about using `INLINE = ON` and are using SQL Server 2019 or later. — Dai, Nov 22 '22 at 05:41
You can also compose `IQueryable` queries provided you're careful to use `Expression<>`, so it's not like you'll violate DRY. — Dai, Nov 22 '22 at 05:42
@Dai The linked question uses a complex method `Equals()`, but in my case, it was the same comparison I used before that; just wrapped in a method. — Damn Vegetables, Nov 22 '22 at 06:09
EF cannot map `String.Equals(String,StringComparison.InvariantCultureIgnoreCase)` (as there is no equivalent in SQL) but it will map `String.Equals(String,StringComparison.Ordinal)` to a `COLLATE` comparison in SQL. — Dai, Nov 22 '22 at 06:11
No. My code does not use a complex method like Equals. It is the same inline code wrapped in a method for reuse. `public bool IsSame(WebArticle other){ return (ArticleId == other.ArticleId && Title == other.Title);}` What I wonder is if there is any way to avoid this same duplicated code. — Damn Vegetables, Nov 22 '22 at 06:32
Move it to an extension method for `Foo(this IQueryable q, T ArticleId )` that does `return q.Where( wa => wa.ArticleId == articleId )` - but isn't the `String title` predicate redundant if `articleId` is the PK? — Dai, Nov 22 '22 at 07:28
Also, I'm a longtime watcher of Dankmus and Dankpods [](https://www.youtube.com/watch?v=FJ7pOlUDrxE) — Dai, Nov 22 '22 at 07:29
*just wrapped in a method* - EF Core cannot translate compiled method. It just cannot look into method body. There are really huge amount of answers what to do in such case. For example [mine](https://stackoverflow.com/a/66386142/10646316) — Svyatoslav Danyliv, Nov 22 '22 at 08:58

score 0 · Answer 1 · answered Nov 22 '22 at 13:37

Changed the code like the following. It worked without using a third-party library.

Program.cs

        //use method
        //if (mydb.Articles.AsExpandable().Any(x => x.IsSame(demo)))
        if (mydb.Articles.Any(Article.IsSame(demo)))
        {
            Console.WriteLine("Exists");
        }
        else
        {
            Console.WriteLine("Doesn't exist");
        }

MyDbContext.cs

class Article
{
    [Key]
    public int Id { get; set; }
    public int ArticleId { get; set; }
    public string Title { get; set; }

    //public bool IsSame(WebArticle other)
    //{
    //  return (ArticleId   == other.ArticleId) && (Title == other.Title);
    //}

    public static Expression<Func<Article, bool>> IsSame(WebArticle other)
    {
        return current => (current.ArticleId == other.ArticleId) && (current.Title == other.Title);
    }

Inline comparison works, but if I wrap that in a method, LINQ expression cannot be translated

1 Answers1