Compare ComplexType EFCore in Where clause

Question

I have a complex type named MSISDN which looks like below:

  public class MSISDN 
  {
      public string NDC { get; set; }
      public string CC { get; set; }
      public string SN { get; set; }
      public string ToString() => $"{NDC}{SN}{CC}";
      public string ToLong() => NDC * Math.Power(10, 6) + SN * Math.Power(10,3) + CC;
  }

I also written an specific ValueConverter for this Complex type:

public class MsisdnValueConverter : ValueConverter<Msisdn, string>
{
     public MsisdnValueConverter() : base(msisdn => msisdn.ToString(), msisdnString => Msisdn.Parse(msisdnString), mappingHints: null) { }
}

Which serialize/deserialize this complex type. I'm gonna write following query:

SubscriberCard card = cardDbSet.FirstOrDefault(P => P.Msisdn == msisdn);

This code throws following exception:

Unable to cast object of type 'System.String' to type 'System.Int64'.

I tried to use ToString() to avoid comparing objects as long as they are stored as string (or more precise NVARCHAR(MAX)) So in my next try I have used following query:

SubscriberCard card = cardDbSet.FirstOrDefault(P => P.Msisdn.ToString() == msisdn.ToString());

It throws following exception also:

could not be translated. Additional information: Translation of method 'object.ToString' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information

The only feasible way to do this for me is to retrieve all the records into the memory by calling ToList(). but it costs a lot.

As the latest exception said I will try to write a DbFunction, Is there any better option for me ?

Answer 1

Thank you for all the answers. I tried to create a clean Console Application and implemented your suggestions. However, something unpredictable occurred. The entire code was correct !!!

As mentioned in my question, the ValueConverter successfully serialized/deserialized the MSISDN class (which is a ValueObject).

The main issue stemmed from EFCore not detecting the changes in my database after some modifications I made to my project. In the clean Console Application, everything worked fine. The following query:

return dbSet.FirstOrDefault(P => P.Msisdn == msisdn);

was correctly translated to:

WHERE [c].[Msisdn] = @__msisdn_0',N'@__msisdn_0 nvarchar(4000)',@__msisdn_0=N'989121234567'

So I've deleted my Migrations and the DatabaseSnapShot and tried to delete the created Database. and finally I added new migration and everything worked as expected.