0

Why asking

I already found a way to fix it, but it duplicates the code in Equals method and moreover I would like to understand the behavior.

Issue

Querying DbContext list of objects and filtering using a where clause that uses the Equals method of the object doesn't return the proper SQL query.

So, here is my Equals method:

public override bool Equals(object obj)
{
    if (obj == null) return false;
    if (ReferenceEquals(this, obj)) return true;
    if (obj is not MetaMovie other) return false;

    if (other.Id > 0 && Id > 0) return other.Id == Id;
    return other.MetaSource == MetaSource && other.ExternalId == ExternalId;
}

Using:

var existingMovie = await db.MetaMovies.Where(m => m.Equals(movie)).FirstOrDefaultAsync();

Generate a where using Id even if movie.Id == 0 and I was expecting having a query that would have used the alternate key (ExternalId + MetaSource). But no!

Patch that would prefer to skip

Instead, I created this method in the MetaMovie class:

public static System.Linq.Expressions.Expression<Func<MetaMovie, bool>> IsEqual(MetaMovie other)
{
    if (other.Id > 0) return m => other.Id == m.Id;

    return m => other.MetaSource == m.MetaSource && other.ExternalId == m.ExternalId;
}

And now it returns the proper query depending if movie.Id == 0 or not using:

var existingMovie = await db.MetaMovies.Where(MetaMovie.IsEqual(movie)).FirstOrDefaultAsync();

Could someone tell me what is difference? Or even better, how could I only have the Equals method having the same behavior?

Master DJon
  • 1,899
  • 2
  • 19
  • 30
  • Because the query is analyzed and translated into SQL instead of just being executed. There is a good explanation here: https://stackoverflow.com/questions/793571/why-would-you-use-expressionfunct-rather-than-funct – Mike Mozhaev Feb 09 '23 at 04:20

3 Answers3

0

Can you try movie.Equals(m)) instead? Without seeing the rest of the code, it's a little tough.

if (obj is not MetaMovie other) return false; could also be the issue... But as I said it's a little tough to figure it out without seeing the rest.

WA E
  • 33
  • 5
0

I think the difference is you are sending in an arbitrary Equals method and EF does not know how to interpret that. The one that works is a LINQ expression body and EF can understand those and is able to create the proper SQL for it.

You can also simply inline the lambda expression.

await db.MetaMovies.Where(m =>
  movie.Id > 0
  ? movie.Id == m.Id
  : movie.MetaSource == m.MetaSource && movie.ExternalId == m.ExternalId)
beautifulcoder
  • 10,832
  • 3
  • 19
  • 29
  • Thank you, but my point not to inline is to have this "where" in the MetaMovie class. So, if change or add something, it would be only there and not everywhere I need this. Moreover, my static method IsEqual works, but I would like to use Equals instead. – Master DJon Feb 09 '23 at 04:34
  • If you want to make it reusable, any method that returns `Func` should do the trick. The Equals technique does not work with EF because it is not a lambda expression. – beautifulcoder Feb 09 '23 at 16:14
  • This seems the way to go, but I am not able to make my head around it. All methods I saw that returns a `Func<>` don't take an argument. I will create another question. – Master DJon Feb 09 '23 at 21:13
  • If you want to take a look: https://stackoverflow.com/questions/75404526/c-sharp-how-to-use-a-method-returning-func-in-a-method-return-an-expression – Master DJon Feb 09 '23 at 21:28
0

EF Core can only translate LambdaExpressions, what you have is a compiled function.

You could write a helper method to apply the where expression to a query.

public static IQueryable<MetaMovies> WhereEqual(this IQueryable<MetaMovies> query, MetaMovie movie)
=> movie.Id > 0
  ? query.Where(m => movie.Id == m.Id)
  : query.Where(m => movie.MetaSource == m.MetaSource && movie.ExternalId == m.ExternalId);


await db.MetaMovies.WhereEqual(movie).FirstOrDefaultAsync();

At least then, this common query is defined in a single location.

Jeremy Lakeman
  • 9,515
  • 25
  • 29
  • Well, isn't it about the same as my static method `MetaMovie.IsEqual`? – Master DJon Feb 09 '23 at 04:42
  • I suppose you could write an interceptor https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-7.0/whatsnew#new-and-improved-interceptors-and-events to translate the equals method into an expression. – Jeremy Lakeman Feb 09 '23 at 04:44
  • I just quickly look and it seems it could do the job, but way too much complex for (as now) a fairly simple project. I will stick with my static method, but I have another complex thing, a class that is using two others Equals method. Now, combining IsEqual seems harder. – Master DJon Feb 09 '23 at 04:52
  • I believe the new EF Core 7 `IQueryExpressionInterceptor`, would replace the first half of the implementation I wrote here; https://stackoverflow.com/a/62708275/4139809 though that was for replacing static methods. – Jeremy Lakeman Feb 09 '23 at 04:54