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

Properties which has conversion cannot be used in LINQ queries.

Define in SubscriberCard property which holds real database string and map it to table's column. Also define property Msisdn, which can populate MSISDN object from string

public class SubscriberCard
{
    ...
    public string MsisdnString {get; set;}   

    [NotMapped]
    public MSISDN Msisdn { get => Msisdn.Parse(MsisdnString); set => MsisdnString = value.ToString(); }   
}

And use in queries MsisdnString property.

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

Answer 2

When you write queries and where clauses against IQueryable, anything used there must be SQL-compliant - so you can use variety of conditions, such as equality, less/greater, etc. as they are "natural" SQL operations. Thus, EF has no problem translating them into query and sending over to SQL database.

You are using specific .NET (C#) mechanism for comparison, and such mechanism cannot be translated into SQL or T-SQL and sent to database. You have to write your logic differently.

And this is the case with all such cases, when there is ".NET specific" code, which is attempted to be sent to SQL database via EF.

Answer 3

You can try the trick via intermediate cast to object and then string:

var card = ctx.Users
    .Where(P => ((string)(object)P.Msisdn) == msisdnStringValueToSearch)
    .FirstOrDefault();

Though it can result in extra cast in the translated query which might not be appropriate in some cases.

Or just parse the value to MSISDN:

var msisdn = ...;
MSISDN parsed = ...; // convert msisdn to MSISDN 
var card = ctx.Users
    .Where(P => P.Msisdn == parsed)
    .FirstOrDefault();

Answer 4

This isn't an issue of conversion or serialization. LINQ queries get converted to SQL and there's no way to convert P => P.Msisdn == msisdn to SQL.

Even in C# though, string comparisons don't work as a way of comparing objects to each other. Both in C# and SQL, the comparison can easily fail due to field ordering or whitespace differences.

In EF Core 7 and later, complex types can be mapped to JSON content stored in text columns and queried just like normal objects.

You can map MSISDN to a JSON column :

modelBuilder.Entity<SubscriberCard>()
    .OwnsOne( card => card.Msisdn, ownedNavigationBuilder =>
        {
            ownedNavigationBuilder.ToJson();
        });

After that, MSISDN will be saved as JSON to the table. LINQ queries on the Msisdn column will be translated using the database's JSON functions.

You should be able to write :

var msisdn=new MSISDN{....};

SubscriberCard card = cardDbSet.FirstOrDefault(P =>
     P.Msisdn.NDC == msisdn.NDC && 
     P.Msisdn.SN==msisdn.SN && 
     P.Msisdn.CC==msisdn.CC);

In SQL Server property access will be converted to JSON_VALUE calls, eg :

WHERE CAST(JSON_VALUE([a].[Msisdn],'$.NDC') AS nvarchar(max)) = N'Whatever'
   AND ...

Performance depends on the database's support for JSON indexes. SQL Server doesn't have such indexes, PostgreSQL does.

Answer 5

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.