Specified type member not supported in LINQ to Entities

Question

I have an enum type called StatusTypes

public enum StatusTypes
{
    Open = 1,
    Allocated = 2,
    WorkInProgress = 3,
    WaitingOnRequestor = 4,
    WaitingOnThirdParty = 5,
    Monitoring = 6,
    Testing = 7,
    OnHold = 8,
    Complete = 9,
    SignedOff = 10,
    Reopened = 11
}

I'm trying to use this in my repository....

public IQueryable<Incident> GetAllOutstandingIncidents()
{
    return from i in db.Incidents
               where i.Status != Types.StatusTypes.SignedOff && i.Status != Types.StatusTypes.Complete && i.DeletedDateTime != null
               orderby i.DueDateTime
               select i;
    }

...and then use it in my view...

<tbody>
     <% foreach (var incident in Model.TotalIncidentsOutstandingList) { %>
                <tr>
                    <td><%: incident.IncidentID %></td>
                    <td><%: incident.Caller.NetworkName %></td>
                    <td><%: incident.Title %></td>
                    <td><%: incident.Service.Title %> / <%: incident.Category.Title %> <% if (incident.Subcategory != null) { %> / <%: incident.Subcategory.Title %><% } %></td>
                    <td><%: incident.Priority %></td>
                    <td></td>
                    <td><%: incident.AllocatedTo %></td>
                    <td><%: incident.DueDateTime %></td>
                </tr>
            <% } %>
        </tbody>

... but I'm getting the error "The specified type member 'Status' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported."

Any help gratefully received!

UPDATE TO SHOW incident.cs

public class Incident
{
    public int IncidentID { get; set; }
    public DomainUser Caller { get; set; }

    [Display(Name = "Caller Type")]
    public Types.CallerTypes CallerType { get; set; }

    public Service Service { get; set; }
    public Category Category { get; set; }
    public Subcategory Subcategory { get; set; }
    public string Title { get; set; }

    [Display(Name = "Problem Description")]
    public string ProblemDescription { get; set; }

    public Equipment Equipment { get; set; }

    public Types.ImpactTypes Impact { get; set; }
    public Types.UrgencyTypes Urgency { get; set; }

    [Display(Name = "Priority")]
    public Types.PriorityTypes Priority { get; set; }

    [Display(Name="Estimated time for completion")]
    public DateTime? DueDateTime { get; set; }

    [Display(Name="Date/Time")]
    public DateTime? CreatedDateTime { get; set; }
    public DomainUser CreatedBy { get; set; }

    [Display(Name = "Allocated To")]
    public HelpDeskMember AllocatedTo { get; set; }
    public DateTime? AllocatedDateTime { get; set; }

    public DateTime? ClosedDateTime { get; set; }
    public int? ClosedBy { get; set; }

    public DateTime? ReopenedDateTime { get; set; }
    public int? ReopenedBy { get; set; }

    public DateTime? DeletedDateTime { get; set; }
    public HelpDeskMember DeletedBy { get; set; }

    public Decimal? EstimatedInternalCost { get; set; }
    public Decimal? EstimatedResources { get; set; }
    public Decimal? RealInternalCost { get; set; }
    public Decimal? EstimatedExternalCost { get; set; }
    public Decimal? RealExternalCost { get; set; }
    public Decimal? EstimatedTotalCost { get; set; }
    public Decimal? RealTotalCost { get; set; }

    public string CostCode { get; set; }

    public string TimeRequired { get; set; }
    public string ActualTimeTaken { get; set; }

    public Types.StatusTypes Status { get; set; }

    public string Solution { get; set; }

    public bool UserSignedOff { get; set; }

    public bool OverdueEmailSent { get; set; }
    public bool EscalatedEmailSent { get; set; }

    public ICollection<Note> Notes { get; set; }
    public ICollection<Attachment> Attachments { get; set; }
    public ICollection<HistoryItem> History { get; set; }

    public Incident()
    {
        Notes = new List<Note>();
        Attachments = new List<Attachment>();
        History = new List<HistoryItem>();
    }
}

