CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-entities
user1560457
user1560457

Reputation: 93

The specified type member is not supported in LINQ to Entities. Using 0 to many relationship for an entity property

I'm essentially trying to create an extension property on an entity named "Report", that can also be used in LINQ expressions.

Each Report entity has a 0 to many relationship with a table called ReportStatus, which will contain a history of all status updates for each Report. New Reports will not yet contain an entry in this table (so I will just return empty string). I want to be able to grab the current status code with ease (most recent entry in ReportStatus) for any given Report, as well as query Reports that match whatever status I'm wanting to filter on. Due to the 0 to many relationship involved, it's getting over my head for a clean solution. If anyone can provide some guidance, it would be much appreciated.

The extension property as it stands right now:

public partial class Report
{
    public string CurrentStatus
    {
        get
        {
            return
                this.ReportStatus.Count == 0 ?
                "" :
                this.ReportStatus.OrderByDescending(r => r.ReportStatusDate).First()
                .StatusCode;
        }
    }

It gives the "The specified type member is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported." on expressions such as _repository.Reports.Where(r => r.CurrentStatus == StatusCodes.Pending).ToList()

Upvotes: 0

Views: 3307

Answers (2)

user1560457
user1560457

Reputation: 93

Took a bit from IronMan84 Here's what I have now.

Made an extension class:

public static class QueryExtensions
{
    public static IQueryable<Report> GetByStatus(
    this IQueryable<Report> query, string statusCode)
    {
        if (statusCode == "")
        {
            return query.Where(r => r.ReportStatus.Count == 0); // new reports, no status history
        }
        else
            return query.Where(r => r.ReportStatus.OrderByDescending(s =>
                   s.ReportStatusDate).FirstOrDefault().StatusCode.StatusCode1 == statusCode);
    }
}

And use it like this:

         _repository.Reports.GetByStatus(StatusCode.Pending)ToList();

I still can use the CurrentStatus extension property shown in my question when just needing to peek at the status for a lone entity. Thanks!

Upvotes: 0

Corey Adler
Corey Adler

Reputation: 16137

As I mentioned in the comment above, you can't actually do that query in LINQ-to-Entities since CurrentStatus is not actually a column in the database. As such, you'll need to do the following as your query to get what you're asking for:

var pendingReports = _repository.Reports.Where(r => r.ReportStatus.Any() && 
                                               r.ReportStatus.OrderByDescending(s =>                        
                                                   s.ReportStatusDate).First().StatusCode == StatusCodes.Pending);

Upvotes: 1

Related Questions