Using a custom data type in Linq to SQL

Question

Is there a way to use a custom data type in the entity returned by Linq to SQL?

Say I have a data type like this:

public struct CustomerNumber : IEquatable<string>
{
    private string _value;

    public static implicit operator CustomerNumber(string value)
    {
        return new CustomerNumber { _value = value };
    }

    ...
}

It also defines operators for == != and so on.

When I try to replace one of the properties in the returned entity I get an exception Invalid cast from 'System.String' to 'CustomerNumber' even though you can assign the CustomerNumber from a string.

Is this not possible or am I doing something wrong?

Edit - Clarification:

The CustomerNumber is used as a data type on the entity that gets generated by the Linq to SQL. The Exception occurs when trying to query the database. It's not something I'm trying to cast, it's somewhere inside the LinqToSQL magic that things go wrong.

My code looks something like this:

var customers = DataContext.Customers;

Edit:

It turns out that if your struct implements an explicit operator it works or rather, it's working with a struct that implements an explicit operator to convert from string to the type but not if it is to convert from System.Int32 to the type.

Edit again: It still does not work.

Can you show us the code where you create the CustomerNumber and try to change a property? — Matt Ellen
– Matt Ellen, Commented Jul 6, 2010 at 11:25

codekaizen · Accepted Answer · 2010-07-06 11:45:37Z

1

I think the only way you'll be able to do this is to write it as a property in the partial class which L2S generates. If you make the Column property private, you can keep it a string, and then just use it as a backing store for your custom property exposing your custom type.

answered Jul 6, 2010 at 11:45

codekaizen

27.4k7 gold badges87 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Kristoffer L Over a year ago

I ended up doing this way even if it is not the way I wanted to do it from the start.

Kristoffer L Over a year ago

This solution does not work with queries like DataContext.Customers.Where(w => w.CustomProperty == 1); as it has no translation to SQL.

codekaizen Over a year ago

Right, you can't query on it, of course. L2S's implementation of IQueryable doesn't know how to map it. I'd wonder if there is a way to provide a custom AttributeMappingSource or change the MetaModel to tell it how to map the custom type.

Kristoffer L Over a year ago

I don't understand what Linq does internally that makes it impossible to cast from the Int or string to my custom data type. I have tried all kinds of casts outside of Linq and they work flawlessly. If only there were a way to see the code that tries to do the cast maybe I can find the problem.

codekaizen Over a year ago

The reason is that Linq to SQL doesn't execute code, like you are doing everywhere else. It merely interprets it and converts it into SQL, where the conversion doesn't make sense.

Denis · Accepted Answer · 2015-02-17 00:48:20Z

0

Correct approach (from MSDN) is:

If a class implements Parse() and ToString(), you can map the object to any SQL text type (CHAR, NCHAR, VARCHAR, NVARCHAR, TEXT, NTEXT, XML). The object is stored in the database by sending the value returned by ToString() to the mapped database column. The object is reconstructed by invoking Parse() on the string returned by the database.

answered Feb 17, 2015 at 0:48

Denis

3,7694 gold badges33 silver badges44 bronze badges

Collectives™ on Stack Overflow

Using a custom data type in Linq to SQL

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related