Appenzell

What type your Status property has? If it's int, you'll get compile time error. If it's enum you have to cast both parts of equals condition to int – Cari

Hi @WarHog, the property Status has a type Types.StatusTypes which is an enum type. Could you give me an example of how I cast both parts to int please? Thanks. – Appenzell

Can we see your Incident class? – Lussi

Answer 1

As I've already said try to cast both part to the int type

public IQueryable<Incident> GetAllOutstandingIncidents()
{
    return from i in db.Incidents
        where (int)i.Status != (int)Types.StatusTypes.SignedOff
            && (int)i.Status != (int)Types.StatusTypes.Complete
            && i.DeletedDateTime != null
        orderby i.DueDateTime
        select i;
}

UPDATE

That's a feature of Code First. You should do following. Change your class so:

[Column("Status", TypeName = "int")]
public int InternalStatus { get; set; }
public StatusTypes Status { get; set; }

And use following query:

context.Incidents.Where(i => i.InternalStatus == (int)StatusTypes.Allocated);

I've found this info here

Cari

Thanks for posting an example. This unfortunately does not resolve this error. – Appenzell

Just adding this work like a charm for me. [Column("Status", TypeName = "int")] public int Status { get; set; } – Orchidaceous

Answer 2

You can also convert to LINQ To Object with .AsEnumerable():

public IQueryable<Incident> GetAllOutstandingIncidents()
{
    return from i in db.Incidents.AsEnumerable()
               where i.Status != Types.StatusTypes.SignedOff && i.Status != Types.StatusTypes.Complete && i.DeletedDateTime != null
               orderby i.DueDateTime
               select i;
}

Depending of what you want, it could be a not so good solution: Instead of just pulling one object from the database, it will pull all objects when you call .AsEnumerable().

Oscine

Answer 3

Try this instead

return from i in db.Incidents
               where i.Status != (int)Types.StatusTypes.SignedOff && i.Status != (int)Types.StatusTypes.Complete && i.DeletedDateTime != null
               orderby i.DueDateTime
               select i;

Leigha

Hi @Greco, thanks for the reply. I've tried that, and I get "Operator '!=' cannot be applied to operands of type 'Cognito.Models.Types.StatusTypes' and 'int'" – Appenzell

oh, then try the following: int signedOff = (int)Types.StatusTypes.SignedOff; int complete = (int)Types.StatusTypes.Complete; and then use the local variables in your query – Leigha

Answer 4

In your Incident class:

private int statusId;
public Types.StatusTypes Status 
{ 
    get 
    {
        return (Types.StatusTypes)statusId;
    }
    set
    {
        statusId = (int)value;
    }
}

public Int StatusId 
{ 
    get 
    {
        return statusId;
    }
}

Then in you method:

public IQueryable<Incident> GetAllOutstandingIncidents()
{
    int signedOffStatusType = (int)Types.StatusTypes.SignedOff;    
    int completeStatusType = (int)Types.StatusTypes.Complete; 

    return from i in db.Incidents
           where i.StatusId != signedOffStatusType 
              && i.StatusId != completeStatusType 
              && i.DeletedDateTime != null
        orderby i.DueDateTime
        select i;
}

Or using method syntax:

public IQueryable<Incident> GetAllOutstandingIncidents()
{
    int signedOffStatusType = (int)Types.StatusTypes.SignedOff;    
    int completeStatusType = (int)Types.StatusTypes.Complete; 

    return db.Incidents.Where(i => i.StatusId != signedOffStatusType 
                                && i.StatusId != completeStatusType 
                                && i.DeletedDateTime != null)
                       .OrderBy(i => i.DueDateTime);
}

Lussi

Thanks. Tried that. Still no luck I'm afraid! – Appenzell

Are you using code first? Can we see your Incident class... ah just seen that, ta – Lussi

You are going to have to create a second property that is an integer that reflects your enum. I will update my answer – Lussi

Thanks for the help @NinjaNye. I'm now getting the same error but with StatusId. Just checking, when you declared public Int StatusId, was it meant to be int? – Appenzell