0

I have a Complex number in a C# class

public class DataToStore
{
  [Key]
  public int Id { get; set; }
  public System.Numerics.Complex ComplexValue { get; set; }
}

I need to store this data in a SQL Server database, but I don't know what the equivalent type is in SQL.

For now, I'm trying to generate the database schema using Entity Framework Core, but when creating the migration EF is complaining that it can't map this type:

The property 'DataToStore.ComplexValue' could not be mapped because it is of type 'Complex', which is not a supported primitive type or a valid entity type. Either explicitly map this property, or ignore it using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.

Which makes sense, but I don't know how this data can be represented in SQL.

I'm not specifically tied to using Entity Framework - I'm only using this as a way to quickly mock up the database - so if this is something that can be done manually in SQL but not EF, then that's absolutely fine. My aim here is to get this data in the database and be able to get it out again in exactly the same format.

What's the best way of storing this type of data in SQL? Or does this value need converting to another type so that it can be stored?

Jamie Burns
  • 1,258
  • 9
  • 21
  • 2
    Simply put, there is no equivalent type. You'd need to store the real and imaginary parts separately. You can still offer `ComplexValue` as a `[NotMapped]` property. – Jeroen Mostert Feb 08 '23 at 12:04
  • 1
    Serialise it and save, then deserialise on load? https://stackoverflow.com/questions/41945353/newtonsoft-jsonconvert-complex-number-issue Prob just save as two numbers though :P – Dan Rayson Feb 08 '23 at 12:05
  • 1
    SQL, the language, doesn't support complex numbers and neither does SQL Server. You'll have to come up with some way of representing such numbers yourself - storing the parts in separate fields perhaps? Or using a specific format? In both cases you can create a [value converter](https://learn.microsoft.com/en-us/ef/core/modeling/value-conversions) that maps your complex type to the storage format. You won't be able to calculate aggregations with SQL though – Panagiotis Kanavos Feb 08 '23 at 12:10
  • 1
    You could, theoretically, create a Complex type using [SQLCLR User Defined Types](https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration-database-objects-user-defined-types/creating-user-defined-types?view=sql-server-ver16). That's how spatial and hierarchy types are implemented. SQLCLR never really caught on though. It uses a semi-custom, restricted .NET Framework runtime. Creating your own types means you also have to deploy a custom assembly to every database server that uses this type – Panagiotis Kanavos Feb 08 '23 at 12:15
  • You could define `Complex` as an [Owned Entity Type](https://learn.microsoft.com/en-us/ef/core/modeling/owned-entities) and store each part to a separate database column. You won't be able to use `System.Numerics.Complex` itself though – Panagiotis Kanavos Feb 08 '23 at 12:23
  • 1
    What do you want to do with these values? How portable should the data be? There's no good answer, only workarounds. There's no `pg_complex` for SQL Server – Panagiotis Kanavos Feb 08 '23 at 12:25
  • PS: SQLCLR was created before package managers and NuGet were a thing, so you can't deploy a Complex package directly to a database. You have to explicitly register and deploy it. You also need to sign it yourself due to security restrictions – Panagiotis Kanavos Feb 08 '23 at 12:29

1 Answers1

3

You would need multiple database fields to store its values. If you want to store the Real and Imaginary values, you could have a field for each of them.

You would need to update your model so that EF does not try to save ComplexValue. You should have properties for the real and imaginary values and use them to make your complex value.

public class DataToStore
{
    [Key]
    public int Id { get; set; }

    [NotMapped]
    public System.Numerics.Complex ComplexValue 
    { 
        get
        {
            return new Complex(Real, Imaginary)
        }
        set
        {
            Real = value.Real; 
            Imaginary = value.Imaginary;
        }
    }

    public double Real { get; set; }
    
    public double Imaginary { get; set; }
}
YungDeiza
  • 3,128
  • 1
  • 7
  • 32
  • 3
    Would it be better if ComplexValue didn't have it's own backing field, instead just served up based on the other two? i.e. `get => new Complex(Real, Imaginary); set { Real = value.Real; Imaginary = value.Imaginary; }` – GarethD Feb 08 '23 at 12:26
  • Yes, that's better. I wondering if the set is really need at all - but will leave as you've suggested. – YungDeiza Feb 08 '23 at 12:40
  • 3
    Can be simplified to `public Complex ComplexValue { get => new(Real, Imaginary); set => (Real, Imaginary) = (value.Real, value.Imaginary); }` (for sufficiently recent versions of C#). – Jeroen Mostert Feb 08 '23 at 13:01
  • Thanks @JeroenMostert, I'm not a massive fan of some of the new lambda notation use - doesn't really increase readability (IMO) and only saves a little space. It's hand in some cases though. – YungDeiza Feb 08 '23 at 13:04