Linq to SQL With Enum On Null Field [MODIFIED]

Kevin Marois

I have a table that store person data. There are NamePrefix and NameSuffix columns in the data. Both are nullable. Here is the Persons table

CREATE TABLE [dbo].[Persons]
(
Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
RemoteId UNIQUEIDENTIFIER NOT NULL,
ClientRepId INT NULL FOREIGN KEY (ClientRepId) REFERENCES Persons(Id),
SupervisorId INT NULL FOREIGN KEY (SupervisorId) REFERENCES Persons(Id),
PersonType INT NOT NULL,
Prefix INT NULL,
FirstName VARCHAR(MAX),
LastName VARCHAR(MAX),
NickName VARCHAR(MAX),
Suffix INT NULL,
Title VARCHAR(MAX)
)

Here is the PersonEntity

namespace CLOI.Entities
{
public class PersonEntity : _EntityBase
{
private PersonType _PersonType;
public PersonType PersonType
{
get { return _PersonType; }
set
{
SetProperty("PersonType", ref _PersonType, value);
}
}

	private NamePrefix \_Prefix;
	public NamePrefix Prefix
	{
		get { return \_Prefix; }
		set
		{
			SetProperty("Prefix", ref \_Prefix, value);
		}
	}

	private string \_FirstName;
	public string FirstName
	{
		get { return \_FirstName; }
		set
		{
			SetProperty("FirstName", ref \_FirstName, value);
		}
	}

	private string \_LastName;
	public string LastName
	{
		get { return \_LastName; }
		set
		{
			SetProperty("LastName", ref \_LastName, value);
		}
	}

	private string \_NickName;
	public string NickName
	{
		get { return \_NickName; }
		set
		{
			SetProperty("NickName", ref \_NickName, value);
		}
	}

	private NameSuffix \_Suffix;
	public NameSuffix Suffix
	{
		get { return \_Suffix; }
		set
		{
			SetProperty("Suffix", ref \_Suffix, value);
		}
	}

	private string \_Title;
	public string Title
	{
		get { return \_Title; }
		set
		{
			SetProperty("Title", ref \_Title, value);
		}
	}

	public string FullName
	{
		get { return $"{FirstName} {LastName}"; }
	}

	private int \_ClientRepId;
	public int ClientRepId
	{
		get { return \_ClientRepId; }
		set
		{
			SetProperty("ClientRepId", ref \_ClientRepId, value);
		}
	}
}

}

I'm trying to get this to work:

results = (from p in dc.Persons
select new PersonEntity
{
RemoteId = p.RemoteId,
ClientRepId = p.ClientRepId ?? 0,
SupervisorId = p.SupervisorId ?? 0,

OriginalGriff

It's going to depend on the definition of the NamePrefix and NameSuffix classes, as well as the p.Prefix property. I'd hope that the two classes are derived from whatever class Prefix is, but you've probably got that covered. Check that Prefix is also nullable.

"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!

Dave Kreskowiak

An Enum on a nullable field? A null value cannot be cast to an enum value. Enums are value types, not reference types, and they must have a value. They cannot be null.

Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak

Kevin Marois

I updated my original post to show the SQL table and the Person entity, as well as my updated Linq-To-SQL quert in my DAL. PrefixType and SuffixType are both enums. The Prefix and Suffix columns in the table are nullable. The PersonEntity has both PrefixType and SuffixType enum properties. But since the underlying data for both is null, I'd like to set the enum to PrefxType.None and SuffixType.None. So I'm trying to coerce the enum property to None for null data values. In this line here, p.Prefix is a null int.

Prefix = (NamePrefix)p.Prefix ?? NamePrefix.None

Thanks

If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.

Kevin Marois

Please see my reply to Griff. Thanks

If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.

Dave Kreskowiak

The way I'd fix this is to make those column NOT nullable. The "None" value would be 0. But, since I don't know the business rules, you may or may not get away with this.

Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak

OriginalGriff

And there is your problem: enum is not a nullable type (it's syntactical sugar for a boring old int) so it can't be tested using the ?? operator - hence the error message. I'd strongly suggest that your data source is the problem, and that that shouldn't be capable of returning null value in that field - but if you cast it to a nullable int, you should be able to use that with the ?? operator to return an enum. I haven't tested it - I'm on a tablet and coding becomes a true PITA without a proper KB and mouse! :laugh:

"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!

Kevin Marois

I just had a thought and tested it. This works

results = (from p in dc.Persons
select new PersonEntity
{
RemoteId = p.RemoteId,
ClientRepId = p.ClientRepId ?? 0,
SupervisorId = p.SupervisorId ?? 0,
PersonType = (PersonType)p.PersonType,
Prefix = p.Prefix == null ? NamePrefix.None : (NamePrefix)p.Prefix,
FirstName = p.FirstName,
LastName = p.LastName,
NickName = p.NickName,
Suffix = p.Suffix == null ? NameSuffix.None : (NameSuffix)p.Suffix,
Title = p.Title,
CreatedById = p.CreatedById,
CreatedDT = p.CreatedDT,
LastModifiedById = p.Id,
LastModifiedDT = p.ModifiedByDT.Value,
DeletedById = p.DeletedById,
DeletedDT = p.DeletedByDT.Value
}).Where(predicate).ToList();

If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.

Kevin Marois

You're right. At the time I didn't have the 'None' enum, but it makes sense. Much better. Thanks

If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.

Lost User

Just curious; How did

Kevin Marois wrote:

CreatedById = p.CreatedById, CreatedDT = p.CreatedDT, LastModifiedById = p.Id, LastModifiedDT = p.ModifiedByDT.Value, DeletedById = p.DeletedById, DeletedDT = p.DeletedByDT.Value

affect the answer?

Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Kevin Marois

It didn't. I had ommitted those properties before for brevity

If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.

Richard Deeming

Depending on how well the ORM works, you may be able to use:

Prefix = (NamePrefix?)p.Prefix ?? NamePrefix.None,

NB: Using an enum for a name prefix or suffix seems like a bad idea. What happens when your users try to add a person whose prefix / suffix doesn't exist in your list? Are you going to make that person wait while you recompile your application? I'd suggest either using a lookup table, or just free-text values.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer