C# Entity Framework Error Converting nvarchar to int by enum

Question

I have an issue receiving the following error:

Conversion failed when converting the nvarchar value 'HolidayRequest' to data type int.

I have an inheritance structure within Entity Framework. My base Model is as follows:

public abstract class Request
{
    public int ID { get; set; }
    public int EmployeeID { get; set; }
    public string Subject { get; set; }

    [Display(Name = "Date Requested")]
    public DateTime DateRequested { get; set; }
    [Display(Name = "Start Date")]
    public DateTime StartDateTime { get; set; }
    [Display(Name = "Return To Work Date")]
    public DateTime EndDateTime { get; set; }
    public RequestStatus Status { get; set; }
    public string Notes { get; set; }
    public int? ResponseID { get; set; }
    public RequestDiscriminator Discriminator { get; set; }

    [ForeignKey("EmployeeID")]
    public virtual Employee Employee { get; set; }
    [ForeignKey("ResponseID")]
    public virtual Response Response { get; set; }
}

When I attempt to retrieve data from the database as follows:

    public IQueryable<Request> GetPendingHolidayRequestsByEmployeeId(int employeeId)
    {
        var list = _context.Requests.Where(p => p.Discriminator == RequestDiscriminator.Holiday && p.EmployeeID == employeeId && p.Status == RequestStatus.NotProcessed);
        return list;
    }

This is where the error occurs.

Given the context of the error I believe that it is an issue with this enum public RequestDiscriminator Discriminator { get; set; }

The structure of the Enum is:

public enum RequestDiscriminator
{
    Holiday,
    Absence
}

The SQL statement that is being sent by entity framework is:

SELECT 
[Extent1].[ID] AS [ID], 
CASE WHEN ([Extent1].[Discriminator1] = N'AbsenceRequest') THEN '0X0X' ELSE '0X1X' END AS [C1], 
[Extent1].[EmployeeID] AS [EmployeeID], 
[Extent1].[Subject] AS [Subject], 
[Extent1].[DateRequested] AS [DateRequested], 
[Extent1].[StartDateTime] AS [StartDateTime], 
[Extent1].[EndDateTime] AS [EndDateTime], 
[Extent1].[Status] AS [Status], 
[Extent1].[Notes] AS [Notes], 
[Extent1].[ResponseID] AS [ResponseID], 
[Extent1].[Discriminator] AS [Discriminator]
CASE WHEN ([Extent1].[Discriminator1] = N'AbsenceRequest') THEN [Extent1].[ReasonCode] END AS [C2], 
CASE WHEN ([Extent1].[Discriminator1] = N'AbsenceRequest') THEN [Extent1].[TakenPaid] END AS [C3], 
CASE WHEN ([Extent1].[Discriminator1] = N'AbsenceRequest') THEN CAST(NULL AS float) ELSE [Extent1].[TotalDays] END AS [C4], 
CASE WHEN ([Extent1].[Discriminator1] = N'AbsenceRequest') THEN [Extent1].[Employee_EmployeeID] END AS [C5], 
CASE WHEN ([Extent1].[Discriminator1] = N'AbsenceRequest') THEN CAST(NULL AS int) ELSE [Extent1].[Employee_EmployeeID1] END AS [C6]
FROM [dbo].[Request] AS [Extent1]
WHERE ([Extent1].[Discriminator1] IN (N'AbsenceRequest',N'HolidayRequest')) AND (0 = [Extent1].[Discriminator]) AND (0 = [Extent1].[Status])

Any help would be greatly appreciated as i am completely stumped.

UPDATE

As per the suggestion in the answer below, I have removed both Discriminator columns from the database and readded mine. However, the SQL query still appears to be looking for the Discriminator1 column that now doesnt exist.

Perhaps because your nvarchar value is 'HolidayRequest' but your enum is 'Holiday'? — Paul Abbott
– Paul Abbott, Commented Apr 6, 2016 at 15:16
I can see that in the SQL but I do not know where this is coming from and therefore how to fix it — DaRoGa
– DaRoGa, Commented Apr 6, 2016 at 15:16
Probably calling your own column Discriminator is not a good idea, especially combined with TPH. EF maintains it's own string column called Discriminator to distinguish the derived classes, so it might been hardcoded somewhere (just a guess). — Ivan Stoev
– Ivan Stoev, Commented Apr 6, 2016 at 15:49

DaRoGa · Accepted Answer · 2016-04-07 08:02:42Z

2

I have managed to solve this. The answer was to remove the discriminator column i created, and allow EF to determine the type by including the type in the linq query as follows:

var test = _context.Requests.Where(r => r.EmployeeID == employeeId).OfType<AbsenceRequest>();

The OfType<>() seems to tell EF to look at the Discriminator column for this type name and only return data of that type.

answered Apr 7, 2016 at 8:02

DaRoGa

2,4149 gold badges37 silver badges65 bronze badges

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

Comments

mriedel90 · Accepted Answer · 2016-04-06 15:55:27Z

0

It looks like you have an extra column in there "Discriminator1". Did you originally have that field as a string, then tried to convert it to an enum? The best option might be to try dropping the field altogether and make sure it drops both Discriminator and Discriminator1. Then try adding it back. This can occur in when having custom mappings, so something may be off there.

answered Apr 6, 2016 at 15:55

mriedel90

365 bronze badges

4 Comments

DaRoGa Over a year ago

Ok that is now retrieving all of the records in that table if i remove the property from my model and update database. However, how would i get only requests that are HolidayRequest's and not AbsenceRequest?

mriedel90 Over a year ago

Look at the database and make sure both columns were removed from the table. Then add the column back and do what you were doing. For some reason you just had an extra column in there that needs to be removed.

DaRoGa Over a year ago

Ok i will try this when i get to work tomorrow and if it works i will accept your answer

DaRoGa Over a year ago

Ok, I have removed both DIscriminator and Discriminator1 columns. I then readded my Discriminator column. However, the query is still looking for Discriminator1 and produces the error Invalid column name 'Discriminator1'. Any Ideas?

Collectives™ on Stack Overflow

C# Entity Framework Error Converting nvarchar to int by enum

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related