My data class:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using static iText.StyledXmlParser.Jsoup.Select.Evaluator;
namespace WebApi.Entities
{
public class AgentTask : IEquatable<AgentTask>, IComparable<AgentTask>
{
[DefaultValue("1")]
[Key]
public int FunctionId { get; set; }
public string PreferredTime { get; set; }
public string UserFunction { get; set; }
public string Group { get; set; }
public Boolean IsGroup { get; set; } = false;
public int CompareTo(AgentTask other)
{
if (UserFunction.CompareTo(other) != 0)
{
return UserFunction.CompareTo(other);
}
else
{
return Group.CompareTo(other);
}
}
public override bool Equals(object obj)
{
var newObj = obj as AgentTask;
if (null != newObj)
{
return Equals(newObj);
}
else
{
return base.Equals(obj);
}
}
public bool Equals(AgentTask other)
{
if (null != other)
{
return this.UserFunction == other.UserFunction
//&& this.PreferredTime == newObj.PreferredTime
&& this.Group == other.Group
&& this.GetHashCode() == other.GetHashCode();
}
else
{
return base.Equals(other);
}
}
public override int GetHashCode()
{
int hash = 19;
unchecked
{ // allow "wrap around" in the int
hash = hash * 31 + this.UserFunction.GetHashCode();
//hash = hash * 31 + this.PreferredTime.GetHashCode(); // assuming integer
hash = hash * 31 + this.Group.GetHashCode();
}
return hash;
}
public override string ToString()
{
return base.ToString();
}
}
}
I have a class that contains a collection of AgentTask called Account:
public class Account : IdentityUser
{
...
public List<AgentTask> UserFunctions { get; set; }
}
I also have a LINQ query that I want to return an array of accounts from whose UserFunctions are equal to a pre-defined AgentTask task. My query:
var accounts = _context.Accounts.Include(x => x.UserFunctions)
.Where((a) => a.UserFunctions.Any((t) => t.Equals(task))).ToArray();
accounts variable is always of 0 length array. Why?
Console output:
SELECT "a"."Id", "a"."AcceptTerms", "a"."AccessFailedCount", "a"."ConcurrencyStamp", "a"."Created", "a"."DOB", "a"."Email", "a"."EmailConfirmed", "a"."FirstName", "a"."LastName", "a"."LockoutEnabled", "a"."LockoutEnd", "a"."NormalizedEmail", "a"."NormalizedUserName", "a"."NotifyThreeDaysBefore", "a"."NotifyWeekBefore", "a"."PasswordHash", "a"."PasswordReset", "a"."PhoneNumber", "a"."PhoneNumberConfirmed", "a"."ResetToken", "a"."ResetTokenExpires", "a"."Role", "a"."SecurityStamp", "a"."Title", "a"."TwoFactorEnabled", "a"."Updated", "a"."UserName", "a"."VerificationToken", "a"."Verified", "u0"."FunctionId", "u0"."AccountId", "u0"."Group", "u0"."IsGroup", "u0"."PreferredTime", "u0"."UserFunction"
FROM "AspNetUsers" AS "a"
LEFT JOIN "UserFunctions" AS "u0" ON "a"."Id" = "u0"."AccountId"
WHERE EXISTS (
SELECT 1
FROM "UserFunctions" AS "u"
WHERE "a"."Id" = "u"."AccountId" AND "u"."FunctionId" = @__entity_equality_task_0_FunctionId)
ORDER BY "a"."Id"
3
Equals is automatically translated by EF Core to a normal = on the SQL side. It cannot execute client code on the server.
Because you are comparing two entities, you end up with a primary key comparison, which is not what you want.
Instead, do the comparison directly in the LINQ expression
var accounts = _context.Accounts
.Include(x => x.UserFunctions)
.Where(a => a.UserFunctions.Any(
t => t.UserFunction == task.UserFunction
&& t.Group == task.Group
)
).ToArray();
3